SqlTransaction has completed it is no longer usable

Question

SqlTransaction has completed; it is no longer usable...

This is the code:

if (mfnSaveForm() == true)
{

// (Before commintting record this error had come.)
GblDeclarations.gblSQLTrans.Commit();

MessageBox.Show("Records Updated Successfully", "RECORD SAVED", MessageBoxButtons.OK, MessageBoxIcon.Information);
btnSave.Enabled = false;
GblDeclarations.gblTransBegin = false;

}

Please Give me the solution....Thank you

Zoran Horvat · Answer

Ah that's the point. You've started nested transaction but obviously it's not treated as nested. So once you commit it in UpdateRecord it's no longer existent. Further operations on database are executed without transaction and finally when you try to commit again in btnSave_Click you get the error that transaction has already been committed. Now to rectify the problem you should decide which method will be in charge of the transaction btnSave_Click or UpdateRecord. Looking at btnSave_Click method it doesn't really do anything with database. So I would suggest that you completely remove transaction-related statements from btnSave_Click and leave UpdateRecord method to control the transaction. In that case btnSave_Click method would become quite simple: private void btnSave_Click(object sender EventArgs e) { try { string StrMsg = null; StrMsg = mfnValidateSave(); boolSave = true; if (mfnSaveForm() == true) { MessageBox.Show(Records Updated Successfully RECORD SAVED MessageBoxButtons.OK MessageBoxIcon.Information); btnSave.Enabled = false; } else { boolSave = false; } } catch (Exception ex) { GV.gprocErrorHandler(ex.Source ex.Message btnSave_Click this.Name); } } Zoran

Sutha Shalini · Answer

why the people are not looking my question.. i'm in a great trouble now.. plz any one help me....

Sutha Shalini · Answer

i've created virtual directory also... but also giving same problem plz help me

Sutha Shalini · Answer

Error 1 : It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS. E:\stores_management\stores management\Web.config I got again such kind of problem... I dont know how to solve this.. Plz any one help me code: Set compilation debug=true to enable ASPX debugging. Otherwise setting this value to false will improve runtime performance of this application. Set compilation debug=true to insert debugging symbols (.pdb information) into the compiled page. Because this creates a larger file that executes more slowly you should set this value to true only when debugging and to false at all other times. For more information refer to the documentation about debugging ASP.NET files. --> defaultLanguage=c# debug=true /> Set customErrors mode=On or RemoteOnly to enable custom error messages Off to disable. Add tags for each of the errors you want to handle. On Always display custom (friendly) messages. Off Always display detailed ASP.NET error information. RemoteOnly Display custom (friendly) messages only to users not running on the local Web server. This setting is recommended for security purposes so that you do not display application detail information to remote clients. --> mode=RemoteOnly /> This section sets the authentication policies of the application. Possible modes are Windows Forms Passport and None None No authentication is performed. Windows IIS performs authentication (Basic Digest or Integrated Windows) according to its settings for the application. Anonymous access must be disabled in IIS. Forms You provide a custom form (Web page) for users to enter their credentials and then you authenticate them in your application. A user credential token is stored in a cookie. Passport Authentication is performed via a centralized authentication service provided by Microsoft that offers a single logon and core profile services for member sites. --> This section sets the authorization policies of the application. You can allow or deny access to application resources by user or role. Wildcards: * mean everyone ? means anonymous (unauthenticated) users. --> roles=[comma separated list of roles]/> roles=[comma separated list of roles]/> --> Application-level tracing enables trace log output for every page within an application. Set trace enabled=true to enable application trace logging. If pageOutput=true the trace information will be displayed at the bottom of each page. Otherwise you can view the application trace log by browsing the trace.axd page from your web application root. --> enabled=false requestLimit=10 pageOutput=false traceMode=SortByTime localOnly=true /> By default ASP.NET uses cookies to identify which requests belong to a particular session. If cookies are not available a session can be tracked by adding a session identifier to the URL. To disable cookies set sessionState cookieless=true. --> mode=InProc stateConnectionString=tcpip=127.0.0.1:42424 sqlConnectionString=data source=127.0.0.1;Trusted_Connection=yes cookieless=false timeout=20 /> This section sets the globalization settings of the application. --> requestEncoding=utf-8 responseEncoding=utf-8 />

Dinesh Beniwal · Answer

Great work Zoran...

Sutha Shalini · Answer

Ya Zoran Fine.. I do agree.. Fine i'll continue with this code only.. Thank you so much.. really thanks..

Zoran Horvat · Answer

What kind of problems do you expect in the future? Basically data component should control the transaction rather than GUI component. Maybe UpdateRecord method is too low to control the transaction. Maybe transaction in your design should be controlled from mfnSaveForm method but in that respect I don't have sufficient insight into your design. General rules regarding transactions are these: 1. Low level data component should not control transactions and should not know about them. 2. GUI components should not do that either. 3. Component which controls data flow should be in the middle and that should control the transactions. 4. Data component performs complete operations on database which may include number of calls to low level data component; all those calls should be wrapped in a single transaction. Zoran

