Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
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))
 
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
 
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
danil33399
Edited 20-Nov-12 18:54pm
__TR__28.9K
v2
Comments
Ramanjaneya002 at 21-Nov-12 0:46am
   
In which line you are getting exception?
danil33 at 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()
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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))
 
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); 
   }
}
  Permalink  
v2
Comments
danil33 at 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 at 21-Nov-12 1:37am
   
did u execute your query in sql server?
 
does it returns any rows??
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  
v3
Comments
danil33 at 21-Nov-12 2:37am
   
Hi,
Actually the problem was with the query
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 1 and 06) AND (datepart(HH, Reading_Date)between 22 and 24) and Meter_Number=@meterid
Mohd. Mukhtar at 21-Nov-12 2:45am
   
Cool, So finally your issue has been resolved.

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 404
1 Nirav Prabtani 307
2 Abhinav S 210
3 PIEBALDconsult 160
4 Dave Kreskowiak 155
0 OriginalGriff 7,580
1 Sergey Alexandrovich Kryukov 6,806
2 Maciej Los 3,919
3 Peter Leow 3,693
4 CHill60 2,742


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 21 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100