Bind two tables in dropdownlist and when selected show its related records

Satyapriya Nayak
13y
19k
0
0

Blog

Bind two tables data in DataList.ra

In this blog we will know how to bind data of two tables into dropdown list and when we select one column data from the dropdown list its related data will be shown in the datalist control.

Table Creation

Create table Mobile (mid varchar(50),mname varchar(50),mprice int)

Create table Laptop (lid varchar(50),lname varchar(50),lprice int)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Bind_two_tables_data_in_DataList._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    </div>
    <asp:DropDownList ID="DropDownList1" runat="server"
        onselectedindexchanged="DropDownList1_SelectedIndexChanged"
        AutoPostBack="True">
    </asp:DropDownList>
    <asp:DataList ID="DataList1" runat="server">
    <FooterStyle BackColor="#FFCC00" ForeColor="#FF3300" />
        <EditItemStyle BackColor="#FF8080" />
        <AlternatingItemStyle BackColor="#FFFF99" />
<HeaderTemplate>

</HeaderTemplate>
    <ItemTemplate>
                <table border="1">
                <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Price</th>
                </tr>
                <tr>
                <td><%#Eval("mid")%></td>
                <td><%#Eval("mname")%></td>
                <td><%#Eval("mprice")%></td>
                </tr>
                </table>
                </ItemTemplate>
    </asp:DataList>


    <asp:DataList ID="DataList2" runat="server">
    <FooterStyle BackColor="#FFCC00" ForeColor="#FF3300" />
        <EditItemStyle BackColor="#FF8080" />
        <AlternatingItemStyle BackColor="#FFFF99" />
<HeaderTemplate>

</HeaderTemplate>
    <ItemTemplate>
                <table border="1">
                <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Price</th>
                </tr>
                <tr>
                <td><%#Eval("lid")%></td>
                <td><%#Eval("lname")%></td>
                <td><%#Eval("lprice")%></td>
                </tr>
                </table>
                </ItemTemplate>
    </asp:DataList>
    </form>
</body>
</html>

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace Bind_two_tables_data_in_DataList
{
    public partial class _Default : System.Web.UI.Page
    {
        string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlCommand com;
        SqlDataAdapter sqlda;
        DataSet ds;
        DataSet ds2;
        string str;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                b1();
                b2();
            }
        }

        void b1()
        {
                SqlConnection con = new SqlConnection(connStr);
                DropDownList1.Items.Add("Choose");
                con.Open();
                str = "select * from Mobile";
                com = new SqlCommand(str, con);
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    DropDownList1.Items.Add(reader["mname"].ToString());
                }
                reader.Close();
                con.Close();
        }
        void b2()
        {
            SqlConnection con = new SqlConnection(connStr);
            con.Open();
            str = "select * from Laptop";
            com = new SqlCommand(str, con);
            SqlDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                DropDownList1.Items.Add(reader["lname"].ToString());
            }
            reader.Close();
            con.Close();
        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            d1();
            d2();
        }

        void d1()
        {
            SqlConnection con = new SqlConnection(connStr);
            con.Open();
            str = "select * from Mobile where mname='" + DropDownList1.SelectedItem.Text + "'";
            com = new SqlCommand(str, con);
            SqlDataReader reader;
            reader = com.ExecuteReader();
            DataList1.DataSource = reader;
            DataList1.DataBind();
            reader.Close();
            con.Close();
        }
        void d2()
        {
            SqlConnection con = new SqlConnection(connStr);
            con.Open();
            str = "select * from Laptop where lname='" + DropDownList1.SelectedItem.Text + "'";
            com = new SqlCommand(str, con);
            SqlDataReader reader;
            reader = com.ExecuteReader();
            DataList2.DataSource = reader;
            DataList2.DataBind();
            reader.Close();
            con.Close();
        }
    }
}