Sutha Shalini · Answer

i've replied the same answer which u've sent now. see this i got answer: But i dont know its correct or not But record is saving properly. GblDeclarations.gblSQLTrans.Commit(); This line i've given in first step itself. But that line has to be remove from the second step Thank you. Plz any one find the solution plz Reply me the same ans. but i've mentioned as Step 1 and step2. Anyway Zoran. Thank you so much... U know one thing this solution will create many problems in future coding... Thank you so much..

Sutha Shalini · Answer

Help me Zoran plz

Sutha Shalini · Answer

Once you click the save button  -> Transaction begins and GblDeclarations.gblTransBegin=true; -> and Its goes to mfnSaveForm() method; -> after that it goes to UpdateRecord() method like given below code //UpdateRecord public long UpdateRecord(string strWhereCondition SqlConnection dbCon string SQLUpdate) { long RowCount = 0; try { SqlCommand dbCom = new SqlCommand(); dbCom.CommandType = CommandType.Text; //GD.gblSQLTrans = dbCon.BeginTransaction(); //GD.gblTransBegin = true; if (!string.IsNullOrEmpty(SQLUpdate)) { dbCom.CommandText = SQLUpdate; } else { dbCom.CommandText = mfnGenerateUpdateStatement() +  WHERE  + strWhereCondition; } dbCom.Connection = dbCon; if (GblDeclarations.gblTransBegin) dbCom.Transaction = GblDeclarations.gblSQLTrans; //dbCon.Close(); //dbCon.Open(); RowCount = dbCom.ExecuteNonQuery(); dbCon.Close(); } catch { } return RowCount; } // -> and the RowCount is returning the value =1 -> then if (mfnSaveForm() == true) { // (Before commintting record this error had come.) GblDeclarations.gblSQLTrans.Commit(); MessageBox.Show(Records Updated Successfully RECORD SAVED MessageBoxButtons.OK MessageBoxIcon.Information); btnSave.Enabled = false; GblDeclarations.gblTransBegin = false; } -> mfnSaveForm() method ==True fine its happening but once its reach GblDeclarations.gblSQLTrans.Commit(); method giving an error. this is the flow

Zoran Horvat · Answer

To make sure that I'm getting the code right - you click the Save button exactly once and get this error?

Zoran Horvat · Answer

Yes but in mfnSaveForm() method you do not work with transactions only in btnSave_Click... That's what is puzzling me. Zoran

Sutha Shalini · Answer

Hi Zoran No. In catch block also gblTransBegin =true only. Eventhough i'mgetting same error. Help me out

Sutha Shalini · Answer

I've mentioned setp 1(Override code) and step 2 (Save code) Anyway  Thank you very much... i'll checkout that now

Zoran Horvat · Answer

I don't understand. What is the first step and what is the second step. Looking at the code I would only suggest that you clear gblTransBegin to false in catch block. Zoran

Sutha Shalini · Answer

Same error Plz give me the solution plzzz

Sutha Shalini · Answer

i got answer: But i dont know its correct or not But record is saving properly. GblDeclarations.gblSQLTrans.Commit(); This line i've given in first step itself. But that line has to be remove from the second step Thank you. Plz any one find the solution plz Reply me

Sutha Shalini · Answer

