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

jazzydonald · February 16th, 2009, 01:40 AM

How exactly do i do that or where should i put the code to do that. Here's my code;

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.CommandText = "sp_GetSumTotals"
cmd.Connection = conn
'cmd.CommandType = CommandType.Text
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Employee_no", Session("Employee_no"))
conn.Open()
cmd.ExecuteNonQuery()
Dim MyReader As SqlDataReader
MyReader = cmd.ExecuteReader
'MyReader.Read()
If MyReader.Read Then
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
EndIf
conn.Close()
cmd.Dispose()
MyReader.Close()

Thanks!

Lee Dumond · February 16th, 2009, 01:48 AM

You set it up in the .aspx page declaratively in the markup, right inside the GridView itself, not in the code behind.

Code:

     <EmptyDataTemplate>
          Put whatever you want to show here when there are no records.
     </EmptyDataTemplate>

jazzydonald · February 16th, 2009, 03:25 AM

Thanks Lee Dumond for your generous reply, but no luck pal! I put the emptyTemplate tags in my gridview but still am getting the error "Object reference not set to an instance of an object", NULL referenceException was unhandeled by user code"

When I debug my code i found out that when my sql returns no records the grid can not even find the controls but when there are records returned thats when the grid can find the controls. I tried to put the default values when no records are found using the ISNULL(filed,0.00) but the grid can not find the controls when no records returned. I really need some help pal!!

Here is the code for my gridview:

Code:

 
<asp:GridView ID="GridViewloans" runat="server" AutoGenerateColumns="False" 
BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" 
CellPadding="4" Width="607px" ShowFooter="True">
<Columns>
<asp:BoundField DataField="Loan_description" HeaderText="Loan Type" />
<asp:BoundField DataField="Date_taken" HeaderText="Date Taken" 
DataFormatString="{0:d}" />
<asp:TemplateField HeaderText="Amount Taken">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("amount_taken") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotAmountTaken" runat="server" Font-Bold="True" 
Font-Names="verdana" ForeColor="Black"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" 
Text='<%# Bind("amount_taken", "{0:n2}") %>'></asp:Label>
</ItemTemplate>
<FooterStyle HorizontalAlign="Right" />
<ItemStyle HorizontalAlign="Right" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Monthly Installment">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" 
Text='<%# Bind("Loan_Installment") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotMonthlyInstallment" runat="server" Font-Bold="True" 
Font-Names="verdana" ForeColor="Black"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" 
Text='<%# Bind("Loan_Installment", "{0:n2}") %>'></asp:Label>
</ItemTemplate>
<FooterStyle HorizontalAlign="Right" />
<ItemStyle HorizontalAlign="Right" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Loan Balance">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("loan_balance") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotLoanBalance" runat="server" Font-Bold="True" 
ForeColor="Black" style="text-align: right"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" 
Text='<%# Bind("loan_balance", "{0:n2}") %>'></asp:Label>
</ItemTemplate>
<FooterStyle HorizontalAlign="Right" />
<ItemStyle HorizontalAlign="Right" />
</asp:TemplateField>
</Columns>
<EmptyDataTemplate>
No Pending Loans!!!
</EmptyDataTemplate>
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
<PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
<RowStyle BackColor="White" ForeColor="#330099" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
</asp:GridView>

gbianchi · February 16th, 2009, 07:46 AM

Hi there..

Lee tells you what you need.
In your code, check for the empty reader (or maybe the reader is returning a null value?).

If this happens, don't do anything in the code, all will be handled by the empty data template. You insert there the labels and they will appears exactly the way you insert them.

jazzydonald · February 16th, 2009, 09:33 AM

Finally!!! I got it running thanks to you all!! here is what i did;
I put the empty template tas in my grid view like this;

Code:

 
<EmptyDataTemplate>
<asp:Label ID="Label4" runat="server" Text="No Data Found"></asp:Label>
</EmptyDataTemplate>

then in my code i had to check if my reader is returning null values, if it does i did nothing (as the EmptyDataTemplate would take care of this) else i continue to execut my code.

Code:

If MyReader.Read() Then
    'I continue to execute my code here
End if

Thats all!!!
Thanks Everyone for your generous help! Cheers!!