Empty Recordset - code review needed

Databases
1

Ok,
I have a strange problem and am trying to narrow it down.

In my OS X app I have 2 properties in a module:
IncidentsRS (type recordset)
IncidentsDB (type SQLiteDatabase)

I have the following code which SHOULD return the database values for the selected listbox row in the main window, and then populate text fields in the edit window with those values:

[code]// DEFINE THE DATABASE PATH
Dim fi As FolderItem = SpecialFolder.ApplicationData.Child(“Incidents Data”).Child(“Incidents.db”)
IncidentsDB = new SQLiteDatabase
IncidentsDB.DatabaseFile = fi

// CHECK DATABASE CONNECTION
If IncidentsDB.Connect = True Then

// RETRIEVE VALUES FROM THE SELECTED LISTBOX ROW IN THE MAIN WINDOW
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+"’")

// NO RECORDSET FOUND, SO DISPLAY THE ERROR MSGBOX
if IncidentsRS = nil then
msgbox(“No record found.”)

// RECORDSET FOUND, SO POPULATE ALL FIELDS IN THE EDIT WINDOW 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[/code]

Does anyone have any ideas why the recordset vales returned seem to be blank?
I even tried a msgbox as below, but that displayed an empty msgbox:

MsgBox(IncidentsRS.field("Number").StringValue)

Could it be something to do with this segment - IRef is an INTEGER PRIMARY KKEY:

// RETRIEVE VALUES FROM THE SELECTED LISTBOX ROW IN THE MAIN WINDOW 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+"'")

Thank you all in advance.

2

Try:

WHERE IRef="+IRNumber)
4

You could also add an error check after IncidentsDB.SQLSelect.

If IncidentsDB.Error = True Then
msgbox IncidentsDB.ErrorMessage
end if
5

Albin - your first code segment made no difference.
Your second error check - also made no difference. I get no error messages at all - but empty values returned??

6

hmm, If it’s an integer type in the db it should not be surrounded by single quotes if I remember correctly :slight_smile:
A stranger problem might be a good thing…or not :stuck_out_tongue:

7

Could you please clarify - I’m not quite with you?

8

You said IRef was of type integer in the db.
An integer should not be surrounded by single quotes in the WHERE clause.
But you said that messed things up even more…strange.

Did you get an error message from the database?

9

Albin’s syntax form is correct since you dimmed IRNumber as a String … I use the same thing all over the place

10

Have you checked to see what’s in IRNumber?

11

I got no error message at all.
I removed the single quotes, and then got a syntax error near =???

Could someone please paste that line of code here, so that I can paste it in.

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

:slight_smile:

13

Thanks - but that first produced an error message stating there was a syntax error near =
After I close that error message - I get a msgbox saying No Record Found

14

Here’s an example of how I’m using that same syntactic form:

    Dim rsTemp As RecordSet = db.SQLSelect("SELECT * FROM caseModel WHERE (CaseName='" + currentModelName + "') AND (Revision='" + currentModelRevision + "')")   // caseID, model name/descriptors/templates used
    
    Dim rowID As String
    rowID = rsTemp.Field("CaseID").StringValue
    
    Dim sql As String
    
    sql = "DELETE from caseModel WHERE CaseID = " + rowID
    
    db.SQLExecute(sql)
    
    // NOTE: Since foreign key relationships have Delete rule set to "Cascade", deleting this parent entry will delete all child entries associated with its primary key ID (as a foreign key)
    
    If db.Error Then
      MsgBox("DELETE DB Error: " + db.ErrorMessage)
    Else
      db.Commit
    End If
15

This is the exact error message I first receive:

Followed by a No Record Found msgbox (as in my code below).

// NO RECORDSET FOUND, SO DISPLAY THE ERROR MSGBOX if IncidentsRS = nil then msgbox("No record found.")
Thanks.

16

Have you looked (e.g., MsgBox) at what’s in IRNumber yet … is it what you expect as the identifier (primary key value)

17

Yes - I used the following code, and got the syntax error, followed by the No Records Found msgbox:

msgbox(IncidentsRS.field("IRef").StringValue)
18

My code now looks as follows:

[code] // DEFINE THE DATABASE PATH
Dim fi As FolderItem = SpecialFolder.ApplicationData.Child(“IncidentReporter Data”).Child(“Incidents.db”)
IncidentsDB = new SQLiteDatabase
IncidentsDB.DatabaseFile = fi

// CHECK DATABASE CONNECTION
If IncidentsDB.Connect = True Then

// 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)

If IncidentsDB.Error = True Then
msgbox(IncidentsDB.ErrorMessage)
end if

// NO RECORDSET FOUND, SO DISPLAY THE ERROR MSGBOX
if IncidentsRS = nil then
msgbox(“No record found.”)
self.close

// RECORDSET FOUND, SO POPULATE ALL FIELDS WITH THE APPROPRIATE ENTRIES
else
msgbox(IncidentsRS.field(“IRef”).StringValue)
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[/code]

This gives me the following syntax error:

Followed by a msgbox stating No Record Found

19

OK … Maybe I’m just not seeing it, but where do you Dim the record set (IncidentsRS)?

20

In my OS X app I have 2 properties in a module:
IncidentsRS (type recordset)
IncidentsDB (type SQLiteDatabase)

21

That particular syntax error means:

Don’t know all the differences between SQLite and MS SQL Server (which is what I use most of the time), but as far as SQL Server goes, I don’t see where you are not providing a proper “IDENTIFIER” with your syntax.