How to use BeginTrans and Commit using DATAADAPTER?

[nomi2000]

Hi i am using dataapter and updating the dataset now i want to know whats the methods for using BeginTrans,RollBack and Commit for this case
Thanks
Nouman

oadpsql = New OleDbDataAdapter("select * from Import_Rating where IRC_ID=''", oconsql)
ocmdbuilder = New OleDbCommandBuilder(oadpsql)
oadpsql.Fill(odataset)
odatatable = odataset.Tables(0)

---------------------------
----------------------------
----------------------------

oadpsql.Update(odataset)

Nouman

 

[Kris11]

This is for SQL Server, might have to write the equivalent statement in OleDB

Just before your oadpsql.Update(odataset)
statement, add the following code,

Dim oConnection as SQLConnection
Dim oTransaction As SQLTransaction

'Create and open a connection
oConnection = New SQLConnection(<Conn. Str> )
oConnection.Open()

'Begin the trasnaction
oTransaction = oConnection.BeginTransaction(IsolationLevel.ReadCommitted)


Now on your Adapter UpdateCommand, set the Trasnaction and connection properties to the one you just created, I am showing a store proc example

oadpsql.UpdateCommand = New SqlCommand
With oadpsql.UpdateCommand
.CommandText = &quot;[myStoreProc]&quot; 'IF UPDATE SQL give here.
.CommandType = System.Data.CommandType.StoredProcedure ' If it is an UPDATE SQL change this is to System.Data.CommandType.Text
.Transaction = oTransaction
.Connection = oConnection
End With


oadpsql.Update(odataset)

oTransaction.Commit 'Or oTransaction.Rollback()

oConnection.Close()

You might have to add more error checks, this is just the skelton. Hope this might help you to do the same in OleDB.

-Kris

 

[nomi2000]

Kris
Its not working
here is my whole code i am first filling the dataset with ADAPRTOR.FILL method here is my code


Dim oconsql As New OleDbConnection()
Dim j As Integer
'open connection
oconsql.ConnectionString = objuser.Data_Connection
oconsql.Open()

' Start a local transaction
''myTrans = oconsql.BeginTransaction(IsolationLevel.ReadCommitted)

For Each myTable In MyDataSet.Tables
Dim oadpsql As New OleDbDataAdapter()
Dim odataset As New DataSet()
Dim odatatable As New DataTable()
Dim ocmdbuilder As OleDbCommandBuilder


Select Case myTable.TableName

Case Is = &quot;Custom Entry&quot;
oadpsql = New OleDbDataAdapter(&quot;select * from Customs_entry where CEC_ID=''&quot;, oconsql)
ocmdbuilder = New OleDbCommandBuilder(oadpsql)

oadpsql.Fill(odataset)
odatatable = odataset.Tables(0)

Case Is = &quot;Shipment Header&quot;
oadpsql = New OleDbDataAdapter(&quot;select * from Shipment_Header where SHC_ID=''&quot;, oconsql)
ocmdbuilder = New OleDbCommandBuilder(oadpsql)

oadpsql.Fill(odataset)
odatatable = odataset.Tables(0)

Case Is = &quot;Shipment Consolidation&quot;
oadpsql = New OleDbDataAdapter(&quot;select * from Shipment_Consolidation where SCC_ID=''&quot;, oconsql)
ocmdbuilder = New OleDbCommandBuilder(oadpsql)

oadpsql.Fill(odataset)
odatatable = odataset.Tables(0)

Case Is = &quot;Line Item&quot;
oadpsql = New OleDbDataAdapter(&quot;select * from Line_Item_Detail where DETC_ID=''&quot;, oconsql)
ocmdbuilder = New OleDbCommandBuilder(oadpsql)

oadpsql.Fill(odataset)
odatatable = odataset.Tables(0)

Case Is = &quot;Import Rating&quot;
oadpsql = New OleDbDataAdapter(&quot;select * from Import_Rating where IRC_ID=''&quot;, oconsql)
ocmdbuilder = New OleDbCommandBuilder(oadpsql)

