Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

USING SQL TRANSACTIONS IN VB.NET

Posted on 2004-10-19
3
Medium Priority
?
601 Views
Last Modified: 2012-06-22
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(tsql)
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.
0
Comment
Question by:hendrix500
2 Comments
 
LVL 10

Accepted Solution

by:
123654789987 earned 750 total points
ID: 12345627
It looks like the connection timeout  problem.

Why are u repeating this statement

 sqlcomd.Transaction = sqltx

Since u are using the same command object sqlcomd , in the for loop, I don't think it is needed.

U should have a try-catch block wherever the query is executed so that u can catch SqlExceptions. In that case u have to abort the transaction
0
 
LVL 1

Author Comment

by:hendrix500
ID: 12346937
Yeah I take you point about repeating the statement. Not sure if it's causing the problem or not but i'll take it out anyway.

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
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question