VB-WEB:The SqlCommand is currently busy Open, Fetching.

[SunshineInTheRain]

I'm trying to modify a long long code within a button click by make the insert/update/delete/select using the same transaction. Purpose is to make sure every operation can be rollback instead of some table inserted but some not. But I get this error:

The SqlCommand is currently busy Open, Fetching.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The SqlCommand is currently busy Open, Fetching.

Source Error:


Line 794: SqlTrans.Commit()
Line 795: Else
Line 796: SqlTrans.Rollback()
Line 797: End If
Line 798: MyConnection.Close()


Source File: D:\Intranet\EFormApplication\preview\PMRF.aspx Line: 796

Stack Trace:


[InvalidOperationException: The SqlCommand is currently busy Open, Fetching.]
System.Data.SqlClient.SqlCommand.set_Transaction(S qlTransaction value) +57
System.Data.SqlClient.SqlTransaction.GetServerTran sactionLevel() +59
System.Data.SqlClient.SqlTransaction.CheckTransact ionLevelAndZombie() +63
System.Data.SqlClient.SqlTransaction.Rollback() +73
ASP.PMRF_aspx.btnApprove_Click(Object sender, EventArgs e) in D:\Intranet\EFormApplication\preview\PMRF.aspx:796
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292



Part of the code as below:

try
Cmd6.Transaction = SqlTrans
    objDRReq = Cmd6.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
 
                If objDRReq.Read() Then
 
                    fReqFormID = objDRReq("ReqForm_ID")
                    fRequestorID = objDRReq("Requestor_ID")
                    fReqName = objDRReq("ReqName")
                    fEFormID = objDRReq("EForm_ID")
                    fEFormName = objDRReq("EForm_Name")
                    fReqDate = objDRReq("ReqDate")
                    fReqTime = objDRReq("ReqTime")
                    fJustification = objDRReq("Justification")
 
                End If
                objDRReq.Close()
            catch
                BooTrans = true
            end try
 
...
...
...
      try
                    Cmd8.Transaction = SqlTrans
                    objDRCount = Cmd8.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
                    If objDRCount.Read() Then
                        CountNum = objDRCount("Num")      '  Count the total number of response (approve or reject) form the approver '              
                    End If
                    objDRCount.Close()
                   catch
                       BooTrans = true
                   end try
...
...
...
 
        If BooTrans = false Then
            SqlTrans.Commit()            
        Else
            SqlTrans.Rollback()
        End If
             MyConnection.Close()
 

Am I using the correct way in code above? Is there any suggestion on how to modify the existing code using the same transaction? Maybe I do not so understand on how to using the transaction code... Appreciating for any suggestion and help given.... :-)

[leoiser]

I am not clear about the coding... if you are doing any insert/delete/upate then only use the transactions.Are you doing? otherwise no need of transactions

I'm trying to modify a long long code within a button click by make the insert/update/delete/select using the same transaction. Purpose is to make sure every operation can be rollback instead of some table inserted but some not. But I get this error:

The SqlCommand is currently busy Open, Fetching.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The SqlCommand is currently busy Open, Fetching.

Source Error:


Line 794: SqlTrans.Commit()
Line 795: Else
Line 796: SqlTrans.Rollback()
Line 797: End If
Line 798: MyConnection.Close()


Source File: D:\Intranet\EFormApplication\preview\PMRF.aspx Line: 796

Stack Trace:


[InvalidOperationException: The SqlCommand is currently busy Open, Fetching.]
System.Data.SqlClient.SqlCommand.set_Transaction(S qlTransaction value) +57
System.Data.SqlClient.SqlTransaction.GetServerTran sactionLevel() +59
System.Data.SqlClient.SqlTransaction.CheckTransact ionLevelAndZombie() +63
System.Data.SqlClient.SqlTransaction.Rollback() +73
ASP.PMRF_aspx.btnApprove_Click(Object sender, EventArgs e) in D:\Intranet\EFormApplication\preview\PMRF.aspx:796
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292



Part of the code as below:

try
Cmd6.Transaction = SqlTrans
    objDRReq = Cmd6.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
 
                If objDRReq.Read() Then
 
                    fReqFormID = objDRReq("ReqForm_ID")
                    fRequestorID = objDRReq("Requestor_ID")
                    fReqName = objDRReq("ReqName")
                    fEFormID = objDRReq("EForm_ID")
                    fEFormName = objDRReq("EForm_Name")
                    fReqDate = objDRReq("ReqDate")
                    fReqTime = objDRReq("ReqTime")
                    fJustification = objDRReq("Justification")
 
                End If
                objDRReq.Close()
            catch
                BooTrans = true
            end try
 
...
...
...
      try
                    Cmd8.Transaction = SqlTrans
                    objDRCount = Cmd8.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
                    If objDRCount.Read() Then
                        CountNum = objDRCount("Num")      '  Count the total number of response (approve or reject) form the approver '              
                    End If
                    objDRCount.Close()
                   catch
                       BooTrans = true
                   end try
...
...
...
 
        If BooTrans = false Then
            SqlTrans.Commit()            
        Else
            SqlTrans.Rollback()
        End If
             MyConnection.Close()
 

Am I using the correct way in code above? Is there any suggestion on how to modify the existing code using the same transaction? Maybe I do not so understand on how to using the transaction code... Appreciating for any suggestion and help given.... :-)

[SunshineInTheRain]

I am not clear about the coding... if you are doing any insert/delete/upate then only use the transactions.Are you doing? otherwise no need of transactions

Yes, I'm doing and a lot, however problem solved. Appreciating your kindness to help and thank you for your reply.