Populate Datagrid from a Query

[lohill]

I am trying to populate a datagrid from a SQL query. The datagrid is 'generic' and only knows that it has columns 'Col 1' thru 'Col 30'. I want to populate that grid and assign column names that correspond to the keys of an array returned by the query. I am able to get a valid array from the query. From the first element of that array (tArray[1]) I get a valid set of column names from the keys. I can step through those keys and set what appear to be valid column names for each column in the datagrid that correspond with the array keys. When I 'set the dgData of group "Results" to tArray', I can see by the scroll bars of the grid that data is present but I cannot see any data. 'Refresh Data Grid' from the Inspector does not make anything visible. Sending "RefreshList" to "Results" does not help nor does sending "ResetList". Sending "ResetControl" apparently does its thing because the scroll bar goes away.
Prior to "ResetControl" a separate button with the following code shows the array for tData but nothing for tText

Code: Select all

on mouseUp
     put the dgData of group "Results" into tData
     put the dgText of group "Results" into tText
end mouseUp

Here is my actual code for the button to populate the grid:

Code: Select all

on mouseUp
   put the selection into tSQL
   if tSQL is empty then
      answer information "A SQL statement must be selected for execution." titled "Execute SQL" as sheet
      exit mouseUp
   end if
   put word 1 of tSQL into tType
   switch tType
      case "Select"
         put revQueryDatabase(getDatabaseID(), tSQL) into tCursor 
         if tCursor is not an integer then 
            answer "Error: Unable to fetch account info -" && tCursor 
            exit mouseUp
         end if 
         set the dgText of group "Results" to empty
         put databaseFetchDataFromCursorAsArray(tCursor) into tArray
         repeat with i=1 to 30
            put "Col " & i into tColName
            set the dgColumnLabel[tColName] of group "Results" to tColName
         end repeat
         put tArray[1] into tSingle
         put the keys of tSingle into tKeys  --each line of tKeys is a column name
         put the number of lines of tKeys into tCount
         repeat with i=1 to tCount
            put line i of tKeys into tColName
            put "Col " & i into tOldCol
            set the dgColumnLabel[tOldCol] of group "Results" to tColName
         end repeat
         set the dgData of group "Results" to tArray
         break
      case "Update"
         answer "UPDATE not written yet."
         break
      case "Delete"
         asnwer "DELETE not written yet."
         break
      default
         answer tType
   end switch
end mouseUp

Thanks in advance for any insights,
Larry

[sritcp]

Hi iohill:

The data array of a datagrid refers to where its data is stored, not what is displayed in the datagrid (you can choose to display only a subset of the data).

Unlike with dgText (where you can supply the names of the column as the first line of the dgText textual data), with dgData you have to separately inform the datagrid which columns it should display and in what order.
This is done by setting its dgProp["columns"], not to be confused with dgProp["column labels"]

Code: Select all

set the dgProp["columns"] of group "Results" to theColumnList

where theColumnList is a line-delimited list of column names.

Regards,
Sri

EDIT: With dgText, when you provide the column names in the first line, they are the names of the columns for the data that follows it. If you want a subset of the data to be displayed in the datagrid, you still have to use dgProp["columns"] property.

MORE EDIT: On further reading, I see that you need to set the columns of the datagrid EVEN in the case the first line of dgText contains column names for the data that follows it.

[lohill]

Thank you Sri,

Once again you have put me on the right track.

Larry