Page 1 of 2

NeoBookBDPro Grid

PostPosted: Tue Mar 19, 2013 2:27 pm
by ebear
Hello All,

I have two questions:

1. I like to have a date picker in a cel of a DBPro Grid. I found this in a older topic:

This works automatically with other types of databases, but since MS Access stores date fields in a somewhat odd way, we need to explicitly tell Access that the field contains date information. To do this set the field's display format. For example:

dbpSetFieldProperties "Orders" "Invoices" "OrderDate" "DisplayFormat=m/d/yyy;"

Once you do this, the grd will display a date picker when you edit this field.


I tried this but the datepicker doesn't show up, the grid cel is a normal input cel. I'm doing some thing wrong???

My code: dbpSetFieldProperties "Fis" "CoreSortItem" "Datesort" "DisplayFormat=m/d/yyy"

2. I need, when adding a record in a grid, that in one field (ID) is auto filled from the master table. (The link in a Master-Detail connection). How to archive this???

Best regards,

Eric

Re: NeoBooBDPro Grid

PostPosted: Tue Mar 19, 2013 8:09 pm
by Neosoft Support
Try using your local date format: d/m/yyy

Re: NeoBooBDPro Grid

PostPosted: Wed Mar 20, 2013 12:57 am
by ebear
Hello Neosoft,

Thanks for you reaction. I changed the code to d/m/yyy (see below) but no differences, do I oversee someting?

Regards,

Eric

Code: Select all
:CoreSortItem_Show
dbpExecSQL "Fis" "SELECT ID, CoreInID, Datesort, ItemNo, Q1, Q2, Q3, Q4, ISNULL(Q1, 0) + ISNULL(Q2, 0) + ISNULL(Q3, 0) + ISNULL(Q4, 0) AS Total FROM dbo.Fis_CoreSortItem WHERE (CoreInID = [Fis.CoreIn.ID])" "CoreSortItem"
dbpOpenTable "Fis" "CoreSortItem" ""
dbpShowGrid "Fis" "CoreSortItem" "P102_CoreSortItem_Rectangle"
dbpSort "Fis" "CoreSortItem" "ItemNo=ASC"
dbpSetFieldProperties "Fis" "CoreSortItem" "ID" "Alignment=Left;TitleAlignment=Left"
dbpSetFieldProperties "Fis" "CoreSortItem" "CoreInID" "Alignment=Left;TitleAlignment=Left;DefaultValue=;ReadOnly=No"
dbpSetFieldProperties "Fis" "CoreSortItem" "Datesort" "Alignment=Left;TitleAlignment=Left;DisplayFormat=d/m/yyy"
dbpSetFieldProperties "Fis" "CoreSortItem" "ItemNo" "Alignment=Left;TitleAlignment=Left"
dbpSetFieldProperties "Fis" "CoreSortItem" "Q1" "Alignment=Right;TitleAlignment=Right"
dbpSetFieldProperties "Fis" "CoreSortItem" "Q2" "Alignment=Right;TitleAlignment=Right"
dbpSetFieldProperties "Fis" "CoreSortItem" "Q3" "Alignment=Right;TitleAlignment=Right"
dbpSetFieldProperties "Fis" "CoreSortItem" "Q4" "Alignment=Right;TitleAlignment=Right"
dbpSetFieldProperties "Fis" "CoreSortItem" "Total" "Alignment=Right;TitleAlignment=Right"
dbpSetColumnOrder "Fis" "CoreSortItem" "ID;CoreInID;Datesort;ItemNo;Q1;Q2;Q3;Q4;Total"
dbpSetColumnWidths "Fis" "CoreSortItem" "ID=70;CoreInID=70;Datesort=110;ItemNo=300;Q1=80;Q2=80;Q3=80;Q4=80;Total=80"
dbpSetColumnTitles "Fis" "CoreSortItem" "ID=ID:;CoreInID=FSEG ID:;Datesort=Date sort: (Y-M-D);ItemNo=Core id:;Q1=Qty 100%:;Q2=Qty 50%:;Q3=Qty 25%:;Q4=Qty 0%:;Total=Total:"
dbpSetGridProperties "Fis" "CoreSortItem" "Color=Window;Font=Arial,10,Normal,EASTEUROPE_CHARSET;FontColor=WindowText;AlternateRowColor=240,240,240;AlternateRowFontColor=WindowText;RowHeight=22;TitleColor=BtnFace;TitleFont=Arial,10,Normal,EASTEUROPE_CHARSET;TitleFontColor=BtnText;TitleRowHeight=25;HighlightColor=Blue+Blue;HighlightFontColor=White;EditColor=Silver;EditFontColor=WindowText;ShowTitles=Yes;3DTitles=Yes;ShowGraphics=Yes;ShowIndicator=Yes;ShowColumnLines=Yes;ShowRowLines=Yes;GridLineWidth=1;GridLineColor=Silver;AllowRowResize=No;AllowColumnResize=No;AllowColumnSort=Yes;ConfirmDelete=Yes;ReadOnly=No;EditOnly=No;RowSelect=No;ShowScrollBars=Vertical;ShowNavigationBar=Yes;GetDefaultValues=Yes"
GotoPage "P102"
Return

