RE: Looking for suggestions on populating dropdown from database table

From: Cowboy (Gregory A. Beamer) - MVP (NoSpamMgbworld_at_comcast.netNoSpamM)
Date: 02/09/05 

Date: Wed, 9 Feb 2005 07:09:02 -0800

DataSet or Collection, you are doing the same thing. You are pulling from a
bindable object into something else. There are times where this makes sense,
but the most efficient use of a DataReader is as follows:

private void Page_Load(object sender, System.EventArgs e)
{
  if(!Page.IsPostBack)
    BindPropertyTypeDropDown();
}

private void BindPropertyTypeDropDown()
{
  //Working with Northwind
  string connString = ConfigurationSettings.AppSettings["connString"];
  //Would normally have a stored procedure here
  string sql = "SELECT EmployeeID, FirstName+' '+LastName AS WholeName FROM
Employees";

  SqlConnection conn = new SqlConnection(connString);
  SqlCommand cmd = new SqlCommand(sql, conn);

  try
  {
    conn.Open();
    SqlDataReader dr = cmd.ExecuteReader();

    //Note that you have to leave a reader open while you bind
    ddlPropertyType.DataSource = dr;
    ddlPropertyType.DataValueField = "EmployeeID";
    ddlPropertyType.DataTextField = "WholeName";
    ddlPropertyType.DataBind();
  }
  finally
  {
    if(conn.State == ConnectionState.Open)
      conn.Close();

    conn.Dispose();
  }
}

Of course, if you are running a multi-tiered application, it is more
efficient to either use business objects or set up a DataSet as a
quasi-business object. The main key here is making sure you are using the
proper interfaces or setting up specific attributes. A good primer:

http://www.codeproject.com/useritems/AspNetBindDatagridVT.asp

Check out the links at the bottom, as well. 

---
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"pantichd" wrote:
> Hello,
> 
> I want to populate an employee name dropdown in a webform with values from a database table. I know I can use a data adapter to retrieve the whole table into a dataset and bind the table from the dataset to the dropdown. However, I thought I could do it a little more efficiently by using a datareader to retrieve the name and id columns from the table, put them in a collection and then bind the collection to the dropdown.
> 
> Below is the method I'm using to retrieve a key/value pair from a database table. It returns a collection. Well, now I'm stuck. I can't figure out how to get the collection into the dropdown. 
> 
> When reading about dropdowns I keep running into documentation about ListItemCollection but I can't figure out how to go from collection to ListItemCollection and then how to get that bound to the dropdown.
> 
> 
> Any help would be greatly appreciated.
> 
> 
> David
> 
> 
> Public Function getKeyValueList(ByVal sql As String) As Collection
> 
>     Dim coll As Collection = New Collection
> 
>     readerConn.Open()
> 
>     readerCmd.Connection = readerConn
> 
>     readerCmd.CommandText = sql
> 
>     reader = readerCmd.ExecuteReader
> 
>     If reader.HasRows Then
> 
>     Do While reader.Read()
> 
>         coll.Add(reader.GetString(0), reader.GetString(1))
> 
>     Loop
> 
>     End If
> 
>     reader.Close()
> 
>     readerConn.Close()
> 
> End Function
> 
> 
>

Relevant Pages

  • Re: Using Database Results Wizard for dropdown
    ... Present SQL for the form (showing just the dropdown portion for GroupMix; ... > Is there a parameter you can add to the sql of the Database Results Wizard ...
    (microsoft.public.frontpage.programming)
  • Followup Question
    ... Present SQL for the form (showing just the dropdown portion for GroupMix; there is a similar select for MeetingLocation further down in my page): ... > I'm using the Database Results Wizard to populate a dropdown list in a form. ...
    (microsoft.public.frontpage.programming)
  • looking for a special kind of table control
    ... One is an SQL bound ... dropdown, another one a simple text item dropdown, the third one a numerical ... third column and each time the first or third column is being changed ... and finally a free form text entry field (just plain ...
    (microsoft.public.dotnet.framework.aspnet.webcontrols)
  • RE: Need some help with simple search query
    ... > dropdown with Resident's names and a dynamic dropdown with Appointment ... > search criteria, then it doesn't return any results. ... Here is my new query, ... > sql = blah,blah ...
    (microsoft.public.sqlserver.programming)
  • Re: Cached DropDownList has items, but wont display. Wheres my error
    ... > I am trying to cache a dropdown list rather than hit the database every ... I was going to cache the data by ... > caching the datareader, ... > Public Function fddPatientsFill(ByVal vlngUserID As Integer, ...
    (microsoft.public.dotnet.framework.aspnet.webcontrols)