SQLlite to DataGrid

[lohill]

I haver a SQLLite database table with 9 columns. Every row is populated but not every field in the row contains data. To add to the confusion, some of the fields have multiple lines. I would like to get the entire table into a datagrid. A snipit of some of the test code is below:

Code: Select all

   put "SELECT sKey,id,ord,radiscript,conditions,steps,labels,refs,notes FROM steps" into tSQL
   put ExecuteSQL(tSQL) into tResult
   put the number of lines of tResult into tLines
   repeat with i=1 to tLines
      put line i of tResult into tLine
      put PrepForSteps(tLine) into tArray
      dispatch "AddData" to group "DataGrid Steps" with tArray
   end repeat

The not yet complete function PrepForSteps looks like this

Code: Select all

function PrepForSteps tLine
   set the itemdelimiter to tab
   put the number of items of tLine into tItems
   answer tItems & return & tLine
   
   return tArray
end PrepForSteps

My intention was to put the items of the line into the array but it turns our that the lines differ in the number items so I am not sure how to handle it. Any suggestion for how to make this work or any alternate methods that are better would be appreciated.

Thanks,
Larry

[bangkok]

Why do you want to use "AddData" to populate your datagrid with the result of your SQLIte query ?

Unless you want to apply some "post SQL query treatement" to the data, it's pointless.

You have 2 ways to populate a datagrid. Using dgtext (easiest) or dgdata (with array).

Code: Select all

put "SELECT sKey,id,ord,radiscript,conditions,steps,labels,refs,notes FROM steps" into tSQL
 put ExecuteSQL(tSQL) into tResult
set the DgText of group "DataGrid Steps" to tResult

For DgData you have to be sure that your array keys have the same name that the columns names in your datagrid.

Last but not least : what happens if your SQL columns contains "lines" (CR or LF character) or tabulation ? It will break the display of the data in the datagrid (because datagrids use tab as column delimiter and CR as row delimiter).

To manage this situation, just use a turn around with delimiters :

For instance :

Code: Select all

put "SELECT sKey,id,ord,radiscript,conditions,steps,labels,refs,notes FROM steps" into tSQL
put revDataFromQuery("$","|",tID,tSQL) into tResult
replace "|" with CR in tResult
replace "$" with tab in tResult
set the DgText of group "DataGrid Steps" to tResult