Recordset’s are Funny Things

We have a new developer working at BKeeney Software and he’s in the learning phase of Xojo. Not only is he new to Xojo but he doesn’t have a ton of experience doing software development in general. As someone who’s been using Xojo for a long time it is interesting to see how people struggle with the language and the IDE.

Today we had one of those “teaching moments” as he was beating his head up against the wall with this bit of code in a save method. This is a simple Invoice application saving the invoice line item to an SQLite database that’s on the local machine. See if you can spot the mistake.

dim rs as recordset =db.sqlselect( "Select * From tinvoicelineitem where invoicelineitem_ID = " + str(iInvoiceLineItem_ID) )
if db.error then
   msgbox CurrentMethodName + " db error: " + db.ErrorMessage
   break
   return false
end
rs.Field("InvoiceID").IntegerValue = iInvoiceID
rs.Field("salesTax_Flag").BooleanValue = bSalesTax_Flag
rs.Update

if db.error then
   msgbox CurrentMethodName + " db error: " + db.ErrorMessage
   break
   return false
end


Have you spotted the error yet? I don’t blame you. It’s easy to miss and it took me a few minutes of staring at it to find it. The code runs with zero code errors and zero database errors. The only thing that’s wrong is that it doesn’t save any line item data.

Okay, I’ll tell you. I forgot to add the rs.edit command at the top of the method. Without telling the recordset that you’re editing it, it will never generate the SQL to update the RecordSet.


dim rs as recordset =db.sqlselect( "Select * From tinvoicelineitem where invoicelineitem_ID = " + str(iInvoiceLineItem_ID) )
if db.error then
   msgbox CurrentMethodName + " db error: " + db.ErrorMessage
   break
   return false
end
rs.Edit // IMPORTANT!
rs.Field("InvoiceID").IntegerValue = iInvoiceID
rs.Field("salesTax_Flag").BooleanValue = bSalesTax_Flag
rs.Update

if db.error then
   msgbox CurrentMethodName + " db error: " + db.ErrorMessage
   break
   return false
end


I can argue that the lack of an error is bad. Sure, I didn’t code it right, but you’d think there would be some sort of error if an update was called without a starting edit. I’ve added <feedback://showreport?report_id=35134> to the Feedback system.

To be honest, we don’t do straight Xojo database calls much any more simply because we’re using ActiveRecord for most projects. AR eliminates most of the tedious database coding and let’s you deal with the data and the UI. When you call the Save in ActiveRecord you don’t need to care if it’s a new record or not. AR handles all the gory (and boring) details for you.

What say you my Xojo friends? Do you get bit by this type of thing when doing database coding?

2 thoughts on “Recordset’s are Funny Things

  1. I avoid this type of problem by having a “DatabaseServices” object which deals with the variations between the different databases I support; it has a set of standard methods to add/update records, so I only read from recordsets not edit with them. The object itself uses SQL execute to do the edits. Agree with your feedback report though for those using Recordsets.

Comments are closed.