<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<title>SQL Demos: Using the WHERE Clause (Example 1)</title>
<link rel="stylesheet" href="/shared/netdemos.css">
<script language="C#" runat="server" src="~/shared/fetchData_sql.cs" />
<script language="C#" runat="server">
string query, country, state, city;
private void Page_Load ( object src, EventArgs e ) {
if ( ! IsPostBack ) {
query = "SELECT CountryCode, CountryName FROM Countries WHERE CountryCode IN ( SELECT DISTINCT CountryCode FROM States ) ORDER BY CountryName";
lstCountries.DataSource = fetchReader ( query );
lstCountries.DataBind ( );
lstCountries.SelectedIndex = 0;
// initialize selection
getStates ( null, null );
}
}
private void getStates ( object src, EventArgs e ) {
country = lstCountries.Text.Replace ( "'", "''" );
query = "SELECT StateCode, StateName FROM States WHERE CountryCode = '" + country + "' ORDER BY StateName";
lstStates.DataSource = fetchReader ( query );
lstStates.DataBind ( );
// initialize selection
getCities ( null, null );
}
private void getCities ( object src, EventArgs e ) {
country = lstCountries.Text.Replace ( "'", "''" );
state = lstStates.Text.Replace ( "'", "''" );
query = "SELECT City FROM Cities WHERE CountryCode = '" + country + "' AND StateProvince = '" + state + "' ORDER BY City";
lstCities.DataSource = fetchReader ( query );
lstCities.DataBind ( );
}
void setCities ( object src, DataListItemEventArgs e ) {
// handles the onItemDataBound event of the cities datalist
if ( e.Item.ItemType == ListItemType.Header ) {
Label lblDivsHeader = ( Label ) e.Item.FindControl ( "lblDivsHeader" );
lblDivsHeader.Text = " Cities in " + lstStates.SelectedItem.Text + ", " + lstCountries.SelectedItem.Text;
}
}
</script>
</head>
<body>
<div class="header"><h2>SQL Demos: Using the <b>WHERE Clause</b> (Example 1)</h2></div>
<!-- #include virtual="~/shared/viewsrc_top.inc" -->
<hr size=1 width=92%>
<center>
<form runat="server">
<table align="center" cellpadding=5>
<tr>
<td>Select Country:
<asp:dropdownlist id="lstCountries" runat="server"
datatextfield="countryname" datavaluefield="countrycode"
onSelectedIndexChanged="getStates" autopostback /></td>
<td>State:
<asp:dropdownlist id="lstStates" runat="server"
datatextfield="statename" datavaluefield="statecode"
onSelectedIndexChanged="getCities" autopostback /></td></tr>
</table>
<br>
<asp:datalist id="lstCities" runat="server"
width="80%" horizontalalign="center"
cellpadding=5 backcolor="beige"
repeatcolumns=4 repeatdirection="horizontal"
onItemDataBound="setCities">
<headerstyle backcolor="sienna" forecolor="palegoldenrod"
font-size=10pt height="24pt" font-bold />
<itemstyle width=25% verticalalign="top" font-size=10pt />
<headertemplate>
<asp:label id="lblDivsHeader" runat="server" />
</headertemplate>
<itemtemplate>
<asp:label runat="server" text='<%# Eval ( "City" ) %>' />
</itemtemplate>
</asp:datalist>
</form>
</center>
<hr size=1 width=92%>
<!-- #include virtual="~/shared/viewsrc.inc" -->
</body>
</html>