CODEWITHSUNDEEP
c#.netsqldatabaseado.net
user108088
user108088

Reputation: 1168

C# SQL query exception

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.

Upvotes: 1

Views: 1341

Answers (9)

šljaker
šljaker

Reputation: 7374

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.

Upvotes: 1

Simon Hughes
Simon Hughes

Reputation: 3574

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

Upvotes: 0

Hans Kesting
Hans Kesting

Reputation: 39284

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.

Upvotes: 6

alxx
alxx

Reputation: 9897

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

Upvotes: 0

Coding Flow
Coding Flow

Reputation: 21881

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.

Upvotes: 0

Sandeep Kumar M
Sandeep Kumar M

Reputation: 3851

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

Upvotes: 0

user333306
user333306

Reputation:

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

Upvotes: 0

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102428

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

Upvotes: 0

William
William

Reputation: 8067

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

Upvotes: 4

Related Questions