LiveCode Forums.

Posted: **Sun Sep 08, 2019 10:02 am**

I Want to add column headers dynamically into the datagrid and then populate it with an SQL Cursor.
The code connects to the database and creates the headers but does not populate the grid or generate any errors.
I must be missing something here.
Here is my code

global conID -- connection ID
global realPath -- where executable was started
global supress
constant sq="'"
on mouseUp
-- use a global variable to hold the connection ID so other scripts can use it
global ConID
-- set up the connection parameters - edit these to suit your database
put "sql9.jnb2.host-h.net" into tDatabaseAddress
put "gigscwsneg_db1" into tDatabaseName
put "gigscwsneg_1" into tDatabaseUser
put "Password" into tDatabasePassword
-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
-- check if it worked and display an error message if it didn't
-- & set the connection ID global
if tResult is a number then
put tResult into ConID
answer info "Connected to the database." & cr & "Connection ID = " & ConID
else
put empty into ConID
answer error "Unable to connect to the database:" & cr & tResult
end if
end mouseUp

populating the grid.

global conID -- connection ID
on mouseUp
local theCursor, temp, theFields, tSQL, pText, dList, ii
if not Connected() then exit to top
put revQueryDatabase( conID, "SELECT * FROM testtable") into theCursor
put the result into temp --for debugging
if "ERROR" is in temp then
answer "ERROR=" & the result
exit to top
end if
put theCursor into fld "rCounter" -- displays the number of lines
put revDatabaseColumnNames(theCursor) into theFields -- get field names from table
if the " revdberr" is in the result then
return "ERROR=" & the result
end if
replace "," with CR in theFields
set the dgProp["columns"] of group "myGrid" to theFields
put "SELECT * FROM testtable" into tSQL
put revdb_querylist(,,conID,tSQL) into ptext
put the result into dlist
if "revdberr" is in the result then
return "ERROR=" & the result
end if
put the number of lines in pText into fld "rCounter" -- displays the # of lines
set the dgColumnWidth["Handle"] of group "myGrid" to 100
set the dgColumnWidth["Entry"] of group "myGrid" to 160
repeat with ii = 1 to fld "rCounter"
put line ii of dlist into temp
dispatch "AddLine" to grp "myGrid" with temp
end repeat
end mouseUp

Any help / pointers would be appreciated.
Thanks
Peter G

Posted: **Sun Sep 08, 2019 11:10 am**

Hi,
2 mistakes :

- put the result into dlist (actually the data from the query are stored into ptext !)

- wrong method to populate the DG (addline. Use instead dgText)

So here is something that should work, more simple :

Code: Select all

     put "SELECT * FROM testtable" into tSQL
     put revDataFromQuery(,,conID,tSQL) into ptext

     put the result into tResult
     if tResult contains "revdberr"  then
          answer "ERROR=" & tResult 
     end if

     put the number of lines in pText into fld "rCounter" -- displays the # of lines

     set the dgText of group "myGrid" to pText --- that's all you need.

You could also use dgData to populate the datagrid, with an array that you would build (with a loop) from the raw data returned by the SQL query.

It could be usefull for instance to change the order of columns displayed versus the columns order in your SQL table.

If your DG has 3 columns : column3, column2 ,column1
And your table : column1, column2, column3

Code: Select all

put "SELECT * FROM testtable" into tSQL
put revDataFromQuery(,,conID,tSQL) into ptext

put empty into pArray
set itemdelimiter to tab

repeat with i = 1 to the number of lines of pText

put  item 3 of line i of pText into pArray[i]["column3"]
put  item 2 of line i of pText into pArray[i]["column2"]
put  item 1 of line i of pText into pArray[i]["column1"]

end repeat

set the dgData of group "myGrid" to pArray

This is of course just an example. It would be much easier to do directly :

Code: Select all

put "SELECT column3,column2,column1 FROM testtable" into tSQL

But it's just to show you the process with an array and dgData.

Posted: **Sun Sep 08, 2019 1:35 pm**

Thanks you Bankok
Got it to populate the datagrid now.
But it does only the first 7 Columns Although it managed to create all the column headers from the selected table.
This this seems to be a bit weird because both are based on the same query result.

Posted: **Sun Sep 08, 2019 1:42 pm**

Hi Bankok
My Apologies.....Had a typo and fixed it.
Works perfectly.

MAny Thanks

LiveCode Forums.

Populating Datagrid From SQL Cursor

Populating Datagrid From SQL Cursor

Re: Populating Datagrid From SQL Cursor

Re: Populating Datagrid From SQL Cursor

Re: Populating Datagrid From SQL Cursor