使用2个不同的命令时报错“There is already an open DataReader associated with this Command which must be closed first” when using 2 distinct commands

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (570)

我有这个代码:

 private void conecta()
 {  
     if (conexao.State == ConnectionState.Closed)
         conexao.Open();
 }

 public List<string[]> get_dados_historico_verificacao_email_WEB(string email)
 {
     List<string[]> historicos = new List<string[]>();
     conecta();

     sql = 
         @"SELECT * 
         FROM historico_verificacao_email 
         WHERE nm_email = '" + email + @"' 
         ORDER BY dt_verificacao_email DESC, hr_verificacao_email DESC";

     com = new SqlCommand(sql, conexao);
     SqlDataReader dr = com.ExecuteReader();

     if (dr.HasRows)
     {
         while (dr.Read())
         {
             string[] dados_historico = new string[6];
             dados_historico[0] = dr["nm_email"].ToString();
             dados_historico[1] = dr["dt_verificacao_email"].ToString();
             dados_historico[1] = dados_historico[1].Substring(0, 10);
             dados_historico[2] = dr["hr_verificacao_email"].ToString();
             dados_historico[3] = dr["ds_tipo_verificacao"].ToString();

             sql = 
                 @"SELECT COUNT(e.cd_historico_verificacao_email) QT 
                 FROM emails_lidos e 
                 WHERE e.cd_historico_verificacao_email = 
                     '" + dr["cd_historico_verificacao_email"].ToString() + "'";

             tipo_sql = "seleção";
             conecta();
             com2 = new SqlCommand(sql, conexao);

             SqlDataReader dr3 = com2.ExecuteReader();
             while (dr3.Read())
             {
                 //quantidade de emails lidos naquela verificação
                 dados_historico[4] = dr3["QT"].ToString(); 
             }
             dr3.Close();
             conexao.Close();

             //login
             dados_historico[5] = dr["cd_login_usuario"].ToString();
             historicos.Add(dados_historico);
         }
         dr.Close();
     }
     else
     { 
         dr.Close();
     }

     conexao.Close();
     return historicos;
 }

我创建了两个分离命令来纠正问题,但它仍然继续:"There is already an open DataReader associated with this Command which must be closed first“。

提问于
用户回答回答于

我建议为第二个命令创建一个额外的连接,将解决它。尝试在一个查询中组合两个查询。为计数创建一个子查询。

while (dr3.Read())
{
    dados_historico[4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verificação
}

if (dr3.Read())
{
    dados_historico[4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verificação
}
用户回答回答于

所属标签

可能回答问题的人

  • 找虫虫

    6 粉丝0 提问3 回答
  • 朝夕熊博客

    1 粉丝2 提问3 回答
  • gulu丶咕噜

    0 粉丝1 提问3 回答
  • 女淘日记

    杭州吱吱吱科技 · 站长 (已认证)

    1 粉丝1 提问3 回答

扫码关注云+社区

领取腾讯云代金券