Populating a Form Datagrid with Records from a Database

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
montymay
Posts: 145
Joined: Thu Jul 18, 2013 5:23 am

Populating a Form Datagrid with Records from a Database

Post by montymay » Fri Feb 09, 2018 10:09 am

Hello LC forum,

I have learned out to programmatically populate a datagrid TABLE with the results of a SELECT query that returns tab- and return-delimited text, but I can find nothing in the User Guide or this forum on how to programmatically populate a datagrid FORM that contains just text fields with the data that the query returns. Is this possible? I am thinking I need or should use a FORM rather than a TABLE because one field from my database will contain a long paragraph of text and I can more attractively place that field in the row of a FORM than a TABLE. Can a TABLE be used instead of a FORM for this? Should a TABLE be used instead of a FORM for this? If I can and should go with a FORM, can one who knows how to write the code give me a tip or clue? Thanks for your reply(ies).

Monty May

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Populating a Form Datagrid with Records from a Database

Post by Klaus » Fri Feb 09, 2018 12:34 pm

Hi Monte,

if your database fields do NOT contain mulit-line text, setting "the dgtext" of your datagrid to the result of a database query should also work with FORM datagrids!

Why not just try it out?
I promise, your computer will not explode if you do! :D


Best

Klaus

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: Populating a Form Datagrid with Records from a Database

Post by bogs » Fri Feb 09, 2018 4:11 pm

(Dodging computer shrapnel) but you said... :shock:

:mrgreen:
Image

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Populating a Form Datagrid with Records from a Database

Post by Klaus » Fri Feb 09, 2018 4:17 pm

Just don't believe everything you read on the internet! 8)

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: Populating a Form Datagrid with Records from a Database

Post by bogs » Fri Feb 09, 2018 4:32 pm

But...but...they can't put it on the interwebz if it isn't true ! Image
Image

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Populating a Form Datagrid with Records from a Database

Post by Klaus » Fri Feb 09, 2018 7:01 pm

They can and they do, my little pumpkin! :D

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: Populating a Form Datagrid with Records from a Database

Post by bogs » Fri Feb 09, 2018 8:20 pm

LOL I really need to come in from the fields more often Image

My apologies to montymay, you will now be returned to your normal trouble shooting channel (I hope) :mrgreen:
Image

montymay
Posts: 145
Joined: Thu Jul 18, 2013 5:23 am

Re: Populating a Form Datagrid with Records from a Database

Post by montymay » Sat Feb 10, 2018 4:47 am

Thanks Klaus and Bogs,

Enjoyed the wordplay, especially Bogs first post, and, newly confident that using a FORM would be trivially simple, I searched the User Guide harder and found the answer on p. 166 of my edition:
If you set the dgText property of a data grid form then the data will be imported, but it is up to you to modify your Row Template Behavior to display the imported data correctly. If pFirstLinesContainsHeader is false then each item of each line in pText will be named "Label X" (where X is the item number) in the array that is passed to FillinData.
The second sentence contained the answer! I had been incorrectly using the names of the fields of the row group. This works:

Code: Select all

    
on fillinData pDataArray
   	set the text of fld "id" of me to pDataArray["Label 1"]
  	set the text of field "plnum" of me to pDataArray["Label 2"]
  	set the text of fld "effectDate" of me to pDataArray["Label 3"]
   	set the text of fld "summary" of me to pDataArray["Label 4"]
   	set the text of fld "action" of me to pDataArray["Label 5"]
   	set the text of fld "keys" of me to pDataArray["Label 6"]
end FillInData
Which leads me to another question for which you might have an answer: is there a workaround so that you can insert multi-line text into the field of a database and then retrieve such formatted text back into a field of a single datagrid record?

Monty May

jameshale
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 474
Joined: Thu Sep 04, 2008 6:23 am
Location: Melbourne Australia

Re: Populating a Form Datagrid with Records from a Database

Post by jameshale » Sun Feb 11, 2018 12:44 am

Placing and retrieving multi line text from a database is possible. The variable(s) containing multiple lines are still just variables.

Retrieve the data from the dB is a bit trickier.

If you didn’t populate the database (and thus do not know if multi lines are present you will need to choose row and column delimiters that you know will not be in the actual data. Then, using those, build an indexed array with the result.

Then set the dgdata of the data grid.
set the dgData of group "DataGrid" to <pDataArray>

Note: if you,are using LC8 or above, the guide tab in the dictionary shows the Data Grid guide. This is in effect the lessons from the website updated to reflect the newer interface.

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Populating a Form Datagrid with Records from a Database

Post by Klaus » Sun Feb 11, 2018 7:44 pm

Hi Monty,
montymay wrote:
Sat Feb 10, 2018 4:47 am
...Which leads me to another question for which you might have an answer: is there a workaround so that you can insert multi-line text into the field of a database and then retrieve such formatted text back into a field of a single datagrid record?
simple answer: don't use TAB/CR as field/record delimiter! :D

Use something that will (and CAN) NOT be in the text of any database field like:

Code: Select all

...
revDataFromQuery(nativeNumtoChar(1),nativeNumToChar(2), databaseID, SQLQuery) into tDBStuff
...
Then use these new delimiters to loop through tDBStuff and create an array which you then can:

Code: Select all

...
set linedelimiter to nativeNumToChar(2)
set itemdelimiter to nativeNumToChar(1)
put 1 into tCounter
repeat for each line tLine in tDBStuff
  put item 1 of tLine into tArray[tCounter]["name of column1"]
  ## etc. for all ITEMS in tLine
  add 1 to tCounter
end repeat
...
set the dgdata of grp "your dg here" to tArray
...
Get the picture? Now an ITEM can have multilinetext!


Best

Klaus

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”