locked
How to divide the value in grid view by 1000000(one million) and put a unit after it RRS feed

  • Question

  • User-1262319794 posted

    I am retrieving data from SQL Server to a grid view in ASP.Net. Below is the grid view after I retrieved the data from the database.

    Time   | City_1    | City_2   | City_3
    02/12  | 30000000  |12000000  |55000000000
    02/12  | 14000000  |1000000   |7200000000  

    So I want the values in the grid view to be divided by 1000000(one million) and put "mil" after it. After dividing it, I also want to put a comma in every thousandth place of the remainder value for the ease of the user. So my grid view will look like this after formatting.

    Time   | City_1 | City_2  | City_3
    02/12  | 30mil  | 12mil   | 55,000mil
    02/12  | 14mil  | 1mil    | 7,200mil  

    What I have tried:

    Below is the code how I am retrieving the data from SQL Server to the grid view.

    SqlConnection con = new SqlConnection("My Connection");
    
    string s = "My Stored Procedure";
    
    con.Open();
    SqlDataAdapter da = new SqlDataAdapter(s, con);
    DataSet ds = new DataSet();
    
    da.Fill(ds);
    
    gridView1.DataSource = ds;
    gridView1.DataBind();
    
    con.Close();

    Tuesday, August 7, 2018 11:36 PM

Answers

  • User-893317190 posted

    Hi tkhan17,

    You could format your data in gridview’s  OnRowDataBound event,this event is usually used to format data.

     Below is my code.

    <form id="form1" runat="server">
            <asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound" Font-Size="Large">
      
            </asp:GridView>
    </form>
    
    

    Code behind.

    protected void Page_Load(object sender, EventArgs e)
            {
            if(!IsPostBack)
                    
                {
               
                    using (SqlDataAdapter adapter = new SqlDataAdapter("select * from cities",    constr))
                    {
    
                        DataTable table = new DataTable();
                        adapter.Fill(table);
                        GridView1.DataSource = table;
                        GridView1.DataBind();
                    }
    
                }
                }
    
            }
    
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowType == DataControlRowType.DataRow)
                {
                   
                    DataRowView data =e.Row.DataItem as DataRowView;
           
                   e.Row.Cells[2].Text = ((long)data["City_1"] / 1000000).ToString("N0")+"mil";
                    e.Row.Cells[3].Text = ((long)data["City_2"]  / 1000000).ToString("N0")+"mil";
                    e.Row.Cells[4].Text = ((long)data["City_3"] / 1000000).ToString("N0")+"mil";
                }
            }
    

     For more information about formatting ,please refer

    https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings

    The result

    Best regards ,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 9, 2018 9:55 AM

All replies

  • User-1716253493 posted
    SELECT CITY1/1000000 as CITY1 FROM TBL
    <asp:BoundField DataField="CITY1" DataFormatString="{0:G} mil" HeaderText="CITY1" SortExpression="CITY1" />

    or you can use eval in itemtemplate

    <%# string.Format(Eval("city_1")/1000000,"{0:G}mil") %>

    Wednesday, August 8, 2018 12:27 AM
  • User-893317190 posted

    Hi tkhan17,

    You could format your data in gridview’s  OnRowDataBound event,this event is usually used to format data.

     Below is my code.

    <form id="form1" runat="server">
            <asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound" Font-Size="Large">
      
            </asp:GridView>
    </form>
    
    

    Code behind.

    protected void Page_Load(object sender, EventArgs e)
            {
            if(!IsPostBack)
                    
                {
               
                    using (SqlDataAdapter adapter = new SqlDataAdapter("select * from cities",    constr))
                    {
    
                        DataTable table = new DataTable();
                        adapter.Fill(table);
                        GridView1.DataSource = table;
                        GridView1.DataBind();
                    }
    
                }
                }
    
            }
    
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowType == DataControlRowType.DataRow)
                {
                   
                    DataRowView data =e.Row.DataItem as DataRowView;
           
                   e.Row.Cells[2].Text = ((long)data["City_1"] / 1000000).ToString("N0")+"mil";
                    e.Row.Cells[3].Text = ((long)data["City_2"]  / 1000000).ToString("N0")+"mil";
                    e.Row.Cells[4].Text = ((long)data["City_3"] / 1000000).ToString("N0")+"mil";
                }
            }
    

     For more information about formatting ,please refer

    https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings

    The result

    Best regards ,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 9, 2018 9:55 AM