Setting Datagrid Table Column Headings

[cmalumphy]

I am having trouble dynamically setting column headings on a datagrid table that I populate with the results of a database query. The sql query is entered by the user into a textfield, so the column headings cannot be set in advance since each query could request different fields in different orders. The query runs and the datagrid is populated, but I have not been able to figure out how to set the column headings. Here is my code:

Code: Select all

on mouseUp
   -- check the global connection ID to make sure we have a database connection
   global gConnectionID
   if gConnectionID is not a number then
      answer error "Please connect to the database first."
      exit to top
   end if
   
   put field SQLField into tSQL  
   put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
   
   //Couldn't even set columns successfully in advance
   //put "Name,Attempts,Success" into theFields
   //replace "," with CR in theFields
   //answer theFields
   //set the dgProp ["columns"] of group "DataGrid1" of card 3 to theFields
   
   -- check the result and display the data or an error message
   if item 1 of tData = "revdberr" then
      answer error "There was a problem querying the database:" & cr & tData
   else
      put tData into field "Data"
      put tData into field "TableField1" of card 2
      
      --tried this but it didn't work
      --put revDatabaseColumnNames(tData) into theFields -- get field names from table
      --replace "," with CR in theFields
      --set the dgProp["columns"] of group "DataGrid1" of card 3 to theFields

      --put tData into field "DataGrid1" of card ID 1078
      put false into firstLineContainsColumnNames
      set the dgText [firstLineContainsColumnNames ] of group "DataGrid1" of card 3 to tData
      
   end if
end mouseUp

Any suggestions would be appreciated.

[Klaus]

Hi cmalumphy,

you did not provide TABLE and gConnectionID to the "revDatabaseColumnNames" function!
Try this:

Code: Select all

...
  else
      put tData into field "Data"
      put tData into field "TableField1" of card 2
  
      put revDatabaseColumnNames(gConnectionID,"Name of your table here!") into theFields -- get field names from table
      replace "," with TAB in theFields
      set the dgText[TRUE ] of group "DataGrid1" of card 3 to (theFields & CR & tData)
...

Best

Klaus

[cmalumphy]

It still doesn't work. The datagrid does not populate using your code and the column headings do not appear.

Code: Select all

      put revDatabaseColumnNames(gConnectionID,"mytable") into theFields -- get field names from table
      replace "," with TAB in theFields
      set the dgText[TRUE ] of group "DataGrid1" of card 3 to (theFields & CR & tData)

The following populates the datagird, but leaves the column names blank.

Code: Select all

    put revDatabaseColumnNames(gConnectionID,"mytable") into theFields -- get field names from table
    replace "," with TAB in theFields
    put false into firstLineContainsColumnNames
    set the dgText [firstLineContainsColumnNams ] of group "DataGrid1" of card 3 to tData

Another problem will be that I do not want to get the field names from the "table", I want the field names from the actual data set returned, since the user could select fewer fields than are in the table or might join two or more tables to obtain the results. I've tried removing the table name, but it fails too.

[Klaus]

Oh, yes, sorry, my fault!

-> dgText [dgText [firstLineContainsColumnNams ]
The list of row column names in "firstLineContainsColumnNams" will "only" manage the ORDER
of the data in the datagrid, in case the data supplied are NOT in the same order of the columns
of the datagrid, so it knows where to put what!

Try this:
...
put revDatabaseColumnNames(gConnectionID,"mytable") into theFields -- get field names from table
replace "," with CR in theFields
set the dgProp ["columns"] of group "DataGrid1" of card 3 to theFields
...

I want the field names from the actual data set returned, since the user could select fewer fields than are in the table or might join two or more tables to obtain the results.

I'm afraid this info is not part of the returned data!
So you may need to get the user-selected db fields from somewhere else in your workflow!
Depends on how the user can select them, where you need to "hook"!


Best

Klaus

[cmalumphy]

Still doesn't work. Again, neither the datagrid nor the column headings appear. Could there be something wrong with the settings for my datagrid.

Also what does the following mean

There are two forms of the revDatabaseColumnNames function. It can either be used to return the list of column names in a record set, or as of version 2.9, the list of columns in a given table.

To get the list of columns in a record set use a form like this:

get revDatabaseColumnNames(tRecordSetId)

Where do you get tRecordSetId?

[MaxV]

Look this scrip, the "Data" tab contains a datagrid that change column labels each time: http://livecodeshare.runrev.com/stack/7 ... administer

[bogs]

It only took me 4 years to see this Max, but I really like that sample