Source language: Translate to:

dbpVarToRec does not write hidden fields

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

dbpVarToRec does not write hidden fields

Postby Cipolla » Sat Nov 23, 2013 12:35 pm

MSSQL Server 2008 R2:

Lets say i have a table like this:

Field1|Field2|Field3|Field4

I have a grid on my stage where i use
Code: Select all
dbpSetColumnOrder

to hide some fields i.e. "field3".

I did a

Code: Select all
dbpRecordToVar "alias" "Table" "[RecSource]" "Delimiter=|;IncludeHidden=Yes;ForceQuotes=No"


i get:

[RecSource]=Field1|Field2|Field3|Field4
(I am getting the hidden field as expected)

And then i do a

Code: Select all
dbpAddRecord "alias" "Table"
dbpVarToRecord "alias" "Table" "[RecSource]" "Delimiter=|;IncludeHidden=Yes"


But Field3 (wich is hidden in the grid) is filled with 'NULL' and Field4 is filled with the content of Field3

I tried also other delimiter and also "ForceQuotes" but the result is the same. The HiddenFields are not taken by the command.

Is this a bug?
Greetings from Germany
Klaus
User avatar
Cipolla
 
Posts: 166
Joined: Fri Apr 01, 2005 1:45 am
Location: Germany

Re: dbpVarToRec does not write hidden fields

Postby Neosoft Support » Sat Nov 23, 2013 3:20 pm

When you add a new record, it won't be posted to the database until you navigate away or execute dbpSaveRecord. With your example, you probably need to add dbpSaveRecord between the two actions. For example:

Code: Select all
dbpAddRecord "alias" "Table"
dbpSaveRecord "alias" "Table"
dbpVarToRecord "alias" "Table" "[RecSource]" "Delimiter=|;IncludeHidden=Yes"


Note: if you don't want to include hidden fields, change this to:

Code: Select all
dbpAddRecord "alias" "Table"
dbpSaveRecord "alias" "Table"
dbpVarToRecord "alias" "Table" "[RecSource]" "Delimiter=|;IncludeHidden=NO"


The fields should be exported in the order that they appear in the grid.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5602
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: dbpVarToRec does not write hidden fields

Postby Cipolla » Sun Nov 24, 2013 3:16 am

Hello Dave,

the data is written to the database, that is not the problem. The problem is, that when i use

Code: Select all
dbpVarToRecord "alias" "Table" "Data" "Delimiter=|;IncludeHidden=YES"


It makes not difference if i set IncludeHidden to Yes or No. The action is still ignoring the hidden
fields.

I need to duplicate a rocord in the database. I take the source record, change the primarykey field, add a new record
and paste the changed record to the new row. thats all. depending on whether i hide fields in the grid or not.
Greetings from Germany
Klaus
User avatar
Cipolla
 
Posts: 166
Joined: Fri Apr 01, 2005 1:45 am
Location: Germany

Re: dbpVarToRec does not write hidden fields

Postby Cipolla » Sun Nov 24, 2013 5:14 am

Ok, did some more tests. This is what i found out:

The Problem is not with the HiddenFields, but the order tehy appear in the grid:

This is working:
Syntax: [ Download ] [ Hide ]
Using Neobook Syntax Highlighting
dbpOpenTable "[db_alias]" "testtable" ""
dbpSetColumnOrder "[db_alias]" "testtable" "Field1;Field5;Field4;Field2"
dbpShowGrid "[db_alias]" "testtable" "testtable"
   
-->    dbpFind "[db_alias]" "testtable" "Field1" "1-1" "ExactMatch=Yes;CaseSensitive=No"

dbpRecordToVar "[db_alias]" "testtable" "[RecSource]" "Delimiter=|;IncludeHidden=Yes;ForceQuotes=No"
StrReplace "[RecSource]" "1-1" "1-1_New" "[RecTarget]" ""
dbpAddRecord "[db_alias]" "testtable"
dbpVarToRecord "[db_alias]" "testtable" "[RecTarget]" "Delimiter=|;IncludeHidden=Yes"
dbpSaveEdits "[db_alias]" "testtable"
dbpRefresh "[db_alias]" "testtable"
Parsed in 0.007 seconds, using GeSHi 1.0.8.10


This is also working (Using sql select instead of dbpFind, select-result in the same table):
Syntax: [ Download ] [ Hide ]
Using Neobook Syntax Highlighting
dbpOpenTable "[db_alias]" "testtable" ""
dbpSetColumnOrder "[db_alias]" "testtable" "Field1;Field5;Field4;Field2"
dbpShowGrid "[db_alias]" "testtable" "testtable"

-->   dbpExecSQL "[db_alias]" "SELECT * |FROM testtable |WHERE field1 = '1-1'" ""

dbpRecordToVar "[db_alias]" "testtable" "[RecSource]" "Delimiter=|;IncludeHidden=Yes;ForceQuotes=No"
StrReplace "[RecSource]" "1-1" "1-1_New" "[RecTarget]" ""
dbpAddRecord "[db_alias]" "testtable"
dbpVarToRecord "[db_alias]" "testtable" "[RecTarget]" "Delimiter=|;IncludeHidden=Yes"
dbpSaveEdits "[db_alias]" "testtable"
dbpRefresh "[db_alias]" "testtable"
Parsed in 0.007 seconds, using GeSHi 1.0.8.10


