数据为空.不能在Null值上调用此方法或属性. [英] Data is Null. This method or property cannot be called on Null values.

问题描述

IAM试图根据月份，年份和ID从数据库(SQL Server)中获取数据.
我的存储过程如下:-

Hi,
Iam trying to fetch data from database(sql server) based on a month,year and id.
My storedprocedure is as follows:-

SELECT SUM(kWDiff),SUM(kVADiff)from dbo.Socket_Final_Insert_Meter_Detail where((datepart(MM, Reading_Date)=@month and datepart(YYYY, Reading_Date)=@year)and(datepart(HH, Reading_Date)between 22 and 06)and Meter_Number=@meterid AND (datepart(HH, Reading_Date)IS NULL))

我用于调用SP的c#代码为:-

And my c# code for calling the SP is:-

#region OffPeakkVA_kW
       public Invoice OffPeakkVA_kW(int Meter_Number, int month, int year)
       {
           try
           {
               Invoice invoice = new Invoice();


               SqlDataReader rdr = null;
               _sqlConnection.Open();
               SqlCommand sql_Cmd = new SqlCommand("Off_peak_kva_kw_calculation", _sqlConnection);
               sql_Cmd.CommandType = CommandType.StoredProcedure;
               sql_Cmd.Parameters.Add(new SqlParameter("@meterid", Meter_Number));
               sql_Cmd.Parameters.Add(new SqlParameter("@month", month));
               sql_Cmd.Parameters.Add(new SqlParameter("@year", year));

               rdr = sql_Cmd.ExecuteReader();
               //int count = rdr.FieldCount;

               while (rdr.Read())
               {

                   invoice.KW_offpeak = rdr.GetDouble(0);
                   invoice.KVA_offpeak = rdr.GetDouble(1);

               }
               sql_Cmd.Dispose();
               _sqlConnection.Close();
               return invoice;

           }
           catch (Exception ex)
           {

               throw ex;
           }
       }
       #endregion

但是在执行时会引发异常''数据为空.无法对Null值调用此方法或属性."但是我的数据库包含足够的值，但仍引发异常.如何解决此问题?

But while executing it is throwing exception ''Data is Null. This method or property cannot be called on Null values.''.But my databse contains sufficient values,still it is throwing the exception.How to resolve this issue?

推荐答案

您的查询应如下所示:

your query should be like below :

SELECT	SUM(KWDIFF), SUM(KVADIFF) 
FROM	DBO.SOCKET_FINAL_INSERT_METER_DETAIL
WHERE	((DATEPART(MM, READING_DATE)=@MONTH AND	DATEPART(YYYY, READING_DATE)=@YEAR)
AND	(DATEPART(HH, READING_DATE) BETWEEN 22 AND 06)
AND	METER_NUMBER=@METERID
AND	(DATEPART(HH, READING_DATE) IS NOT NULL))

并且您的C#代码应如下所示:

and your c# code should be like below :

if (rdr.HasRows)
{
   while (rdr.Read())
   { 
      invoice.KW_offpeak = rdr.GetDouble(0);
      invoice.KVA_offpeak = rdr.GetDouble(1); 
   }
}

丹尼尔，

您的选择语句不正确.

从dbo中选择SELECT SUM(kWDiff)，SUM(kVADiff).Socket_Final_Insert_Meter_Detail
其中((datepart(MM，Reading_Date)= @ month
和datepart(YYYY，Reading_Date)= @ year)
和(datepart(HH，Reading_Date)在22到06之间)
和Meter_Number = @ meterid
AND(datepart(HH，Reading_Date)IS NULL))

在上面的查询中，您使用And条件两次检查Reading_Date的HH，首先使用(22和06)之间的条件进行检查，然后使用null检查其是否相同.并且两个条件不能同时成立.因此，您不会获得任何结果.

在sql server中以适当的值执行此查询，并检查是否获得任何结果.

还要如下修改C#代码段.

Hi Danil,

Your select statement is incorrect.

SELECT SUM(kWDiff), SUM(kVADiff) from dbo.Socket_Final_Insert_Meter_Detail
where ((datepart(MM, Reading_Date)=@month
and datepart(YYYY, Reading_Date)=@year)
and (datepart(HH, Reading_Date) between 22 and 06)
and Meter_Number=@meterid
AND (datepart(HH, Reading_Date) IS NULL))

In the above query you are checking HH of Reading_Date with And condition two time first with between (22 and 06) and then you are checking same with is null. And both condition can not be true at the same time. And hence you are not getting any result.

Execute this query in sql server and with appropriate value and check if you are getting any resule.

Also modify C# code snippet as below.

if(rdr.HasRow)
{
  while (rdr.Read())
  { 
    invoice.KW_offpeak = rdr.GetDouble(0);
    invoice.KVA_offpeak = rdr.GetDouble(1); 
  }
}

希望对您有帮助.

Hope this will help you.

这篇关于数据为空.不能在Null值上调用此方法或属性.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！