Re: NeoBooBDPro Grid

PostPosted: Thu Mar 21, 2013 11:14 am
by Neosoft Support
Try this:

dbpSetFieldProperties "Fis" "CoreSortItem" "Datesort" "DisplayFormat=ddddd"

Also, the field type must be "Date". This will not work with "DateTime" fields.

Re: NeoBooBDPro Grid

PostPosted: Thu Mar 21, 2013 6:19 pm
by ebear
Hello Neosoft,

Its a very strange behavior. The field I'm using is a date field on a SQL2008 server. The application I'm building is an complex app for stock taking and already working live, so the DB access is normal working.
I want to give the user an possibility for some input to have a edit Grid available.

After you last post I created a simple app with only the Grid to try. One time it worked but after this (I wanted to try the code I use in the main app) it doesn't work anymore.

The code is simple:

Code: Select all
dbpOpenTable "Fis" "Fis_CoreSortItem" ""
dbpOpenTable "Fis" "Fis_CoreId" ""

dbpDefineValueList "Fis" "Fis_CoreSortItem" "ItemNo" "Fis_CoreId" "RegCis" "RegCis"   .<<<< This works perfect

dbpSetFieldProperties "Fis" "Fis_CoreSortItem" "Datesort" "DisplayFormat=ddddd"  .<<<<< Shows the date grid is editable but no calendar
dbpShowGrid "Fis" "Fis_CoreSortItem" "Rectangle1"


The Grid shows with the data from table, also the value list works, everything seems ok but no calendar.......ยจ

Any Idea???


Thanks a lot for your kind support,

Eric

Re: NeoBooBDPro Grid

PostPosted: Fri Mar 22, 2013 11:18 am
by Neosoft Support
The calendar works correctly in the small sample app, but not in the main app? If that's the case then the only thing I can think of is that there is something in the main app that is changing the field's formatting.

Re: NeoBooBDPro Grid

PostPosted: Fri Mar 22, 2013 1:40 pm
by ebear
Hello NeoSoft,

It's a strange behavior, I got it once working in the sample app but after I changed the code a little it didn't work any more.

The sample app has only one page with "Rectangle1" for the grid. In the "Book > Properties > actions" I open the database with the following code:

Code: Select all
dbpOpenDatabase "Fis" "Provider=SQLNCLI.1;Password=XXXXXX;Persist Security Info=True;User ID=XX;Initial Catalog=Fis;Data Source=PC-006\SQL2008"


And in the "Page > Properties > actions" I have the following:

Code: Select all
dbpOpenTable "Fis" "Fis_CoreSortItem" ""
dbpOpenTable "Fis" "Fis_CoreId" ""
dbpShowGrid "Fis" "Fis_CoreSortItem" "Rectangle1"
dbpDefineValueList "Fis" "Fis_CoreSortItem" "ItemNo" "Fis_CoreId" "RegCis" "RegCis"
dbpSetFieldProperties "Fis" "Fis_CoreSortItem" "Datesort" "DisplayFormat=ddddd"


