[dba-VB] Combo box

LOL, and it only gets better (worse).   


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of William Hindman
Sent: Saturday, April 28, 2007 2:56 AM
To: ebarro at verizon.net; dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] Combo box

...sigh ...shakes head ...closes "VB.Net for Dummies" ...mumbles to self,
what in the heck is the world coming to when there is no such thing as a
simple d&d combo available ...y'all give me a headache, you do ...turning
off the lights :(

William Hindman

----- Original Message -----
From: "Eric Barro" <ebarro at verizon.net>
To: <dba-vb at databaseadvisors.com>
Sent: Saturday, April 28, 2007 2:14 AM
Subject: Re: [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.