Click here to Skip to main content
15,029,369 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hai all,

I am using transaction in my asp.net2.0 web page.

Can I use a seperate sqlstatement to fetch some value in between tran.open and trans.commit other than transaction statements.

I have used samething in asp.net 1.1 and it is working perfectly.
But in 2.0 it is giving an error saying "this transaction is completed" and it is no more usable.

Ex:
sqlconn.Open()
                   Try 
                       myTrans = sqlconn.BeginTransaction()
                       s1.Connection = sqlconn
                       s1.Transaction = myTrans
                       s1.CommandText ="insert statement"
                        s1.ExecuteNonQuery()


Here i wanted to use a select statement which will fetch some value from databae which am using in my update statement by opening and closing a db connection.
this phenomena is working in .net 1.1 and giving the said error in .net 2.0
s1.CommandText ="some update statement"
                     s1.ExecuteNonQuery()
                     myTrans.Commit()
                   Catch ex As Exception
                       myTrans.Rollback()
                       mb.ShowMessagebox(Page, ex.Message)
                   Finally
                       sqlconn.Close()
                   end try

if somebody knows the solution please let me know.
thanks in advance
Posted
Updated 18-Aug-10 22:00pm
v2
Comments
Dalek Dave 19-Aug-10 4:00am
   
Edited for Readability.

Hi,

Probable reasons which i can think off are,

In .Net 2.0, If you want to execute more than one statement for one connection , you need to switch on the MARS feature(You need to turn on this feature in connection string, by default this is off).

The above mentioned error you will get if the first statement turns out to be error prone....for instance it could be ruling out some of the data base constraints during its insertion etc..

I hope this helps!.


Regards,
-Vinayak
   
Comments
vinayakshenoy2000@gmail.com 19-Aug-10 23:14pm
   
Reason for my vote of 5
Makes sense
If you're writing SQL in your ASP.NET pages, and not in a datalayer dll, then your design is already broken. However, your question is far from clear. You can't run more than one command in a transaction ? What if you just bunch all your SQL in to one command and run it ? I hope you're not string mashing your SQL together ? Why are you not using stored procs ?
   
I think you can do that with Gather all the values which you want in Dataset and Use it in between transaction as a offline rather then live.

Thanks
Hiren Solanki
   
v2
Comments
Christian Graus 27-Aug-10 7:26am
   
Reason for my vote of 1
This person has joined tonight and is posting random answers to try to boost his score. They are rarely helpful and often flat out wrong. He's also dumb enough to post his email address in a public forum.
Prosanta Kundu online 27-Aug-10 7:29am
   
Reason for my vote of 1
Christian is right.
Ankur\m/ 27-Aug-10 8:02am
   
Reason for my vote of 1
Consider the advices given to you on your previous posts or the moderators will step in.
Hiren solanki 27-Aug-10 8:08am
   
ok will surely follow.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900