What am I doing wrong?

Hi all.

I’m pulling my hair out here (and I don’t have much left). Running the following code in a LostFocus event and it will only perform the try/Catch in the if/else where rowsFound <> Nil. If I swap the = and <> signs around, it just performs as if the rowsFound are not nil, even when there are no rows. But, if I remove the if/else all together, it throws a NilObjectException (as it should, there are no rows).

Anybody???

If Me.Value <> “” Then
Var customerID As String = App.CustID
Var Content As String = Me.Value
Var rowsFound As RowSet

Try
rowsFound = App.db.SelectSQL(“SELECT * FROM customers”)

Catch error As DatabaseException
MessageBox(“Database error.” +EndOfLine+EndOfLine+ “Please contact support, quoting error number PP1LF28.”)
End Try

If rowsFound <> Nil Then

Try
  
  Var ps As SQLitePreparedStatement
  ps = SQLitePreparedStatement(App.db.Prepare("UPDATE customers SET content = ?, mod_date = ? WHERE customerID = ?;"))
  ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
  
  
  ps.ExecuteSQL(Content, Today, customerID)
  
Catch error As DatabaseException
  MessageBox("Error writing to the database" +EndOfLine+ "Couldn't write " +ppContent+ " as expected")
End Try

Elseif rowsFound = Nil Then

Try
  
  Var ps As SQLitePreparedStatement
  ps = SQLitePreparedStatement(App.db.Prepare("INSERT INTO customers (customername, customer, content, mod_date) 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.ExecuteSQL(customerVal, customerID, Content, Stage,)
  
Catch error As DatabaseException
  MessageBox("Can't update database." +EndOfLine+EndOfLine+ "Please contact support, quoting error number PP1LF54.")
  
End Try

End If

End If

Hi Shane,

Not fully understanding here what is being reported as a fault (I’m still looking at it), but do remember that a rowset can be not-nil even if there are no rows. A nil will be returned if the SelectSQL fails (such as if the SQL statement is bad and raises a database exception), but will return an empty rowset (which does not raise an exception) if the SQL is good but happens to return no rows.

A typical pattern might therefore be:

// Check for valid rowset
if rowset <> nil then
    // check for available records
    if not rowset.afterlastrow then 
            // Do something with records
    end if
end if

i think rowsFound = App.db.SelectSQL(“SELECT * FROM customers”)
should always return a object but if there are no rows you have a property AfterLastRow

you could also use the values/arguments instead of extra prepare statement
.SelectSQL(SQLStatement as String [,ParamArray values() as Variant]) As RowSet
.ExecuteSQL(SQLStatement as String [,ParamArray values() as Variant])

output also the Exception Message property.

also check that App.db is a valid database object

Thanks Craig and Marcus. I figured the problem was that what an empty rowset returned was not defined by 0 or Nil. Trawled the docs to no real enlightenment. Awesome.

So, for anyone else looking for an answer to the same question, the code I went with in the end was to take the RowCount of rowsFound and base the if/else on a row count of if = 0 (Insert) else (update).

Thanks again Craig and Marcus. Confirming for me that a RowSet can be Not Nil whilst the number of rows in it are zero was a huge help.

Or you can do such as:

rowsFound = App.db.SelectSQL(“SELECT * FROM customers”)

while  (rowsFound.AfterLastRow=false)

  // handle this row here

  rowsFound.MoveToNextRow ()
      
wend

which will work whether your rowset has any rows or not.

Thanks Tim but the intention here is to simply establish if there are any rows in the set. If yes, do something, else if not, bail.

Then it must be something like:

rowsFound = App.db.SelectSQL(“SELECT * FROM customers”)
if  (rowsFound.RowCount()=0)  Then Return
1 Like

at least it is supported at 4 of 6 database classes.
https://docs.xojo.com/RowSet.RowCount

Correct. My app is based on SQLite databases and RowCount was the solution I went with.