Empty Recordset - code review needed

sqlite db looks fine.

lets baby step this.

[code]// RETRIEVE VALUES FROM THE SELECTED LISTBOX ROW
Dim Step01 as String = MainWindow.IncidentsListBox.cell(MainWindow.IncidentsListBox.ListIndex,0)
Dim Step02 as String = "SELECT * FROM Incidents WHERE IRef = " + Step01
IncidentsRS = IncidentsDB.SQLSelect(Step02)

break
[/code]

is Step01 populated with correct result?
is Step02 populated with select statement with Step01 added?
does IncidentsRS return recordset?

Can you post the code that populates your listbox.

Rich Hatfield,
I tried your code above and the resulting screenshot is as below:

Which once again reinforces what Tim Hare and I agreed on - that there is a problem with retrieving the actual value from the database.

Wayne Golding,
Here is the code which populates the Listbox (without any problems):

// NO PROBLEMS WITH THE DATABASE, SO POPULATE THE LISTBOX WITH THE RECORDSET RESULTS else while not IncidentsRS.eof MainWindow.IncidentsListbox.addRow MainWindow.IncidentsListbox.Cell(MainWindow.IncidentsListbox.LastIndex, 1) = IncidentsRS.Field("Number").StringValue MainWindow.IncidentsListbox.Cell(MainWindow.IncidentsListbox.LastIndex, 2) = IncidentsRS.Field("Type").StringValue MainWindow.IncidentsListbox.Cell(MainWindow.IncidentsListbox.LastIndex, 3) = IncidentsRS.Field("Date").StringValue MainWindow.IncidentsListbox.Cell(MainWindow.IncidentsListbox.LastIndex, 4) = IncidentsRS.Field("Involved").StringValue MainWindow.IncidentsListbox.Cell(MainWindow.IncidentsListbox.LastIndex, 5) = IncidentsRS.Field("Dealing").StringValue MainWindow.IncidentsListbox.RowTag(MainWindow.IncidentsListbox.LastIndex) = IncidentsRS.Field("IRef").StringValue IncidentsRS.moveNext wend

Just for the heck of it…have you tried using a PreparedStatement?
It’s friday afternoon and my brain has officially shut down :stuck_out_tongue:

Ok,
I have changed my code to use a prepared statement (my preferred way).
Here is my complete code:

[code]// RETURN THE DETAILS FROM THE INCIDENTS RECORDSET
Dim SelectedIRef As String = MainWindow.IncidentsListBox.cell(MainWindow.IncidentsListBox.ListIndex,0)
Dim ps As SQLitePreparedStatement = IncidentsDB.Prepare(“SELECT * FROM Incidents WHERE IRef LIKE ?;”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, SelectedIRef)
IncidentsRS = ps.SQLSelect

// DATABASE ERROR, SO DISPLAY THE ERROR MSGBOX AND THEN CLOSE THE WINDOW
If IncidentsDB.Error Then
MsgBox("Error: " + Str(IncidentsDB.ErrorCode) + " - " + IncidentsDB.ErrorMessage)
self.close

else

// NO RECORD FOUND, SO DISPLAY THE ERROR MSGBOX AND THEN CLOSE THE WINDOW
if IncidentsRS = nil then
msgbox(“No records found!”)
self.close

// RECORDSET FOUND, SO POPULATE ALL FIELDS WITH THE APPROPRIATE ENTRIES
else
Win_Edit.NumberTextField.text = IncidentsRS.field(“Number”).StringValue
Win_Edit.TypeTextField.text = IncidentsRS.field(“Type”).StringValue
Win_Edit.DateTextField.text = IncidentsRS.field(“Date”).StringValue
Win_Edit.InvolvedTextField.text = IncidentsRS.field(“Involved”).StringValue
Win_Edit.DealingTextField.text = IncidentsRS.field(“Dealing”).StringValue

end if

End if[/code]

Once again - I changed the first 2 lines of code regarding the column number and the where clause, and the correct values were returned for ALL of them.
It is ONLY column 0 which my statement fails on.

Why so many lines to add one row? Normally an add row looks like this:

MainWindow.IncidentsListbox.addRow("", IncidentsRS.Field(“Number”).StringValue, IncidentsRS.Field(“Type”).StringValue, IncidentsRS.Field(“Date”).StringValue, IncidentsRS.Field(“Involved”).StringValue, IncidentsRS.Field(“Dealing”).StringValue)

Horst - I personally find it easier to look at and understand :slight_smile:

Alternatively - does anyone have TeamViewer and a spare 5 minutes?
Thanks.

Try changing this:

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

To this:

ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)

No change :frowning:
I am pretty sure it has something to do with the data type - as all of my other columns are strings, and they work perfectly.
Column 0 is the only integer column, and that is the one which fails to return a value.

Do you have this line in the change event of the ListBox?
Dim IRNumber as String = MainWindow.IncidentsListBox.cell(MainWindow.IncidentsListBox.listindex,0)

Better you define the IRNumber as a shared property in the main window and use this in the change event of your listbox.

IRNumber = me.cell(me.listindex,0)
if debugBuild then
system.log(system.loglevelinformation, "IRNumber = " + IRNumber)
end if

So with every click on you listbox you should see the value for IRNumber of the selected line in message area of Xojo.

Horst - I defined SelectedIRef as a shared property in my module, but when I view the result in the system log area of Xojo it says: SelectedIRef =

