C# SQL query exception

Question

I'm using C# in .NET 2.0 and I'm trying to access and manipulate a database. I can read as many times from the DB as I want and everything works, but as soon as I try to insert an item I get the following error message:

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

I've tried to look this up, but the fixes I was able to find either didn't work or weren't applicable.

I have the following code:

using (SqlConnection conn = new SqlConnection(SQLConnectionString))
{
    SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT [Col1] FROM [Table1] WHERE [Col2]='" + val2 + "'", conn);
    DataSet dataSet = new DataSet();
    dataAdapter.Fill(dataSet);

    if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count != 1)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO [Table1] ([Col1], [Col2]) VALUES ('" + val1 + "', '" + val2 + "')", conn);
        cmd.ExecuteNonQuery();
    }
}

Note: I'm sure I have permissions set up properly, since Visual Studio can insert with the same SQLConnectionString. Also, I am still fairly new to databases, so if I'm doing anything blantently wrong, please let me know.

Thanks.

Answer 1

You don't appear to be opening the connection to perform your update (the exception tells you this).

Try this

SqlCommand cmd = new SqlCommand("INSERT INTO [Table1] ([Col1], [Col2]) VALUES ('" + val1 + "', '" + val2 + "')", conn);
            conn.Open();    
            cmd.ExecuteNonQuery();
            conn.Close();

You can get more details and working example on MSDN at http://msdn.microsoft.com/en-us/library/sd2728ad.aspx

Answer 2

You didn't open the connection.

This page shows you how to open a SqlConnection with the using statement.

Your Friend the C# Using Statement

Answer 3

Call conn.Open() before calling cmd.ExecuteNonQuery().

Answer 4

You have to open connection, before executing command

 conn.Open()  
    SqlCommand cmd = new SqlCommand("INSERT INTO [Table1] ([Col1], [Col2]) VALUES ('" + val1 + "', '" + val2 + "')", conn);  
    cmd.ExecuteNonQuery();  
    conn.Close()

Answer 5

You need to call con.open before you call con.ExecuteNonQuery and con.Close after it. Dataadapter.fill is doing it for you behind the scenes in the earlier code.

Answer 6

Check conn.IsOpen property before using cmd. And SqlCommand is disposable object too, its better practice to enclose it into "using" block.

Answer 7

The .Fill() opens the connection if it was not open and then closes it after it's done (only if it did open it itself). That's why that Fill did work. See MSDN.

But the .ExecuteNonQuery() doesn't do that, so you need to manually open the connection, with a

conn.Open();

either just before the ExecNonQuery or before the Fill.

As you are using a "using block", you don't need to explicitly Close() the connection, but that would not be wrong.

Answer 8

No-one above is checking to make sure the connection opened. I use this in my production code:

using (var conn = new SqlConnection(SQLConnectionString))
{
    conn.Open();
    if (conn.State == ConnectionState.Open)
    {
        SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT [Col1] FROM [Table1] WHERE [Col2]='" + val2 + "'", conn);
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);

        if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count != 1)
        {
            SqlCommand cmd = new SqlCommand("INSERT INTO [Table1] ([Col1], [Col2]) VALUES ('" + val1 + "', '" + val2 + "')", conn);
            cmd.ExecuteNonQuery();
        }
    }
}

Answer 9

When you call dataAdapter.Fill(dataSet); it will automatically open and close connection.
So you need to reopen connection before using insert statement, or replace DataAdapter with SqlCommand and keep connection opened until you execute insert statement.