Empty Recordset - code review needed

I am totally lost - my code seems to be correct, but it produces those 2 crappy errors :frowning:
The syntax error only occurred after using the line of code below which Albin recommended:

IncidentsRS = IncidentsDB.SQLSelect("SELECT IRef, Number, Type, Date, Involved, Dealing FROM Incidents WHERE IRef="+IRNumber)

Just out of curiosity, what do you get for the value of IRNumber putting the MsgBox statement immediately after where the IRNumber assignment is made, like:

Are you getting what you expected from the list box?

What happens if you try the same query in a SQLite db app?

Don - I get an empty MsgBox???

Albin - My app does use SQLite.

There’s your problem … need to figure out why … essentially, you’re using “” (blank string) as an identifier which will give you those exact same two errors

Try hard coding a value that you know exists (primary key) in for IRNumber (forget getting it from the list box) and see what happens.

So basically, the problem must be in these 2 lines of code?

// RETRIEVE VALUES FROM THE SELECTED LISTBOX ROW Dim IRNumber as String = MainWindow.IncidentsListBox.cell(MainWindow.IncidentsListBox.listindex,0) IncidentsRS = IncidentsDB.SQLSelect("SELECT IRef, Number, Type, Date, Involved, Dealing FROM Incidents WHERE IRef="+IRNumber)

I have just checked my database file and I have 3 entries - with IRef’s of:
1
2
3

Hard code one of those and see what shakes

I’m shaking at the moment! :slight_smile:

Just don’t stir ^^

Ok, I changed this:
// RETRIEVE VALUES FROM THE SELECTED LISTBOX ROW
Dim IRNumber as Integer = MainWindow.IncidentsListBox.cell(MainWindow.IncidentsListBox.ListIndex, 0)
IncidentsRS = IncidentsDB.SQLSelect(“SELECT IRef, Number, Type, Date, Involved, Dealing FROM Incidents WHERE IRef=”+str(IRNumber))

To This:
// RETRIEVE VALUES FROM THE SELECTED LISTBOX ROW
Dim IRNumber as Integer = 1
IncidentsRS = IncidentsDB.SQLSelect(“SELECT IRef, Number, Type, Date, Involved, Dealing FROM Incidents WHERE IRef=”+str(IRNumber))

And my fields get populated as expected.

Richard, does this work?

IncidentsRS = IncidentsDB.SQLSelect("SELECT * FROM Incidents")

If yes then does this work?

IncidentsRS = IncidentsDB.SQLSelect("SELECT * FROM Incidents WHERE IRef=1")

If yes then does this work?

IncidentsRS = IncidentsDB.SQLSelect("SELECT * FROM Incidents WHERE IRef="+IRNumber)

If that worked then check the column names you’re selecting in the original. Otherwise where did it stop working?

Wayne,
When I use your second option, and change the hard-coded values - it works as expected.
When I use your 3rd option - I get a syntax error.
This indicates that there is a problem with the listbox selection in the main window?

[quote=130318:@Don Lyttle]Just out of curiosity, what do you get for the value of IRNumber putting the MsgBox statement immediately after where the IRNumber assignment is made, like:

Dim IRNumber as String = MainWindow.IncidentsListBox.cell(MainWindow.IncidentsListBox.listindex,0)
MsgBox IRNumber

Are you getting what you expected from the list box?[/quote]

Stop thrashing and take a step back. The problem is that IRNumber is coming back blank from the listbox. There was nothing wrong with the SQLSelect statement. The problem is the contents of IRNumber, or more specifically the contents of the listbox. So look there.

What is the value of MainWindow.IncidentsListBox.listindex?
Does that row contain a value in column 0?

Tim,
I have spent so many hours looking at this problem, that I have now gone totally brain-dead!

ALL columns in my database contain values - I looked inside the database file.

I tried this code, and I got a syntax error, followed by an empty Msgbox

// RETRIEVE VALUES FROM THE SELECTED LISTBOX ROW Dim IRNumber as String = MainWindow.IncidentsListBox.cell(MainWindow.IncidentsListBox.ListIndex, 0) IncidentsRS = IncidentsDB.SQLSelect("SELECT IRef, Number, Type, Date, Involved, Dealing FROM Incidents WHERE IRef="+IRNumber) MsgBox(MainWindow.IncidentsListBox.cell(MainWindow.IncidentsListBox.ListIndex, 0))

I then tried just this, and also got an empty MsgBox:

MsgBox(MainWindow.IncidentsListBox.cell(MainWindow.IncidentsListBox.ListIndex, 0))

My code refuses to return a value from the selected row??

Ok,
I have just selected a listbox row and then pressed a button containing this code:

MsgBox(IncidentsListBox.cell(IncidentsListbox.ListIndex, 5)) MsgBox(IncidentsListBox.cell(IncidentsListbox.ListIndex, 4)) MsgBox(IncidentsListBox.cell(IncidentsListbox.ListIndex, 3)) MsgBox(IncidentsListBox.cell(IncidentsListbox.ListIndex, 2)) MsgBox(IncidentsListBox.cell(IncidentsListbox.ListIndex, 1)) MsgBox(IncidentsListBox.cell(IncidentsListbox.ListIndex, 0))

All message boxes display the correct column values EXCEPT the one which should display the value for column 0 - which displays an empty msgbox!!!

This seems to indicate that column 0 in my database is empty.
However, if you look at the database file screenshots below, you will clearly see that all of my columns have values.

Also, if I hard code the IRef value, as opposed to using the selected row - it works as expected - also confirming that column 0 has an integer value.
Therefore, the problem MUST be with the following code:

// RETRIEVE VALUES FROM THE SELECTED LISTBOX ROW Dim IRNumber as String = MainWindow.IncidentsListBox.cell(MainWindow.IncidentsListBox.ListIndex,0) IncidentsRS = IncidentsDB.SQLSelect("SELECT IRef, Number, Type, Date, Involved, Dealing FROM Incidents WHERE IRef="+IRNumber)

Could it be due to me Dimming IRNumber as a String, when the column IRef is an Integer?

Richard,

can you provide dropbox link to a sample db we can look at?

Be my guest :slight_smile:

link text