Roll Back error with insert query

Question

0.00/5 (No votes)

See more:

Hi,

I Have the following Code.

VB

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim sqlcmd As New SqlClient.SqlCommand

    Dim con As New SqlClient.SqlConnection("Data Source=kgmmurugesh\sqlexpress;Initial Catalog=KGMERP_STORES;Integrated Security=True")

    Try
        con.Open()
        sqlcmd.Connection = con
        sqlcmd.CommandText = "begin tran ItemCreation"
        sqlcmd.ExecuteNonQuery()

        sqlcmd.CommandText = "Insert into Item_table(Item_Name,Item_PRate) values ('apple','a150')"
        sqlcmd.ExecuteNonQuery()

        sqlcmd.CommandText = "commit tran ItemCreation"
        sqlcmd.ExecuteNonQuery()

    Catch ex As Exception

        sqlcmd.CommandText = "rollback tran ItemCreation"
        sqlcmd.ExecuteNonQuery()

    End Try

End Sub

It works well, but sometimes it shows the following error:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

what is the mistake in my code?

Posted 25-Jun-14 3:27am

kgmmurugesh

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sprint89 · Answer 1 · 2014-06-25T03:30:00

Solution 1

The rollback doesn't seem to be part of the original transaction, it's on a separate sqlcmd

Posted 25-Jun-14 3:30am

Sprint89

Comments

kgmmurugesh 25-Jun-14 9:47am

I use only one sqlcmd in my code.

KaushalJB · Answer 2 · 2014-06-25T03:36:00

Solution 2

You need to perform SqlTransaction.Commit() when success.
and SqlTransaction.Rollback() in Catch.

Refer this links : Commit and
Rollback

Posted 25-Jun-14 3:36am

KaushalJB

Comments

kgmmurugesh 25-Jun-14 9:49am

I use commit tran in sucess and rollback when error in my code.

Sprint89 25-Jun-14 9:57am

But that is in your SQL code. If you want to do it that way you need to do the whole transaction, commit/rollback/error-handling in the one SqlCommand. The other way to do it (as in solution2) is to handle the transaction in VB

kgmmurugesh 25-Jun-14 10:02am

I don't want to do this in sqltransaction, tell me the error in my code!

Sprint89 · Answer 3 · 2014-06-25T04:40:00

You would need something along the lines of:

VB

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim sqlcmd As New SqlClient.SqlCommand
 
    Dim con As New SqlClient.SqlConnection("Data Source=kgmmurugesh\sqlexpress;Initial Catalog=KGMERP_STORES;Integrated Security=True")
 
        con.Open()
        sqlcmd.Connection = con
        sqlcmd.CommandText = @"begin tran  ItemCreation;
		                   insert into Item_table(Item_Name,Item_PRate) values ('apple','a150')
                               if @@error <> 0
                                   rollback tran ItemCreation;
			       else
				   commit tran ItemCreation; "       
	
	sqlcmd.ExecuteNonQuery()

End Sub

The SQL is OK but I don't have Visual Studio open right now so I'm not sure about the VB. Thing is, your VB code won't know if your transaction has committed or rolled back

CHill60 · Answer 4 · 2014-06-25T06:35:00

I'm not going to explain this particularly well, but your problem is that there is nothing to "tie together" all those ExecuteNonQuery statements. Each of them "stands alone" - similar to putting GO into a SQL query directly - for example

SQL

DECLARE @T VARCHAR(20);
GO
SET @T='Hello World'

will throw an error because @T is not declared by the time you execute the SET. This is the basis of Solution 3 - putting all of the sql into a single ExecuteNonQuery statement is one way around your problem. Putting your sql (including the rollback) into a Stored Procedure is another alternative.

This very good CodeProject article - Using Transactions in ADO.NET[^] - explains the concepts far better than I and includes an example solution that will fit right in with what you already have