I am trying to read from a database table, if the rowtype is set to boolean. If that is the case, I would like to populate the corresponding row in the listbox with checkboxes. But I am having trouble to read out the rowtype from the sql database. Any tips?
If I’m not mistaken then SQLite saves true as 1, false as 0. it is up to you to convert accordingly.
I strongly recommend using Bob Keeney’s free ActiveRecord class for working with databases.
You can get the FieldType from the Recordset.ColumnType: http://documentation.xojo.com/index.php/RecordSet.ColumnType
Personally, I’d stay away from ActiveRecord until you become familiar with the way the Database/RecordSet/DatabaseRecord classes work. Yes, it abstracts all that work away, but underpinnings are still the same old Xojo db classes.
Thanks a lot Bob for the great tipps! It’s exactly what I was looking for. Thanks also Markus for the hint.
Trying desperately to get this code to run, but I don’t succeed:
[code]Dim colType As Integer
Dim stringValue As String
For i As Integer = 0 To rs.FieldCount-1
colType = rs.ColumnType(i)
If colType = 5 Or colType = 18 Then // String
stringValue = rs.IdxField(i).StringValue
It says 'NilObjectException on the line ‘stringValue = rs.IdxField(i).StringValue’. Might it be, because I am running realStudio 2011? How can I find out, whether idxField is supported by my older version of the software?
IdxField is 1 based.
When you encounter a problem then the first step should be to read the documentation.
so idxField(i+1)? With that it runs through, but delivers empty strings. I tried to find something in the documentation: http://documentation.xojo.com/index.php/RecordSet.IdxField. But couldn’t find anything. Do you know of another link?
update: I am sorry, I messed something up. I am actually trying to read out the “row-name” or “fieldName” of the rs-entry, not the value itself. I had the impression, idxField was doing that. But that’s wrong. Any idea, how to get the name of the header from the database table?
From my 34 minute video “Subscriptions/Language 2/13.0 Display Data From Any Database Connection” at http://xojo.bkeeney.com/XojoTraining/. Most videos (over 200) come with source code you can use in your own project.
This code fills in the column headers
[code]dim rs as recordset = mdb.SQLSelect(sSQL )
if mdb.Error then
MsgBox "DB Error: " + EndOfLine + mdb.ErrorMessage
lbValues.ColumnCount = rs.FieldCount //lbValues is a Listbox
for i as integer = 1 to rs.FieldCount
sFieldName = rs.IdxField(i).name
lbValues.heading(i-1) = sFieldName
This code populates the listbox. It doesn’t do checkboxes but it could easily be modified to do it.
[code] while rs.eof = false
lbValues.AddRow rs.IdxField(1).StringValue dim iRow as integer = lbValues.LastIndex for i as integer = 2 to rs.FieldCount lbValues.cell(irow, i - 1) = rs.IdxField(i).StringValue next rs.MoveNext
Wow, you are great! This helps a lot. Thanks so much.
Update: works like a charm!