Sql Transaction Problem

Question

4.00/5 (1 vote)

See more:

I am Developing one application.
I want to store the names of chemist in database
The code is as follows

if (TextBoxName.Text == "")
    {
        LabelMessage.Text = "Please Enter Chemist name";
    }
    else if( ClassValidation.checkExist("Chemist","ChemistName",TextBoxName.Text )==true)
    {
        LabelMessage.Text = "Chemist Name Already Exist";
    }
    else
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MRProjectConnectionString"].ConnectionString);
        SqlCommand cmd;
        //if (con.State == ConnectionState.Open)
        //{
        //    con.Close();
        //}
        con.Open();
        SqlTransaction trans;
        trans = con.BeginTransaction(IsolationLevel.Serializable);
        try
        {

            cmd = new SqlCommand("Insert into Chemist(ChemistName,Qualification,Birthdate,Email,Website ,MobNo,HqName,Anniversary,Hobbies,other ,Submittedby,Status)Values(@ChemistName,@Qualification,@Birthdate,@Email,@Website ,@MobNo,@HqName,@Anniversary,@Hobbies,@other ,@Submittedby,@Status)", con, trans);
            cmd.Parameters.Add("@ChemistName", SqlDbType.NVarChar).Value = TextBoxName.Text;
            cmd.Parameters.Add("@Qualification", SqlDbType.NVarChar).Value = TextBoxQualification.Text;
            cmd.Parameters.Add("@Birthdate", SqlDbType.SmallDateTime).Value = TextBoxBirthdate.Text;
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = TextBoxEmail.Text;
            cmd.Parameters.Add("@Website", SqlDbType.NVarChar).Value = TextBoxWebsite.Text;
            cmd.Parameters.Add("@MobNo", SqlDbType.NVarChar).Value = TextBoxMobileNo.Text;
            cmd.Parameters.Add("@HqName", SqlDbType.NVarChar).Value = DropDownListHqName.Text;
            cmd.Parameters.Add("@Anniversary", SqlDbType.SmallDateTime).Value = TextBoxAnniversary.Text;
            cmd.Parameters.Add("@Hobbies", SqlDbType.NVarChar).Value = TextBoxHobbies.Text;
            cmd.Parameters.Add("@other", SqlDbType.NVarChar).Value = TextBoxOther.Text;
            cmd.Parameters.Add("@Submittedby", SqlDbType.NVarChar).Value = "1";
            cmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = "P";
            cmd.ExecuteNonQuery();
            trans.Commit();
            con.Close();
            LabelCode.Text = ClassChemist.getChemistCode(TextBoxName.Text).ToString();
            LabelMessage.Text = "Chemist Saved Successfully";
        }
        catch (Exception exp)
        {
            trans.Rollback();//error occures here***********
            LabelMessage.Text = exp.Message;
        }
        finally
        {
           // trans.Dispose();
        }
    }

Problems:

If i enter all values correct then code works fine but when i enter some wrong values such as wrong date format It gives error message as i am expecting ,
But after getting exception (error message ) if i corrects the data data is saved successfully but one Exception is thrown

This SqlTransaction has completed; it is no longer usable
in catch block..
Please help me.

[edit]Code block added - OriginalGriff[/edit]

Posted 26-Mar-11 20:12pm

nitin bhoyate

Updated 26-Mar-11 20:19pm

OriginalGriff

v3

Add a Solution

1 solution

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)

OriginalGriff · Answer 1 · 2011-03-26T20:31:00

If you are getting an exception in your catch block, which says that the SqlTransaction has completed, then it is likely that that the the original error is coming after the Commit. I would suggest that you nest catch blocks:

C#

try
{
    try
        {     
        cmd = new SqlCommand("Insert into Chemist(ChemistName,Qualification,Birthdate,Email,Website
...
        cmd.ExecuteNonQuery();
        trans.Commit();
        con.Close();
        }
    catch (Exception exp)
        {
        trans.Rollback();
        LabelMessage.Text = exp.Message;
        }
    LabelCode.Text = ClassChemist.getChemistCode(TextBoxName.Text).ToString();
    LabelMessage.Text = "Chemist Saved Successfully";
    }
catch (Exception expOuter)
    {
    LabelMessage.Text = expOuter.Message;    
    }
}

You might also want to look at using Close and Dispose on your SqlConnection and SqlCommand objects as well as the SqlTransaction.