how to commit

[iqueen]

My code as follow :

Dim conn As OleDb.OleDbConnection = New OleDb.OleDbConnection(connStr)
Try
conn.Open()
Catch e As OleDb.OleDbException
m_addStatus(m_exception(e), "error", -1)
End Try
Dim da As System.Data.OleDb.OleDbDataAdapter
Dim ds As DataSet
ds = Nothing
grd.DataSource = Nothing
ds = New DataSet("ds")
Try
da = New OleDb.OleDbDataAdapter(aStmt, conn)
da.Fill(ds, "result")
If ds.Tables(0).Columns.Count > 0 Then
grd.SetDataBinding(ds, "result")
Else
grd.DataSource = Nothing
End If
conn.Close()
Catch ex As OleDb.OleDbException
m_addStatus(m_exception(ex), "error", -1)
End Try

how to commit/rollback?
thanks.

[RichardAtherton]

Yes I struggled with this as well and it's not easy or obvious, firstly beleive it or not I create a seperate connection for every dataset I want to update!!!

I found that if I didn't I caught the exception message that there was already an exisitng pending local transaction.

and this is the code I came upwith:

' commit changes to Database

Dim Cb2 As SqlCommandBuilder = New SqlCommandBuilder(da2)
da2.InsertCommand = Cb2.GetInsertCommand
da2.UpdateCommand = Cb2.GetUpdateCommand
da2.DeleteCommand = Cb2.GetDeleteCommand
Dim trn2 As SqlTransaction = Cn2.BeginTransaction()
da2.InsertCommand.Transaction = trn2
da2.UpdateCommand.Transaction = trn2
da2.DeleteCommand.Transaction = trn2

Try
da2.Update(ds2, "Tenancies")
Catch ex As Exception
DbError("Tenancies")
End Try


Dim Cb4 As SqlCommandBuilder = New SqlCommandBuilder(da4)
da4.InsertCommand = Cb4.GetInsertCommand
da4.UpdateCommand = Cb4.GetUpdateCommand
da4.DeleteCommand = Cb4.GetDeleteCommand
Dim trn4 As SqlTransaction = Cn4.BeginTransaction()
da4.InsertCommand.Transaction = trn4
da4.UpdateCommand.Transaction = trn4
da4.DeleteCommand.Transaction = trn4

Try
da4.Update(ds4, "RentTransactions")
Catch ex As Exception
DbError("Rent transactions")
End Try

Dim Cb5 As SqlCommandBuilder = New SqlCommandBuilder(da5)
da5.InsertCommand = Cb5.GetInsertCommand
da5.UpdateCommand = Cb5.GetUpdateCommand
da5.DeleteCommand = Cb5.GetDeleteCommand
Dim trn5 As SqlTransaction = Cn5.BeginTransaction()
da5.InsertCommand.Transaction = trn5
da5.UpdateCommand.Transaction = trn5
da5.DeleteCommand.Transaction = trn5

Try
da5.Update(ds5, "RentTransferedList")
Catch ex As Exception
DbError("Rent Transfered list")
End Try

Dim Cb6 As SqlCommandBuilder = New SqlCommandBuilder(da6)
da6.InsertCommand = Cb6.GetInsertCommand
da6.UpdateCommand = Cb6.GetUpdateCommand
da6.DeleteCommand = Cb6.GetDeleteCommand
Dim trn6 As SqlTransaction = Cn6.BeginTransaction()
da6.InsertCommand.Transaction = trn6
da6.UpdateCommand.Transaction = trn6
da6.DeleteCommand.Transaction = trn6

Try
da6.Update(ds6, "Landlords")
Catch ex As Exception
DbError("Landlords")
End Try


If DbErr Then
Try
trn2.Rollback()
Catch ex As Exception
End Try

Try
trn4.Rollback()
Catch ex As Exception
End Try
Try
trn5.Rollback()
Catch ex As Exception
End Try

Try
trn6.Rollback()
Catch ex As Exception
End Try
Application.Exit()
End
Else
trn2.Commit()
trn4.Commit()
trn5.Commit()
trn6.Commit()
End If

Private Sub DbError(ByVal ds As String)
Dim response As MsgBoxResult
response = MsgBox("A database error occured whilst updating " & ds, MsgBoxStyle.OKOnly, "Contact Classic Software technical Support")
DbErr = True
End Sub


ps Closing the connnnection causes a commit anyway but that was just my style of coding

Not in the least bit elegant but it works!!!

[iqueen]

many thanks.