oadpsql.Fill(odataset)
odatatable = odataset.Tables(0)
'odatatable.TableName = &quot;Import_Rating&quot;
Case Is = &quot;Charges&quot;
oadpsql = New OleDbDataAdapter(&quot;select * from Charges where CHC_ID=''&quot;, oconsql)
ocmdbuilder = New OleDbCommandBuilder(oadpsql)

oadpsql.Fill(odataset)
odatatable = odataset.Tables(0)
'odatatable.TableName = &quot;Charges&quot;
Case Is = &quot;Entity&quot;
oadpsql = New OleDbDataAdapter(&quot;select * from Entity_Table where ENTC_ID=''&quot;, oconsql)
ocmdbuilder = New OleDbCommandBuilder(oadpsql)
''oadpsql.UpdateCommand = New OleDbCommand()
''With oadpsql.UpdateCommand
'' .CommandText = &quot;Entity_Table&quot;
'' .CommandType = CommandType.TableDirect
'' .Transaction = myTrans
'' .Connection = oconsql
''End With

oadpsql.Fill(odataset)
odatatable = odataset.Tables(0)
''odatatable.TableName = &quot;Entity_Table&quot;
''oadpsql.TableMappings.Add(&quot;Entity_Table&quot;, &quot;Table&quot


Case Is = &quot;Item Master&quot;
oadpsql = New OleDbDataAdapter(&quot;select * from Item_Master where ITMC_ID=''&quot;, oconsql)
ocmdbuilder = New OleDbCommandBuilder(oadpsql)

oadpsql.Fill(odataset)
odatatable = odataset.Tables(0)
'odatatable.TableName = &quot;Item_Master&quot;
End Select


For j = 0 To myTable.Rows.Count - 1
Dim oNewdatarow As DataRow = odatatable.NewRow
Dim odataRow As DataRow = myTable.Rows(0)

'odatarow = myTable.Rows(0)
Dim col As Integer
For col = 0 To odatatable.Columns.Count - 1
oNewdatarow.Item(col) = odataRow.Item(col)
Next
odatatable.Rows.Add(oNewdatarow) ' Save the Datarow

oadpsql.Update(odataset)

'clean up
odataRow = Nothing
Next
oadpsql = Nothing
odatatable = Nothing

Next
'''myTrans.Commit()
oconsql.Close()
oconsql = Nothing

 

[Kris11]

Onething I noticed here is that, it is actually not an UPDATE because you are doing newRow, which is an INSERT to the table so instead of UpdateCommand use InsertCommand and try the same. So the only change would be just change the

With oadpsql.UpdateCommand to With oadpsql.InsertCommand.

Let me know if this works

-Kris

 

[nomi2000]

Kris
where i should put this
With oadpsql.InsertCommand ?
Steps which i did
1) Begin Trans
2)Set up the adaptor's oadpsql.InsertCommand
3) Fill the adaptor
4) Add Row and update the adaptor's dataset
5) Commit if successful otherwise rollback
Thanks
Nouman

 

[Kris11]

In your code you have a commented section where you do

Case Is = &quot;Entity&quot;
oadpsql = New OleDbDataAdapter(&quot;select * from Entity_Table where ENTC_ID=''&quot;, oconsql)
ocmdbuilder = New OleDbCommandBuilder(oadpsql)
''oadpsql.UpdateCommand = New OleDbCommand()
''With oadpsql.UpdateCommand
'' .CommandText = &quot;Entity_Table&quot;
'' .CommandType = CommandType.TableDirect
'' .Transaction = myTrans
'' .Connection = oconsql

Replace this UpdateCommand here with InsertCommand. So the steps which you are following, looks good to me

1) Begin Trans
2)Set up the adaptor's oadpsql.InsertCommand
3) Fill the adaptor
4) Add Row and update the adaptor's dataset
5) Commit if successful otherwise rollback

Let me know if this helps...
-Kris