C# - Loading data from stored procedure to gridview dynamically

Question

I am using stored procedure to load data in my gridview. Based on user selection, He /she should be able to view the data in asp.net gridview either the itemized , detailed sales figure of all tenants in one location OR the overall sum total sales of that location.

This would mean changing of field column.

For PER DETAILED:

tenantcode tenantname, location, Gross sales

enter image description here

For TOTAL per location:

Location, Gross sales (using group by clause to achieve this)

enter image description here

IN STORED PROCEDURE , everything works fine of course, but in asp.net, per detailed option is working, but when I choose the next option which is over all sales per location it produces this error:

A field or property with the name 'tenantcode' was not found on the selected data source.

Markup:

<asp:GridView ID="grdMarketingReport1" runat="server" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical" Font-Size="Smaller" EmptyDataText="No Records Found" ShowHeaderWhenEmpty="True" Width="100%" AutoGenerateColumns="false">
    <EmptyDataRowStyle BackColor="white" ForeColor="black" />
    <EmptyDataTemplate>No Data Found.</EmptyDataTemplate>
    <AlternatingRowStyle BackColor="White" />
    <Columns>
        <asp:BoundField HeaderText="RP Code" DataField="tenantcode"  />
        <asp:BoundField HeaderText="Retail Partner" DataField="name" />
        <asp:BoundField HeaderText="Location" DataField="locationd" />
        <asp:BoundField HeaderText="Gross Sales" DataField="gs"  />
    </Columns>
    <FooterStyle BackColor="#CCCC99" />
    <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" Height="25px" />
    <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
    <RowStyle BackColor="#F7F7DE" />
    <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#FBFBF2" />
    <SortedAscendingHeaderStyle BackColor="#848384" />
    <SortedDescendingCellStyle BackColor="#EAEAD3" />
    <SortedDescendingHeaderStyle BackColor="#575357" />
</asp:GridView>

I am actually thinking of creating two different gridview to cater my needs, but that's the least option unless it is the only way. COULD IT BE DONE IN ONE GRIDVIEW ONLY?

STORED PROC (PART OF THE ACTUAL SP)

if (@RP = 'ALL' and @Location = 'ALL' and @Business = 'ALL')
begin




        --step 1: get the sales per date    
           SELECT a.tenantcode ,  b.name , a.location , c.locationd , d.[desc] as 'Business' , a.date , a.gsc, b.sdate , b.cdate 
           into #Sample5
           FROM DAILYMOD a INNER JOIN TENANT b on a.tenantcode = b.tenantcode 
                           INNER JOIN LOCATION c on b.location = c.location 
                           INNER JOIN BUSINESS d on b.business = d.code 

              WHERE ((a.date between @DateFrom1  and @DateTo1 )
                  or (a.date between @DateFrom2  and @DateTo2 )
                  or (a.date between @dateFromLY  and @dateToLY))


           ORDER BY a.location , a.tenantcode , a.date



         --step 2: group the gsc per date ranges
           select tenantcode , name, location,  locationd, business , sdate, cdate,

           SUM(case when date between @DateFrom1  and @DateTo1  then [gsc] else 0 end) 'date1',
           SUM(case when date between @DateFrom2  and @DateTo2   then [gsc] else 0 end) 'date2',
           SUM(case when date between @dateFromLY  and @dateToLY then [gsc] else 0 end) 'dateLY'
           from #Sample5
           GROUP BY tenantcode , name, location, locationd , business, sdate, cdate





end




else
begin

        --step 1: get the sales per date    
           SELECT a.tenantcode ,  b.name , a.location , c.locationd , d.[desc] as 'Business' , a.date , a.gsc, b.sdate , b.cdate 
           into #Sample7
           FROM DAILYMOD a INNER JOIN TENANT b on a.tenantcode = b.tenantcode 
                           INNER JOIN LOCATION c on b.location = c.location 
                           INNER JOIN BUSINESS d on b.business = d.code 

              WHERE ((a.date between @DateFrom1  and @DateTo1  )
                  or (a.date between @DateFrom2  and @DateTo2 )
                  or (a.date between @dateFromLY  and @dateToLY ))


           ORDER BY a.location , a.tenantcode , a.date



         --step 2: group the gsc per date ranges
           select  location, locationd, 

            SUM(case when date between @DateFrom1  and @DateTo1  then [gsc] else 0 end) 'date1',
           SUM(case when date between  @DateFrom2  and @DateTo2  then [gsc] else 0 end) 'date2',
           SUM(case when date between @dateFromLY  and @dateToLY then [gsc] else 0 end) 'dateLY'
           from #Sample7
           GROUP BY  location, locationd 




end

Hi! Is it really necessary to yell? And also include the whole gridview markup, is everything here related to the question? — abatishchev
– abatishchev, Commented Mar 11, 2015 at 4:44
You don't need styles actually, just columns. I'd remove everything unrelated to help a reader to focus on the binding problem. — abatishchev
– abatishchev, Commented Mar 11, 2015 at 4:50
Your sp, what is its underlying query? What is output, its fields? — abatishchev
– abatishchev, Commented Mar 11, 2015 at 4:52

Irfan TahirKheli · Accepted Answer · 2015-03-11 09:38:46Z

1

In your store procedure else part step 2 does not return tenantcode

  --step 2: group the gsc per date ranges
           select  location, locationd, 

            SUM(case when date between @DateFrom1  and @DateTo1  then [gsc] else 0 end) 'date1',
           SUM(case when date between  @DateFrom2  and @DateTo2  then [gsc] else 0 end) 'date2',
           SUM(case when date between @dateFromLY  and @dateToLY then [gsc] else 0 end) 'dateLY'
           from #Sample7
           GROUP BY  location, locationd

try to return that column too and check it.

UPDATE:

--step 2: group the gsc per date ranges
               select '' AS tenantcode ,location, locationd, 

                SUM(case when date between @DateFrom1  and @DateTo1  then [gsc] else 0 end) 'date1',
               SUM(case when date between  @DateFrom2  and @DateTo2  then [gsc] else 0 end) 'date2',
               SUM(case when date between @dateFromLY  and @dateToLY then [gsc] else 0 end) 'dateLY'
               from #Sample7
               GROUP BY  location, locationd

edited Mar 11, 2015 at 9:38

answered Mar 11, 2015 at 5:39

Irfan TahirKheli

3,6621 gold badge25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

rickyProgrammer Over a year ago

that is intentional to group or to get the total sales of one location. please see the table image.

Irfan TahirKheli Over a year ago

yes but in your gridview you have refrence to this column and datasource doesn't have this column thats why it is throwing exception. @rickyProgrammer

Irfan TahirKheli Over a year ago

you can add "tenantcode" to your select clause and groupby clause. it will still group it. @rickyProgrammer

rickyProgrammer Over a year ago

yes, but the result is not what I want to achieve, when I include the tenantcode in the select clause, also in group by clause, the output will still be detailed per tenant and not consolidated sales per location. (Like what I have illustrated above) in the table images

Irfan TahirKheli Over a year ago

ok.. can you try my update query by replacing your query. @rickyProgrammer

|

Collectives™ on Stack Overflow

C# - Loading data from stored procedure to gridview dynamically

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related