Gridview showing sum of rows in one row

Hello, I have a gridview populated by the following sql query: When I run the query, I get the error that there is no row.datarow for "Amount". I think it is because I am using a "SUM" function in the sql query. I need to know how to structure the gridview to display the sumed amounts from columns "amount" and "amountbilled". Thank you:


SqlDataSource2.SelectCommand = "SELECT Billing.InvoiceID, Billing.OrdersID, Billing.ClientID, SUM(Billing.Amount), SUM(Billing.AmountPaid), Client.ClientID, Company.CompanyID, Client.ClientFirmName, Company.CompanyName, Client.ClientAddress, Company.CompanyAddress, Client.ClientCity, Company.CompanyCity, Client.ClientState, Company.CompanyState FROM Billing Left Outer Join Client ON ((Billing.ClientID = Client.ClientID) and (BillClient = 1)) Left Outer Join Company ON ((Billing.ClientID = Company.CompanyID) and (Billing.BillClient = 0)) Group by Billing.InvoiceID, Billing.OrdersID, Billing.ClientID, Client.ClientID, Company.CompanyID, Client.ClientFirmName, Company.CompanyName, Client.ClientAddress, Company.CompanyAddress, Client.ClientCity, Company.CompanyCity, Client.ClientState, Company.CompanyState"

My gridview code is:

</asp:GridView>
                        <asp:GridView id="GridView2" runat="server" ShowFooter="true" BackColor="White" AllowSorting="True" AutoGenerateColumns="False" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1" DataKeyNames="InvoiceID" DataSourceID="SqlDataSource2" GridLines="None" Width="864px">
                    <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
                    <Columns>
                       
                        <asp:TemplateField HeaderText="Client/Company Name" SortExpression="ClientID">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox35211" runat="server" Text='<%# Bind("ClientID") %>'></asp:TextBox>
                                <asp:TextBox ID="TextBox22511" runat="server" Text='<%# Bind("CompanyID") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:hyperlink ID="Label5234241" NavigateUrl='<%# Eval("clientID", "ClientAdminEdit.aspx?clientNumber={0}&type=" + Request.QueryString("type")) %>' runat="server" Text='<%# Bind("ClientFirmName") %>'></asp:hyperlink>
                                <asp:hyperlink ID="Label5523421" NavigateUrl='<%# Eval("companyID", "CompanyAdminEdit.aspx?companyNumber={0}&type=" + Request.QueryString("type")) %>' runat="server" Text='<%# Bind("CompanyName") %>'></asp:hyperlink>
                            </ItemTemplate>
                        </asp:TemplateField>
                       
                        <asp:TemplateField HeaderText="Address" SortExpression="ClientAddress">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox41" runat="server" Text='<%# Bind("ClientAddress") %>'></asp:TextBox>
                                <asp:TextBox ID="TextBox441" runat="server" Text='<%# Bind("CompanyAddress") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label51" runat="server" Text='<%# Bind("ClientAddress") %>'></asp:Label>
                                <asp:Label ID="Label551" runat="server" Text='<%# Bind("CompanyAddress") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="City" SortExpression="ClientCity">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox51" runat="server" Text='<%# Bind("ClientCity") %>'></asp:TextBox>
                                <asp:TextBox ID="TextBox541" runat="server" Text='<%# Bind("CompanyCity") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label61" runat="server" Text='<%# Bind("ClientCity") %>'></asp:Label>
                                <asp:Label ID="Label621" runat="server" Text='<%# Bind("CompanyCity") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="State" SortExpression="ClientState">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox61" runat="server" Text='<%# Bind("ClientState") %>'></asp:TextBox>
                                <asp:TextBox ID="TextBox651" runat="server" Text='<%# Bind("CompanyState") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label711" runat="server" Text='<%# Bind("ClientState") %>'></asp:Label>
                                <asp:Label ID="Label731" runat="server" Text='<%# Bind("CompanyState") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                       
                        <asp:TemplateField HeaderText="Amount Billed" SortExpression="Amount">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox3631" runat="server" Text='<%# Bind("Amount") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label36771" runat="server" Text='<%# Bind("Amount") %>'></asp:Label>
                            </ItemTemplate>
                           
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Amount Paid" SortExpression="AmountPaid">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox9331" runat="server" Text='<%# Bind("AmountPaid") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label1321" runat="server" Text='<%# Bind("AmountPaid") %>'></asp:Label>
                            </ItemTemplate>
                           
                        </asp:TemplateField>                        
                                 
                        <asp:HyperLinkField HeaderText="Order #" DataTextField="OrdersID" DataNavigateUrlFields="OrdersID"  DataNavigateUrlFormatString="Control_Page_Copy2.aspx?NewJobNumber={0}"/>
                       
                       
                    </Columns>
                    <RowStyle BackColor="#DEDFDE" ForeColor="Black" />
                    <SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
                    <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
                </asp:GridView>
LVL 1
mlg101Asked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
SUM(Billing.AmountPaid) as Amount   , Make this change

SqlDataSource2.SelectCommand = "SELECT Billing.InvoiceID, Billing.OrdersID, Billing.ClientID, SUM(Billing.Amount), SUM(Billing.AmountPaid) as Amount, Client.ClientID, Company.CompanyID, Client.ClientFirmName, Company.CompanyName, Client.ClientAddress, Company.CompanyAddress, Client.ClientCity, Company.CompanyCity, Client.ClientState, Company.CompanyState FROM Billing Left Outer Join Client ON ((Billing.ClientID = Client.ClientID) and (BillClient = 1)) Left Outer Join Company ON ((Billing.ClientID = Company.CompanyID) and (Billing.BillClient = 0)) Group by Billing.InvoiceID, Billing.OrdersID, Billing.ClientID, Client.ClientID, Company.CompanyID, Client.ClientFirmName, Company.CompanyName, Client.ClientAddress, Company.CompanyAddress, Client.ClientCity, Company.CompanyCity, Client.ClientState, Company.CompanyState"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.