set checkbox in listbox row, when database rowtype is boolean

Databases
1

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?

2

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.

http://www.bkeeney.com/rbinto/activerecord/

3

You can get the FieldType from the Recordset.ColumnType: http://documentation.xojo.com/index.php/RecordSet.ColumnType

4

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.

5

Thanks a lot Bob for the great tipps! It’s exactly what I was looking for. Thanks also Markus for the hint.

6

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
End If
Next[/code]

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?

7

IdxField is 1 based.

When you encounter a problem then the first step should be to read the documentation.

8

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?

9

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?

10

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
return
end

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
next
[/code]

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

wend
[/code]

11

Wow, you are great! This helps a lot. Thanks so much.
Update: works like a charm!