Getting System.InvalidOperationException Error which I am confused to how to fix it?

Question

Alright so I have part of the script I am writing that will insert new addresses if its already in one table but the issue is i get an exception that I can't seem to fix, I have debugged many times and it says

System.InvalidOperationException: 'There is already an open DataReader associated with this Connection which must be closed first.'

Which I have googled and nothing really helped so here is the code chunk that is giving me the error

 try
 {
      _conn.Open();
      string compare = 
           "SELECT address, COUNT(*) FROM melena_edws m " + 
           "WHERE EXISTS (SELECT address FROM actuals a WHERE m.address = a.address) " + 
           "GROUP BY address ";

      CtreeSqlCommand cmd = new CtreeSqlCommand(compare, _conn);

      CtreeSqlDataReader reader = cmd.ExecuteReader();
      int count = (int)cmd.ExecuteScalar();//<------Error is here
      if(count > 0)
      {
           while (reader.Read())
           {
                Console.WriteLine(string.Format("{0,12}", reader["address"]));
                //cmd2.ExecuteNonQuery();
                reader.Close();
                Compare($"{reader["address"]} ");    
           }

           reader.Dispose();
           _conn.Close();
      }
      else
      {
            Console.WriteLine("Address already within table");
      }    
 }
 catch (CtreeSqlException ctsqlEx)
 {
       Console.WriteLine(ctsqlEx + " error running command script ");
 }

Remove the ExecuteScalar call. You don't need it. If the number of rows is zero then the while(reader.Read()) returns false immediately and you will never enter the loop — Steve, Aug 07 '18 at 17:43
Well, of course you could replace _if(count > 0)_ with _if(reader.HasRows)_ and go on with the _else_ message — Steve, Aug 07 '18 at 17:50
Looks like you are using a global/shared connection. Since *that* DataReader is not closed or disposed when you are done with it there are likely others which quite explains the error. — Ňɏssa Pøngjǣrdenlarp, Aug 07 '18 at 17:52
Where can I read about reader.hasRows? because I never knew that — Bartholomew Allen, Aug 07 '18 at 17:52

score 0 · Answer 1 · answered Aug 07 '18 at 17:54

A connection object cannot be used for another command if you have a DataReader opened with that connection. The alternative is to use a different connection, but in your context you don't really need to do anything to count the records returned by the sql text.

The DataReader.Read will return immediately false if you don't have rows. Or you can just use the DataReader.HasRows property to discover this fact without entering the loop and display a proper error message.

So you can rewrite your code with

_conn.Open();
string compare = "SELECT address, COUNT(*) FROM melena_edws m WHERE EXISTS (SELECT address FROM actuals a WHERE m.address = a.address) GROUP BY address ";

CtreeSqlCommand cmd = new CtreeSqlCommand(compare, _conn);
CtreeSqlDataReader reader = cmd.ExecuteReader()
if(reader.HasRows)
{
    while (reader.Read())
    {
        Console.WriteLine(string.Format("{0,12}", reader["address"]));
        Compare($"{reader["address"]} ");
    }
}
else
{
    // Display your message
}

reader.Dispose();
_conn.Close();

If I were to change the code I actually get an error that points to the reader object — Bartholomew Allen, Aug 07 '18 at 17:58
Nevermind, it was something I changed but this did work thank you! — Bartholomew Allen, Aug 07 '18 at 18:01

Getting System.InvalidOperationException Error which I am confused to how to fix it?

1 Answers1