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
' regular code to create connection
'Now we set culture of date on this sql connection.
Dim sqlcmd As New SqlCommand
.Connection = sqlconn
.CommandText = "Set DateFormat dmy"
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?