Get database values into HTML

Question

I am developing an app written in C# .Net 4.0 -- basically I have a stored proc that returns my orders from my database...I am binding those orders to a GridView --

CODEBEHIND

protected void Page_Load(object sender, EventArgs e)
{
    // connect to the database
    this.DbConnection();
    // open the connection
    sqlConn.Open();
    // call the stored procedure
    sqlCmd = new SqlCommand("usp_myorders", sqlConn);
    sqlCmd.CommandType = CommandType.StoredProcedure;
    // add parameter 
    sqlCmd.Parameters.Add("@startdate", SqlDbType.DateTime);
    sqlCmd.Parameters["@startdate"].Value = "06/01/2015 18:00:00"; 
    sqlRdr = sqlCmd.ExecuteReader();

    // get the count
    // @TODO: This is a very expensive method -- there must be a better way
    int count = 0;
    if (sqlRdr.HasRows)
    {
        while (sqlRdr.Read())
        {
            ++count;
        }
    }
    // close the connection
    sqlConn.Close();

    // reopen the connection, to create the grid
    sqlConn.Open();
    // bind data to the grid
    grid1.EmptyDataText = "No Record(s) Found...";
    grid1.DataSource = sqlCmd.ExecuteReader();
    grid1.DataBind();
    MakeGridViewPrinterFriendly(grid1);
    // close the connection
    sqlConn.Close();
    //Get the number of rows returned, and then assign it to the Label control
    lblRowCount.Text = count.ToString() + " Record(s) Found...";
    lblRowCount2.Text = count.ToString() + " Record(s) Found...";
}

On the HTML side I have the following code

HTML CODE

<asp:Label ID="lblRowCount" runat="server" Text="Label"></asp:Label>
        <!-- Table combined Styles -->
        <asp:GridView ID="grid1" runat="server" class="striped sortable lcpa_ord_nonprintable" AutoGenerateColumns="False" ShowHeaderWhenEmpty="true">
            <Columns>
                <asp:BoundField DataField="fname" HeaderStyle-HorizontalAlign="Left" HeaderText="First Name" SortExpression="fname" ReadOnly="true" />
                <asp:BoundField DataField="lname" HeaderStyle-HorizontalAlign="Left" HeaderText="Last Name" SortExpression="lname" ReadOnly="true" />
                <asp:BoundField DataField="item" HeaderStyle-HorizontalAlign="Center" HeaderText="Order" SortExpression="item" ReadOnly="true" />
                <asp:BoundField DataField="num_seats_pur" HeaderStyle-HorizontalAlign="Center" HeaderText="Quantity" SortExpression="num_seats_pur" ReadOnly="true" />

                <asp:TemplateField HeaderText="Action" HeaderStyle-HorizontalAlign="Center">
                    <ItemTemplate>
                        <a class="btn green small inline" href="#Order11">
                            <i class="fa fa-ticket"></i> View Order</a>
                        <a class="btn green small" href="#" onclick="printContent('Ticket11')">
                            <i class="fa fa-print"></i> Print</a>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView> 
        <asp:Label ID="lblRowCount2" runat="server" Text="Label"></asp:Label>

The piece that I am have difficulty with is here..

<a class="btn green small inline" href="#Order11">
    <i class="fa fa-ticket"></i> View Order</a>
        <a class="btn green small" href="#" onclick="printContent('Ticket11')">

I want to replace the href="#Order11" with the actual OrderID from the database and replace printContent('Ticket11') again with the actual OrderID from the database -- so it would read href="#Order12345" and printContent('Ticket12345')

Please keep in mind that there on average 30 - 50 orders in my table

Where I am stuck is I can't get the OrderIDs out of the database (in the CodeBehind) and into the HTML code block

" @TODO: This is a very expensive method -- there must be a better way" - Correct: select count(*) from mytable — Mitch Wheat
– Mitch Wheat, Commented May 29, 2015 at 0:28
Does your stored procedure return the OrderID? If not modify it so it does. — Jon P
– Jon P, Commented May 29, 2015 at 0:39
I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented May 29, 2015 at 0:42
Also look at DataSets for your data. It takes a couple of more line of code to fill it but it is a little more flexible than DataReader in this instance. You will be able to get a rowcount and bind the dataset to the GridView — Jon P
– Jon P, Commented May 29, 2015 at 0:45

Tran Nguyen · Accepted Answer · 2015-05-29 00:56:56Z

3

There are a few ways you can do this. Let's say your OrderID field in your database is orderId column.

1) You can use template data binding like this:

<asp:TemplateField HeaderText="Action" HeaderStyle-HorizontalAlign="Center">
                <ItemTemplate>
                    <a class="btn green small inline" href='#Order<%# Eval("orderId")%>'>
                        <i class="fa fa-ticket"></i> View Order</a>
                    <a class="btn green small" href="#" onclick="printContent('Ticket<%# Eval("orderId")%>')">
                        <i class="fa fa-print"></i> Print</a>
                </ItemTemplate>
            </asp:TemplateField>

2) You can use a server control and update it on the ItemDataBound event of the grid:

Template file:

<asp:GridView ID="grid1" runat="server" 
    class="striped sortable lcpa_ord_nonprintable" AutoGenerateColumns="False" 
    ShowHeaderWhenEmpty="true" onrowdatabound="grid1_RowDataBound">
    ...
    <asp:TemplateField HeaderText="Action" HeaderStyle-HorizontalAlign="Center">
        <ItemTemplate>
            <asp:HyperLink runat="server" ID="hlView" CssClass="btn green small inline">
                <i class="fa fa-ticket"></i> View Order
            </asp:HyperLink>
            <asp:LinkButton runat="server" ID="lbPrint" CssClass="btn green small">
                <i class="fa fa-print"></i> Print
            </asp:LinkButton>
        </ItemTemplate>
    </asp:TemplateField>
    ...
</asp:GridView>

Code behind:

    protected void grid1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DataRow row = (DataRow)e.Row.DataItem;
                HyperLink hlView = (HyperLink)e.Row.FindControl("hlView");
                LinkButton lbPrint = (LinkButton)e.Row.FindControl("lbPrint");
                if (row != null && lbPrint != null && hlView != null)
                {
                    hlView.NavigateUrl = "#Order" + row["orderId"];
                    lbPrint.OnClientClick = string.Format("printContent('Ticket{0}')", row["orderId"]);
                }
            }
        }

Your page_load should also be changed to improve performance and to put data inside a DataTable object instead so that it can be retrieved in the dataBound event as a DataRow:

        ...
        sqlCmd.Parameters.Add("@startdate", SqlDbType.DateTime);
        sqlCmd.Parameters["@startdate"].Value = "06/01/2015 18:00:00";
        SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd, sqlConn);
        DataTable tblOrder = new DataTable();
        adapter.Fill(tblOrder);

        // get the count
        // @TODO: This is a very expensive method -- there must be a better way
        int count = tblOrder.Rows.Count;
        ...

        grid1.DataSource = tblOrder;
        grid1.DataBind();
        ...

edited May 29, 2015 at 0:56

answered May 29, 2015 at 0:39

Tran Nguyen

1,39111 silver badges16 bronze badges

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

3 Comments

Tran Nguyen Over a year ago

I totally agree with the SqlCommand, but i'm not sure if his intent is to keeps the SqlConnection for later use because it has been declared as class property. Anyway, it is out of scope for this answer.

Didier Jean Charles Over a year ago

Thank you VERY much -- all of you gave me excellent guidance -- especially you Wery Nguyen

John Saunders Over a year ago

@WeryNguyen: looking at the code, it seems they're both members. So, forget I said that!

Collectives™ on Stack Overflow

Get database values into HTML

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related