Diamond4Dotnet: Fill Dropdown1 from DB and fill Dropdown2 based on Dropdown1 selection

.aspx

<table>

<tr>

<td>

Select Sports:

</td>

<td>

<asp:dropdownlist id="ddlsport" runat="server" autopostback="true" height="24px"

onselectedindexchanged="ddlsport_SelectedIndexChanged" width="111px">

</asp:dropdownlist>

</td>

<td>

<asp:requiredfieldvalidator id="reqddlsport" runat="server" errormessage="*" forecolor="Red"

controltovalidate="ddlsport" initialvalue="-Select-">

</asp:requiredfieldvalidator>

</td>

<td>

Select Sports Person:

</td>

<td>

<asp:dropdownlist id="ddlsportname" runat="server" height="21px" width="106px">

</asp:dropdownlist>

</td>

<td>

<asp:requiredfieldvalidator id="reqddlsportname" runat="server" errormessage="*"

forecolor="Red" controltovalidate="ddlsportname" initialvalue="-Select-">

</asp:requiredfieldvalidator>

</td>

<td>

<asp:button id="btnsubmit" runat="server" text="Submit" onclick="btnsubmit_Click" />

</td>

</tr>

<tr>

<asp:label id="lbldetails" runat="server"></asp:label>

</td>

</tr>

</table>

.aspx.cs

public partial class Testb_Default : System.Web.UI.Page

{

SqlCeConnection conn = null;

SqlCeCommand cmd = null;

SqlCeDataReader rdr = null;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

List<loaddata> list = new List<loaddata>();

try

{

// Open the connection and create a SQL command

conn = new SqlCeConnection(System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString);

conn.Open();

cmd = new SqlCeCommand("SELECT * FROM sports", conn);

rdr = cmd.ExecuteReader();

// Iterate through the results

while (rdr.Read())

{

loaddata obj = new loaddata();

obj.SportID = Convert.ToInt32(rdr["sport_id"]);

obj.SportName = rdr["Sport_Name"].ToString();

list.Add(obj);

}

// Always dispose data readers and commands as soon as practicable

rdr.Close();

cmd.Dispose();

}

finally

{

// Close the connection when no longer needed

conn.Close();

}

ddlsport.DataSource = list;

ddlsport.DataValueField = "SportID";

ddlsport.DataTextField = "SportName";

ddlsport.DataBind();

ddlsport.Items.Insert(0, "-Select-");

}

public SqlCeDataReader LoadData(string query)

{

SqlCeConnection con = new SqlCeConnection(System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString);

SqlCeCommand cmd = new SqlCeCommand();

cmd.Connection = con;

con.Open();

cmd.CommandText = query;

cmd.CommandType = CommandType.Text;

SqlCeDataReader dr = cmd.ExecuteReader();

// con.Close();

return dr;

}

public class loaddata

{

string _sportname, _sportpersonname;

int _sportid, _sportpersonid;

public string SportName

{

get { return _sportname; }

set { _sportname = value; }

}

public string SportPersonName

{

get { return _sportpersonname; }

set { _sportpersonname = value; }

}

public int SportID

{

get { return _sportid; }

set { _sportid = value; }

}

public int SportPersonID

{

get { return _sportpersonid; }

set { _sportpersonid = value; }

}

protected void ddlsport_SelectedIndexChanged(object sender, EventArgs e)

{

List<loaddata> list = new List<loaddata>();

try

{

// Open the connection and create a SQL command

conn = new SqlCeConnection(System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString);

conn.Open();

cmd = new SqlCeCommand("SELECT * FROM sport_persons where sport_id='" + ddlsport.SelectedValue + "'", conn);

rdr = cmd.ExecuteReader();

// Iterate through the results

while (rdr.Read())

{

loaddata obj = new loaddata();

obj.SportPersonID = Convert.ToInt32(rdr["sport_Person_id"]);

obj.SportPersonName = rdr["Sport_person_Name"].ToString();

list.Add(obj);

}

// Always dispose data readers and commands as soon as practicable

rdr.Close();

cmd.Dispose();

}

finally

{

// Close the connection when no longer needed

conn.Close();

}

ddlsportname.DataSource = list;

ddlsportname.DataValueField = "SportPersonID";

ddlsportname.DataTextField = "SportPersonName";

ddlsportname.DataBind();

ddlsportname.Items.Insert(0, "-Select-");

}

protected void btnsubmit_Click(object sender, EventArgs e)

{

lbldetails.Text = "You are selected " + ddlsport.SelectedItem + ":" + ddlsport.SelectedValue + " and " + ddlsportname.SelectedItem + ":" + ddlsportname.SelectedValue;

}

web.config

<connectionStrings>
<add name="constring" connectionString="Data Source=|DataDirectory|Database.sdf;"/>

</connectionStrings>