Data is Null. This method or property cannot be called on Null values.

Question

3.00/5 (2 votes)

See more:

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

SQL

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

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

C#

#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

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?

Posted 20-Nov-12 18:31pm

danil33

Updated 20-Nov-12 18:54pm

__TR__

v2

Add a Solution

Comments

[no name] 21-Nov-12 0:46am

In which line you are getting exception?

danil33 21-Nov-12 0:49am

It is not entering into the while loop
while (rdr.Read())
{

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

}
Before while it is executing and then jumping to the catch()

2 solutions

Solution 1

your query should be like below :

SQL

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

and your c# code should be like below :

C#

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

Posted 20-Nov-12 18:58pm

Bhushan Shah1988

Updated 20-Nov-12 19:09pm

v2

Comments

danil33 21-Nov-12 1:05am

Hi Bhushan Shah,
Thanks for your reply.After checking HasRows,it is showing 'true',still it is not entering into the while loop and throwing the same exception.
Is there any problem with the query?

Bhushan Shah1988 21-Nov-12 1:37am

did u execute your query in sql server?

does it returns any rows??

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mohd. Mukhtar · Accepted Answer · 2012-11-20T19:02:00

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.

C#

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

Hope this will help you.