I’ve already created a few projects using sqlite database to fill a listbox. But this time, there is something wrong as I get a NilObjectException when I run the project, and try to fill the LB with data.
This is, like the previous ones, a Xojo Desktop project, with the database on my machine, not on a server
I connect to the database in the app.open event, & that works using this code:
var db as new SQLiteDatabase
db.DatabaseFile = new FolderItem(“HPC Mk 2.db”)
try
db.Connect
messagebox(“Connected to DB” ) ’ Temporary for checking connection ok
Catch e as DatabaseException
MessageBox e.Message
end try
In the LB.open event I use this:
var sqltext as string = “SELECT Composer, Title from Titles inner join Composers on Titles.CompID = Composers.ComposerID ;”
var rs as RowSet = app.db.SelectSQL(sqltext)
The NilObjectException comes on the last line above, & I’m wondering if anyone has an idea as to where I should look, and what I should look for.
I don’t think the problem is with the SQLite query string, as it works when I use it in DB Browser working with the database
Thanks for your help!
It looks like your db variable is out of scope. In App.Open you’re using:
var db as new SQLiteDatabase
When you should use:
db = new SQLiteDatabase
The way it’s currently written, the db variable from App.Open is disposed of at the end of the event, so the App.db property will always be Nil.
1 Like
Using Title and Titles is subject to error(s) IMHO.
I suggest using either a Prefix or a Suffix like
Tbl_Titles or Titles_TBL (if this is the name of a TABLE).
Also, uppercase SQL commands, so at debug time, you know what is what in a SQLCommand string.
1 Like
Thanks Anthony - problem solved!
I might have guessed that Scope would have something to do with it, as I’ve had trouble with that in the past. I didn’t mention in the post that i have DB in the App.Properties, and it doesn’t work without that.
And I don’t fully understand how I can start using DB as a variable in the app.open event without declaring it using var. But perhaps it isn’t a variable. More reading required!
The NEW keyword and when to use it is also a bit puzzling, but I obviously need to do some more reading about that as well
Thanks Emile. I’m glad to say the “Titles” does not stop my particular project working - see reply to Anthony. But I’ll be conscious of a possible problem with Titles in future!
if you “declare” the variable as a property in app, it stays in scope during the whole lifecycle of your app. The issue is, that you can still declare a variable with the same name as this property in your code, but then that one only lives as long as the method / event you are declaring it is alive.
If you are declaring it as a property you are basically only saying what type that property will have, means your property db will contain an SQLite database, but you don’t yet have created an instance. That’s what you are doing with the NEW statement. You don’t need the “Var” declaration though, as you have defined db already as a property. I hope this makes sense.
in short:
Var is used to create a variable
New is used to create an instance
You don’t need Var if you already created a property in the IDE
*Properties are useful to the “extend” the lifetime of your property"
And yes, you need to read and try a bit more to get into this, but you will learn this over time.