What am I doing wrong?

Getting Started
1

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

2

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
3

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

4

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.

5

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.

6

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.

7

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.

8

Then it must be something like:

rowsFound = App.db.SelectSQL(“SELECT * FROM customers”)
if  (rowsFound.RowCount()=0)  Then Return
2 Likes
9

at least it is supported at 4 of 6 database classes.
https://documentation.xojo.com/api/databases/rowset.html#rowset-rowcount

10

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