! Commit problem

[iqueen]

how to let commit work with object:

odbcommand,
odbcdataadapter,
dataset,
odbctransaction

my code as follow

Dim meetError As Boolean = False
Dim conn As Odbc.OdbcConnection
Dim trans As Odbc.OdbcTransaction
Dim cmd As Odbc.OdbcCommand
Dim da As System.Data.ODBC.OdbcDataAdapter
Dim ds As DataSet = New DataSet("dsTable")
Dim dt As DataTable
Dim i As Integer
'
Try
conn = New Odbc.OdbcConnection(connStr)
conn.Open()
'
trans = conn.BeginTransaction(IsolationLevel.ReadCommitted)
cmd = conn.CreateCommand()
cmd.Transaction = trans
Catch e As Odbc.OdbcException
MsgBox("open error " & e.Message)
meetError = True
Return False
End Try
'
'exec sql
Try
For i = 0 To in_stmt.GetUpperBound(0)
cmd.CommandText = in_stmt(i).value
da = New Odbc.OdbcDataAdapter(cmd.CommandText, conn)
Try
dt = New DataTable(i.ToString)
da.Fill(dt)
If dt.Columns.Count > 0 Then
ds.Merge(dt)
End If
Catch ex As Exception
MsgBox("merge error " & ex.Message)
End Try
cmd.ExecuteNonQuery()
Next
Catch ex As Odbc.OdbcException
MsgBox("execute error " & ex.Message)
meetError = True
End Try
'
If meetError Then
trans.Rollback()
Else
trans.Commit()
End If
'
'close connectoin
'
grd.DataSource = ds
Try
conn.Close()
conn = Nothing
Catch e As Odbc.OdbcException
MsgBox("close error " & e.Message)
End Try
Return True

got a exception.message as follow

Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized

thanks.

[iqueen]

and also get the follow exeception

An unhandled exception of type 'System.NullReferenceException' occurred in system.windows.forms.dll

Additional information: Object reference not set to an instance of an object.

[RichardAtherton]

Yes and yu are mot the only one, I am experimenting with the commanbuilder refresh schema method, but still having problems, If I get it solved, I will post again.

[CyberHawke]

While commandbuilders do "work", they are unreliable and not recommended for use in a production system. They fall under the heading of "Nice To Have, but not ready for realtime".
If you want to work with transactions, build your command objects by hand, once you do you will find that your transactions will commit or rollback as necessary.

[RichardAtherton]

I agree on one hand and their is even an article, from Microsoft entitled weaning programmers off the commandbuilder, having said that, the thought of going back through the application to change it from commandbuilder, to handwritten, is just something I cannnnot face.

Think, I havef ound the way to do use transactions and commandbuilder and will post when, I have it tested.