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

    Using stored procedure how to fill dropdownlist

    hi,

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

    try this link
    http://www.sqlteam.com/article/stored-procedures-returning-data

    Thanks&Regards
    Venkatesh.P

  • #488762
    Hi,

    Dim sqldr as sqlreader

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

    sqldr = sqlcommand.executeReader()
    dropDownId.Items.Clear()
    Dim lt As ListItem
    While dr.Read()
    lt = New ListItem
    lt.Value = dr(0)
    lt.Text = dr(1)
    dropDownId.Items.Add(lt)
    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

    catch


    Sethu

  • #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);
    }
    else
    {
    // 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
    Bhubaneswar,Odisha
    Thanks and Regards,
    Abhay Kumar Raut

  • #488827
    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
    }
    }

    “Talent wins games, but teamwork and intelligence wins championships.”
    umardaraj07@gmail.com

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

    Thanks


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.