manjirit
asked on
Transactions in VB.net
Hello,
I am trying to use transactions in my VB.net program. I have opened a connection, and have opened a transaction on it and
Dim m_objConn As OleDb.OleDbConnection
Dim m_objTrans As OleDb.OleDbTransaction
Dim m_objDA As OleDb.OleDbDataAdapter
m_objTrans = m_objConn.BeginTransaction ()
I have a function like this :
Public Function ExecuteCommand(ByVal strQuery As String) As DataSet
'*** Create a new Data Adapter by passing the query and the connection
'*** object to it. Fill the dataset against that Dataadapter and
'*** return the dataset back to the calling function.
Dim objDS As New DataSet()
Dim objDA As New OleDb.OleDbDataAdapter(str Query, m_objConn)
objDA.Fill(objDS)
Return objDS
End Function
I am not using any command object in the code.
1. Is that ok? Can i populate my DAtaSet without using the command object?
2. When I execute the program, an InvalidOperationException is thrown with the following additional info:
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.
Now The question is, when I am not using the command object, why am I getting this error?
Thanks.
I am trying to use transactions in my VB.net program. I have opened a connection, and have opened a transaction on it and
Dim m_objConn As OleDb.OleDbConnection
Dim m_objTrans As OleDb.OleDbTransaction
Dim m_objDA As OleDb.OleDbDataAdapter
m_objTrans = m_objConn.BeginTransaction
I have a function like this :
Public Function ExecuteCommand(ByVal strQuery As String) As DataSet
'*** Create a new Data Adapter by passing the query and the connection
'*** object to it. Fill the dataset against that Dataadapter and
'*** return the dataset back to the calling function.
Dim objDS As New DataSet()
Dim objDA As New OleDb.OleDbDataAdapter(str
objDA.Fill(objDS)
Return objDS
End Function
I am not using any command object in the code.
1. Is that ok? Can i populate my DAtaSet without using the command object?
2. When I execute the program, an InvalidOperationException is thrown with the following additional info:
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.
Now The question is, when I am not using the command object, why am I getting this error?
Thanks.
When you use Adapter, internally the adapter object uses the Command objects
C here
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlDataAdapterClassTopic.asp?frame=true
C here
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlDataAdapterClassTopic.asp?frame=true
ASKER
Hi Ramesh,
Thanks for ur reply. THe thing is I am using the data update sqls, which I pass into strQuery string. I need to have the beginTransaction on connection. The thing is the adapter may be using the command object internally. But since I am not using one explicitely, how can I enlist a transaction to it? I am using only strQuery string and no command object.
Thanks for ur reply. THe thing is I am using the data update sqls, which I pass into strQuery string. I need to have the beginTransaction on connection. The thing is the adapter may be using the command object internally. But since I am not using one explicitely, how can I enlist a transaction to it? I am using only strQuery string and no command object.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
when you are not using any data update sqls like Insert/Update/Delete why to start a transaction?
Remove m_objTrans = m_objConn.BeginTransaction