CodeSnip: Populate a DropDownList with a Stored Procedure
page 1 of 1
Published: 19 Jan 2005
Unedited - Community Contributed
Abstract
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
Feedback
Average Rating: 
Views (Total / Last 10 Days): 59844/ 201

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

Example Stored Procedure:

CREATE Procedure [Cust_GetCompanyName]
as
Select CompanyName 
from Customers
GO

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" >
<HTML>
 <HEAD>
  <title>Populate a dropdown via stored procedure</title>
 </HEAD>
 <body>
  <form id="Form1" method="post" runat="server">
   <P>Company Name:
    <asp:DropDownList id="CompanyNameDropDownList" runat="server"></asp:DropDownList></P>
   <P>
    <asp:Label id="dbErrorLabel" runat="server" Visible="False">Label</asp:Label></P>
  </form>
 </body>
</HTML>

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();


   try
   {
    // 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";
     CompanyNameDropDownList.DataBind();
    }
   }
   catch(SqlException ex)
   {
    dbErrorLabel.Text = ex.Message;
    dbErrorLabel.Visible = true;
   }
   finally
   {
    // Close the database connection
    dbHelper.closeDatabaseConnection(dbConn);
   }
  }


  #region Web Form Designer generated code
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: This call is required by the ASP.NET Web Form Designer.
   //
   InitializeComponent();
   base.OnInit(e);
  }
  
  /// <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);


  }
  #endregion
 }
}

End result should be similar to the following:

Dropdown listing



User Comments

Title: Re: Very Nice Atricle   
Name: Steven M. Swafford
Date: 1/20/2005 4:48:28 PM
Comment:
Contact me at sswafford@aspalliance.com and I will do my best to answer your questions.
Title: Very Nice Atricle   
Name: Mahmoud Tahoon
Date: 1/20/2005 1:43:20 PM
Comment:
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 asp.net, 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-2014 ASPAlliance.com  |  Page Processed at 7/24/2014 8:25:53 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search