none
'ExecuteReader requires an open and available Connection RRS feed

  • Question

  • Hi,

    Why I am getting:

    System.InvalidOperationException: 'ExecuteReader requires an open and available Connection. The connection's current state is connecting.'

    for below code although my connection is already open as you can see...

    sql_connection = new SqlConnection("Server=my.domain.com; Database=myDB; User Id=sa; Password=MyPassword;");
    await sql_connection.OpenAsync();
    
    sql_command = new SqlCommand("sp_populate_students", sql_connection);
    sql_command.CommandType = CommandType.StoredProcedure;
    sql_command.Parameters.AddWithValue("@code", GlobalVariables.system_code);
    
    var dataAdapter = new SqlDataAdapter(sql_command);
    
    var commandBuilder = new SqlCommandBuilder(dataAdapter);
    var ds = new DataSet();
    dataAdapter.Fill(ds);
    
    GridStudents.ShowBusyIndicator = false;
    GridStudents.Columns.Clear();
    
    GridStudents.DataSource = ds.Tables[0];
    

    Thanks,

    Jassim

    Wednesday, July 3, 2019 11:11 PM

Answers

  • Please have a look at the code below. I don't think, we need to open connections asynchronously.  Look at your code snippet modified below:

    sql_connection = new SqlConnection("Server=my.domain.com; Database=myDB; User Id=sa; Password=MyPassword;");
    sql_connection.Open();

    sql_command = new SqlCommand("sp_populate_students", sql_connection);
    sql_command.CommandType = CommandType.StoredProcedure;
    sql_command.Parameters.AddWithValue("@code", GlobalVariables.system_code);

    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql_command);
    DataSet ds = new DataSet();
    dataAdapter.Fill(ds);

    sql_connection.Close();

    Hope this helps and resolve the issue!


    • Edited by KGund Thursday, July 4, 2019 9:08 AM some change
    • Marked as answer by Jassim Rahma Thursday, July 4, 2019 9:30 AM
    Thursday, July 4, 2019 8:58 AM

All replies

  • Any particular reason you are using OpenAsync?

    Don't call your procedures sp_something. The sp_ prefix is reserved for system procedures, and SQL Server will first look in the resource database for these, which can lead to surprises.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, July 4, 2019 8:24 AM
  • Please have a look at the code below. I don't think, we need to open connections asynchronously.  Look at your code snippet modified below:

    sql_connection = new SqlConnection("Server=my.domain.com; Database=myDB; User Id=sa; Password=MyPassword;");
    sql_connection.Open();

    sql_command = new SqlCommand("sp_populate_students", sql_connection);
    sql_command.CommandType = CommandType.StoredProcedure;
    sql_command.Parameters.AddWithValue("@code", GlobalVariables.system_code);

    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql_command);
    DataSet ds = new DataSet();
    dataAdapter.Fill(ds);

    sql_connection.Close();

    Hope this helps and resolve the issue!


    • Edited by KGund Thursday, July 4, 2019 9:08 AM some change
    • Marked as answer by Jassim Rahma Thursday, July 4, 2019 9:30 AM
    Thursday, July 4, 2019 8:58 AM