CODEWITHSUNDEEP
c#sqlasp.netsql-serverstored-procedures
rickyProgrammer
rickyProgrammer

Reputation: 1167

Format Numbers to Money in ASP.NET Gridview using SQL Stored Procedure as Source

I am using a SQL Server stored procedure as source of my gridview when it loads. The query has column that results to numbers (money), I want the numerical outputs to have comma when it loads in gridview. Originally in SQL, The query result has no any formatting, as I have tried to practice putting the formatting in the front-end.

Before, I am able to achieve that by putting a boundfield in the gridview definition, and then put attribute name datafield, giving its value based on the name of the column or alias in SQL. However in my case this time, the stored procedure column has a dynamic alias, because it is resulting a column with a specific date (it is changing depending on the days of the week).

Stored procedure goes something like this (Last part)

 set @query = 'select locationd, Name ' +
        ',(SUM(Day1)) as '+ '[' + @sdateVC1 + ']'  +
        ',(SUM(Day2)) as '+ '[' + @sdateVC2 + ']'  +
        ',(SUM(Day3)) as '+ '[' + @sdateVC3 + ']'  +
        ',(SUM(Day4)) as '+ '[' + @sdateVC4 + ']'  +
        ',(SUM(Day5)) as '+ '[' + @sdateVC5 + ']'  +
        ',(SUM(Day6)) as '+ '[' + @sdateVC6 + ']'  +
        ',(SUM(Day7)) as '+ '[' + @sdateVC7 + ']'  +
        ',(SUM(ISNULL(Day1,0)) + SUM(ISNULL(Day2,0)) + SUM(ISNULL(Day3,0)) + SUM(ISNULL(Day4,0)) + SUM(ISNULL(Day5,0)) + SUM(ISNULL(Day6,0)) + SUM(ISNULL(Day7,0)))as ''TOTAL'' ' +  
        ',RANK() over (partition by locationd order by ((SUM(ISNULL(Day1,0)) + SUM(ISNULL(Day2,0)) + SUM(ISNULL(Day3,0)) + SUM(ISNULL(Day4,0)) + SUM(ISNULL(Day5,0)) + SUM(ISNULL(Day6,0)) + SUM(ISNULL(Day7,0)))) DESC) as ''Ranking'' ' +    
        'from #newestWSR4 ' +
        'group by locationd,  Name ' +
        --'order by locationd, Name ' 
         'union all' +
          ' select ''-'', ''TOTAL'',  sum(day1),  sum(day2),  sum(day3), sum(day4), sum(day5),  sum(day6), sum(day7), (SUM(ISNULL(Day1,0)) + SUM(ISNULL(Day2,0)) + SUM(ISNULL(Day3,0)) + SUM(ISNULL(Day4,0)) + SUM(ISNULL(Day5,0)) + SUM(ISNULL(Day6,0)) + SUM(ISNULL(Day7,0))), ''-''    ' +
          'from #newestWSR4 '

The gridview code in ASP.net is like this

<asp:GridView ID="grdWSR" 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  DataField="locationd" />
     <asp:BoundField  DataField="name"   />
     <asp:BoundField Datafield=""  DataFormatString="{0:#,##0.00;(#,##0.00);0}" />
   </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>

Please take note of this part in the code above

 <asp:BoundField  DataField="locationd" />
 <asp:BoundField  DataField="name"   />
 <asp:BoundField  Datafield="" DataFormatString="{0:#,##0.00;(#,##0.00);0}" />

As you can see, I've put only three boundfield for testing purposes. In there I don't know what to put in the datafield value since , the alias column in SQL stored procedure is dynamically changing or not fixed, unlike with the first two boundfields (which column names / alias in SQL are fixed)

Is this case possible to be answered? or should I consider now doing the solution on back-end (SQL) to achieve my desired result?

Any help will be greatly appreciated!

Upvotes: 3

Views: 1700

Answers (2)

VISHMAY
VISHMAY

Reputation: 709

i suggest you don't do any formatting thing in sp, it will overload your system in terms of optimization,

prefer c# instead

    DataTable dt=mySP();
    for(i=0;i<dt.Rows.Count;i++)
    {
    dt.Rows[i][10]=Convert.ToDouble(dt.Rows[i][10]).ToString("#,###,###,###,###,##0.00");
    }
    GridView1.DatSource=dt;

Upvotes: 1

CoOl
CoOl

Reputation: 2797

try formatting the string like this in your stored procedure:

CONVERT(varchar, CAST(SUM(Day1) AS money), 1)

or like this -

FORMAT(SUM(Day1),'###,###,###')

Upvotes: 0

Related Questions