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

Answer 1

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