urgent help needed with transactions 1

[purplehaze1]

I am using dataAdapter to update records (add/delete/update). I want to use transactions
with dataAdapter to do updates. I am not sure how I can do it using dataAdapter. I have
following coded, is it going to work to insert/delete/update records to the database?



Public Function SavePatient(Optional ByVal bolStartTran As Boolean = True) As Boolean
Dim strsql As String
Dim myCommand As OleDbCommand
Dim dr As DataRow, bolInTran As Boolean

strsql = "SELECT * FROM t_recip WHERE recip_id=" & m_recipientID
myCommand = New OleDbCommand(strsql, myConnection)
Dim da As New OleDbDataAdapter(myCommand)
Dim cb As New OleDbCommandBuilder(da)
Dim ds As New DataSet()

Try
If Not IsDirty Then GoTo Skip_Save

If bolStartTran Then
myConnection.Open()
Trans = myConnection.BeginTransaction()
bolInTran = True
End If

da.Fill(ds, "Recipient")

If ds.Tables(0).Rows.Count = 0 Then
dr = ds.Tables(0).NewRow()
IsNew = True
GoTo SaveRecord
Else
dr = ds.Tables(0).Rows(0)
End If

If m_IsDeleted Then
dr.Delete()
End If
SaveRecord:
dr("cust_id") = m_custID
dr("gender_id") = CType(m_genderID, Integer)
dr("recip_care_status_id") = m_recipCareStatusID
dr("first_nme") = IIf(m_fName = String.Empty, String.Empty, m_fName)
dr("mi_nme") = IIf(m_Init = String.Empty, String.Empty, m_Init)
dr("last_nme") = IIf(m_lName = String.Empty, String.Empty, m_lName)


If IsNew Then
ds.Tables(0).Rows.Add(dr)
End If

da.UpdateCommand.Transaction = Trans
''Delegate for Handling RowUpdated event
AddHandler da.RowUpdated, AddressOf HandleRowUpdated
da.Update(ds, "Recipient")
Skip_Save:
If bolInTran Then
Trans.Commit()
bolInTran = False
End If
IsDirty = False
IsNew = False
Return True
Rollback_Save:
If bolInTran Then Trans.Rollback()
myConnection.Close()
Catch ex As Exception
If bolStartTran Then GoTo Rollback_Save
ErrNum = Err.Number
ErrMsg = Err.Description
Call ErrHandler(ErrNum, ErrMsg, "Recipient", "Save")
Finally
myConnection.Close()
End Try

End Function

 

[purplehaze1]

It blows up at line
da.UpdateCommand.Transaction = Trans

Error msg is "object reference not set to an instance.."

 

[purplehaze1]

when I mouse over da.UpdateCommand, it shows nothing.
What could be the reason? Thanks

 

[Becks25Not]

Hi purplehaze1,

You need to create a variable for the Transaction and use that:

Dim t As New Data.OleDb.OleDbTransaction

 

[purplehaze1]

Beck,
I cannot instantiate oledbtransaction because it shows
error when I try to.
I guess the reason it's bombing is because in

da.UpdateCommand.Transaction = Trans
da.InsertCommand.Transaction = Trans
da.DeleteCommand.Transaction = Trans

da.updatecommand, da.insertcommand and da.deletecommand is
nothing and that's where it blows up. How can I populate these three commands? Thanks again.

 

[Becks25Not]

I see ...

you need to Initialize those commands withcode like this:

Dim strUpdateSQL As String = "UPDATE [TableName] " & _
                                         "SET [Field1] = @Parameter1 " & _
                                         "WHERE [Field2] = @Parameter2 "
            Dim cmdUpdate As New SqlClient.SqlCommand(strUpdateSQL, ConnectionVariable)
            cmdUpdate.Parameters.Add("@Parameter1", SqlDbType.SmallInt, Nothing, "Field1")
            Dim prmUpdate As SqlClient.SqlParameter = cmdUpdate.Parameters.Add("@Parameter2", SqlDbType.VarChar, 50, "Field2")
            prmUpdate.SourceVersion = DataRowVersion.Current
            DataAdaprtVariable.UpdateCommand = cmdUpdate

 

[Becks25Not]

Forget to put this: As you can see, this is SQL but the OLE DB just use the OLE DB variables. You'll need to do that for Insert and Delete as well. After you added, deleted, or inserted into your data table, to save the changes to the database:

ConnectionVariable.Open()
DataAdapterVariable.Update(DataSetVariable)
'** If you want to refresh your dataset:
DataSetVariable.Clear()
DataAdpaterVariable.Fill(DataSetVariable)
ConnectionVariable.Close()