CodeSnip: Populate a DropDownList with a Stored Procedure
page 1 of 1
Published: 19 Jan 2005
Unedited - Community Contributed
This articles provides sample code that demonstrates a quick and simple method of populating a drop-down list via a stored procedure.
by Steven Swafford
Average Rating: 
Views (Total / Last 10 Days): 31768/ 21

This sample uses the SQL Managed Provider and the Northwind database to populate a DropDownList.

Example Stored Procedure:

CREATE Procedure [Cust_GetCompanyName]
Select CompanyName 
from Customers

Example ASPX Page:

<%@ Page language="c#" Codebehind="Article618.aspx.cs" AutoEventWireup="false" Inherits="ASPAllianceArticles.Article618.Article618" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
  <title>Populate a dropdown via stored procedure</title>
  <form id="Form1" method="post" runat="server">
   <P>Company Name:
    <asp:DropDownList id="CompanyNameDropDownList" runat="server"></asp:DropDownList></P>
    <asp:Label id="dbErrorLabel" runat="server" Visible="False">Label</asp:Label></P>

Code Behind:

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using ASPAlliance.Utilities;

namespace ASPAllianceArticles.Article618
 /// <summary>
 /// Summary description for Article618.
 /// </summary>
 public class Article618 : System.Web.UI.Page
  protected System.Web.UI.WebControls.Label dbErrorLabel;
  protected System.Web.UI.WebControls.DropDownList CompanyNameDropDownList;
  private void Page_Load(object sender, System.EventArgs e)
   SqlConnection dbConn = null;
   string sqlStmt = null;
   ASPAlliance.Utilities.SqlDatabaseHelper dbHelper =
    new ASPAlliance.Utilities.SqlDatabaseHelper();

    // Get an open database connection
    dbConn = dbHelper.openDatabaseConnection();

    // define the sql to perform the database insert
    sqlStmt = "Cust_GetCompanyName"; // Stored Procedure to use

    // Establish a new OracleCommand
    SqlCommand cmd = new SqlCommand();

    // Set command to create your SQL statement
    cmd.CommandText = sqlStmt;

    // Set the OracleCommand to your database connection
    cmd.Connection = dbConn;
    // Set the command type to text
    cmd.CommandType = CommandType.StoredProcedure;

    // Execute the SQL Statement
    SqlDataReader sqlReader = cmd.ExecuteReader();

    if (sqlReader.HasRows)
     CompanyNameDropDownList.DataSource = sqlReader;
     CompanyNameDropDownList.DataTextField = "CompanyName";
     CompanyNameDropDownList.DataValueField  = "CompanyName";
   catch(SqlException ex)
    dbErrorLabel.Text = ex.Message;
    dbErrorLabel.Visible = true;
    // Close the database connection

  #region Web Form Designer generated code
  override protected void OnInit(EventArgs e)
   // CODEGEN: This call is required by the ASP.NET Web Form Designer.
  /// <summary>
  /// Required method for Designer support - do not modify
  /// the contents of this method with the code editor.
  /// </summary>
  private void InitializeComponent()
   this.Load += new System.EventHandler(this.Page_Load);


End result should be similar to the following:

Dropdown listing

User Comments

Title: Re: Very Nice Atricle   
Name: Steven M. Swafford
Date: 2005-01-20 4:48:28 PM
Contact me at and I will do my best to answer your questions.
Title: Very Nice Atricle   
Name: Mahmoud Tahoon
Date: 2005-01-20 1:43:20 PM
Hello Mr.Steven Swafford
This is a very nice atricle and i needed it so much , but i have some more question about that article
First: I want to know where can i store my procedure and is this applicable to MS Access DB?
Second: In the article i didn't know the real data source of your program or it may be due to my little experience in developing with, so could you please tell me about the real data source you used in the program and How to use it?
It will be very nice from you to answer my little questions and this doesn't change anything from my opinion about this article so Thank you very much for your great effort.
Mahmoud Tahoon
20 - Jan - 2005

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-06-18 4:50:39 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search