While not rs.eof code, one works the other fails

General
1

I have a problem that I cannot resolve. Below are to SELECT*FROM codes using the same database and the SAME Table. The upper code will show a list in the list box while the lower code will have a While not rs.eof problem.

I have spent days trying to find the error but to me the codes are the same. Does anyone know why one block of code will work while another will not? Programmers on the forum were kind enough to help me before so if someone would point out my mistake I’d appreciate it.

I’ve been looking for a work-around for this code but haven’t found any in the tutorials.

Thank you in advance for giving my problem your consideration. I believe this will be my last inquiry.

Jim Backus

THIS CODE WILL SHOW RECORDS AND DOES NOT HAVE A WHILE NOT RS.EOF ERROR
Dim sql as String
Dim SearchExpense as String

Dim rs as recordSet
SearchExpense = txtSearchExpense.text

sql =“SELECT * FROM Purchases WHERE ExpenseType = '” + SearchExpense + “'”
rs = App.SLivingDB.SQLSelect(sql)

PopulateSearchListBox SearchLB, rs

rs.close

THIS CODE HAS A WHILE NOT RS.EOF ERROR
Dim sql as String
Dim SearchMonth as String

Dim rs as recordSet
SearchMonth = txtSearchMonth.text

sql = "SELECT * FROM Purchases WHERE Month = "’ + SearchMonth + “'”
rs = App.SLivingDB.SQLSelect(sql)

PopulateSearchListBox SearchLB, rs
rs.close

BOTH ABOVE CODES USE THE SAME POPULATE code below
// Set the headings for the searchLB list box
SearchLB.HasHeading = True
SearchLB.Heading(0) = “ID”
SearchLB.Heading(1) = “Expense”
SearchLB.Heading(2) = “Payment”
SearchLB.Heading(3) = “Company”

SearchLB.Heading(4) = “Amount”
SearchLB.Heading(5) = “Note”
SearchLB.Heading(6) = “Month”
SearchLB.Heading(7) = “Year”

Dim I as integer

// Clear the listbox
lb.deleteAllRows

// Loop unitl reach end of recordSet
While not rs.eof
lb.addRow""

For i = 1 to rs.fieldCount
lb.cell(lb.lastIndex, i - 1 ) = rs.idxField(i).stringValue

Next

rs.moveNext
Wend

2
  1. What version of Xojo?
  2. What OS?
  3. What “rs.EOF problem” ?
  4. Have you stepped through the code with the debugger and checked that variables (such as rs) contain what you expect them to contain?
3

If you’re getting a NilObjectException error, it means your second sql statement is malformed. Examine it in the debugger. Also check App.SLivingDB for error indications.

4

Is “Month” defined as Varchar or Integer in your database?

5

In other words, While not rs.eof
will fail if rs is nil

wrap it in a test, or a try … catch statement, like so:

try
While not rs.eof
...
wend
catch
msgbox "Error of some kind"  
//you can add specific error types to test for if you wish
end try

But also after your select statement:

if   App.SLivingDB.Error Then
MsgBox("Error: " + App.SLivingDB.ErrorMessage)
else
PopulateSearchListBox SearchLB, rs
rs.close
End If

In your quoted code, the quotes are in the wrong order, but that cant be the actual code you are running - it would fail to compile.
Month = "’ +
ends with {double quote, single quote} , but should be the other way round, like ExpenseType = '” is

7

Good morning, to answer questions, I am using Windows 11 Pro, using latest version of Xojo. The month is varchar as I am using March not 3 or 03 to indicate month.

I will check sql statement as Tim suggests.

Thank you for responding to my inquiry. If I need to ask another question, do I use this link or begin a new topic?

8

Why won’t you use the iterator?
I see you used RecordSet in the latest version of Xojo. You could use the RowSet alternative.

I stopped iterating through a RowSet with rs.MoveNext a long time ago.
The only time I checked the EOF (or rather BefroreFirstRow / AfterLastRow) only to see if there are rows in my SelectSQL result. I changed it to just check the RowCount property.

I made a simple convenience function:

Function isValidRS( rs As RowSet, autoClose As Boolean = True ) As Boolean
  Var ok As Boolean

  ok = (rs <> Nil) And (rs.RowCount > 0)
  
  // If ok Then ok = Not rs.BeforeFirstRow
  // If ok Then ok = Not rs.AfterLastRow
  If Not ok And autoClose Then rs.Close

  Return ok
End Function

I call this function right after SelectSQL statement.

Function getInvoices( clientID As String ) As Dictionary()
  Var dInvoices(), dInvoice As Dictionary
  If clientID.Trim = "" Then Return dInvoices()

  Var rs As RowSet
  rs = db.SelectSQL("SELECT * FROM invoices WHERE LOWER(clientID) = ?", clientID.Trim.Lowercase)
  If Not isValidRS(rs) Then Return dInvoices // RowSet will be closed, and an empty Dictionary array will be returned.

  For Each dRow As DatabaseRow In rs
    // Processing the RowSetData
    // And store the result in a dictionary, which I could later convert to JSON data
  Next
  rs.close

  Return dInvoices()
End Function
9

If the new question is still about iterating through records in a database, you could continue here.

If the new question is about something else… even if it is about databases… you should start a new topic.
Just try to see if the title of this current topic is still relevant for your new question :slight_smile:

10

The code now works. Tim indicated that my sql statement was written wrong. It in fact was and with the change the code works. I’d like you all to know that I did appreciate your giving the time to review my problem. Learned quite a bit from your responses. Thank you.

2 Likes
11

I have resolved this problem. Thank you.

1 Like
12

Your immediate problem, yes. But your original code uses API 1 and is at risk of SQL injection problems. Changing to use API 2 methods for your database code will avoid that, such as this

 rs = db.SelectSQL("SELECT * FROM invoices WHERE LOWER(clientID) = ?", clientID.Trim.Lowercase)
``'

as posted above by Edwin.
2 Likes
13

Thank you, Tim

14

Thanks for pointing that out, Tim!
But wasn’t there something like PreparedSQLStatement in API 1? :thinking:

Of course the API 2 way is a lot easier.

15

Yes, at the expense of badly cluttering up one’s code. My app has nearly 900 SelectSQL and ExecuteSQL calls - can you imagine adding prepared statement stuff to all of those? Once I realised that avoiding SQL injection was something to worry about, what I did was modify the wrappers for SelectSQL and ExecuteSQL that I already have (which handle errror reporting) to do statement prep for arguments in a similar way to what API2 actually does now, although I only handled strings. So I was mighty pleased when the API2 calls actually appeared.

You can still do prepared statements in API2, just as before, but it seems to me the only time it makes sense to do it that way is if one is actually reusing a prepared statement a really large number of times. You can always do timings to determine whether that is worth it.

1 Like