I have been getting to know Xojo for the past few days and really love what I see so far. Unfortunately, I’ve hit an odd roadblock with the following code:
If Not IsConnected Then
MsgBox("Database not found.")
Return
End If
Dim ps As SQLitePreparedStatement = DB.Prepare("SELECT * FROM Login WHERE Name = ? AND ID >= ?")
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
Dim rs As SQLiteRecordSet = ps.SQLSelect(txtName.Text, txtUserID.Text)
If rs <> Nil Then
While Not rs.EOF
CurrentUser = rs.Field("UserID").TextValue
MsgBox("UserID: " + rs.Field("UserID").TextValue)
rs.MoveNext
Wend
Else
If db.Error Then MsgBox(db.ErrorMessage)
End If
When I go to run the app with the above code, I get a “Can’t find a type with this name” error pointing to SQLitePreparedStatement as well as a “Type “SQLiteDatabase” has no member named “Prepare”” error below where I try to prepare the statement. I followed the code examples in the docs but have not had any luck getting them to work.
Been really banging my head against the wall on this one, so, any help would be greatly appreciated…
Your prepared statement is using field names “Name” and “ID”, but you’re referencing rs.Field(“UserID”). in your code. Try using rs.field(“ID”). instead.
@Norman Palardy: DB is a constant I have declared as SQLiteDatabase @Tanner Lee : Thanks for noticing that, but it still did not fix my previous errors unfortunately
I even tried the following with the same results:
Dim ps As SQLitePreparedStatement
ps = DB.Prepare("SELECT * FROM Login WHERE Name = ? AND ID >= ?")
Go back and make sure your DB property really is of type SQLiteDatabase… As a matter of fact, select it in the navigator, copy it and paste it here. I’d really like to see how it’s declared.
He appears to be using the new framework, based on sqliterecordset. The new framework documentation doesn’t list a Prepare method, but the example code does. Is that an error in the docs?
Greg, below is the declaration for the DB property…
DB As SQLiteDatabase
Tim, yes, I switched from RecordSet (as per the docs) to SQLiteRecordSet due to a compile error.
Greg, I had no idea that I was mixing up frameworks… I assume I am mixing code from the REALBasic days with today’s Xojo code? The docs are confusing the crap out of me…
[quote=240242:@Norman Palardy]A constant ???
Or a property
That makes an enormous difference[/quote]
Sorry, Norman. I meant to write Property, not constant. DB is a property of type SQLiteDatabase that I had defined in a module with Global scope.
Ok, gents… I did a bit more digging around and eventually came across this thread, where @Norman Palardy’s reply in that thread gave me the fix I needed.
Below is the working code:
If Not DB.Connect Then
MsgBox("Database not found.")
Return
End If
Dim sql As Text = "SELECT * FROM Login WHERE Name = ?1 AND ID = ?2"
Try
Dim rs As SQLiteRecordSet = DB.SQLSelect(sql, txtName.Text, txtUserID.Text)
If rs <> Nil Then
While Not rs.EOF
CurrentUser = rs.Field("ID").TextValue
MsgBox("UserID: " + rs.Field("ID").TextValue)
rs.MoveNext
Wend
Else
MsgBox("No records found.")
Return
End If
Catch e As SQLiteException
Dim err As Text = e.Reason
MsgBox("Error: " + err)
End Try
Getting the above to work, however, did not get me any closer to figuring out how to use SQLitePreparedStatement in an IOS project…
Thanks for all of your help so far, guys! I’ll get there, slowly but surely…
I read through the entire thread I had mentioned in my last message and lo and behold: @Norman Palardy’s post (the last of the thread) says that every SQLExecute in the new framework is via a prepared statement that doesn’t have to be created or have bound values/types declared beforehand.
Wish I had seen that thread sooner :-(… Would have wasted a lot less of everyone’s time!
Anyway, thanks again for all of the patience and help! Xojo’s dev community is truly awesome!