Populating a datagrid form with database values

[jalz]

Hey Guys,

I've got a fairly simply query I hope. I know the problem is, however I just can't figure out how to do this. I have a data grid form with 4 fields on it. I want to query my database and the populate the 4 fields in the form with the data that has been returned. i can seem to do this with a data grid table fine, its the forms I'm having trouble with as I need to convert the returned data into an array so I can handle.

Here is the code I have so far on the opencontrol handler for the data grid

Code: Select all

   if (gConnID > 0) then
     put "SELECT description, item_price, qty, line_price FROM purchase_line WHERE OrderID =" && "'" & tOrderID & "'" & "ORDER BY lineID" into tTheSQLQuery
      
      put revDataFromQuery(tab, cr, gConnID, tTheSQLQuery) into tTheData
      set itemdelimiter to tab
      put 1 into Counter
      
      if tTheData begins with "revdberr," then
         delete item 1 of tTheData
      else
         
         repeat for each key tIndex in tTheData
            put item 1 of tIndex into tTheArrayData["label 2"]
            put item 2 of tIndex into tTheArrayData["label 3"]
            put item 3 of tIndex into tTheArrayData["label 4"]
            put item 4 of tIndex into tTheArrayData["label 5"]
            
            dispatch "AddData" to me with tTheArrayData, tIndex
            --set the dgData of me to tTheArrayData
         end repeat
      end if

the fillindata I have for the form is as below

Code: Select all

on FillInData pDataArray
   if (pDataArray["label 1"] is a color) then
      set the backcolor of graphic "Background" of me to pDataArray["label 1"]
   end if
   set the HTMLText of field "fld_description" of me to pDataArray["label 2"]
   set the text of field "fld_item_price" of me to pDataArray["label 3"]
   set the text of field "fld_qty" of me to pDataArray["label 4"]
   set the text of field "fld_line_price" of me to pDataArray["label 5"]
end FillInData

Anyone out there to guide me with my set the dgData command for the data grid form? Am I right, the data needs to be in an array before I can populate the dg?

Thanks
Jalz

[Klaus]

Hi Jalz,

Am I right, the data needs to be in an array before I can populate the dg?

yes, when setting the dgDATA of a datagrid, you MUST supply an array!

But this:
...
put revDataFromQuery(tab, cr, gConnID, tTheSQLQuery) into tTheData
..
will only return a TAB & CR delimited text list.

And this:

Code: Select all

...
repeat for each key tIndex in tTheData
            put item 1 of tIndex into tTheArrayData["label 2"]
            put item 2 of tIndex into tTheArrayData["label 3"]
            put item 3 of tIndex into tTheArrayData["label 4"]
            put item 4 of tIndex into tTheArrayData["label 5"]         
            dispatch "AddData" to me with tTheArrayData, tIndex
            --set the dgData of me to tTheArrayData
end repeat
...

does not produce anything useful!?
"theData" is not an array here and so it does not have any keys that could be computed!

And meaningful key names would also help a bit

Do something like this with "theData"

Code: Select all

...
set itemdel to TAB
put 1 into tCounter
put empty into tArray
repeat for each line i in theData
  put item 1 of i into tArray[tCounter]["label 2"]
  put item 2 of i into tArray[tCounter]["label 3"]
  put item 3 of i into tArray[tCounter]["label 4"]
  put item 4 of i into tArray[tCounter]["label 5"] 
  add 1 to tCounter
end repeat
## NOW this will work:
set the dgData of me to tArray
...

Best

Klaus

[jalz]

Hi Klaus,

Almost! the data that is returned from the description field contains carriage returns, thats why I was trying to use the repeat for each key i in tTheData as I thought this may allow me to split entire chunks and update the data grid form appropriately. Having said that your code works, it just a splits the whole description field in separate rows in the datagrid for ever CR there is in the data.

Jalz

[Klaus]

AHA! In that case you have to change the way you retrieve your data, right!

Do like this:

Code: Select all

...
## This is the trick:
put numtochar(1) into tRowDel
put numtochar(2) into tLineDel
put revDataFromQuery(tRowDel,tLineDel, gConnID, tTheSQLQuery) into tTheData
...
## This is the other trick:
set itemdel to tRowDel
set linedel to tLineDel

put 1 into tCounter
put empty into tArray
repeat for each line i in theData
  put item 1 of i into tArray[tCounter]["label 2"]
  put item 2 of i into tArray[tCounter]["label 3"]
  put item 3 of i into tArray[tCounter]["label 4"]
  put item 4 of i into tArray[tCounter]["label 5"] 
  add 1 to tCounter
end repeat
## NOW this will work:...
set the dgData of me to tArray
...

Best

Klaus

[jalz]

Hi Klaus,
Thanks once again, you've solved it

I've got to check up on your new delimiters, not quite sure I understand them (start of header, start of text) - but the rest of the code makes sense to me.

Many thanks
Jalz

[Klaus]

Hi Jalz,

I've got to check up on your new delimiters, not quite sure I understand them (start of header, start of text)

that is quite simple, but maybe I should have used tItemDel instead of tRowDel in my script

