Populate HTML Table from Database display thousand separator in ASP.Net using C# and VB.Net

Last Reply one year ago By kalpesh

Posted one year ago

Mr.Mudassar

I am Using your code for display data in HTML table. It is working fine.

Now Issue is How to display the number with Separator like

10,000.00 or 1,23,5005.00 ?

Pls advise me

Thank you in advance

Maideen

Below is code

 TOTAL, TARGETAMT ....

            Dim query As String = String.Format("SELECT SALESEXE,SALESEXENAME,SUM(SUBTOTAL) AS TOTAL,TARGETAMT,SUM(BalanceAchieve) AS [BAL ACHV],(Achv_Presentage) AS [ACHV%] " & _
                                                "FROM [dbo].[vw_WR_TargetAchieve_CPanel] WHERE yearNo = '" & DateTime.Now.Year & _
                                                "' GROUP BY YEARNO,SALESEXE,SALESEXENAME,TARGETAMT,Achv_Presentage ORDER BY SALESEXE")
            Dim dt As DataTable = GetData(query)

            Dim html As New StringBuilder()
            html.Append("<table border = '1'>")
            html.Append("<tr>")
            For Each column As DataColumn In dt.Columns
                html.Append("<th>")
                html.Append(column.ColumnName)
                html.Append("</th>")
            Next
            html.Append("</tr>")

            For Each row As DataRow In dt.Rows
                html.Append("<tr>")
                For Each column As DataColumn In dt.Columns
                    html.Append("<td>")
                    html.Append(row(column.ColumnName))
                    html.Append("</td>")
                Next
                html.Append("</tr>")
            Next
            html.Append("</table>")
            PlaceHolder3.Controls.Add(New Literal() With {.Text = html.ToString()})
            cmd = Nothing
            conn.Close()

 

You are viewing reply posted by: kalpesh one year ago.
Posted one year ago Modified on one year ago

You need to check the column name which contains the amount value and then have to apply ToString("N") for its value it will format into the thousand separator values.

also if your amount column values are multiple then just check the multiple column name and convert the column value to Decimal and apply ToString("N").

Refer below sample code for your reference.

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:PlaceHolder  ID = "PlaceHolder1" runat="server" />  
    </form>
</body>
</html>

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        //Populating a DataTable from database.
        DataTable dt = this.GetData();

        //Building an HTML string.
        StringBuilder html = new StringBuilder();

        //Table start.
        html.Append("<table border = '1'>");

        //Building the Header row.
        html.Append("<tr>");
        foreach (DataColumn column in dt.Columns)
        {
            html.Append("<th>");
            html.Append(column.ColumnName);
            html.Append("</th>");
        }
        html.Append("</tr>");

        //Building the Data rows.
        foreach (DataRow row in dt.Rows)
        {
            html.Append("<tr>");
            foreach (DataColumn column in dt.Columns)
            {
                html.Append("<td>");
                if (column.ColumnName.ToUpper() == "TOTALAMOUNT" || column.ColumnName.ToUpper() == "OTHERCOLUMNNAME")
                {
                    html.Append(Convert.ToDecimal(row[column.ColumnName]).ToString("N"));
                }
                else
                {
                    html.Append(row[column.ColumnName]);
                }
                html.Append("</td>");
            }
            html.Append("</tr>");
        }

        //Table end.
        html.Append("</table>");

        //Append the HTML string to Placeholder.
        PlaceHolder1.Controls.Add(new Literal { Text = html.ToString() });
    }
}

private DataTable GetData()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT [OrderId],[CustomerName],[OrderDate],[TotalAmount] FROM [OrderDetails]"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        'Populating a DataTable from database.
        Dim dt As DataTable = Me.GetData()

        'Building an HTML string.
        Dim html As New StringBuilder()

        'Table start.
        html.Append("<table border = '1'>")

        'Building the Header row.
        html.Append("<tr>")
        For Each column As DataColumn In dt.Columns
            html.Append("<th>")
            html.Append(column.ColumnName)
            html.Append("</th>")
        Next
        html.Append("</tr>")

        'Building the Data rows.
        For Each row As DataRow In dt.Rows
            html.Append("<tr>")
            For Each column As DataColumn In dt.Columns
                html.Append("<td>")
                If column.ColumnName.ToUpper() = "TOTALAMOUNT" Or column.ColumnName.ToUpper() = "OTHERCOLUMNNAME" Then
                    html.Append(Convert.ToDecimal(row(column.ColumnName)).ToString("N"))
                Else
                    html.Append(row(column.ColumnName))
                End If
                html.Append("</td>")
            Next
            html.Append("</tr>")
        Next

        'Table end.
        html.Append("</table>")

        'Append the HTML string to Placeholder.
        PlaceHolder1.Controls.Add(New Literal() With { _
          .Text = html.ToString() _
        })
    End If
End Sub

Private Function GetData() As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand("SELECT [OrderId],[CustomerName],[OrderDate],[TotalAmount] FROM [OrderDetails]")
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Using
End Function

Screenshot