hendrix500
asked on
USING SQL TRANSACTIONS IN VB.NET
Hi everyone,
I'm using VB.NET and I have long standing problem that I really need a solution to concerning inserting and updating to a SQL database using transactions. Sample code from my save routine is as follows
'Create and open the connection
Dim tsql As String = "server=MYSERSVER;uid=ME;" + _
"pwd=MYPASS;database=MYDB; Connect Timeout=5;pooling=false"
Dim sqlconn As New SqlClient.SqlConnection
sqlconn = New SqlClient.SqlConnection(ts ql)
sqlconn.Open()
'Create transaction
Dim sqltx As SqlClient.SqlTransaction
sqltx = sqlconn.BeginTransaction
'Specify the command to run
Dim sqlcomd As New SqlClient.SqlCommand
Dim sql as string = "INSERT INTO wherever"
'Add command to transaction
sqlcomd.Connection = sqlconn
sqlcomd.Transaction = sqltx
sqlcomd.CommandText = (sql)
pbrid = sqlcomd.ExecuteScalar()
-------------------------- -------
Thats the first part of the update - next I loop through an array and add
sql statements to the transaction, as follows
For i = 0 to totalrows
Dim sql1 as string = "INSERT INTO WHERERVER (brid, pcid) VALUES (pbrid, i)"
sqlcomd.Connection = sqlconn
sqlcomd.Transaction = sqltx
sqlcomd.CommandText = (sql1)
sqlcomd.ExecuteNonQuery()
next
'Finally, I commit the whole thing
sqltx.Commit()
sqlconn.Close()
sqltx = Nothing
sqlconn = Nothing
-------------------------- ---------- ---------- ---------- --------
The problem I am having is that occasionally an error occurs. The error is either 'ExecuteNonQuery: Connection property has not been initialised' OR
'SQL Transaction has completed; it is no longer usuable'. I have never been able to reproduce this error myself (but users have reported it) so i'm not sure exactly where in the code the error is occuring, I would assume as the sqltx.commit line. Fortunately, on the majority of occasions the save is still successfull but occasionally it isn't, which makes it even harder to find out where it is occuring.
Any help would be great.
I'm using VB.NET and I have long standing problem that I really need a solution to concerning inserting and updating to a SQL database using transactions. Sample code from my save routine is as follows
'Create and open the connection
Dim tsql As String = "server=MYSERSVER;uid=ME;"
"pwd=MYPASS;database=MYDB;
Dim sqlconn As New SqlClient.SqlConnection
sqlconn = New SqlClient.SqlConnection(ts
sqlconn.Open()
'Create transaction
Dim sqltx As SqlClient.SqlTransaction
sqltx = sqlconn.BeginTransaction
'Specify the command to run
Dim sqlcomd As New SqlClient.SqlCommand
Dim sql as string = "INSERT INTO wherever"
'Add command to transaction
sqlcomd.Connection = sqlconn
sqlcomd.Transaction = sqltx
sqlcomd.CommandText = (sql)
pbrid = sqlcomd.ExecuteScalar()
--------------------------
Thats the first part of the update - next I loop through an array and add
sql statements to the transaction, as follows
For i = 0 to totalrows
Dim sql1 as string = "INSERT INTO WHERERVER (brid, pcid) VALUES (pbrid, i)"
sqlcomd.Connection = sqlconn
sqlcomd.Transaction = sqltx
sqlcomd.CommandText = (sql1)
sqlcomd.ExecuteNonQuery()
next
'Finally, I commit the whole thing
sqltx.Commit()
sqlconn.Close()
sqltx = Nothing
sqlconn = Nothing
--------------------------
The problem I am having is that occasionally an error occurs. The error is either 'ExecuteNonQuery: Connection property has not been initialised' OR
'SQL Transaction has completed; it is no longer usuable'. I have never been able to reproduce this error myself (but users have reported it) so i'm not sure exactly where in the code the error is occuring, I would assume as the sqltx.commit line. Fortunately, on the majority of occasions the save is still successfull but occasionally it isn't, which makes it even harder to find out where it is occuring.
Any help would be great.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
As I mentioned the problem only happens occasionally so I will have to monitor any potential fixes for a couple of weeks before accespting an answer.
Thanks