CODEWITHSUNDEEP
sqlconnection
Phil N DeBlanc
Phil N DeBlanc

Reputation: 398

Connection is open, but I get "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed."

I'm getting the dreaded "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed." error, even though the connection is open ( see image). Here's my code. Where am I going wrong? I'm getting the error at the queryInsertSong.ExecuteNonQuery(); call.

        connectionManager = new ConnectionManager();
        using (SqlConnection conn = new SqlConnection(connectionManager.ConnectionString))
        {
            conn.Open();
            string insertSong = "Insert into Songs (SongTitle, Artist, Genre, Album, Year, Length, FullPath) " +
                "VALUES (@SongTitle, @Artist, @Genre, @Album, @Year, @Length, @FullPath)";
            using (SqlCommand queryInsertSong = new SqlCommand(insertSong, conn))
            {
                queryInsertSong.Connection = connectionManager.DBConnection;
                queryInsertSong.Parameters.Add("@SongTitle", SqlDbType.VarChar, 80).Value = song.SongTitle;
                queryInsertSong.Parameters.Add("@Artist", SqlDbType.VarChar, 50).Value = song.Artist;
                queryInsertSong.Parameters.Add("@Genre", SqlDbType.VarChar, 30).Value = song.Genre;
                queryInsertSong.Parameters.Add("@Album", SqlDbType.VarChar, 30).Value = song.Album;
                queryInsertSong.Parameters.Add("@Year", SqlDbType.Int, 4).Value = song.Year;
                queryInsertSong.Parameters.Add("@Length", SqlDbType.VarChar, 80).Value = song.Length;
                queryInsertSong.Parameters.Add("@FullPath", SqlDbType.VarChar, 80).Value = song.FullPath;

                queryInsertSong.ExecuteNonQuery();
            }
        }

Connection is open

Upvotes: 0

Views: 959

Answers (2)

Ananth MK
Ananth MK

Reputation: 392

Already you have initialized a sqlCommand with connnew SqlCommand(insertSong, conn). The below line will replace the existing connection and assign new one and it will be in closed state. So, remove the below line.

queryInsertSong.Connection = connectionManager.DBConnection;

Upvotes: 1

Aravind Aravind
Aravind Aravind

Reputation: 189

Better use this if condition before execute query

if (conn.State != ConnectionState.Open)
{
conn.Close();
conn.Open();
}

And no need this line

 queryInsertSong.Connection = connectionManager.DBConnection;

Upvotes: 1

Related Questions