You must Sign In to post a response.
  • Category: ASP.NET

    Using stored procedure how to fill dropdownlist


    Using stored procedure how to fill dropdownlist in c# coding.
  • #488755

    try this link


  • #488762

    Dim sqldr as sqlreader

    ...Connection for db
    ...define stored procedure name

    sqldr = sqlcommand.executeReader()
    Dim lt As ListItem
    While dr.Read()
    lt = New ListItem
    lt.Value = dr(0)
    lt.Text = dr(1)
    If lt.Value = SelectedValue.Trim Then
    lt.Selected = True
    End If
    End While
    If ExtraColumn = True Then
    dropDownId.Items.Insert(0, New ListItem("---Select---", "0", "true"))
    End If

    End try



  • #488801
    Use this

    public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
    if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
    if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

    // If we receive parameter values, we need to figure out where they go
    if ((parameterValues != null) && (parameterValues.Length > 0))
    // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
    SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

    // Assign the provided values to these parameters based on parameter order
    AssignParameterValues(commandParameters, parameterValues);

    // Call the overload that takes an array of SqlParameters
    return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
    // Otherwise we can just call the SP without params
    return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
    pass the parameter it will return you a dataset and just set the datasource of dropdown

    DNS MVM and .Net Guru
    Team Lead at CirrusCloud Systems
    Thanks and Regards,
    Abhay Kumar Raut

  • #488827
    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);


    “Talent wins games, but teamwork and intelligence wins championships.”

  • #489039
    SqlConnection sConn = new SqlConnection();
    sConn.ConnectionString = lstrConnectionString;
    SqlDataAdapter sadap = new SqlDataAdapter("sp_getAllRecord",sConn);
    DataSet ds = new DataSet();
    sadap.Fill(ds, "Table1");
    if (ds.Tables[0].Rows.Count > 0)
    drpProducts.DataSource = ds;
    drpProducts.DataTextField = "Productname";
    drpProducts.DataValueField = "Productid";


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.