Solved

Gridview showing sum of rows in one row

Posted on 2007-11-18
1
1,867 Views
Last Modified: 2008-02-01
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>
0
Comment
Question by:mlg101
1 Comment
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 20310340
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now