Connection is open, but I get "ExecuteNonQuery requires an open and available Connection. The c-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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;