INSERT error using C#/Asp.Net/SQL

Question

I have tried to run the query to insert data into the database, but I got error while runnnig the code..

ExecuteNonQuery requires an open and available connection. The connection's current state is closed.

Could you please tell me what the error is and why it happened?

{
    con2.Open();

    if (TextBox1.Text == "")
    {
        Response.Write("<script>alert('please enter Login Name')</script>");
    }
    else if (TextBox2.Text == "")
    {
        Response.Write("<script>alert('please enter Password')</script>");
    }
    else if (TextBox3.Text == "")
    {
        Response.Write("<script>alert('please enter Confirm Password')</script>");
    }
    else
    {
        //if (TextBox2.Text == TextBox3.Text)
        //{

            string a;
            a = "insert into tbl_Purchase_Users(Login_Name, Password, Uname, Uid, EmailID, Role, Status) values(@LName, @Pswd, @Uname, @uid, @Eid, @role, @stat)";
            SqlCommand cm = new SqlCommand(a, con1);
            cm.Parameters.AddWithValue("@LName", TextBox1.Text);

            string original;
            original = TextBox2.Text.Trim();
            int h = original.GetHashCode();
            string withHash = original;
            b1 = Encoding.BigEndianUnicode.GetBytes(withHash);
            encrypted = Convert.ToBase64String(b1);
            cm.Parameters.AddWithValue("@Pswd", encrypted);
            cm.Parameters.AddWithValue("@Uname", TextBox3.Text);
            cm.Parameters.AddWithValue("@uid", TextBox4.Text);
            cm.Parameters.AddWithValue("@Eid", TextBox5.Text);
            cm.Parameters.AddWithValue("@role", TextBox6.Text);
            cm.Parameters.AddWithValue("@stat", TextBox7.Text);

            cm.ExecuteNonQuery();
            Response.Write("<Script>alert('inserted')</script>");
        }
        con2.Close();
    }

Tamilmaran · Accepted Answer · 2012-05-22 05:00:48Z

2

You have opened con2 only not con1. You passed con1 in SqlCommand. Use the below code:

SqlCommand cm = new SqlCommand(a, con2);

answered May 22, 2012 at 5:00

Tamilmaran

1,3571 gold badge11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1402552 Over a year ago

Thanks friend.. This is what the small mistakes i made.. thanks for correcting me

maxpayne Over a year ago

@user1402552 If an answer helped you,then you need to accept it as answer.

SCB · Accepted Answer · 2012-05-22 04:59:21Z

0

You are opening the connection called con2 but you are using con1 on you SqlCommand.

From what I can see you haven't opened con1

answered May 22, 2012 at 4:59

SCB

3,0942 gold badges27 silver badges25 bronze badges

Comments

Marty · Accepted Answer · 2012-05-22 04:59:30Z

0

It looks like you have two different SqlConnection objects - con1 and con2. You are opening con2, but passing in con1 to the SqlCommand constructor.

As the error message states, the connection that you are using must be open.

If you pass con2 to the SqlCommand constructor, or if you open con1, your code should work.

answered May 22, 2012 at 4:59

Marty

7,5221 gold badge34 silver badges52 bronze badges

Comments

Rami Alshareef · Accepted Answer · 2012-05-22 05:00:15Z

0

Make sure the the con1 is opend, since the only .Open() call shown in your call related to con2

con1.Open();

answered May 22, 2012 at 5:00

Rami Alshareef

7,19813 gold badges51 silver badges78 bronze badges

Comments

Rakesh_HBK · Accepted Answer · 2012-05-22 05:03:02Z

0

I can't see con1.open(). you have used Con1 in Sqlcommand. Please open Con1.Open();

answered May 22, 2012 at 5:03

Rakesh_HBK

1813 silver badges12 bronze badges

Comments

Talha · Accepted Answer · 2012-05-22 05:28:40Z

0

just use one connection object like

 con2.Open();
 SqlCommand cm = new SqlCommand(a, con2)

edited May 22, 2012 at 5:28

answered May 22, 2012 at 5:02

Talha

19.3k8 gold badges53 silver badges66 bronze badges

Comments

Syed Yunus · Accepted Answer · 2012-05-22 05:38:28Z

First you need to connect with database by using Open(), in ur case con1.Open(); then perform action and close the connection. con1.Close(); else { //if (TextBox2.Text == TextBox3.Text) //{

        string a;
        a = "insert into tbl_Purchase_Users(Login_Name,Password,Uname,Uid,EmailID,Role,Status) values(@LName,@Pswd,@Uname,@uid,@Eid,@role,@stat)";
        SqlCommand cm = new SqlCommand(a, con1);
        con1.Open();
        cm.Parameters.AddWithValue("@LName", TextBox1.Text);

        string original;
        original = TextBox2.Text.Trim();
        int h = original.GetHashCode();
        string withHash = original;
        b1 = Encoding.BigEndianUnicode.GetBytes(withHash);
        encrypted = Convert.ToBase64String(b1);
        cm.Parameters.AddWithValue("@Pswd", encrypted);
        cm.Parameters.AddWithValue("@Uname", TextBox3.Text);
        cm.Parameters.AddWithValue("@uid", TextBox4.Text);
        cm.Parameters.AddWithValue("@Eid", TextBox5.Text);
        cm.Parameters.AddWithValue("@role", TextBox6.Text);
        cm.Parameters.AddWithValue("@stat", TextBox7.Text);

        cm.ExecuteNonQuery();
        Response.Write("<Script>alert('inserted')</script>");
        con1.Close();

}

Collectives™ on Stack Overflow

INSERT error using C#/Asp.Net/SQL

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related