Just started to play around with Xojo and creating a simple web app with sqllite database. For some reason I am creating a data recordset and is set to nil.
I have created an mDB property of type SQLLiteDatabase and a mlsConnected property of type boolean as in the example.
I get no compilation errors and code seems to execute all the way through.
Here is my code. Can anyone see anything wrong? Appreciate any assistance.
Thanks again.
Jim
=============================
Dim dbFile As New FolderItem(“moneyactivity.sqlite”)
mDB = new SQLiteDatabase
mDB.DatabaseFile=dbFile
If mDB.CreateDatabaseFile Then
mlsConnected = True
Else
mlsConnected = False
End If
Dim sql As String
sql = “SELECT * FROM TillTaking”
Dim data as RecordSet
data = mDB.SQLSelect(sql) //----> After this executes, it does not return a recordset
If data <> Nil Then
While not data.EOF
lbTillTakings.AddRow(data.Field(“Date”).StringValue, data.Field(“TillNo”).StringValue, data.Field(“Cash”).StringValue, data.Field(“Visa”).StringValue, data.field(“PettyCash”).StringValue)
data.MoveNext
Wend
data.Close
End If
==============================================
Here are my Variables when reaching the end of this code.
data is nil
dbFile Exists is true so it is finding the database
It was the fact I had my code wrong. It appears that I was creating a blank database but obviously no tables. Hence it could not find the table specified in the SQL statement.
One problem I am having is how do I specify a database that is already in existence. I have already created my database with tables taht lives in my main project folder. But every time I run the application, it seems to be referencing a subdirectory of the main directory called Money Activity.debug directory.
Here is the piece of code:
Dim dbFile As FolderItem
dbFile = GetFolderItem(“moneyactivity.sqlite”)
mDB.DatabaseFile=dbFile //----> This is not finding my database file. It points to a subdirectory called Money Activity.debug where database does not exist.
The solution is to point to the database file where it exists. Either change your GetFolderItem to point to the actual location of the database or copy the database to the “Money Activity.debug” folder (using a Copy File Build Step).
If you want to leave the DB file where it is (in the main project folder), then you have to navigate to it:
Keep in mind that for shipping apps, you will not typically put the database file alongside the app itself. Most operating systems these days do not allow you to write to files in the App folder.
Apologies for not responding earlier. Was busy with other tasks. I have managed to work out pointing to the database file but still just a minor hiccup which I cannot figure out.
I open up a recordset but get a failure that it there is a NilObjectException. I am not certain that it is talking about the recordset here. However my variables show that a reforest exists. See images. https://www.dropbox.com/sh/3hrrrdrjjuhlibg/f9Kj2UGFYI
(copy/paste link if clicking on it does not work).
If I click on the rs object it shows that there is a field count of 15. I I further click into the Contents, it shows me some values, but initially there are all nil. But when I move my mouse cursor over the value fields, they fill up. I am not certain what is happening here and if this is what is causing me the error.
If I continue to run the program, the web page shows the following error. Am I missing something?
I think your field “Cash” doesn’t exist, at least not spelled that way. Try this and see what you get for dbField:
while not rs.EOF
dim dbField as DatabaseField = rs.Field( "Cash" )
if dbField is nil then
MsgBox ""It's nil"
exit
end if
lbTillTakings.AddRow dbField.StringValue
wend
I don’t recommend leaving it this way, only to use it for debugging purposes. (But I do recommend leaving the assignment to dbField in place. Breaking up your code like this makes debugging easier.)
If, as I suspect, dbField is nil, try this somewhere:
dim fields() as string
dim rs as RecordSet = MyDatabase.FieldSchema( "TillTaking" )
if rs <> nil then
while not rs.EOF
fields.Append rs.idxField( 1 ).StringValue
rs.MoveNext
wend
end if
// See what you get in fields
Examine the variable fields to see if you get what you expect to get. If “cash” is there, use it exactly as spelled in your other code, and mind the case.
(I’ve done this all off the top of my head, so forgive any bugs in this code.)
BTW, you don’t need the semicolons in the SQL statements if you only have one statement.
This in in the OPEN event of page & control in a web project ?
RaiseExceptionClass
RaiseNilObjectException
TillTakings.TillTakings.Populate%%o<TillTakings.TillTakings>
TillTakings.TillTakings.Event_Open%%o<TillTakings.TillTakings>
WebView.Event_Open%%o
WebControl.Event_Open%%o
Thanks for you suggestion and debugging tip. The field ‘Cash’ in the TillTakin DB does exist for sure and is in the correct format as well. I placed a break point in the following code and stepped through it line by line. I can confirm that the dbField is not nil as it does not enter the if block.
while not rs.EOF
dim dbField as DatabaseField = rs.Field( “Cash” )
if dbField is nil then
MsgBox (“It’s nil”)
exit
end if
lbTillTakings.AddRow dbField.StringValue –> Exception is raised here saying: NilObjectException
wend
When I click on the NilObjectException in the variables section, I have the following:
ErrorNumber 0
Message
Message does not show anything in the value column.
The strange thing is that if I continue/resume the code after the first exception error, it outputs the Cash value items in my list box i the browser along with the Stack error message dialog box.
Not sure if it is because it is an sqlite database. I don’t think that there should be issues. I might try an MySQL / PostgreSQL database.
Hi Norman. Yes I am trying to put data into a web listbox in the open event of the page.
When I get the NilObjectException the row that is highlighted in code is the lbTillTaking.AddRow. It has a bug icon next to it and when I hover over it is says NilObjectException.
Just tried your suggestion and i do get the same result. I commented out all reference code to the database stuff and just had the single line lbTillTakings.AddRow “123”
I am wandering if it is because of this reason. The page that has the open event has the following code:
If Not OpenDatabase then
MsgBox “Unable to open database”
else
Populate
End If
It definitely gets to the Populate function. The Populate function is a method in the web page that has the web listbox and is as follows:
Dim sql As String
Dim rs as RecordSet
sql = “SELECT * FROM TillTaking;”
rs = MyDatabase.SQLSelect(sql)
while not rs.EOF
dim dbField as DatabaseField = rs.Field( “Cash” )
if dbField is nil then
MsgBox (“It’s nil”)
exit
end if
lbTillTakings.AddRow.AddRow dbField.StringValue
rs.MoveNext
wend
It is as if the web page does not know anything about the web list box yet. Perhaps I should place this code in a different event handler. I’ll continue to experiment.
[quote=57138:@Jim Pandaz]
It is as if the web page does not know anything about the web list box yet. Perhaps I should place this code in a different event handler. I’ll continue to experiment.[/quote]
Don’t bother - you’ll just have problems
MOVE YOUR CODE TO THE SHOWN EVENT
The controls literally do NOT exist yet in the Open event of a WEB APP
Yes I just moved the code to the shown event. I actually had my code in the open event of the web page not the web listbox. It makes all sense now. There was no way the web page knew anything about my web list box yet .doh.
I tried also using the open event of the web list box and that works just as well.
Is there any best practice here. Should I place my code in the shown event or the open event of the list box? Either way, my code works as expected now.
Again thank you for your input to this. It has really helped my understand the events sequence better.
