Need clarification on how transactions work

Databases
1

I have a database with two tables, an “invoices” table and “invoice_line” table. When I update (or enter) data into them, if one fails, then they both have to fail. I know that I should be using a Transaction. I would appreciate if someone could tell me if the following (as an example) is correct:

db.SQLExecute("BEGIN TRANSACTION") If Not Invoice.EditMode Then For i = 0 to count -1 Dim row As New DatabaseRecord row.Column("invoice_number") = Str(invoiceNum) row.Column("product_quantity") = invoiceDetailsList.Cell(i,0) row.Column("product_desc") = invoiceDetailsList.Cell(i,1) row.Column("unit_price") = invoiceDetailsList.Cell(i,2) row.Column("discount") = invoiceDetailsList.Cell(i,3) row.Column("subTotal") = invoiceDetailsList.Cell(i,4) db.InsertRecord("invoice_line_items", row) If db.Error Then MsgBox(db.ErrorMessage) db.Rollback End If Next //the following invoice table entry was done when the invoice was first created, now we need to add the data sql = "UPDATE invoices SET grand_total = '" + GrandTotalLabel.Text + "', contact_person = " + Str(attentionPopup.ListIndex) + ", invoice_notes = '" + notesTextArea.Text + "' WHERE invoice_number = " + Str(invoiceNum) +"" db.SQLExecute(sql) If db.Error Then MsgBox(db.ErrorMessage) db.Rollback End If //Changed the data db.Commit Else sql = "UPDATE invoice_line_items SET product_quantity = " + invoiceDetailsList.Cell(i,0) + ", productDesc = '" + invoiceDetailsList.Cell(i,1) + "', unit_price = '" + invoiceDetailsList.Cell(i,2) + "', discount = '" + invoiceDetailsList.Cell(i,3) + "', subTotal = '" + invoiceDetailsList.Cell(i,4) + "' WHERE invoice_number = " + Str(invoiceNum) + "" count = invoiceDetailsList.ListCount For i = 0 to count -1 db.SQLExecute(sql) Next sql = "UPDATE invoices SET grand_total = '" + GrandTotalLabel.Text + "', contact_person = " + Str(attentionPopup.ListIndex) + ", invoice_notes = '" + notesTextArea.Text + "' WHERE invoice_number = " + Str(invoiceNum) +"" InvoiceDatabase.SQLExecute(sql) End If If db.Error Then MsgBox(db.ErrorMessage) db.Rollback End If //Changed the data db.Commit Me.Enabled = False Invoice.EditMode = False invoiceWindow.Show Self.Close

The main reason I set the sql string is so I can see in the debugger if it looks right. If this code IS OK, but there is a better way to handle it, then any help would be appreciated.

2

With database transactions, for each Begin there can be only one Commit or Rollback. Once the Commit or Rollback is issued, the database transaction is written or aborted. You’ll need to refactor your code accordingly.

In your case of writing an invoice header in one table and the line items in another table, wrap all of that activity with one Begin at the top of the method, like you already have, and one Commit or Rollback at the bottom.

3

For update, you need to create the sql string inside the loop, so you get fresh values each time.

4

Thanks Tim

I just ran it through the Debugger and noticed that :{