SQlite SQL error

I have this code which looks for an ID in a SQlite table and writes a date.

The recordset returns nil though the record with this ID is actually there. I use SQlite Manager to test SQL statements before I write them in code. The SQL statement returns a recordset in SQLite manager, but not in my program. In the debugger the SQL string is correct and is shown like “select * from CDD2_Mitarbeiter where M_ID = ‘13’” (verbose copy from the debugger).

Why does it not find this record???

[code] dim sqlString as string
dim theDate as new Date
dim rs as recordset
sqlString = “select * from CDD2_Audit where A_ID = '” + EscapeQuotes(Str(CDD2.UserSessionID)) + “’”

rs = CDD2.DB.SqLSelect(sqlString)
if rs <> nil then
rs.Edit
if not CDD2.DB.error then
rs.field(“A_LOGGEDOUT”).DateValue = theDate
rs.update
rs.close
else
msgBox("Datenbank Fehler: " + CDD2.DB.ErrorMessage)
end if
end if[/code]

Two things I’d try. First make sure CDD2.UserSessionID is what you expect it to be. Second try reproving the quotes from around the ID if its just a number.

Oh, and Nil is different than returning an empty record set. I’d also check CDDB2.DB.ErrorMessage right after the SQLSelect. You may be getting an error.

Hi Greg,

reading the DB.Error did the job… stupid error… I closed the database already :frowning:

Thanks.

[quote=25878:@Alexander van der Linden]

[code]
sqlString = “select * from CDD2_Audit where A_ID = '” + EscapeQuotes(Str(CDD2.UserSessionID)) + “’”

rs = CDD2.DB.SqLSelect(sqlString)
[/code][/quote]
Do read the sections on prepared statements as they will help save you from all kinds of glitches

Yes, I’ll give it a try. Thanks.

What if there are multiple variables in the sql. both for the SELECT and the WHERE?

I have search string:

sql = "SELECT quads FROM exercises WHERE quads = '"+nameforedit.SafeSQL+"'"

this works fine. However the ‘quads’ is variable and I call it: exgroup as string

How do I put the quotes/single quotes around the variable exgroup to make this work?

sql = "SELECT exgroup FROM exercises WHERE exgroup = '"+nameforedit.SafeSQL+"'"
sql = "SELECT " + quads + " from exercises WHERE " + quads " = '" + nameforedit.SafeSQL + "'"

String concatenation can be tricky. Run through it a couple of times in the debugger until you get a string that looks right.

Thanks a lot Tim,