DataReader返回零行但数据存在。

Question

我正在尝试按月获取警报统计信息。以下是我的SQL语句：

SELECT datepart(year,incidentDate),datepart(month,incidentDate) ,COUNT(*) AS CNT 
From alarm_actions,alarm_licenses 
where licType = 'AR' and alarm_actions.alarmNo = ALARM_LICENSES.alarmNo and alarmCount > 0 and
ignoreAlarm = 0 and incidentDate between '5/1/12' and '7/31/14'
group by datepart(year,incidentDate) , datepart(month,incidentdate)
order by datepart(year,incidentDate)

当我在SQL 2008R2中执行此查询时，我得到了27行数据，但是当我在Visual Studios中使用SqlDataReader时却没有获取到数据。chartFrom和chartTo是我用来获取日期的文本框。

string alarmCount = ("Select datepart(year,incidentDate) , datepart(month,incidentDate),COUNT(*) as cnt From Alarm_Actions,Alarm_Licenses Where licType = 'AR' and alarm_actions.alarmNo = alarm_licenses.alarmNo and alarmCount > 0 and ignoreAlarm = 0 and incidentDate between " + chartFromDate + " and " + chartToDate + "group by DATEPART(YEAR, incidentDate), DATEPART(MONTH,incidentDate) order by  DATEPART(YEAR, incidentDate)");
SqlDataReader dr = data.myDataReader(alarmCount);
while(dr.Read())
{
   //Do something
}

这是我的DataReader代码：

 public SqlDataReader myDataReader(string mSQL) 
    {
        SqlConnection myConnection = new SqlConnection(connectionString);
        SqlDataReader myReader;
        SqlCommand myCommand1;
        try
        {
            myConnection.Open();
            //myCommand1 = new System.Data.OleDb.OleDbCommand(mSQL, myConnection);
            myCommand1 = new SqlCommand(mSQL, myConnection);

            //=============== 1/26/2012 =============
            //There have been errors running recently. I am increasing the timeout here
            myCommand1.CommandTimeout = 60000;

            myReader = myCommand1.ExecuteReader(CommandBehavior.CloseConnection);
            //return myReader;
        }
        catch (Exception e1)
        {
           MessageBox.Show("An error occurred retrieving the requested data\r\n\r\n" + mSQL + "\r\n\r\n" + e1.Message);
            myReader = null;
            try
            {
                string mSentBy = ConfigurationManager.AppSettings["emailSentBy"].ToString();
                string mSendTo1 = ConfigurationManager.AppSettings["emailSendTo"].ToString();
                string mSubject = ConfigurationManager.AppSettings["emailSubject"].ToString();
                string mHost = ConfigurationManager.AppSettings["emailHost"].ToString();
                string mSignon = ConfigurationManager.AppSettings["emailSignon"].ToString();
                string mPassword = ConfigurationManager.AppSettings["emailPassword"].ToString();
            }
            catch { }
        }
        finally
        {
            //myConnection.Close();
            myCommand1 = null;
            //myReader = null;
            myConnection = null;
            //myReader = null;
        }
        return myReader;
    }

感谢您的帮助

我在日期字段中缺少了单引号。感谢所有抽出时间帮助我的人。

- Mr. Blue

---

你有收到任何异常吗？能否请您发布整个代码，包括创建Sql连接的方式，构建命令以及执行它等等？ - Christos

你能否同时发布代码中查询的样子，以便我们看看是否与格式问题有关？ - NaNey

我刚刚更新了它。ChartFromDate和ChartToDate是我用来获取日期的文本框。我输入了与上面查询相同的日期。 - Mr. Blue

chartToDate和chartFromDate有单引号吗？如果没有，我认为你需要将其更改为类似于`incidentDate BETWEEN ' "+ chartFromDate +" ' and ' "+ chartToDate +" '。最好的方法是在分配chartFromDate和chartToDate的值之后打印alarmCount的值，并尝试在SQL Server中运行查询。 - NaNey

啊，日期字段中是单引号。感谢您指出这一点。 - Mr. Blue

Answer 1

我没有测试过，但希望它能对你有用：

SqlConnection con = new SqlConnection(connectionString);  

SqlCommand cmd = new SqlCommand();  
cmd.Connection = con;  
cmd.CommandText = "SELECT datepart(year,incidentDate), ......";  

con.Open();  

cmd.ExecuteNonQuery();  

SqlDataReader dr = cmd.ExecuteReader();

while(dr.Read())
{
   //Do something
}

con.Close();  

- Orkun Bekar