Even that is returning an empty value!
If I change the column number in your code - it displays the correct values for all the other columns.
Whatever I try - it continually returns nothing for column 0???

It seems extremely strange that as soon as I select a listbox row, all column values get returned, except for column 0???

Sorry If I’m missing the obvious…as I said, my brain…
Isn’t Iref stored in the RowTAG and not CELL 0?

MainWindow.IncidentsListbox.RowTag(MainWindow.IncidentsListbox.LastIndex) = IncidentsRS.Field("IRef").StringValue

So this line should change to:

Dim SelectedIRef As String = MainWindow.IncidentsListBox.RowTag(MainWindow.IncidentsListBox.ListIndex)

? :slight_smile:

Even this shows the correct values for all columns except 0:

SelectedIRef = me.cell(me.listindex,0) SelectedB = me.cell(me.listindex,1) SelectedC = me.cell(me.listindex,2) if debugBuild then system.log(system.loglevelinformation, "SelectedIRef = " + SelectedIRef) system.log(system.loglevelinformation, "SelectedIRef1 = " + SelectedB) system.log(system.loglevelinformation, "SelectedIRef2 = " + SelectedC) end if

I have had problems like this with ODBC connections to Jet databases and with mysql.
It could be that what you are writing to your listbox is an integer and it has no stringvalue… try =IncidentsRS.Field(“Number”).GetString instead as this will convert the integer from your recordset into a string to write into the listbox. This would be the same as =str(IncidentsRS.Field(“Number”).value) which I have also had problems with.

Albin - I see what you mean but that has thrown an extra dilemma:
The line of code below copies the IRef into the RowTag - therefore, IRef definitely has a value:

MainWindow.IncidentsListbox.RowTag(MainWindow.IncidentsListbox.LastIndex) = IncidentsRS.Field("IRef").StringValue

I have also just looked at my database file’s contents again and noticed that the IRef column is not set to auto increment, so I am not sure how it is receiving a value?

Even when I save a new entry to my database - the line of code does not insert a value for IRef???

Dim ps As SQLitePreparedStatement = IncidentsDB.Prepare("INSERT INTO Incidents (Number, Type, Date, Involved, Dealing) VALUES (?, ?, ?, ?, ?);"

Therefore - where is my IRef column getting it’s value from - AND EVEN STRANGER - if it isn’t getting a value, why does it have a value in the actual database file???

So just to clarify:

This code writes new entries to the database:

[code]// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = IncidentsDB.Prepare(“INSERT INTO Incidents (Number, Type, Date, Involved, Dealing) VALUES (?, ?, ?, ?, ?);”)

// BIND THE VALUES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, NumberTextField.text)
ps.Bind(1, TypeTextField.text)
ps.Bind(2, DateTextField.text)
ps.Bind(3, InvolvedTextField.text)
ps.Bind(4, DealingTextField.text)
ps.SQLExecute[/code]

This code updates / populates the listbox:

while not IncidentsRS.eof MainWindow.IncidentsListbox.addRow MainWindow.IncidentsListbox.Cell(MainWindow.IncidentsListbox.LastIndex, 1) = IncidentsRS.Field("Number").StringValue MainWindow.IncidentsListbox.Cell(MainWindow.IncidentsListbox.LastIndex, 2) = IncidentsRS.Field("Type").StringValue MainWindow.IncidentsListbox.Cell(MainWindow.IncidentsListbox.LastIndex, 3) = IncidentsRS.Field("Date").StringValue MainWindow.IncidentsListbox.Cell(MainWindow.IncidentsListbox.LastIndex, 4) = IncidentsRS.Field("Involved").StringValue MainWindow.IncidentsListbox.Cell(MainWindow.IncidentsListbox.LastIndex, 5) = IncidentsRS.Field("Dealing").StringValue MainWindow.IncidentsListbox.RowTag(MainWindow.IncidentsListbox.LastIndex) = IncidentsRS.Field("IRef").StringValue IncidentsRS.moveNext wend

And this code TRIES to populate the edit window with the values from the selected listbox row:

// RETURN THE DETAILS FROM THE INCIDENTS RECORDSET Dim SelectedIRef As String = MainWindow.IncidentsListBox.cell(MainWindow.IncidentsListBox.ListIndex,0) Dim ps As SQLitePreparedStatement = IncidentsDB.Prepare("SELECT * FROM Incidents WHERE IRef LIKE ?;") ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) ps.Bind(0, SelectedIRef) IncidentsRS = ps.SQLSelect

THEREFORE - where on earth is column 0 getting it’s value from???
The database file clearly shows:
RowID = 1 and IRef = 1 (for the first database entry)
RowID = 2 and IRef = 2 (for the second database entry)

John - what line of my code above do you recommend changing?

Richard… I don’t see where your putting any value in the first column of the listbox. Maybe I’m overlooking something.

I see where you’re putting the IRef field in the row tag.

Have tried changing your code to:

Dim SelectedIRef As String = MainWindow.IncidentsListBox.RowTag(MainWindow.IncidentsListBox.ListIndex)

Johnny - This is where I am confused - I thought column 0 was set to auto-increment - but I have now noticed it isn’t.
Therefore, I do not seem to be writing any value into the IRef column (column0), but it is getting a value from somewhere, otherwise the RowTag would not be getting a value either?

The IRef column (Column 0) is set to INTEGER PRIMARY KEY - so I presume that is automatically getting a value (even though it is not set to auto-increment)??

Or am I completely wrong?