LiveCode Forums.

Posted: **Mon May 30, 2011 10:10 pm**

This is the code from my CRUD SQLite Example.

Amazing! Only 10 lines of code!
No matter how many fields are in the table--
LiveCode fully populates DataGrid.

Code: Select all

on doRead ----------------------------------------------------------
   if not Connected() then exit to top
   try
      put "SELECT * FROM users" into tSQL
      put revdb_querylist(,,gConID,tSQL) into ptext
      set the dgText of group "myGrid" to ptext
   catch theError
      answer warning theError
   end try
end doRead

Note: "Connected()" is a function that checks for an active connection

But column names are created as: "column1, column2, etc."

This code could be better, if afterwards, it could then do a second SQL query,
to read all the field names in the table, then update the DataGrid column headings.
To do this, there's two things I'm stuck on.

1- The SQL to query to read all field names in a table.

2- How to reference the individual DataGrid column headers directly.

In this way, my CRUD SQLite Example code, would be able to take
any table name and populate DataGrid with field proper names.

Posted: **Mon May 30, 2011 10:31 pm**

revQueryDatabase will give you a recordset ID which you can then use with revDatabaseColumnNames to get the field/column names in the recordset.

EDIT: And

Code: Select all

set the dgProp["columns"] of group "DataGrid 1" to theColumnHeadings

where 'theColumnHeadings is a return-separated string of all the column heading values.

Posted: **Tue May 31, 2011 7:52 pm**

Thanks dglass! From the instructions in your post I found this lesson:
Converting a Database Cursor to a Data Grid Array

My code has been udpated to:

Code: Select all

on doRead ----------------------------------------------------------
     if not Connected() then exit to top
     try
          put revQueryDatabase( gConID, "SELECT * FROM users") into theCursor
          put revDatabaseColumnNames(theCursor) into theFields -- get field names from table
          replace "," with CR in theFields
          set the dgProp["columns"] of group myGrid to theFields
          put "SELECT * FROM users" into tSQL
          put revdb_querylist(,,gConID,tSQL) into ptext
          set the dgText of group "myGrid" to ptext
     catch theError
          answer warning theError
          exit to top
     end try
end doRead

Note: setting the column names, clears the DataGrid, so this must be done before it is populated.

Posted: **Wed Jun 01, 2011 2:58 am**

townsend,

did you update the download on the other post? i would love to download it once its updated. its really helped me to understand how to do things more

with your code I have managed to add on an editing window when double clicked brings up a form for editing and when saved refreshes the datagrid
to show the changes.

Posted: **Wed Jun 01, 2011 4:31 am**

townsend,

i tried your code but im not getting column names just col1, col2, etc... I copied it exactly as you have it.
another odd thing is i have an action TryMe attached to a button that calls a modified version of your doread read function.
The first time its clicked it only loads three columns but the second time it loads all the columns from the database into
the datagrid. Any suggestions or ideas on either issue? I thought maybe revDatabaseColumnNames would be the answer
but i cant seem to get it to work for me.

This is my button code:

Code: Select all

on TryMe
   local countera
   put counter into countera
   doRead3 12
end TryMe

This is my modified doread

Code: Select all

on doRead3 Maxrecords ----------------------------------------------------------
   if not Connected() then exit doRead3
   try      
      if LimitBegin is empty then 
         //answer "empty"
         put "0" into LimitBegin
      end if
      
      put LimitBegin + Maxrecords into LimitEnd
      
      put revQueryDatabase( gConID, "SELECT * FROM products  LIMIT 1") into theCursor
      put LimitEnd into LimitBegin
      
              replace "," with CR in theFields
              set the dgProp["columns"] of group myGrid to theFields
              put ("SELECT * FROM products  LIMIT " & LimitBegin &","& LimitEnd)  into tSQL
              put revdb_querylist(,,gConID,tSQL) into ptext
              set the dgText of group "myGrid" to ptext
         catch theError
              answer warning theError
              exit to top
         end try
    end doRead3

Posted: **Wed Jun 01, 2011 5:06 am**

jesse wrote: put revQueryDatabase( gConID, "SELECT * FROM products LIMIT 1") into theCursor
put LimitEnd into LimitBegin

replace "," with CR in theFields
set the dgProp["columns"] of group myGrid to theFields
put ("SELECT * FROM products LIMIT " & LimitBegin &","& LimitEnd) into tSQL
put revdb_querylist(,,gConID,tSQL) into ptext
set the dgText of group "myGrid" to ptext
catch theError
answer warning theError
exit to top
end try
end doRead3[/code]

Attention Jesse... You have something wrong here.... theCursor is supposed to contains your SQL column names, right ? But then you use theFields.

Posted: **Wed Jun 01, 2011 5:17 am**

Well I feel so dumb. lol. Its been a long day. Thanks! Its now works as expected

LiveCode Forums.

SQLite: SQL for reading field names

SQLite: SQL for reading field names

Re: SQLite: SQL for reading field names

Re: SQLite: SQL for reading field names

Re: SQLite: SQL for reading field names

Re: SQLite: SQL for reading field names

Re: SQLite: SQL for reading field names

Re: SQLite: SQL for reading field names