Binding data to a DropDownList with ADO.NET

Using ADO.NET to pull data from the Northwind database (Access version), using the saved query Current Product List.

Continuing with my preference for using saved queries in Access, for very much the same reasons that I prefer stored procedures in SQL Server, here is a straightforward example of calling "Current Product List" from the Northwind database and binding it programmatically to a DropDownList.

Two things of note are that the name of the query is surrounded with [ ] brackets, to get over the ill-advised use of embedded spaces in Access object names, and secondly, calling a saved query in Access requires that the CommandType property is set to StoredProcedure.

string ConnString = Utils.GetConnString();
string SqlString = "[Current Product List]";
using (OleDbConnection conn = new OleDbConnection(ConnString ))
{	
  using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
  {
    cmd.CommandType = CommandType.StoredProcedure;
    conn.Open();
    OleDbDataReader rd = cmd.ExecuteReader();
    while (rd.Read())
    {
      DropDownList1.DataSource = rd;
      DropDownList1.DataValueField = "ProductID";
      DropDownList1.DataTextField = "ProductName";
      DropDownList1.DataBind();
    }
  }
}

You might also like...