Cascaded Drop down using stored procedure

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:

State Table

District table:

District Table

City 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

 

 

 

 

 

 

 

 

 

 

 

1 thought on “Cascaded Drop down using stored procedure”

  1. 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!

    Reply

Leave a Comment