C# – ExecuteNonQuery requires an open and available Connection. The connection’s current state is closed.

ado.netasp.netcc#-4.0c#-5.0

This seems silly but I can't put my finger on this. What am I doing wrong here? It's a common error but I don't seem to be making the same mistakes as on the other couple of dozens of posts on here…

    public static string Insert(SqlCommand cmd, SqlConnection connection) 
    {
        //feedback to user on whether the insert was successful
        string success = "Success! Thank you for contributing to this project";

        int added = 0;

        //connect and insert the data
        try
        {
            using (connection)
            {
                connection.Open();
                added = cmd.ExecuteNonQuery();

                //update success variable
                success += "\n" + added.ToString() + " records inserted.";
            }//end using(connection)
        }//end try

        catch (Exception err)
        {
            //if we have an error, return this message for debugging purposes for now
            string message = err.Message;
            return message;
        }//end catch

        return success;
    }//end Insert()

Best Answer

Your Command object probably does not have the connection, as you are passing both Connection and Command to your method. You need to assign the connection to command object property Connection. 

try
{
    using (connection)
    {
        cmd.Connection = connection; //Here assign connection to command object
        connection.Open();
        added = cmd.ExecuteNonQuery();

        //update success variable
        success += "\n" + added.ToString() + " records inserted.";
    }//end using(connection)
}//end try
Related Topic