problem in displaying null value to the control in the footer template of gridview

jazzydonald · February 13th, 2009, 06:27 AM

Hi folks!
I am developing a web application in which I have a gridview in which there is footer template where i've put 3 labels that displays the sum of the values in their corresponding columns after retrieving data from the database. The sum of values are displayed correctly when there's some records retrieved from the database, but it doesnt work when there are no records retrieved from the db i get an erros tha says

Object reference not set to an instance of an object.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Here is my code:

Sub GetTotals()
Dim conn As SqlConnection = New SqlConnection(strConn)
Dim cmd As SqlCommand = New SqlCommand
cmd.CommandText = "select ISNULL(SUM(amount_taken),0.00) as 'Amount_Taken', ISNULL(SUM(loan_Installment),0.00) as 'loan_Installment',ISNULL(SUM(loan_balance),0.00) as 'loan_balance' from Loans where employee_no=@Employee_no"
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@Employee_no", Session("Employee_no"))
conn.Open()
cmd.ExecuteNonQuery()
Dim MyReader As SqlDataReader
MyReader = cmd.ExecuteReader
MyReader.Read()

'The error occurs here when trying to get the controls in the footer template
Dim Total_AmountTaken As Label = CType(GridViewloans.FooterRow.FindControl("lblTotAmountTaken"), Label)
Dim Total_LoanInstallment As Label = CType(GridViewloans.FooterRow.FindControl("lblTotMonthlyInstallment"), Label)
Dim Total_LoanBalance As Label = CType(GridViewloans.FooterRow.FindControl("lblTotLoanBalance"), Label)

Total_LoanInstallment.Text = String.Format("{0:#,###.00}", MyReader("Loan_Installment")).ToString
Total_LoanBalance.Text = String.Format("{0:#,###.00}", MyReader("Loan_Balance")).ToString
Total_AmountTaken.Text = String.Format("{0:#,###.00}", MyReader("amount_taken")).ToString
conn.Close()
cmd.Dispose()
MyReader.Close()
EndSub

As you can from the sql statement i tried to use the ISNULL() just the default values when the it is null but still it does not work.

I hope i've describe my problem clearly
Please somebody help me.

February 13th, 2009, 07:11 AM

Try like this

Code:

Dim MyReader As SqlDataReader
MyReader = cmd.ExecuteReader

 
'The error occurs here when trying to get the controls in the footer template
Dim Total_AmountTaken As Label = CType(GridViewloans.FooterRow.FindControl("lblTotAmountTaken"), Label)
Dim Total_LoanInstallment As Label = CType(GridViewloans.FooterRow.FindControl("lblTotMonthlyInstallment"), Label)
Dim Total_LoanBalance As Label = CType(GridViewloans.FooterRow.FindControl("lblTotLoanBalance"), Label)

If MyReader.Read() Then
Total_LoanInstallment.Text = String.Format("{0:#,###.00}", MyReader("Loan_Installment")).ToString
Total_LoanBalance.Text = String.Format("{0:#,###.00}", MyReader("Loan_Balance")).ToString
Total_AmountTaken.Text = String.Format("{0:#,###.00}", MyReader("amount_taken")).ToString
End If

conn.Close()
cmd.Dispose()
MyReader.Close()

jazzydonald · February 13th, 2009, 08:03 AM

Thanks for your generous reply but your solution did no work!! am stillgetting th error "Object reference not set to an instance of an object" , "Null reference exception was unhandled by the user code"

Thanks

gbianchi · February 13th, 2009, 08:07 AM

If the error occurs when you are trying to get the footer controls, maybe they are not rendered yet??? can you debug your code and see if the grid can really find the controls??

jazzydonald · February 13th, 2009, 08:33 AM

thanks gbiachi for the reply. I debuged the code and find out that the grid can only find the controls only when my datareader can read data from the db i.e when records are found. but the grid can not find the controls when no records are returned after exeuting my command.

gbianchi · February 13th, 2009, 08:42 AM

Ok, we found the problem them.

to try to solve it.. what if you invert the isnull in the SQL ?? (use SUM(ISNULL(field,0)) )

jazzydonald · February 13th, 2009, 09:39 AM

Still It does not work when i try to invert the sql as you suggested, I get the same error.
What i was thinking is to find a way of assigning 0 value or default values in the labels in the footer template of the gridview if no records are returned from the database.

gbianchi · February 13th, 2009, 09:44 AM

Are you using sql server or access?

jazzydonald · February 13th, 2009, 09:48 AM

am using sql server 2008.

Lee Dumond · February 13th, 2009, 10:44 AM

This really has nothing to do with how you are retrieving data from the database. It has to do with the fact that the footer row will not render when there are no records found.

You need to set up an EmptyDataTemplate. The GridView shows the EmptyDataTemplate whenever no data is returned. Then, you can put anything you want in there. You could even hard-code "$0.00" labels if you want to.