As already told before the Grid is filled with the right data, the ValueList is working, but no calendar.
There is nothing else used than NeoBookDBP, no other actions no plugins etc. My "main" application is otherwise working fine. The grid for editing with the calendar option was for a further improvement.

As always thanks for the perfect support.

Best regards,


Eric Beerhorst

Re: NeoBookBDPro Grid - SQL Server Native Client

PostPosted: Fri Mar 22, 2013 2:15 pm
by ebear
Hello Dave,

Problem is solved, but maybe other can do also there favor with the solution:

The problem was in the SQL Native Client used. I have on my development computer two SQL Native Clients, this because of the past version of our ERP.
Solution:Solution: The "SQL Native Client" version 2005.90.4035.00 doesn't support the calendar in the Grid, while the "SQL Server Native Client 10.0" version 2009.100.2500.00 supports the calendar fail-less.
When I changed the sample app do to a mistake I choose the older version and the calendar in the Grid stopped working.

Thanks,

Eric Beerhorst

Re: NeoBooBDPro Grid

PostPosted: Mon Mar 25, 2013 10:23 am
by Neosoft Support
I'm glad that you found a solution. I never would have guessed that the SQL Native Client version was to blame.

Re: NeoBooBDPro Grid

PostPosted: Mon Mar 25, 2013 12:50 pm
by ebear
Hello All,

An impression what kind of problems a SQL client can give you NeoBookDB project:

date:
SQL Server Native Client (SNAC9): varchar
SQL Server 2008 Native Client (SNAC10): date

datetime2:
SQL Server Native Client (SNAC9): varchar
SQL Server 2008 Native Client (SNAC10): datetime2

datetimeoffset:
SQL Server Native Client (SNAC9): varchar
SQL Server 2008 Native Client (SNAC10): datetimeoffset

time:
SQL Server Native Client (SNAC9): varchar
SQL Server 2008 Native Client (SNAC10): time

For an complete description : http://blogs.msdn.com/b/sqlnativeclient/archive/2008/02/27/microsoft-sql-server-native-client-and-microsoft-sql-server-2008-native-client.aspx

Regards,

Eric Beerhorst

Re: NeoBookBDPro Grid

PostPosted: Mon Mar 25, 2013 12:53 pm
by ebear
Hello All,

In my previous question (about the calendar in a Grid) there was another question. Until now I didn't solve this. Any one???

2. I need, when adding a record in a grid, that in one field (ID) is auto filled from the master table. (The link in a Master-Detail connection). How to archive this???

Regards,

Eric Beerhorst

Re: NeoBookBDPro Grid

PostPosted: Mon Mar 25, 2013 2:48 pm
by Neosoft Support
You can't change the value of an auto-inc field because it's always set automatically by the database engine. If you need a field to keep track of an invoice/customer number you will need to create a new (non auto-inc) field.

Re: NeoBookBDPro Grid

PostPosted: Mon Mar 25, 2013 3:07 pm
by ebear
Hello NeoSoft.

It isn't a auto-inc field, i understand this. I want if the user adds a record in a grid one of the fields is auto filled with the data from a field out of the Master table. The user will fill in the rest of the needed fields.

Let say:

Master:
ID, ItemNo, Description etc.

Detail:
ID, MasterID, Qty etc.

When the user adds a record in the detail table: Detail.MasterID = Master.ID something like this......


Regards,

Eric Beerhorst.

Re: NeoBookBDPro Grid

PostPosted: Tue Mar 26, 2013 10:54 am
by Neosoft Support
Yes, something like this:

SetVar "[MyDB.Detail.MasterID]" "[MyDB.Master.ID]"

Re: NeoBookBDPro Grid

PostPosted: Sat Mar 30, 2013 1:20 pm
by ebear
Hello Dave,

This is true:
SetVar "[MyDB.Detail.MasterID]" "[MyDB.Master.ID]"

But if the user "Tabs" further and another record is add than the SetVar isn't working anymore and the Link between master and detail is lost.

Regards,

Eric