CODEWITHSUNDEEP
c#
user1402552
user1402552

Reputation: 11

INSERT error using C#/Asp.Net/SQL

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();
    }

Upvotes: 0

Views: 439

Answers (7)

Syed Yunus
Syed Yunus

Reputation: 308

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();

}

Upvotes: 0

Talha
Talha

Reputation: 19252

just use one connection object like

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

Upvotes: 0

Rakesh_HBK
Rakesh_HBK

Reputation: 181

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

Upvotes: 0

Tamilmaran
Tamilmaran

Reputation: 1347

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

SqlCommand cm = new SqlCommand(a, con2);

Upvotes: 2

Rami Alshareef
Rami Alshareef

Reputation: 7140

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

con1.Open();

Upvotes: 0

Marty
Marty

Reputation: 7522

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.

Upvotes: 0

SCB
SCB

Reputation: 3084

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

Upvotes: 0

Related Questions