Cascading DropDownList with ASP.Net SqlDataSource using single Stored Procedure

Sumeet

SOLVED CLOSED ACTIVE ANSWERED
User: Sumeet
Posted: on Mar 17, 2021 05:27 AM
Forum: Data Controls
Answer: 1
Views: 6032
Sample Code: Download

I am having 3 DropDownList District, Zone, CEOs.

CEOs are District and Zone wise

If I select district then it should load all CEOs based on districtid

if I select district & zone then it should load all CEOs based on districtid and zoneid.

I have binded CEOs DropDownList using sp with sqldatasource and it showing an error as "Invalid input string"

below is my code:

html:

<table width="100%">
    <tr>
        <td align="center" colspan="2">
            <asp:Label ID="lblmsg" runat="server" ForeColor="Maroon" Font-Names="lucida bright" Font-Size="13px"></asp:Label>
        </td>
    </tr>
    <tr>
        <td align="right" class="auto-style2" valign="top">District <font style="color: red;">*</font></td>
        <td align="left" valign="top">
            <asp:DropDownList ID="dropdowndistrict" runat="server" AutoPostBack="True" Width="150px"></asp:DropDownList><br />
 
        </td>
    </tr>
    <tr>
        <td align="right" class="auto-style2" valign="top">Zone <font style="color: red;">*</font></td>
        <td align="left" valign="top">
            <asp:DropDownList ID="dropdownzone" runat="server" Width="150px" ValidationGroup="ceo"></asp:DropDownList><br />
            <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="*Required" ControlToValidate="dropdownzone" InitialValue="0" ValidationGroup="ceo" SetFocusOnError="true" CssClass="err" Display="Dynamic"></asp:RequiredFieldValidator><asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="*Required" ControlToValidate="dropdownzone" ValidationGroup="ceo" SetFocusOnError="true" CssClass="err" Display="Dynamic"></asp:RequiredFieldValidator>
        </td>
    </tr>
    <tr>
        <td align="right" class="auto-style2" valign="top">Select CEO <font style="color: red;">*</font></td>
        <td align="left" valign="top">
            <asp:DropDownList ID="dropdownlistceo" DataSourceID="SqlDataSource1" runat="server" Width="150px" DataValueField="userid" DataTextField="post" ValidationGroup="ceo" AppendDataBoundItems="true"></asp:DropDownList><br />
            <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="*Required" ControlToValidate="dropdownlistceo" InitialValue="0" ValidationGroup="ceo" SetFocusOnError="true" CssClass="err" Display="Dynamic"></asp:RequiredFieldValidator><asp:RequiredFieldValidator ID="RequiredFieldValidatordropdownlistceo" runat="server" ErrorMessage="*Required" ControlToValidate="dropdownlistceo" ValidationGroup="ceo" SetFocusOnError="true" CssClass="err" Display="Dynamic"></asp:RequiredFieldValidator>
            <br />
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:dsejConnectionString %>" SelectCommand="usp_getdata" SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:Parameter DefaultValue="15" Name="type" Type="Int32" />                                                
                    <asp:ControlParameter ControlID="dropdowndistrict" DefaultValue="" Name="districtid" PropertyName="SelectedValue" Type="Int32" />
                    <asp:ControlParameter ControlID="dropdownzone" DefaultValue="" Name="zoneid" PropertyName="SelectedValue" Type="Int32" ConvertEmptyStringToNull="false" />                                               
                </SelectParameters>
            </asp:SqlDataSource>
        </td>
    </tr>
</table>

Imports System.Data
Imports System.Data.SqlClient
 
Partial Class testing
    Inherits System.Web.UI.Page
    Dim vali As New validate_data()
    Sub filldistrict()
        Dim i As Integer = 0
        Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("dsejConnectionString").ConnectionString)
            Try
                Using cmd As New SqlCommand("SELECT [districtid], [districtname] FROM [district_master] order by districtname asc", conn)
                    conn.Open()
                    Dim dr As SqlDataReader = cmd.ExecuteReader()
                    If dr.HasRows Then
                        dropdowndistrict.Items.Clear()
                        dropdowndistrict.Items.Add("--Select--")
                        dropdowndistrict.Items(i).Value = "0"
                        While dr.Read
                            i += 1
                            dropdowndistrict.Items.Add(Server.HtmlEncode(dr("districtname")))
                            dropdowndistrict.Items(i).Value = Server.HtmlEncode(dr("districtid"))
                        End While
                    End If
                End Using
            Catch ex As Exception
                Response.Redirect("errorpage.html", False)
            Finally
                If ConnectionState.Open Then
                    conn.Close()
                End If
            End Try
        End Using
    End Sub
    Sub fillzone(ByVal d As Integer)
        Dim i As Integer = 0
        Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("dsejConnectionString").ConnectionString)
            Try
                Using cmd As New SqlCommand("SELECT [zoneid], [zone_name] FROM [zone_master] WHERE ([districtid] = @districtid) order by [zone_name]", conn)
                    cmd.Parameters.AddWithValue("@districtid", Server.HtmlEncode(d))
                    conn.Open()
                    Dim dr As SqlDataReader = cmd.ExecuteReader()
                    If dr.HasRows Then
                        dropdownzone.Items.Clear()
                        dropdownzone.Items.Add("--Select--")
                        dropdownzone.Items(i).Value = "0"
                        While dr.Read
                            i += 1
                            dropdownzone.Items.Add(Server.HtmlEncode(dr("zone_name")))
                            dropdownzone.Items(i).Value = Server.HtmlEncode(dr("zoneid"))
 
                        End While
                    End If
                End Using
                'dropdowndistrict.Items.Add(New ListItem("--Select--", "0"))
            Catch ex As Exception
                Response.Redirect("errorpage.html", False)
            Finally
                If ConnectionState.Open Then
                    conn.Close()
                End If
            End Try
        End Using
    End Sub
      
    Protected Sub dropdowndistrict_SelectedIndexChanged(sender As Object, e As EventArgs) Handles dropdowndistrict.SelectedIndexChanged
        dropdownzone.Items.Clear()
        dropdownlistceo.Items.Clear()
        If (dropdowndistrict.SelectedIndex <> 0) Then
            fillzone(vali.remove_bad_words(dropdowndistrict.SelectedItem.Value.ToString()))
            dropdownlistceo.DataBind()
        End If
    End Sub
 
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            filldistrict()
        End If
    End Sub
