Cascaded Drop down using SQL server 2008 stored procedure and Asp.net
Introduction(Cascaded Drop down using stored procedure):
Here I will explain drop down using SQL server 2008 R2 stored procedure and C# function.
Description:
Here I will explained how to populate drop down based on another drop down with help of SQL server 2008 R2 Stored Procedure and C# function. A stored procedure is nothing more than SQL code save to reuse. If a repetitive SQL query has to execute with in an application, then the best way for it is use store procedure.
Here I will explain with three drop downs State , District, City I need to populate District drop down based on State drop down and I need to populate City drop down based on District drop down for that we create three tables in SQL server with data like this
State Table:
District table:
City table:
Create aspx page in asp.net like this
<html xmlns=”http://www.w3.org/1999/xhtml”> <head runat=”server”> <title></title> </head>
<body style="background:white">
<form id=”form1″ runat=”server”>
<h2 align=”center”> populate dropdown </h2>
<table>
<tr>
<td>
<asp:Label ID=”lblState” runat=”server” Text=”State”></asp:Label>
</td>
<td>
<asp:DropDownList runat=”server” ID=”ddState” Width=”208p”
AutoPostBack=”true” onselectedindexchanged=”ddState_SelectedIndexChanged”>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
<asp:Label ID=”lbldistrict” runat=”server” Text=”District”></asp:Label>
</td>
<td>
<asp:DropDownList runat=”server” ID=”dddistrict”
Width=”208px” style=’text-transform:uppercase’ AutoPostBack=”True”
onselectedindexchanged=”dddistrict_SelectedIndexChanged” >
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
<asp:Label ID=”lblcity” runat=”server” Text=”City”></asp:Label>
</td>
<td>
<asp:DropDownList runat=”server” ID=”ddcity” CssClass=”select” Width=”208px” style=’text-transform:uppercase’ >
</asp:DropDownList>
</td>
</tr>
</table>
</form>
</body>
</html>
After that add following namcespaces in your asp.net cs page
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Configuration;
using System.Web.UI.WebControls;
write C# code like this
SqlConnectio conK = new SqlConnection(ConfigurationManager.ConnectionStrings[“StarterSite”].ConnectionString.ToString());
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillState(ddState);
}
}
// create function Fillcombo and call store procedure USP_FillDDL
private DataSet FillCombo(string oprtype, string ParentId)
{
try
{
SqlCommand cmdK = new SqlCommand(“USP_FillDDL”, conK);
cmdK.CommandType = CommandType.StoredProcedure;
cmdK.Parameters.AddWithValue(“@OPR”, oprtype);
cmdK.Parameters.AddWithValue(“@ParentId”, ParentId);
conK.Open();
SqlDataAdapter da = new SqlDataAdapter(cmdK);
DataSet ds = new DataSet();
da.Fill(ds);
conK.Close();
return ds;
}
catch (Exception ex)
{
return null;
}
finally
{
conK.Close();
}
}
//function to fill State
private void FillState(DropDownList ddl)
{
ddl.DataSource = FillCombo(“STA”, “0”);
ddl.DataTextField = “Description”;
ddl.DataValueField = “ID”;
ddl.DataBind();
}
//function to fill District
private void FillDistrict(DropDownList ddlDist, DropDownList ddlSta)
{
ddlDist.DataSource = FillCombo(“DIS”, ddState.SelectedValue.ToString());
ddlDist.DataTextField = “Description”;
ddlDist.DataValueField = “ID”;
ddlDist.DataBind();
}
//function to fill City
private void FillCity (DropDownList ddlcity, DropDownList ddlDist)
{
ddlTahl.DataSource = FillCombo(“CTY”, ddlDist.SelectedValue.ToString());
ddlTahl.DataTextField = “Description”;
ddlTahl.DataValueField = “ID”;
ddlTahl.DataBind();
}
protected void ddState_SelectedIndexChanged(object sender, EventArgs e)
{
FillDistrict(dddistrict, ddState);
}
protected void dddistrict_SelectedIndexChanged(object sender, EventArgs e)
{
FillCity (ddcity, dddistrict);
}
After that create store procedure in sql server 20008 R2
Create procedure [dbo].[USP_FillDDL]
(
@OPR VARCHAR(3) ,
@ParentID VARCHAR(15) = ‘0’
)
As
Begin
if(@OPR=’STA’)–FOR STATE
begin
SELECT NULL AS ID,’–Select State–‘ AS [Description], 0 AS Seq UNION
SELECT StateCode, StateName, 1 FROM StateTable
ORDER BY Seq,[Description]
end
if(@OPR=’DIS’)–FOR DISTRICT
begin
SELECT NULL AS ID,’–Select District–‘ AS [Description], 0 AS Seq UNION
SELECT DistrictCode, DistrictName, 1 FROM DistrictTable
WHERE (Statecode = @ParentID)
ORDER BY Seq,[Description]
end
if(@OPR=’CTY’)–FOR City
begin
SELECT NULL AS ID,’–Select City–‘ AS [Description], 0 AS Seq UNION
SELECT citycode, NameOfcity, 1 FROM CityTable
WHERE (districtcode = @ParentID)
ORDER BY Seq,[Description]
End
End
My partner and I absolutely love your blog and find nearly all of your post’s to be just what I’m looking for.
Does one offer guest writers to write content to suit your needs?
I wouldn’t mind publishing a post or elaborating on many of the subjects you write in relation to here.
Again, awesome blog!