的ExecuteScalar后：对象没有设置等 [英] After ExecuteScalar: Object reference not set etc

问题描述

什么码我要补充接受WHERE语句空

  {
 INT numApprovals = 0; 
字符串SQL =SELECT COUNT（类型）AS OpenforApproval+ 
FROM dbo.LeaveRequest+ 
表示输入（2，3，4，5，6，8，13 ，14，16，22）+ 
GROUP BY MgtApproval+ 
，其中MgtApproval IS NULL 
 //SELECT COUNT（EFFECTIVEDATE）AS OpenforApproval FROM LeaveRequest GROUP BY TimeStampApproval HAVING（TimeStampApproval IS NULL）;使用
 
（CN =新的SqlConnection（的ConnectionString（）））
 {
 cn.Open（）;使用
（CMD =新的SqlCommand（SQL，CN））
 {
 cmd.CommandType = CommandType.Text; 
 numApprovals =（INT）cmd.ExecuteScalar（）; 
} 
} 
 
返回numApprovals; 
} 
  

解决方案

刚：

  WHERE型（2，3，4，5，6，8，13，14，16，22）或类型为null 
  

但我并不相信那是你真正想要的，或者问题的原因。

如果你要在C＃代码的异常，它不会是从where子句。

我被你的连接似乎被重用现有变量，顺便感到关切。馊主意。几乎可以肯定应是一个局部变量。您也可以使你的代码更简单从它的中间返回：

  SQL字符串= ...; 
 
使用（VAR CN =新的SqlConnection（的ConnectionString（）））
 {
 cn.Open（）;使用
（CMD =新的SqlCommand（SQL，CN））
 {
 cmd.CommandType = CommandType.Text; 
回报（INT）cmd.ExecuteScalar（）; 
} 
} 
  

如果问题是杰米说，这的ExecuteScalar是返回null，越来越一轮的最简单的方法是将其转换为可空int和使用空合并运算符：

 回归（INT？）cmd.ExecuteScalar（）？ 0; 
  

What code I should add to accept null from WHERE statement.

{
    int numApprovals = 0;
    string sql = "SELECT COUNT(Type) AS OpenforApproval " +
       "FROM dbo.LeaveRequest " +
       "WHERE Type IN (2, 3, 4, 5, 6, 8, 13, 14, 16, 22) " +
       "GROUP BY MgtApproval " +
       "HAVING MgtApproval IS NULL";
       //"SELECT COUNT(EffectiveDate) AS OpenforApproval FROM LeaveRequest GROUP BY TimeStampApproval HAVING (TimeStampApproval IS NULL)";

    using (cn = new SqlConnection(ConnectionString()))
    {
        cn.Open();
        using (cmd = new SqlCommand(sql, cn))
        {
            cmd.CommandType = CommandType.Text;
            numApprovals = (int)cmd.ExecuteScalar();
        }
    }

    return numApprovals;
}

解决方案

Just:

WHERE Type IN (2, 3, 4, 5, 6, 8, 13, 14, 16, 22) OR Type IS NULL

But I'm not at all convinced that's what you really want, or the cause of the problem.

If you're getting an exception in the C# code, it's not going to be from the where clause.

I'm concerned by the fact that your connection seems to be reusing an existing variable, by the way. Bad idea. It should almost certainly be a local variable. You can also make your code simpler by returning from the middle of it:

string sql = ...;

using (var cn = new SqlConnection(ConnectionString()))
{
    cn.Open();
    using (cmd = new SqlCommand(sql, cn))
    {
        cmd.CommandType = CommandType.Text;
        return (int) cmd.ExecuteScalar();
    }
}

If the problem is as Jamie says, that ExecuteScalar is returning null, the easiest way of getting round that is to cast it to a nullable int and use the null coalescing operator:

return (int?) cmd.ExecuteScalar() ?? 0;

这篇关于的ExecuteScalar后：对象没有设置等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！