vj_mi
asked on
Using Transaction in vb.net 2008
Hello,
I have a windows app. Following is the scenario:
a. In Form1, I have Save button. This creates a sql connection and sets the transaction.
b. It then creates hashtable of fields to be updated in database.
c. This hash table is then passed to Class file (in another project but in same solution). Say the method in the class file is called UpdateTable. I also pass the sql connection and the transaction object to the method UpdateTable.
d. This method UpdateTable checks the parameters. If one of the paramter has value 0, it means Add new record and it calls another method InsertRecord. This insert record is in another DLL (CommonFunc.dll) that is not part of this solution. I also pass the connection object and transaction object. If one of the parameter is 1, it means the existing record is edited and it calls method UpdateRecord from CommonFunc.Dll.
e. The function InsertRecord takes this connection and assigns it to local variable as follows:
Public Function InsertReecord(xxxxx, objconn, objTrans) as xxxx
'Note that objconn and objTrans is both optional parameters
Dim sqlcon as new sqltransaction
if objConn is nothing = false then
sqlconn = objconn
'Open method not requiired since objConn is already open
else
' regular code to create connection
endif
'Now we set culture of date on this sql connection.
Dim sqlcmd As New SqlCommand
Try
With sqlcmd
.Connection = sqlconn
.CommandText = "Set DateFormat dmy"
.ExecuteNonQuery()
End With
Return True
Catch
Return False
Finally
sqlcmd.Dispose()
End Try
End Function
Now the above code where I execute nonquery gives error saying that for pending local transaction, I need to set the transaction. So my question is how do I set the transaction objTrans in the InsertRecord method?
Regards,
MI
I have a windows app. Following is the scenario:
a. In Form1, I have Save button. This creates a sql connection and sets the transaction.
b. It then creates hashtable of fields to be updated in database.
c. This hash table is then passed to Class file (in another project but in same solution). Say the method in the class file is called UpdateTable. I also pass the sql connection and the transaction object to the method UpdateTable.
d. This method UpdateTable checks the parameters. If one of the paramter has value 0, it means Add new record and it calls another method InsertRecord. This insert record is in another DLL (CommonFunc.dll) that is not part of this solution. I also pass the connection object and transaction object. If one of the parameter is 1, it means the existing record is edited and it calls method UpdateRecord from CommonFunc.Dll.
e. The function InsertRecord takes this connection and assigns it to local variable as follows:
Public Function InsertReecord(xxxxx, objconn, objTrans) as xxxx
'Note that objconn and objTrans is both optional parameters
Dim sqlcon as new sqltransaction
if objConn is nothing = false then
sqlconn = objconn
'Open method not requiired since objConn is already open
else
' regular code to create connection
endif
'Now we set culture of date on this sql connection.
Dim sqlcmd As New SqlCommand
Try
With sqlcmd
.Connection = sqlconn
.CommandText = "Set DateFormat dmy"
.ExecuteNonQuery()
End With
Return True
Catch
Return False
Finally
sqlcmd.Dispose()
End Try
End Function
Now the above code where I execute nonquery gives error saying that for pending local transaction, I need to set the transaction. So my question is how do I set the transaction objTrans in the InsertRecord method?
Regards,
MI
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Shaun Kline
One other thing, if you create the transaction object in the insert method, make sure you commit it in the Finally portion of your try catch (and similarly close the connection object if you created it in the method).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
One general comment:
Any reason why Commit is done in Finally? I did commit as the last line in Try and rollback in Catch. Pl. let me know your thoughts.
Regards,
MI
Any reason why Commit is done in Finally? I did commit as the last line in Try and rollback in Catch. Pl. let me know your thoughts.
Regards,
MI
I misspoke. The Commit should be done just prior to the Catch. The Rollback (as you mentioned) is done in the Catch statement. What I meant to say was make sure you do any cleanup in the Finally statement, which would include closing any open connections. I personally like to kill connection and transaction objects in the finally statement by setting them equal to nothing, but that is more preference that need. The framework should handle the disposal of the objects.