Forums » .NET » ASP.NET »

Using stored procedure how to fill dropdownlist


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



hi,

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




Responses

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

hi

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

Thanks&Regards
Venkatesh.P


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

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    Author: Abhay Kumar Raut      Member Level: Gold      Member Rank: 39     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);
}
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
Team Lead at MavStack Solutions
Bhubaneswar,Odisha
Thanks and Regards,
Abhay Kumar Raut





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

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    Author: Jayesh L Lolariya      Member Level: Silver      Member Rank: 1821     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();
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


 
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

Pawan Awasthi
More...
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India