End Class

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Mar 18, 2021 02:10 AM
Modified: on Mar 18, 2021 02:11 AM
Report

Hi Sumeet,

Check this example. Now please take its reference and correct your code.

Database

I have made use of three tables Countries, States and Cities with the following schema.

Countries

States

Cities

You can download the database table SQL by clicking the download link below.

Download SQL file

Stored Procedure

--EXEC sp_CascadingDDL 1,1
CREATE PROCEDURE [dbo].[sp_CascadingDDL]
	@CountryId INT = 0,
	@StateId INT = 0
AS
BEGIN
	IF(@CountryId = 0 AND @StateId = 0)
    BEGIN
        SELECT '0' Id,'--Select--' Name
        UNION
        SELECT a.CountryId,a.CountryName
        FROM Countries a
    END
	ELSE IF(@CountryId > 0 AND @StateId = 0)
    BEGIN
        SELECT '0' Id,'--Select--' Name
        UNION
        SELECT a.StateId,a.StateName
        FROM States a
        WHERE a.CountryId = @CountryId
    END
    ELSE IF(@CountryId > 0 AND @StateId > 0)
    BEGIN
        SELECT '0' Id,'--Select--' Name
        UNION
        SELECT a.CityId,a.CityName
		FROM Cities a
		WHERE a.StateId = @StateId
    END
END

HTML

<table>
    <tr>
        <td align="right" valign="top">Country <font style="color: red;">*</font></td>
        <td align="left" valign="top">
            <asp:DropDownList ID="ddlCountries" runat="server" DataSourceID="sdsCountry" AutoPostBack="True"
                DataTextField="Name" DataValueField="Id" OnSelectedIndexChanged="OnCountryChanged" Width="150px">
            </asp:DropDownList>
            <asp:SqlDataSource ID="sdsCountry" runat="server" ConnectionString="<%$ ConnectionStrings:constr %>"
                SelectCommand="sp_CascadingDDL" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
        </td>
    </tr>
    <tr>
        <td align="right" valign="top">State <font style="color: red;">*</font></td>
        <td align="left" valign="top">
            <asp:DropDownList ID="ddlStates" runat="server" DataSourceID="sdsState" AutoPostBack="True"
                DataTextField="Name" DataValueField="Id" OnSelectedIndexChanged="OnStateChanged" Width="150px">
            </asp:DropDownList>
            <asp:SqlDataSource ID="sdsState" runat="server" ConnectionString="<%$ ConnectionStrings:constr %>"
                SelectCommand="sp_CascadingDDL" SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:ControlParameter ControlID="ddlCountries" Name="CountryId" PropertyName="SelectedValue"
                        Type="Int32" ConvertEmptyStringToNull="false" />
                    <asp:Parameter Name="StateId" Type="Int32" DefaultValue="0" />
                </SelectParameters>
            </asp:SqlDataSource>
        </td>
    </tr>
    <tr>
        <td align="right" valign="top">City <font style="color: red;">*</font></td>
        <td align="left" valign="top">
            <asp:DropDownList ID="ddlCities" runat="server" DataSourceID="sdsCity"
                DataTextField="Name" DataValueField="Id" Width="150px">
            </asp:DropDownList>
            <asp:SqlDataSource ID="sdsCity" runat="server" ConnectionString="<%$ ConnectionStrings:constr %>"
                SelectCommand="sp_CascadingDDL" SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:ControlParameter ControlID="ddlCountries" Name="CountryId" PropertyName="SelectedValue"
                        Type="Int32" ConvertEmptyStringToNull="false" />
                    <asp:ControlParameter ControlID="ddlStates" Name="StateId" PropertyName="SelectedValue"
                        Type="Int32" ConvertEmptyStringToNull="false" />
                </SelectParameters>
            </asp:SqlDataSource>
        </td>
    </tr>
</table>

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        ddlStates.Enabled = false;
        ddlCities.Enabled = false;
    }
}

protected void OnCountryChanged(object sender, EventArgs e)
{
    ddlStates.Enabled = true;
    ddlCities.Enabled = false;
}

protected void OnStateChanged(object sender, EventArgs e)
{
    ddlCities.Enabled = true;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        ddlStates.Enabled = False
        ddlCities.Enabled = False
    End If
End Sub

Protected Sub OnCountryChanged(ByVal sender As Object, ByVal e As EventArgs)
    ddlStates.Enabled = True
    ddlCities.Enabled = False
End Sub

Protected Sub OnStateChanged(ByVal sender As Object, ByVal e As EventArgs)
    ddlCities.Enabled = True
End Sub

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Cascading DropDownList with ASP.Net SqlDataSource using single Stored Procedure

Company

Explore

Follow us

Contact Us