howto: Autofiltering dropdownlists!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Everyone!

I am working on a project in C# and have a table the contains state info,
plus a variety of other info that I will reference from another dropdown
list. How can I do this programatically?

What I am doing is this,
1. create dbconn, to tblStates (already done!)
2. User has a dropdown list of states, when they select the state the box
next to it with Counties displays only the ones for that state, and on
selection of said county 4-5 others fields from the table are passed to my
session vars.

What is the best way to implement this?
 
Hi Dewright_ca,

Welcome to MSDN newsgroup.
From your description, you have some states and counties info and want to
arrange them into a dropdownlist and a listbox so that when users select
certain state in the dropdownlist, the listbox will displaying the counties
of that selected state automatically , yes?

As for this question, I think it depends on serveral things:
1. How are your state and county data stored in db, with two separate
tables? And the county table's record has a state field to reference its
state? If so , we can query the state and county data out from db and store
them into two DataTables. And we can use the DataTable.Select method to do
data filtering.

2. Is your state and county data frequently changed? If they're almost
static data, we can put them (the DataTable we retrieved through db
connection) into the ASP.NET 's Applicaiton Cache colleciton( we can add
time dependency to make it expire after some period of time). Then, we
will bind the State table's record to the dropdownlist and set the
dropdownlist as autopOstback. When the user change the DropDownList's
index, in its selectedIndexChanged event, we retrieve the County datatable
from the Cache and select those counties of the selected state and rebind
the county listbox on the page.
How do you think of this?

Please feel free to post here if you have any other questions or ideas.
Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Thanks for the reply!

To your item 1, right now they are in a single table with state as one index
and my other field as a index w/ duplicates as there are no duplicates in the
same state.

To your item 2, the data is very static as there is not much updated here,
and if they were they would do it through a frontend to the mdb file so the
data would be visible at the next render.


What you are describing sounds great, because what my user will do is select
there state, this will then return a valid list just for that state, then
they will select the county and then fill in a couple of box's on the form to
buildout the rest of the query.

I userstand what you are describing, but being very new to C#/ASP.Net this
is a little out of my range.

D - Premier Data

Thanks again for the reply, now I have a direction to work towards! Any info
you can forward would be greatly appreciated!
 
Steven,
I have built 2 functions, one that retrieves the States section of the list,
and the other retrieves the Counties section. They both bind to the right
dropdown's, how do I set the selectindex and pass to the other, I have been
trying to do this and still can't get the right response.

Right now on Page_Init it is loading my loadStateDetails() and
loadMerDetails() and these both showup and list properly.

Thanks
 
Thanks for your response D - Premier Data,

OK. Here is a simple demo page which has two Lists on the webform
displaying the States and Counties. When user select a certain State, the
page will be posted back and the County Listbox will displaying the
counties of that selected state. I've also using the ASP.NET's Application
Cache to cache the DataTable( state and county). YOu can have a look if you
have interests.

Hope also helps :--)

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

===========aspx================
<HTML>
<HEAD>
<title>state_county</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<table width="80%">
<tr>
<td width="80">
Current State:
</td>
<td>
<asp:DropDownList id="lstState" runat="server"
AutoPostBack="True"></asp:DropDownList>
</td>
</tr>
<tr>
<td>Current County:</td>
<td><asp:ListBox id="lstCounty" runat="server"
Width="226px"></asp:ListBox></td>
</tr>
</table>
</form>
</body>
</HTML>
========code behind============
public class state_county : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DropDownList lstState;
protected System.Web.UI.WebControls.ListBox lstCounty;

private void Page_Load(object sender, System.EventArgs e)
{

if(!IsPostBack)
{
BindStateList();
BindCountyList();
}

}


private void BindStateList()
{
if(Cache["STATE_COUNTY"] == null)
{
CacheStaticData();
}

DataSet ds = Cache["STATE_COUNTY"] as DataSet;
DataTable dtState = ds.Tables["State"];

lstState.DataSource = dtState;
lstState.DataTextField = lstState.DataValueField = "Name";
lstState.DataBind();
lstState.SelectedIndex = 0;

}

private void BindCountyList()
{
string currentstate = lstState.SelectedValue;

if(Cache["STATE_COUNTY"] == null)
{
CacheStaticData();
}

DataSet ds = Cache["STATE_COUNTY"] as DataSet;
DataTable dtCounty = ds.Tables["County"];

DataView dv = new DataView(dtCounty, "State='" + currentstate +
"'",null, DataViewRowState.CurrentRows);

lstCounty.DataSource = dv;
lstCounty.DataTextField = lstState.DataValueField = "Name";
lstCounty.DataBind();



}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.lstState.SelectedIndexChanged += new
System.EventHandler(this.lstState_SelectedIndexChanged);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion



#region ---Helper Functions---

private void CacheStaticData()
{
System.Random rand = new Random((int)DateTime.Now.Ticks);

DataSet ds = new DataSet("ds");

DataTable dtState = new DataTable("State");
DataTable dtCounty = new DataTable("County");

dtState.Columns.Add("Name");

dtCounty.Columns.Add("Name");
dtCounty.Columns.Add("State");

ds.Tables.Add(dtState);
ds.Tables.Add(dtCounty);

int i = 0,j=0;

for(i = 0; i<8; i++)
{
DataRow drS = dtState.NewRow();
drS[0] = "State_" + i;

for(j=0;j< rand.Next(5,10); j++)
{
DataRow drC = dtCounty.NewRow();
drC[0] = "County_" + j + "_" +i;
drC[1] = drS[0];

dtCounty.Rows.Add(drC);
}

dtState.Rows.Add(drS);

}

Cache.Add("STATE_COUNTY", ds, null, DateTime.MaxValue,
TimeSpan.FromHours(12), System.Web.Caching.CacheItemPriority.Normal, null);

}


#endregion

private void lstState_SelectedIndexChanged(object sender,
System.EventArgs e)
{
BindCountyList();
}

}
 
Back
Top