SQLite error using SelectSQL

I have a database with 11 columns. I am trying to figure out how to retrieve the first record in the database and populate 10 TextFields with the data. The first column of the database is “record”, the second is “name” and the 3rd is “meaning”. I want to populate TextField1 with the data in the “name” column and TextField2 with the data in the “meaning” column.

I have been looking at different examples of getting data from a database and so far all I have is the following, which may well be incorrect…

Dim dbFile As FolderItem = GetFolderItem(“dinobase.sqlite”)

Dim db As New SQLiteDatabase

db.DatabaseFile = dbFile

If db.Connect Then



MsgBox("Error: " + db.ErrorMessage)

End If

Var sql As RowSet

sql = db.SelectSQL(“SELECT * FROM dino WHERE record = 1”)

The app does connect to the database but I get an error on the line sql = db.SelectSQL(“SELECT * FROM dino WHERE record = 1”)

The error says: MainWindow.Open, line 10

This item does not exist

sql = db.SelectSQL(“SELECT * FROM dino WHERE record = 1”)

It highlights SELECT * in yellow.

I can’t figure out why it is flagging this error.

Any suggestions please?

You’re using straight quotes and not curly quotes in your code right?

This is not a good idea; use instead sqlRS for example, or anything that refect your Var is a RecordSet.

Probably not the source of your problem…

I am using curly quotes I believe. In this post the quotes look like straight quotes but in the xojo IDE they are curly quotes.

Here is an image:

The curly quotes are the issue change them to straight quotes & you should be right.

1 Like

@Wayne Golding Thanks Wayne, I changed them to straight quotes and I do not get that error any more however, it is flaging a different error. That line has the little red bug on it and it shows me the following:

Name | Value
db | SQLiteDatabase
dbFile | Nil
Exception |DatabaseException
self | MainWindow.MainWindow
sql | Nil

It says that it has connected to the database so I’m not sure why it doesn’t seem to find the data.

sql = Nil usually means there’s an issue with the sql select statement. Does the dino table exist? Does it have a column record?

This should only work if you have a column named record but guessing from your error that you do not.

SQLite normally has a hidden column rowid but I believe that does not necessarily start at 1 if you have deleted records. It should be an increasing number though.

And it has row_number(), but that counts row in the result set. Not the original table.

A couple of choices that I suspect will work:

* sql = db.SelectSQL("SELECT * FROM dino ORDER BY rowid LIMIT 1")
* sql = db.SelectSQL("SELECT * FROM dino WHERE row_number() = 1 ORDER BY rowid")

I’d suggest using LIMIT 1 instead of row_number(). Then if you want to later get a row other than the first, add the OFFSET xx clause. The OFFSET is the number of rows to skip at the beginning of the result set. The LIMIT is the number of rows to return in the result set.

So this gets the 5th record (i.e., it skips over 4 record):

sql = db.SelectSQL("SELECT * FROM dino ORDER BY rowid LIMIT 1 OFFSET 4")

Edit: Changed a few instances of smart/curly quotes to standard quotes and wrapped in code tags to make more readable.

@Wayne Golding Yes, the dino table does exist and the first column in the database is “record”.

On my hard disk I have this path: Xojo Projects\PrehistoricLife\dinobase.db The PrehistoricLife.xojo_binary_project is in the PrehistoricLife folder with the database.

The columns in the table “dino” are:
record, name, meaning, pronounce, period, group, size, lived, diet, fossils, factfile

The column “record” is INT and the rest are CHAR.
I don’t know why it can connect to the database but not find the “dino” table.

Ahh, you have the wrong path here, as evidenced your dbFile object is nil

   `Dim dbFile as FolderItem = GetFolderItem("\Xojo Projects\PrehistoricLife\dinobase.db")`

may work. But only on your machine. Is this an application you will distribute? And if so, are you intending to include a copy of the database with the app’s resources?

There are various ways to skin this cat. But as coded, you are not finding the file at the specified path. So the SQL select has to fail.

@Douglas_Handy I tried all 3 of your suggestions and I’m still getting the same error. So dbFile is Nil and sql is Nil. If dbFile is Nil, it seems to me that it is not finding the database but if that were the case, how is it connecting?

It isn’t connecting. First you need to solve the path to the database.

John, @Douglas_Handy is correct. I don’t know why you’re getting a “Connected!” message but the code doesn’t seem to allow that. Can I suggest that you move the database to your documents folder and access it using

Dim dbFile As FolderItem = SpecialFolder.Documents.Child("dinobase.sqlite")

1 Like

@Wayne_Golding My mistake… the database file is actually dinobase.sqlite and not dinobase.db. That was an error in my last post. I tried using ```
`Dim dbFile as FolderItem = GetFolderItem("\Xojo Projects\PrehistoricLife\dinobase.sqlite")
but got the same error.

@Douglas_Handy When I run the code I get the messageBox saying “Connected” the when I click the “OK” button I get the error. I may distribute it and the database would be included because all of the data on each prehistoric animal is in the database. There are 115 records.

I suggest you look at this blog article.

1 Like

@Douglas_Handy I looked at the blog but I can’t add a Build Step because it requires a license to do so. I am using the free version to test xojo before deciding if I will purchase it. So far, while it is much easier and faster to design a UI in xojo, I find it much easier to code and work with databases in Purebasic, which is what I was using prior to checking out xojo. I am using the database from my PrehistoricLife app that I wrote in Purebasic. I was trying to duplicate that app as a way of testing xojo.

Can you share your xojo code and a sample db with a link to dropbox? I think it will be easier for people to help by reviewing the code.

I don’t have a dropbox account. You can download it here.

A couple of comments about your database schema, you don’t need the [ and ] around the column names, and SQLite ignores the character counts. So this would do:

create table dino (record integer, name text, meaning text, ...

Also after any statement such as:

Dim dbFile As FolderItem = GetFolderItem("dinobase.sqlite")

you should put:

if  (dbfile.Exists=False)  Then
  MsgBox ("Could not find database")
  Quit ()
End if

That way you know at what stage you have a problem.