Multiple columns in dropdown list...

[Isadore]

This topic was covered some time ago but thought I'd repost for beginners. It's rather simple but useful. I have a table of results, one of the fields is Zip Code. In another table I have all Zip Codes with respective Cities, Counties and States. The following is a sample of (1) joining the two tables in SQL, (2) calling for two fields to sort on (County, City respectively) and (3) creating the multiple column field for display in the dropbox. The code is (using OLEDB Access; ddZip is the id of the drop box):

Sub Page_Load(....)
If Not IsPostBack Then
Dim dbconn As OleDbConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & Server.MapPath("Sites.mdb;&quot)
dbconn.Open()

Dim DBCommand = New OleDbCommand ("SELECT DISTINCT tblZipCodes.City, tblZipCodes.County, WebMasterSites.ZipCode & ' ' & tblZipCodes.County & ' Co., ' & tblZipCodes.City & ', ' & tblZipCodes.State As ZipInfo FROM WebMasterSites LEFT JOIN tblZipCodes ON WebMasterSites.ZipCode = tblZipCodes.Zip_Code ORDER BY tblZipCodes.County, tblZipCodes.City", dbconn)

Dim reader As OleDbDataReader
ddZip.DataSource = DBCommand.ExecuteReader()
ddZip.DataTextField = "ZipInfo"
ddZip.DataBind()
dbconn.Close()
ddZip.SelectedItem.Text = "Select Zip Code..."
End If

...note the "As ZipInfo" creates the field bound to the drop box, and calling City and County is required to sort by City and County in the dropbox. Just a tidbit for those who need to implement this technique.

 

[link9]

Isadore, does this line:

ddZip.SelectedItem.Text = "Select Zip Code..."

not replace the text of an existing option? I think it does.

Instead, you can insert one:

dim li as new ListItem("0","Select Zip Code...&quot
ddZip.Items.Insert(0,li)

Or am I off base? (Still only one cup of coffee in me. ;-))

 

[Isadore]

Thanks Paul. You're right, the text call was put in there to prevent a zip code from appearing, and should more correctly be added as a list item itself...thanks for the tip!