I'm having trouble executing any SQL query that has a WHERE clause in it. I have two years of experience with SQL, but I can't see where the problem is in my B4A code. I've searched the documentation and forums, and don't see any other reports of this basic difficulty.
In short, I can perform SELECT, INSERT, DROP, and CREATE TABLE queries with no problem (and probably other types of queries, but I haven't tried them yet in B4A) if I do not use a WHERE clause. However, when I use a WHERE clause, the typical error I get for the code below is "no such column: curruserid".
Here's the simplified code that illustrates my problem:
I have been able to get queries with WHERE clauses to work when I set the WHERE condition to an actual, existing value, such as "WHERE name = 'John'".
I guess I am missing something very basic, but I don't know what it is. I would be very grateful for any advice.
Thank you!
In short, I can perform SELECT, INSERT, DROP, and CREATE TABLE queries with no problem (and probably other types of queries, but I haven't tried them yet in B4A) if I do not use a WHERE clause. However, when I use a WHERE clause, the typical error I get for the code below is "no such column: curruserid".
Here's the simplified code that illustrates my problem:
B4X:
Sub Process_Globals
Dim SQL1 As SQL
Dim readqry As Cursor
Dim username(40) As String
Dim userid(40) As Int
Dim currname As String
Dim curruserid As Int
End Sub
Sub Globals
End Sub
Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
SQL1.Initialize(File.DirInternal, "testdelete.db", True)
End If
'drop query needed for repeated running of app because delete query doesn't work
SQL1.ExecNonQuery("DROP TABLE Users")
SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS Users(userid INTEGER PRIMARY KEY,name TEXT)")
curruserid=4
currname="John"
'insert query
SQL1.ExecNonQuery2("INSERT INTO Users Values (?,?)", Array As Object(curruserid, currname))
'select all query, before delete
readqry = SQL1.ExecQuery("SELECT userid, name FROM Users")
For i=1 To readqry.RowCount
readqry.Position = i-1
username(i)=readqry.GetString("name")
userid(i)=readqry.GetInt("userid")
Log("before delete: name = " & username(i) & " userid = " & userid(i))
Next
'delete query
SQL1.ExecNonQuery("DELETE FROM Users WHERE name='currname'")
'select query with where clause
readqry = SQL1.ExecQuery("SELECT userid, name FROM Users WHERE userid=curruserid")
For i=1 To readqry.RowCount
readqry.Position = i-1
username(i)=readqry.GetString("name")'
userid(i)=readqry.GetInt("userid")
Log("Select query with where clause: name = " & username(i) & " userid = " & userid(i))
Next
'select all query, after delete
readqry = SQL1.ExecQuery("SELECT userid, name FROM Users")
For i=1 To readqry.RowCount
readqry.Position = i-1
username(i)=readqry.GetString("name")
userid(i)=readqry.GetInt("userid")
Log("after delete: name = " & username(i) & " userid = " & userid(i))
Next
End Sub
Sub Activity_Resume
End Sub
Sub Activity_Pause (UserClosed As Boolean)
Activity.Finish
End SubI have been able to get queries with WHERE clauses to work when I set the WHERE condition to an actual, existing value, such as "WHERE name = 'John'".
I guess I am missing something very basic, but I don't know what it is. I would be very grateful for any advice.
Thank you!
Last edited: