Home > OS >  How to run the SQL code and loop/display the results on the same aspx page?
How to run the SQL code and loop/display the results on the same aspx page?

Time:09-07

I'm trying to create a homepage for my asp .net project, which should display all the products available from the database. The code below is my aspx code, and I've labelled the code I'm trying to loop and display all the results from the database with --> and <--.

<%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true" CodeBehind="home.aspx.cs" Inherits="Try.WebForm1" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">

</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
<section >      
            <div >
                <div >
                  
                    <div >
                        <div >
                       -->  <!-- Product image-->
                            <img  width="205.99px" height="205.99px" src="data:image/jpg;base64,<%= @Convert.ToBase64String(*product image*) %>" alt="..." />
                            <!-- Product details-->
                            <div >
                                <div >
                                    
                                    <!-- Product name-->
                                    <h5 >*product name*</h5>
                                    <!-- Product price-->
                                    *product price*<br/>
                                    *product description*
                                </div>
                            </div> <--
                            
                            <!-- Product actions-->
                            <div >
                            <div ><a  href="productDetails.aspx">Details</a></div><br />
                            <div ><a  href="home.aspx">Add to cart</a></div> 

                            </div>
                        </div>
                    </div>

                </div>
            </div>
        </section>
</asp:Content>

And the code below are the code which I currently left at the aspx.cs page to run the SQL and retrieve the result. I wish to put it somewhere within the aspx page with <% %> but there are errors.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Try
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string strCon = ConfigurationManager.ConnectionStrings["WebConfigConString"].ConnectionString;
            SqlConnection con = new SqlConnection(strCon);
            con.Open();

            string strHome = "SELECT * FROM PRODUCT";
            SqlCommand cmdHome = new SqlCommand(strHome, con);

            SqlDataReader dtrHome = cmdHome.ExecuteReader();

            while (dtrHome.Read())
            {
                //display the result from database on the aspx page
            }

        }
    }
}

CodePudding user response:

Well, the most common way to "display" repeating data?

Is to use one of the built in controls that displays repeating data. Then you can "feed" the control the data source, and asp.net does the work for you!

So, say I drop in a GridView (often a great choice).

So, now our markup is this:

        <asp:GridView ID="GridView1" runat="server" Width="70%">

        </asp:GridView>

And our code on page load is now this:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadData();

    }

    void LoadData()
    {

        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            string strSQL = 
                @"SELECT ID, Fighter, Engine, Thrust, Description FROM Fighters";
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                DataTable rstData = new DataTable();
                rstData.Load(cmdSQL.ExecuteReader());

                GridView1.DataSource = rstData;
                GridView1.DataBind();
            }
        }
    }

And our results are now this:

enter image description here

So, in effect we can layout "one thing", and then have it repeated.

Now, my Fighters table has a column that links to picture.

So, I can use the wizard to create the GV, and then delete the sql data source on the page, and say with this markup:

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="ID" CssClass="table"  >
        <Columns>
            <asp:BoundField DataField="Fighter" HeaderText="Fighter"  />
            <asp:BoundField DataField="Engine" HeaderText="Engine"  />
            <asp:BoundField DataField="Thrust" HeaderText="Thrust"  />
            <asp:BoundField DataField="Description" HeaderText="Description" />

            <asp:TemplateField HeaderText="View">
                <ItemTemplate>
                    <asp:ImageButton ID="cmdView" runat="server" Width="150px"
                        ImageUrl = '<%# Eval("ImagePath") %>'
                        OnClick="cmdView_Click" />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>

And now I get this:

enter image description here

But, maybe I don't want a grid, but a card like view?

Ok, then we can use a "repeater".

Again, the idea is we layout ONE thing, and then feed it data, and it "repeats" for us.

So, lets now use repeater.

Inside the repeater, we create "one" thing we want to display.

So, say this markup:

<asp:Repeater ID="Repeater1" runat="server" >
    <ItemTemplate>                   
        <div style="border-style:solid;color:black;width:320px;height:450px;float:left;margin-right:10px;margin-bottom:10px">
            <div style="text-align:center;padding:2px 10px 2px 10px">
                <h3><%# Eval("Fighter") %></h3>
                <asp:Image ID="Image2" runat="server" 
                    ImageUrl = '<%# Eval("ImagePath") %>' Width="170" />
                <h4>Engine</h4>
                <asp:Label ID="EngineLabel2" runat="server" Text='<%# Eval("Engine") %>' />
                <h4>Description</h4>
                <asp:Label ID="DescLabel" runat="server" Text='<%# Eval("Description") %>' />
            </div>
        </div>
    </ItemTemplate>
</asp:Repeater>

Code to load - idential (just send that rstData (our table) to the repeater.

We now get this:

enter image description here

And because each "repeated" thing is a simple div (floated left),

Then if I re-size my browser, then I see this (for a big wide screen browser and monitor).

enter image description here

As you can see, you get a LOT of nice repeating data, and for VERY little efforts on your part.

Since you wanting to display products? Then use a GridView, or often better a ListView. You can even let the wizard generate that grid or listview for you. Then you can clean it up a bit, or leave as is.

  • Related