SQLite SELECT problem

General
1

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
2

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?

3

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.

4

What happens if you do this:

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

I have no explanation why but this worked…

thanks everyone

6

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

7

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