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
State Table

District table:

district
District Table

City table:

City
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

 

 

 

 

 

 

 

 

 

 

 

🏅www.etrade.com Login Guide|✅US Etrade Tips And Tricks

Are you looking for some information related to Etrade login, or simply you want the guidance for Etrade login, or you just...

👉Best 2020 GUIDE For merrickbank.com login|All about Merrick Bank Login Tips and Tricks

If you want to do a successful Merrick Bank Login and if you do not understand the complete step-by-step login processes,...

👉Everything about Avid Login|✅Best Guidelines|💯For Amazing 2020

Are you looking for Avid login guidelines, or you just want the information related to Avid, or else you have some query...

👉Everything about MyVanillaDebitCard- Application, Activation & Benefits-[✅Updated 2020]

One of the major features MyVanillaDebitCard provides is that it makes cash management very simple for the...

🏅BAML Cards|✅Best Bank Of America Merrill Lynch Visa Reward Card Review 2020 {GUIDE}

Are you doubtful about the Bank Of America Merrill Lynch Card? Do you need a complete guide on the reward card? The...

Quick Tips About Intel Iris Graphics 6100 | Updated

Are you looking for the information related to Intel Iris Graphics 6100, or you just looking for Intel information to keep yourself...

Download Free Spotify Premium Apk | Spotify Mod Apk Free

Are you looking for a free spotify premium apk? If the answer the question is a yes, then you're at the right place. We've...

How To Download Xender For PC Windows 7,8,10 and Mac

Are you looking for information related to Xender application, or you looking for how to download Xender for PC, or you just...

✅Best Credit One Bank Login|🔒Secure Registration Guide|2020

Are you in search of a reliable Credit One Bank login and registration guide? If yes, then this is the place to...

🏅Everbank Login|✅Best Guidelines|For Amazing 2020

Are you looking for information about EverBank, or you just simply looking for the guidelines to log into the Everbank financial...

Related Stories

1 Comment

  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!

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox