SQLite SELECT problem

macOS 11.4, Xojo 2021.2.1, Desktop App.

I have a sqlite database with a table name “Times”. It has an integer column named fkTask. There is currently 1 record in the table and the value of fkTask is 1.

If I execute the following code I get the message that says “No Task Found…”, If I just say “SELECT * FROM Times” then I get the 1 record with the fkTask value of 1.

The SQL SELECT statement looks correct in the debugger. If I type the select statement in an app called BASE, it executes properly and retrieves the record.

What the hell am I missing???

Shared Function RowSetTask(iTask As Integer) As RowSet
  var rs   As RowSet
  var sSQL As String
  var sMsg As String
  
  sSQL = "SELECT * FROM Times WHERE fkTask = " + iTask.ToString
  
  try
    rs = dbSQL.SelectSQL(sSQL)
    if rs.RowCount = 0 then
      MessageBox("No Tasks Found for Task " + iTask.ToString)
    end if
  catch Err as DatabaseException
    sMsg = "Database error occured retrieving Times " + Err.Message
    clsSystemLog.WriteLog(CurrentMethodName, sMsg)
  end try
  
  Return rs
End Function

I can’t see anything wrong with your code.

In fact, I copied your code using the SQLiteExample from the Xojo Examples and used:

data = RowSetTask(1)

and is working correctly.

Can you create a simple sample and share it using dropbox or another sharing service?

Edit: maybe the fkTask value in the database has an invisible character?

try to avoid using mixedcase characters in tables and fields names.
some databases engines are case aware and some don’t. it can lead to difficult bugs.

What happens if you do this:

 sSQL = "SELECT * FROM Times WHERE fkTask = ?;"
...
rs = dbSQL.SelectSQL(sSQL, iTask)

I have no explanation why but this worked…

thanks everyone

If that worked, my first suspicion is that the fkTask field is not an integer. Perhaps a string (text or var char) field?

From your original code, can you paste here what sSQL looks like in the debugger after you execute

sSQL = "SELECT * FROM Times WHERE fkTask = " + iTask.ToString

Forum for Xojo Programming Language and IDE. Copyright © 2021 Xojo, Inc.