CODEWITHSUNDEEP
c#sqlasp.netwebforms
Craiten
Craiten

Reputation: 109

ASP.NET C# How to fill table from database

This is my table in my .aspx file: 

        <asp:Table ID="Tournament" runat="server">
            <asp:TableHeaderRow>
                <asp:TableHeaderCell>Name</asp:TableHeaderCell>
                <asp:TableHeaderCell>Start date</asp:TableHeaderCell>
            </asp:TableHeaderRow>
        </asp:Table>

This is how I get the data from my database:

        string conStr = "...";
        SqlConnection sqlConnection = new SqlConnection(conStr);
        string sqlString = "SELECT name, startdate FROM table WHERE startdate > @end_date AND name = ...";
        IDbCommand idbCommand = new SqlCommand(sqlString, sqlConnection);
        IDbDataParameter parameter = idbCommand.CreateParameter();
        parameter.ParameterName = "@end_date";
        parameter.Value = DateTime.Now;
        idbCommand.Parameters.Add(parameter);
        sqlConnection.Open();

How do I fill my asp:Table with the data from my sql databae?

Upvotes: 2

Views: 13832

Answers (3)

Just_Ice
Just_Ice

Reputation: 563

    SqlCommand cmd = new SqlCommand();
    SqlConnection con = new SqlConnection();


       try
        {
            using (SqlConnection con = new SqlConnection("Data Source = [SERVERNAME]; Initial Catalog = CustomerOrders; Integrated Security = true"))
            {
                String name = dropDownList.SelectedItem.Text;
                SqlDataAdapter cmd = new SqlDataAdapter("SELECT * FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.ReferenceID WHERE Name = '" + name + "'", con);
                con.Open();
                DataTable dtbl = new DataTable();
                cmd.Fill(dtbl);
                gvPhoneBook.DataSource = dtbl;
                gvPhoneBook.DataBind();

            }
        }
        catch (Exception Ex)
        {
            Console.WriteLine(Ex.Message);
        }



        <asp:GridView ID="gridView" runat="server" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField DataField="OrderID" HeaderText="OrderID" />
            <asp:BoundField DataField="ProductID" HeaderText="ProductID" />
            <asp:BoundField DataField="Quantity" HeaderText="Quantity" />
            <asp:BoundField DataField="Date" HeaderText="Date" />
            <asp:BoundField DataField="Dispatched" HeaderText="Dispatched" />
        </Columns>
        </asp:GridView>

Upvotes: 0

fubo
fubo

Reputation: 45947

You should use a DataControl like a asp:GridView instead of a asp:Table. In case of a asp:Table you have to add the whole data maually to your Rows / Columns. In case of a asp:GridView it's just a databinding.

cs:

string query = "SELECT name, startdate FROM table WHERE startdate > @end_date AND name = ...";
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand(query, myConnection))
    {
        myConnection.Open();
        SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        DataTable dt = new DataTable();
        dt.Load(dr);
        Tournament.DataSource = dt;
        Tournament.DataBind();
    }
}

aspx:

 <asp:GridView ID="Tournament" runat="server"></asp:GridView>

Upvotes: 5

Nazir Ullah
Nazir Ullah

Reputation: 610

use this way

 string conStr = "...";
            SqlConnection sqlConnection = new SqlConnection(conStr);
            string sqlString = "SELECT name, startdate FROM table WHERE startdate > @end_date AND name = ...";
            SqlDataAdapter da = new SqlDataAdapter(sqlString, sqlConnection);
            da.SelectCommand.Parameters.AddWithValue("@end_date", DateTime.Now);
            DataTable dt = new DataTable();
            da.Fill(dt);

            Tournament.DataSource = dt;
            Tournament.DataBind();
        }

Upvotes: 0

Related Questions