rollback in sqlserver

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

, +

Hi All,

I have two web forms and two different tables for save data of these two web forms,
on first web form i have a hyperlink which is used to redirect to second page from first page . Now when i click on hyperlink page i save the data of first page in first table now when i click on save of second page if second page insertion failed i want to rollback first insertion i.e., made on first page
for insertion i simple made a stored procedure.

how to rollback insertion..

Thanks in Advance...

Posted 10-Apr-11 18:13pm

sharmarun

Updated 10-Apr-11 18:25pm

Ramalinga Koushik

v2

Add a Solution

2 solutions

Solution 2

Hi,

If I understood your problem correctly, you're trying to do the transaction in two parts, having a user conversation in between. This should never be done.

When a transaction starts, you simply make the necessary modifications, check for errors etc and in the end, commit or rollback. Having any kind of UI operations in the middle will cause locking related problems etc.

So my advice is that when the user changes to second page, store the necessary data somewhere else (session etc) and when the user finally presses the save, make necessary validity checks against all data and save the data in a single, short, transaction.

Posted 10-Apr-11 18:33pm

Wendelius

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)

*dan!sh* · Accepted Answer · 2011-04-10T18:21:00

There are two ways I can think of:

1. Do Transaction management in the application. Create a connection and begin transaction in the form1. Now use the same connection object in the form2. If the save in form2 works well, do a commit. Or else do a rollback. Be sure to take care of closing and disposing connection object. If one opens the connection in page 1 and does the save. Then goes to page 2 and just closes the window. Your connection must get closed and transaction must get rolled back.

2. You can do transaction in your stored procedure. In the application, when user moves from page1 to page2, just keep the data in the Session. When the user clicks on save, save all the data in one DB hit. Here is how your application would work:

Page1 -> Page2: Save data in the Session
Page2.Save: Get Page1 and Page2 data and end it to DB.
In your stored procedure:

1. Main SP called
2. Begin tansaction
3. Execute SP to save Page1 data
4. Execute SP to save Page2 data
5. Commit or rollback
6. Return output to page2