Forums » .NET » ASP.NET »

Using stored procedure how to fill dropdownlist

Posted Date: 31 Mar 2010      Posted By:: patience     Member Level: Silver  Member Rank: 0     Points: 1   Responses: 5


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


#488755    Author: venkatesh.ponna      Member Level: Gold      Member Rank: 0     Date: 31/Mar/2010   Rating: 2 out of 52 out of 5     Points: 2


try this link


#488762    Author: Sethu      Member Level: Silver      Member Rank: 0     Date: 31/Mar/2010   Rating: 2 out of 52 out of 5     Points: 2


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    Author: Abhay Kumar Raut      Member Level: Gold      Member Rank: 0     Date: 01/Apr/2010   Rating: 2 out of 52 out of 5     Points: 2

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

Team Lead at MavStack Solutions
Thanks and Regards,
Abhay Kumar Raut

#488827    Author: YuDi      Member Level: Gold      Member Rank: 0     Date: 01/Apr/2010   Rating: 2 out of 52 out of 5     Points: 2

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    Author: Jayesh L Lolariya      Member Level: Silver      Member Rank: 0     Date: 01/Apr/2010   Rating: 2 out of 52 out of 5     Points: 2

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";


Post Reply     

 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.

  • Return to Discussion Forum
  • Start new thread

  • Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Ankush Bindra
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India