[dba-VB] Combo box

This is my snippet of code should give you an idea...it is however in C# and
specific to ASP.NET (web-based). It should be fairly easy to port it over to
VB.NET and change a couple of lines to be Win32 specific.

The function is a public method stored in my Data class...

public static int BindDropDownList(DropDownList ddl, 	
			string sql, string sqlConnect, Hashtable
sqlParameters, 
			string ddlTextField, string ddlValueField, bool
showDefaultSelection)
		{
			DataSet sqlDs;

			//count of records returned
			int totalRecords;
			//grab the connection string
			string ConnectionString =
ConfigurationSettings.AppSettings.Get(sqlConnect);
			//define and initialize the connection object
			SqlConnection sqlConn = new
SqlConnection(ConnectionString);
			//define and initialize the command object
			SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
			//define the command type
			sqlCmd.CommandType = CommandType.StoredProcedure;

			if (sqlParameters.Count > 0)
			{
				//get a collection of the keys
				ICollection sqlParams = sqlParameters.Keys;
				foreach (string key in sqlParams)
				{			
					// add parameters to pass to the
sproc
					sqlCmd.Parameters.Add(new
SqlParameter(key, sqlParameters[key].ToString()));
				}
			}
			//open the connection
			sqlConn.Open();
			//define the data adapter
			SqlDataAdapter sqlDa = new SqlDataAdapter();
			//define the select command
			sqlDa.SelectCommand = sqlCmd;
			//initialize the dataset
			sqlDs = new DataSet("MyList");
			//fill the dataset
			sqlDa.Fill(sqlDs, "MyList");
			//define a session variable containing our dataset
			System.Web.HttpContext.Current.Session["MyList"] =
sqlDs;			
			//grab the total number of records returned by the
sproc
			totalRecords = sqlDs.Tables[0].Rows.Count;	
			if (totalRecords > 0)
			{
				if (showDefaultSelection)
				{
					DataRow newListRow =
sqlDs.Tables[0].NewRow();
					//this is the value that shows on
the dropdownlist
					//specify an option that will be the
default selection
					newListRow[ddlTextField] = "--Please
select one--";
					//this is the value that will be
posted to the database
					newListRow[ddlValueField] = 0;
					//add the new row
	
sqlDs.Tables[0].Rows.Add(newListRow);
					//specify a sort order based on
ddlValueField; this ensures that the newly added row will show up at the top
					sqlDs.Tables[0].DefaultView.Sort =
ddlValueField;
				}
				//close the connection			
				ddl.DataSource =
sqlDs.Tables[0].DefaultView;
				ddl.DataTextField = ddlTextField;
				ddl.DataValueField = ddlValueField;
				ddl.DataBind();
				sqlConn.Close();
			}
			return totalRecords;
		} //end BindDropDownList 


This is how I call it...

Data.BindDropDownList(uxZipCodeList, sql, connectionString, sqlParameters,
"ZipCity", "ZipCode", true);

ZipCity is what I use to display in the dropdownlist and ZipCode is what I
use to lookup and store values. I believe this is referred to as
DisplayMember and ValueMember in Win32 comboboxes.

In my SQL stored procedure I simply make sure that ZipCity is concatenated
with whatever value I want to show in the dropdownlist.



-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Friday, April 27, 2007 10:35 PM
To: dba-vb at databaseadvisors.com
Subject: [dba-VB] Combo box

I am running into the issue of how to display multiple lines in a combo box.
IIRC this is a "no can do" in the combos in VB.Net but there must be common
workarounds.  
 
For example I need to allow the user to select a zip code, but he really
needs to see the zip AND the city.  Furthermore my code is going to need to
load a record based on that zip code to get lat/long.
 
So, is there a way other than using a concatenation of the zip and the city
to display both in the combo?  If I do a concatenation, then I can no longer
use the zip to lookup a record in a recordset since it now has city data in
the string.
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.467 / Virus Database: 269.6.1/777 - Release Date: 4/26/2007
3:23 PM