SQL Confused about Result Set

I am clearly confused or missing something. I have created a section of code where I try to read in values stored in a SQL table and based on those results, activate or inactivate buttons on an on-screen menu.

When I run my query below in SQL Management Studio, I get a result like

SELECT AdminInspectionSetup,PartsToyotaNoAutoDash,EmployeeNo,AdminProgram from Secure where EmployeeNo ='98084' 

AdminInspectionSetup,PartsToyotaNoAutoDash,EmployeeNo,AdminProgram
1,0,98084,-1

where the -1or a 1 indicates that the user would have access to a feature.

Based on the results of the query I am trying to call methods to disable various buttons on screen.

ISSUE —
When I view the results of the query or write them to a label, such as in the code below, I am not getting a 0 as expected but instead it returns “false”. Instead of
0,0,98084,-1
as I get in raw SQL Xojo returns
true, false, 98084,-1

I do not understand why it is converting the 0 to false in the recordset. The SQL field type is smallint for all of the field

Any thoughts?

[code] dim db as new ODBCDatabase
dim strSQL as string

db.DataSource=""+uDSN+""

if db.connect then
strSQL = “SELECT AdminInspectionSetup,PartsToyotaNoAutoDash,EmployeeNo,AdminProgram from Secure where EmployeeNo =’” + str(EmpID) + "’ "
Dim rs As RecordSet = db.SQLSelect(strSQL)
if rs = nil then
msgbox "There was an error in my sql: "+ db.ErrorMessage
else
While Not rs.EOF
lblShop.Text=rs.Field(“AdminInspectionSetup”).StringValue
lblParts.Text=rs.Field(“PartsToyotaNoAutoDash”).StringValue
lblAdminProg.Text=rs.Field(“AdminProgram”).StringValue
If rs.Field(“AdminInspectionSetup”).StringValue =“0” then
DisableShopForeman
End If
If rs.Field(“PartsToyotaNoAutoDash”).StringValue =“0” then
DisableShopParts
End If

    rs.MoveNext
  Wend
  rs.Close
end if

end if
[/code]

How are the columns defined in the DB ?

Use IntegerValue instead of StringValue. Xojo is interpreting the column as being a boolean column. (How is it defined in the table?)

correcting my typo

When I view the results of the query or write them to a label, such as in the code below, I am not getting a 0 as expected but instead it returns “false”. Instead of
1,0,98084,-1
as I get in raw SQL Xojo returns
true, false, 98084,-1

THE FIELDS ARE ALL DEFINED AS TYPE SMALLINT IN SQL SERVER.

Then definitely use IntegerValue to get the data. StringValue has to try to interpret the contents (since it doesn’t contain a string).

Or get the BooleanValue since thats what you’re looking for (true / false)
ie/

If not rs.Field("AdminInspectionSetup").BooleanValue then DisableShopForeman End If If not rs.Field("PartsToyotaNoAutoDash").BooleanValue then DisableShopParts End If

Ok silly question i think

how do i get

lblShop.Text=rs.Field("AdminInspectionSetup").StringValue

to display an integer value - I tried

lblShop.Text=rs.Field("AdminInspectionSetup").IntegerValue

and of course I get a Type Mismatch because I am trying to put a number in text field

lblShop.Text=str(rs.Field("AdminInspectionSetup").IntegerValue)

STR and FORMAT give you more control over the exact appearance of the value

I am truly grateful for the members of this forum.

Thank you.