I’m following Brad Rhine and Paul Lefebvre’s tutorial on Databases from their book “Introduction to Xojo Programming.” Although I’m not new to Xojo, I’ve never worked with Databases before and I’m at an impasse. When I run the project, I’m getting a “Database Exception” and “rs” is coming up as Nil. The code was copied and pasted directly from their book. I’m also including a screenshot of the debugger if that will help. Any assistance is greatly appreciated. Thanks!
Var sql As String
Var rs As RowSet
sql = "SELECT name, email"
sql = sql + "FROM addressbook"
If search.Text <> "" Then
sql = sql + "WHERE LOWER(name) LIKE LOWER('%" + _
SQLify(search.Text) +"%') "
sql = sql + "OR LOWER(email) LIKE LOWER('%" + _
SQLify(search.Text) +"%')"
End If
sql = sql + "ORDER BY name"
Try
rs = MyDatabase.SelectSQL(sql)
AddressBox.RemoveAllRows
While Not rs.AfterLastRow
AddressBox.AddRow(rs.Column("name").StringValue)
AddressBox.CellValueAt(AddressBox.LastAddedRowIndex, 1) = _
rs.Column("email").StringValue
rs.MoveToNextRow
Wend
Catch error As DatabaseException
MessageBox(error.Message)
End Try
Spaces are important and you’re missing some. For example there is no space between email & FROM.
Var rs As RowSet
sql = "SELECT name, email"
sql = sql + " FROM addressbook"
If search.Text <> "" Then
sql = sql + " WHERE LOWER(name) LIKE LOWER('%" + _
SQLify(search.Text) +"%') "
sql = sql + " OR LOWER(email) LIKE LOWER('%" + _
SQLify(search.Text) +"%')"
End If
sql = sql + " ORDER BY name"
Try
rs = MyDatabase.SelectSQL(sql)
AddressBox.RemoveAllRows
While Not rs.AfterLastRow
AddressBox.AddRow(rs.Column("name").StringValue)
AddressBox.CellValueAt(AddressBox.LastAddedRowIndex, 1) = _
rs.Column("email").StringValue
rs.MoveToNextRow
Wend
Catch error As DatabaseException
MessageBox(error.Message)
End Try
I’ve added spaces above where I think they are needed.
Thanks again, Rainer! I’ll definitely check out that editor. As I said, I’m new to Databases and until now, I’ve been using text files to store my data. That’s works fine for Desktop, but I’m creating an iOS app for the App Store and that method of storing data won’t work to well in that case. Is there a good resource you could recommend that will help me to get a better handle on SQL?
How about Valentina ( have a look at omega bundle) , or even Xcode, or the mbs plugin… If you stay with sqlite there are many on the web @Beatrix_Willius : whats your SQL Editor ?
I did try it out. When I went to “Apply Changes,” which appears to be a necessary step, it told me that this was only available as a purchase option. But I agree with you about the price. I looked into Navicat and discovered that it will set you back 64.99 a month, 649.99 a year, or 1,299.00 for a license (USD). 79.99 is definitely fair.
I never looked at the before this as I stated using Xojo/RS/RB before the book existed, but out of curiosity and just checked it out
In it when speaking about Select Case it says:
But while I don’t really know C, I thought that the switch switch statement will execute the code in every clause matching the condition, while Select Case exits after executing the code in first matching condition only… Seems like a big difference to me.
Given the influence of C, I assume other languages that have Switch would behave the same way as in C. Am I mistaken?
You can code it either way. If you do not want it to fall through to the next section of code, you add “break;” – if you leave off the “break;” (hopefully intentionally…) it will fall through. Which can be useful in some instances as well. But when I intentionally do it, I add a comment to that effect and why because more often than not you do want to stop it from falling through.
One thing to help with formatting SQL statements is to put them in a constant. You can put in spaces and returns as needed. Also, get a tool to view the database. The one from Valentina is excellent. You can then try out your queries there.
Many years ago I tried C (even bought the K&R book) … but hated the syntax, so I quickly stoped looking at it, but I do recall a few things .
In C I recalled that every condition is evaluated in a switch and if true the corresponding code execute by default UNLESS one puts a Break statement at the end of the condition code that evaluated to true (Similar in concept to Xojo Exit for loops) …
Which is why I found the statement in the Xojo book misleading
So by default every condition would be tested and executed if true, though one can optionally change that behavior with break statements. That is not true with Xojo Select Case and I think that is a BIG significant difference between the two statements.
