Page 1 of 1

dbpVarToRec does not write hidden fields

PostPosted: Sat Nov 23, 2013 12:35 pm
by Cipolla
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?

Re: dbpVarToRec does not write hidden fields

PostPosted: Sat Nov 23, 2013 3:20 pm
by Neosoft Support
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.

Re: dbpVarToRec does not write hidden fields

PostPosted: Sun Nov 24, 2013 3:16 am
by Cipolla
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.

Re: dbpVarToRec does not write hidden fields

PostPosted: Sun Nov 24, 2013 5:14 am
by Cipolla
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?

Re: dbpVarToRec does not write hidden fields

PostPosted: Sun Nov 24, 2013 7:00 am
by Gaev
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.

Re: dbpVarToRec does not write hidden fields

PostPosted: Sun Nov 24, 2013 7:45 am
by Cipolla
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.

Re: dbpVarToRec does not write hidden fields

PostPosted: Sun Nov 24, 2013 8:25 am
by Gaev
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"

Re: dbpVarToRec does not write hidden fields

PostPosted: Sun Nov 24, 2013 1:43 pm
by Neosoft Support
@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.