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

Question

My Code :

public bool Insertcustomer()
{
    try
    {
        SqlCommand cmd = new SqlCommand("Insertcustomermaster", dal.con);
        cmd.Parameters.Add("@customercode", SqlDbType.Int).Value = customercode;
        cmd.Parameters.Add("@customername", SqlDbType.NChar).Value = customername;
        cmd.Parameters.Add("@address1", SqlDbType.NChar).Value = address1;
        cmd.Parameters.Add("@address2", SqlDbType.NChar).Value = address1;
        cmd.Parameters.Add("@phoneno", SqlDbType.Int).Value = phoneno;
        cmd.Parameters.Add("@mobileno", SqlDbType.Int).Value = mobileno;
        cmd.Parameters.Add("@mailid", SqlDbType.NChar).Value = mailid;
        cmd.Parameters.Add("@website", SqlDbType.NChar).Value = website;
        cmd.Parameters.Add("@occupation", SqlDbType.NChar).Value = occupation;
        cmd.Parameters.Add("@status", SqlDbType.Bit).Value = status;


        cmd.CommandType = CommandType.StoredProcedure;
        return Convert.ToBoolean(cmd.ExecuteNonQuery());  //Error
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

App Config :

im new in c# .net..im not able to find this error..anybody help..

When you say your new to program, then you must first read MSDN/related articles about this. I dont see the use of opening a thread here with out reading about it. — Zenwalker, Aug 26 '11 at 08:22
You need to dispose `SqlCommand`, and `SqlConnection` as soon as you've finished using them. — TheCodeKing, Sep 01 '11 at 21:52
possible duplicate of [ExecuteReader requires an open and available Connection. The connection's current state is Connecting](http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren) — Kevin Panko, Mar 14 '14 at 19:26

score 2 · Answer 1 · edited Sep 10 '12 at 15:21

2

You should open the connection and close it .

string connectionString = "";
SqlConnection con = new SqlConnection(connectionString);
con.Open();

//do your coding 
con.Close();

edited Sep 10 '12 at 15:21

Owen Blacker

4,117
2
33
70

answered Aug 26 '11 at 08:19

62071072SP

1,963
2
19
38

score 1 · Answer 2 · answered Aug 26 '11 at 08:13

The error message says what's wrong. The connection is not open, but closed.

Ensure the connection is opened. Use the Open()-Method to open a connection.

BTW, get rid of the try/catch-block. What's the point in catching an exception and throw it again?

score 1 · Answer 3 · answered Aug 26 '11 at 08:40

From your code you would need to open the connection specified in the SqlCommand declaration (dal.con)

You should open it with

dal.con.Open()

before you ExecuteNonQuery but remember to close it after (dal.con.Close()).

I would suggest changing

return Convert.ToBoolean(cmd.ExecuteNonQuery());

to

dal.con.Open();
bool result = Convert.ToBoolean(cmd.ExecuteNonQuery());
dal.con.Close();
return result;

score 0 · Answer 4 · edited Oct 21 '13 at 10:54

Could you post more information regarding the connection string?

Try this in the Webconfig:

<connectionStrings>
    <add name="connName" connectionString="//ConnectionString here"/>
</connectionStrings>

In the Class:

public static SqlConnection GetConnection()
{
    string conString = ConfigurationManager.ConnectionStrings["connName"].ConnectionString;
    SqlConnection con = new SqlConnection(conString);
    return con;
}

And instead of this:

SqlCommand cmd = new SqlCommand("Insertcustomermaster", dal.con);
        cmd.Parameters.Add("@customercode", SqlDbType.Int).Value = customercode;
        cmd.Parameters.Add("@customername", SqlDbType.NChar).Value = customername;
        cmd.Parameters.Add("@address1", SqlDbType.NChar).Value = address1;
        cmd.Parameters.Add("@address2", SqlDbType.NChar).Value = address1;
        cmd.Parameters.Add("@phoneno", SqlDbType.Int).Value = phoneno;
        cmd.Parameters.Add("@mobileno", SqlDbType.Int).Value = mobileno;
        cmd.Parameters.Add("@mailid", SqlDbType.NChar).Value = mailid;
        cmd.Parameters.Add("@website", SqlDbType.NChar).Value = website;
        cmd.Parameters.Add("@occupation", SqlDbType.NChar).Value = occupation;
        cmd.Parameters.Add("@status", SqlDbType.Bit).Value = status;


        cmd.CommandType = CommandType.StoredProcedure;
        return Convert.ToBoolean(cmd.ExecuteNonQuery());  //Error
    }
    catch (Exception ex)
    {
        throw ex;
    }

Do this:

//Method + Insert string (Guessing you want to create a register page)
    try
    {
        SqlCommand cmd = new SqlCommand(sql, GetConnection());
    //You don't have to point out the data type if it's just going to be filled in like that.
        cmd.Parameters.AddWithValue("@customercode", customercode);
        cmd.Parameters.AddWithValue("@customername", customername);
        cmd.Parameters.AddWithValue("@address1", address1);
        cmd.Parameters.AddWithValue("@address2", address2);
        cmd.Parameters.AddWithValue("@phoneno", phoneno);
        cmd.Parameters.AddWithValue("@mobileno", mobileno);
        cmd.Parameters.AddWithValue("@mailid", mailid);
        cmd.Parameters.AddWithValue("@website", website);
        cmd.Parameters.AddWithValue("@occupation", occupation);
        cmd.Parameters.AddWithValue("@status", status);
        cmd.ExecuteNonQuery();
    }
        catch (System.Data.SqlClient.SqlException ex)
        {
            // You can catch it with an error, or Just leave it empty
            string msg = "Error";
            msg += ex.Message;
            throw new Exception(msg);
        }
        //Close the connection (Not Necessary)
        GetConnection().Close();
    }

score 0 · Answer 5 · answered Aug 26 '11 at 08:13

0

dal.con hasn't been set or opened or whatever. As per the error message.

answered Aug 26 '11 at 08:13

gbn

422,506
82
585
676

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

5 Answers5

Linked