FLOW OF mY CODE pLZ Help me out: Overrides the save form: step 1: public override bool mfnSaveForm() { bool functionReturnValue = false; try { long lngPID = 0; ColSaveData CSave = new ColSaveData(); CSave.Table_Name = tblPen_PensionerDetails; CSave.ID_Field = PensionerID; //Personal Details CSave.Add(PensionClass cmbClassOfPension.Textfalsetrue); if (cmbPensionerTitle.SelectedIndex > -1) { CSave.Add(TitleOfRespect cmbPensionerTitle.Text false true ); } if (cmbPensionerGender.SelectedIndex > -1) { CSave.Add(Gender cmbPensionerGender.Text false true ); } CSave.Add(PensionerName txtPensionerName.Text false true ); CSave.Add(PPONo txtPPONo.Text false true ); CSave.Add(PensionerCode txtPensionerCode.Text false true); CSave.Add(DateOfBirth GV.gfnFormatSQLDate(udtPensionerDOB.Value) false true ); if (udtPensionerDOR.Value != null) { CSave.Add(DateOfRetirement GV.gfnFormatSQLDate(udtPensionerDOR.Value) false true ); } CSave.Add(PPOwef GV.gfnFormatSQLDate(udtPPO_WEF.Value)falsetrue); //Office Details // string DESIGName = GV.gfnGetName(SELECT DesignationName FROM tblMast_DesignationMaster where DesignationID=' + Convert.ToInt32(cmbDesignation.SelectedValue) + '); CSave.Add(DesignationID cmbDesignation.SelectedValue.ToString() false true ); CSave.Add(EmployeeGroup cmbGroup.Text false true ); CSave.Add(DAType cmbPensionerDAType.Text false true ); CSave.Add(PayScaleIDConvert.ToString(cmbPayScale.SelectedValue) false true ); //string strDivisionName = GV.gfnGetName(SELECT DivisionName FROM tblMast_DivisionMaster where DivisionID=' + Convert.ToInt32(cmbDivision.SelectedValue) + '); CSave.Add(DivisionID cmbDivision.SelectedValue.ToString() false true ); //string strUnitName = GV.gfnGetName(SELECT SSAUnitNameSSAUnitID FROM tblMast_SSAUnitMaster where SSAUnitid=' + Convert.ToInt32(cmbUnit.SelectedValue) + '); CSave.Add(SSAUnitID cmbUnit.SelectedValue.ToString() false true ); //CSave.Add(PensionThrough cmbPaythrough.Textfalsetrue); //Contact Details CSave.Add(ResidencePhone txtPensionerContact.Text false true ); CSave.Add(MobileNo txtPensionerMobileNo.Text false true ); CSave.Add(EmailID txtPensionerEmail.Text false true ); CSave.Add(ContactAddress rtbPensionerAddress.Text false true ); //Other Details if (cmbMilitaryPension.Text == Yes) { CSave.Add(MilitaryPensionerTruefalse true ); } else { CSave.Add(MilitaryPensioner False false true ); } if (chkCeased.CheckState == CheckState.Checked) { CSave.Add(Ceased chkCeased.Checked.ToString() false true ); CSave.Add(CeasedDate udtCeaseDt.Value.ToString()falsetrue); } else { CSave.Add(Ceased chkCeased.Checked.ToString() false true ); } //------- PensionType = Superannuation Pension; CSave.Add(PensionType PensionTypefalsetrue); CSave.Add(PreparedBy StrPreparedByfalsetrue); CSave.Add(ModifiedBy StrModifiedByfalsetrue); lblStatus.Text = UnAuthorised; if (StrAuthorisedBy != null && mfnEnable_Authorise()) { if (MessageBox.Show(Do you want to Authorise this document? AUTHORISE MessageBoxButtons.YesNo MessageBoxIcon.Question MessageBoxDefaultButton.Button2) == System.Windows.Forms.DialogResult.Yes) { CSave.Add(VerifiedBy StrAuthorisedBy false true); boolVerified = true; lblStatus.Text = Authorised; } } if (mlngPID == 0) { lngPID = CSave.InsertRecord(GV.GetConnection()); mlngPID = lngPID; } else { lngPID = CSave.UpdateRecord(PensionerID= + mlngPID GV.GetConnection()); } CSave = null; if (lngPID > 0) { this.Tag = true; functionReturnValue = true; btnPrint.Enabled = true; } else { this.Tag = false; functionReturnValue = false; } } catch (Exception ex) { GV.gprocErrorHandler(ex.Source ex.Message mfnSaveForm this.Name); } return functionReturnValue; } step 2: save: private void btnSave_Click(object sender EventArgs e) { try { string StrMsg = null; StrMsg = mfnValidateSave(); //if (string.IsNullOrEmpty(StrMsg)) //{ // //MessageBox.Show(StrMsg REPEATED VALUE MessageBoxButtons.OK MessageBoxIcon.Exclamation) // //Exit Sub //} boolSave = true; GblDeclarations.gcnERMS = GV.GetConnection(); GblDeclarations.gblSQLTrans = GblDeclarations.gcnERMS.BeginTransaction(); GblDeclarations.gblTransBegin = true; if (mfnSaveForm() == true) { //GblDeclarations.gblSQLTrans.IsolationLevel; GblDeclarations.gblSQLTrans.Commit(); MessageBox.Show(Records Updated Successfully RECORD SAVED MessageBoxButtons.OK MessageBoxIcon.Information); btnSave.Enabled = false; GblDeclarations.gblTransBegin = false; } else { boolSave = false; try { if (GblDeclarations.gblTransBegin) { GblDeclarations.gblSQLTrans.Rollback(); GblDeclarations.gblTransBegin = false; } } catch (Exception extrans) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail. GV.gprocErrorHandler(extrans.Source extrans.Message btnSave_ClickTry this.Name); } } } catch (Exception ex) { GV.gprocErrorHandler(ex.Source ex.Message btnSave_Click this.Name); } } Plz help me out

Zoran Horvat · Answer

That error means that you have either committed or rolled back that transaction before you tried to use the object representing it. Try to discover program flow which leads to such result. Note that transactions may sometimes get terminated implicitly e.g. due to some using block or result of an exception where it's rolled back in the catch block etc. Zoran

SqlTransaction has completed, it is no longer usable