The LC default ITEM delimiter -> Comma
The LC default LINE delimiter -> CR

But luckily we can define other (single) characters as item or line deliniter!
And we can of course supply them to our "revDataFromQuery" function, which is a must
when it comes to multi-line content of database fields.

Here the default CR as linedelimiter will of course wreck the retrieved data

Since numtochar(1) and numtochar(2) are usually not "typable" they will surely not be contained in any
database field and we can safely use them to retrieve our data and have them correctly "delimited".


Best

Klaus

[fko]

Hi,

I discovered LiveCode ten days ago, and it seems very nice and easy...

Except how to work with the DataGrid...At least for me...

Before to begin with the DataGrid i tried to manage ( Select, insert, delete, drop, update) some tables and it is working fine...

But now i am trying to display data in one DataGrid and from this DataGrid make also update and delete rows.
But i am not able...

Someone can help me?
One little example ( with three rows and three columns ) about how to access to these rows and update and delete it will be great...

For the application i am trying to do, i would like to choose how many columns and the name of this columns during the execution and not from the 'Inspector'...Is it posible? How i should make this?

Thanks in advance!!!

[Klaus]

Hi fko,

1. welcome to the forum!

2. Well, you picked the most complex object EVER in Livecode, the datagrid, as a start

Do this:
Go here: http://lessons.runrev.com/m/datagrid
and load the datagrid PDF, the only docs about the datagrid!

Then read it at least 10 times , work throught the examples to get a feel for this stuff
and come back with more specific questions.


Best

Klaus

P.S.
You may want to take a look at these stacks, great learning resources!
http://www.hyperactivesw.com/revscriptc ... ences.html

[fko]

Hi Klaus,

Thanks for your answer and your welcome!!!

I read this pdf ( not 10 times but twice for the moment), and finally i managed to do some things i wanted to do...

Except how to put the name of columns during the execution...

i tried this:

put "Nif_cif,Name,adress,village,Email,PhoneNumber" into theColNames
set the dgText [true] of group "DataGrid1" to theColNames & cr & theData

that i addapted from one example i saw...But, it didn't work...

I have one table that is 'personalInfo'...And the user of this application he can decide in the beginning which information he wants to keep...And once he choosed which information and the name of the column ( for example he can change 'PhoneNumber' for 'phone' or 'cellular'...) i make the CREATE TABLE.
So for this i can't know in advance the name of the columns to prepare the DataGrid...

But i tried wit the two lines above and i didn't manage...


Then with the help of bangkok i discovered this:

"set the dgProp["columns"] of grp "myDG" to tColumns"...

But this is changing only the first column...

So how can i do this for the rest of columns?

Regards,
fko

[Simon]

Hi fko,
Try "set the dgProp["column labels"] of grp "myDG" to tColumns"

From the information here:
http://lessons.runrev.com/m/datagrid/l/ ... properties

column labels
- Line delimited list of labels for columns in your table.

Simon

[fko]

Hi Simon,

I really don't manage...
I think i am not understanding what i have to do...I begin to feel null...

Can you explain me how it is working with one short but complete example?

imagine that i have to add four names of columns...Imagine that for example these names are in four fields 'field1' 'field2' 'field3' and 'field4'

and the value of these fields are 'name' 'surname' 'age' and 'email'

what i have to do with the instruction you suggested to me? Because really i don't see it even if i already read the API of DataGrid and other proposed links...

I don't see how to make that the name of the first column will be 'name', the second 'surname' the third 'age' and the fourth 'email'
Where and how can i choose the order of the columns to finally put in each one the name i want?

I tried to do this in NET and i reached it quite easy, but here i don't see how to do it...


Thanks in advance!!!

[Simon]

Well from this

column labels
- Line delimited list of labels for columns in your table.

it should be

Code: Select all

put "name" & cr & "surname" & cr & "age" & cr & "email" into tColumns
set the dgProp["column labels"] of grp "myDG" to tColumns

Just make sure you have 4 columns to before you start.

Simon

[fko]

Hi Simon,

so i put this:

put "name" & cr & "surname" & cr & "age" & cr & "phone" into tColumns
set the dgProp["column labels"] of grp "myDG" to tColumns

And now the in the datagrid i can see wriiten "name" "surname" "age" and "phone"...

But if i don't put in the inspector in the Column name field "name" "surname" "age" and "phone" by hand is not working...

And i was looking but i didn't see nothing to change during i execute the progam...
There is nothing like this? set the dgProp["column names"] of grp "myDG" to tColumns

And the other thing is that before to run the program i don't know how much columns will have the table...
I would like to add something like this "put 4 into numberofColumns"...But this too i didn't find in the DataGrid API? It exists something like this?

Regards,
fko

[Klaus]

Hi fko,

you already had it!

LABEL is only the visible part of the datagrid header, but not the NAME of the column, so you have to:
...
set the dgProp["columns"] of grp "myDG" to tColumns
...


Best

Klaus

[fko]

Hi Klaus,


Thanks!!! Now is working fine!!!

Which 'instruction' i need to define the number of columns during the execution?


Regards,
fko