This is NOT working (Using sql select instead of dbpFind, select-result in a temp table):
Syntax: [ Download ] [ Hide ]
Using Neobook Syntax Highlighting
dbpOpenTable "[db_alias]" "testtable" ""
dbpSetColumnOrder "[db_alias]" "testtable" "Field1;Field5;Field4;Field2"
dbpShowGrid "[db_alias]" "testtable" "testtable"

-->   dbpExecSQL "[db_alias]" "SELECT * |FROM testtable |WHERE field1 = '1-1'" "TTItem"
-->   dbpRecordToVar "[db_alias]" "TTItem" "[RecSource]" "Delimiter=|;IncludeHidden=Yes;ForceQuotes=No"

StrReplace "[RecSource]" "1-1" "1-1_New" "[RecTarget]" ""
dbpAddRecord "[db_alias]" "testtable"
dbpVarToRecord "[db_alias]" "testtable" "[RecTarget]" "Delimiter=|;IncludeHidden=Yes"
dbpSaveEdits "[db_alias]" "testtable"
dbpRefresh "[db_alias]" "testtable"
Parsed in 0.007 seconds, using GeSHi 1.0.8.10


Note: The content of [RecTarget] is always the same in the right order!

So it seems i can´t use two different tables in conjunction with RecordToVar and VarToRecord.

Here is an image of the table (including hiddenFields). See row 3 when using the select and temptable
The Order of the grid is
Field1|Field5|Field4|Field2|Field3 (Hidden)

Image

Do i have an error in reasoning?
Greetings from Germany
Klaus
User avatar
Cipolla
 
Posts: 166
Joined: Fri Apr 01, 2005 1:45 am
Location: Germany

Re: dbpVarToRec does not write hidden fields

Postby Gaev » Sun Nov 24, 2013 7:00 am

Klaus:

First, add an AlertBox command after the dbpRecordToVar command so you can see the contents of the variable [RecSource] ... specifically to see the order in which the fields are "downloaded".

I suspect that they are in the order Field1 to Field5. ... i.e. the order in the database table.

If so, when you do the subsequent dbpVarToRecord, it might be that the delimiter-separated-values are being loaded ...

- first in the order of the "column order"
- remaining fields filled in "by order of field in the database table"

Have not verified it myself ... but you might try and specify the specific fields (and order in which they should appear) for the Temporary Table e.g. ...
Code: Select all
dbpSetColumnOrder "[db_alias]" "testtable" "Field1;Field5;Field4;Field2"
dbpShowGrid "[db_alias]" "testtable" "testtable"
dbpExecSQL "[db_alias]" "SELECT Field1,Field5,Field4,Field2 |FROM testtable |WHERE field1 = '1-1'" "TTItem"
... that way the "downloaded" and "uploaded" delimiter-separated-values will be consistent.
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: dbpVarToRec does not write hidden fields

Postby Cipolla » Sun Nov 24, 2013 7:45 am

Gaev, thank u for your reply.

Gaev wrote:[...]

Have not verified it myself ... but you might try and specify the specific fields (and order in which they should appear) for the Temporary Table e.g. ...
Code: Select all
dbpSetColumnOrder "[db_alias]" "testtable" "Field1;Field5;Field4;Field2"
dbpShowGrid "[db_alias]" "testtable" "testtable"
dbpExecSQL "[db_alias]" "SELECT Field1,Field5,Field4,Field2 |FROM testtable |WHERE field1 = '1-1'" "TTItem"
... that way the "downloaded" and "uploaded" delimiter-separated-values will be consistent.[...]


Gaev, u are my man!

I did a ColumnOrder for the Temp-Table just before RecToVar. That´s the magic!

It seems, that RevToVar is linked to the source-table and loose the memory when using another table than the source-table for the RecToVar.
I did not thought about that, because the content or the order of the variable was in all cases exactly the same.

@Dave: i did not expect this behaviour, but maybe a hint in the Helpfile would be a good idea.

Thanks a bunch.
Greetings from Germany
Klaus
User avatar
Cipolla
 
Posts: 166
Joined: Fri Apr 01, 2005 1:45 am
Location: Germany

Re: dbpVarToRec does not write hidden fields

Postby Gaev » Sun Nov 24, 2013 8:25 am

Klaus:
I did a ColumnOrder for the Temp-Table just before RecToVar. That´s the magic!

Yes, that would also make the sequence of fields being "downloaded" to be compatible with the (visible, order of)fields in the Table to which the data was being "uploaded"
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: dbpVarToRec does not write hidden fields

Postby Neosoft Support » Sun Nov 24, 2013 1:43 pm

@Dave: i did not expect this behaviour, but maybe a hint in the Helpfile would be a good idea.


I didn't realize that you were using a temp table. The temp table is created by the database engine so DBPro doesn't have any control over the order of the fields. Unless you specify the output fields in your SQL statement (which Gaev points out), the db engine will create the temp table using the source table's original field order.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5602
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron