0

I appreciate this question will have been asked before, however, I have spent the last 5 days trawling SO and the web and haven't yet found a solution to my problem.

I am trying to use C# to retrieve a record from a MySQL Database.

The code compiles fine, however when I test the application and try to bind the data to a DataTable(), the MySqlDataReader throws the following Exception

"Invalid Attempt to Read When Reader Is Closed"

I've not been able to diagnose the issue; as far as I can see, the reader isn't being closed before I try to read from it.

The method is below:

    /// <summary>
    /// Performs a select statement
    /// </summary>
    /// <param name="query">String of the SQL Query</param>
    /// <param name="parameters">List of SQL Parameters</param>
    /// <returns>DataTable</returns>
    public DataTable SelectQuery(string query, List<MySqlParameter> parameters)
    {
        MySqlConnection connection = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand(query, connection);
        foreach(MySqlParameter p in parameters)
        {
            cmd.Parameters.Add(p);
        }

        connection.Open();
        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        return dt;
    }

The actual database connection is working fine, I've placed a watch on the MySqlConnection and the connection is opening as it should.

I am passing the below query to this method:

string query = "SELECT IDS_USER, USRNM, PSSWRD, USR_SALT FROM factUser WHERE USRNM = ?username";

Which I have tested in MySql Workbench and it returned the correct data.

When I watch the MySqlCommand, the parameters have been added to the command as they should have been.

Any help would be greatly appreciated as I am absolutely stumped!

Smithygotlost
  • 26
  • 2

1 Answers1

0

You're close, but you have to set the ExecuteReader() function to a MySqlDataReader. Also, you should really get in the habit of using using statements especially in SQL related matters as if you do not properly dispose of your connections, you will run out quickly. Using using statements automatically disposes of the item in the using statement.

Your code with the appropriate variable set

MySqlConnection connection = new MySqlConnection(connectionString);
MySqlCommand cmd = new MySqlCommand(query, connection);
foreach(MySqlParameter p in parameters)
{
    cmd.Parameters.Add(p);
}

connection.Open();
DataTable dt = new DataTable();
MySqlDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
return dt;

With the appropriate using statements

using(MySqlConnection connection = new MySqlConnection(connectionString)){
    using(MySqlCommand cmd = new MySqlCommand(query, connection)){
          foreach(MySqlParameter p in parameters)
          {
              cmd.Parameters.Add(p);
          }

           connection.Open();
           DataTable dt = new DataTable();
           using(MySqlDataReader reader = cmd.ExecuteReader()){
               dt.Load(reader);
               return dt;
           }
       }
  }
Tommy
  • 38,021
  • 8
  • 85
  • 116
  • Hi Tommy, thanks for your answer, I've tried both versions of the code you posted but alas, I'm still running into the same issue! I've checked the Query and the Database connection 100 times over and they both work just fine :( – Smithygotlost Sep 08 '17 at 22:11
  • Have you tested if you can read the data in the code? After the `MySqlDataReader reader = cmd.ExecuteReader();` line, try a while loop to see if you can even see the records. `while(reader.Read()){//access the results}` – Tommy Sep 09 '17 at 02:11
  • I've tried the while loop already, I place a watch on the variable and I can see it finds the 4 database columns and the Primary Key but then gives that exception when I open the Results View – Smithygotlost Sep 09 '17 at 09:11