Could not find control ‘DropCity’ in ControlParameter ‘City’ ERROR

Context: I am trying to nest two dropdownlist(ddl) inside a gridview, the first ddl is called “Ciudad” and the second one is called “Comuna”, I want that according to the “Ciudad” I choose the “Comunas” change to that corresponding “ciudad”. but I get an error and I can not solve it.

My GridView:

       <asp:GridView ID="GridLectores" runat="server" AllowPaging="True" DataKeyNames="CODLIB,PAR_COD_PAR" CssClass="gridview" Style="margin-left: 100px" AllowSorting="True" AutoGenerateColumns="False" CellPadding="0" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="Horizontal" PageSize="5" Width="50%" Height="270px" HorizontalAlign="Center" CellSpacing="1" OnRowUpdating="Actualizar_Lectores" OnRowDeleting="Borrar_Lector" OnRowDataBound="GridLectores_RowDataBound">
           <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
           <Columns>
               <asp:TemplateField HeaderText="Ciudad" SortExpression="Ciudad">
                   <EditItemTemplate>
                       <asp:DropDownList ID="DropCiudad" runat="server" DataSourceID="SqlDataSourceCiudad" DataValueField="PAR_COD_PAR" DataTextField="PAR_DES_PAR"></asp:DropDownList>
                   </EditItemTemplate>
                   <ItemTemplate>
                       <asp:Label ID="Label11" runat="server" Text='<%# Bind("Ciudad") %>'></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField>
               <asp:TemplateField HeaderText="Comuna" SortExpression="Comuna">
                   <EditItemTemplate>
                       <asp:DropDownList ID="DropComuna" runat="server" DataSourceID="SqlDataSourceComuna" DataValueField="PAR_COD_PAR" DataTextField="PAR_DES_PAR"></asp:DropDownList>
                   </EditItemTemplate>
                   <ItemTemplate>
                       <asp:Label ID="Label10" runat="server" Text='<%# Bind("Comuna") %>'></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField>
               <asp:TemplateField HeaderText="Estado" SortExpression="Estado">
                   <EditItemTemplate>
                       <asp:DropDownList ID="DropEstado" runat="server" DataSourceID="SqlDataSourceEstado" DataValueField="PAR_COD_PAR" DataTextField="PAR_DES_PAR"></asp:DropDownList>
                   </EditItemTemplate>
                   <ItemTemplate>
                       <asp:Label ID="Label12" runat="server" Text='<%# Bind("Estado") %>'></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField>
               <asp:TemplateField HeaderText="Seleccione">
                   <EditItemTemplate>
                       <asp:Button ID="btnupdate" class="btn btn-info" runat="server" CausesValidation="True" CommandName="Update"
                           Text="Actualizar" Style="padding: 3px 3px 3px 3px"></asp:Button>
                       <asp:Button ID="btnborrar" class="btn btn-danger" runat="server" CausesValidation="True" CommandName="Delete"
                           Text="Borrar" Style="padding: 3px 3px 3px 3px"></asp:Button>
                       <asp:LinkButton ID="btncancelar" runat="server" CausesValidation="False" CommandName="Cancel"
                           Text="Cancelar" CssClass="BotonRojo" Style="padding: 3px 3px 3px 3px; margin-top: 3px"></asp:LinkButton>
                   </EditItemTemplate>
                   <ItemTemplate>
                       <asp:LinkButton ID="btnedit" class="btn btn-primary" runat="server" CausesValidation="False" CommandName="Edit"
                           Text="Editar" Style="padding: 3px 3px 3px 3px"></asp:LinkButton>
                   </ItemTemplate>
               </asp:TemplateField>
           </Columns>
       </asp:GridView>

My SqlDataSource’s:

        <asp:SqlDataSource ID="SqlDataSourceComuna" runat="server" ConnectionString="<%$ ConnectionStrings:CadenaConexion %>" SelectCommand="SELECT par_cod_par, par_des_par, par_cod_aux FROM PAR WHERE (par_cod_tab = 2) AND (par_cod_aux = @Ciudad)">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropCiudad" PropertyName="SelectedValue" DefaultValue="0" Name="Ciudad"></asp:ControlParameter>
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSourceCiudad" runat="server" ConnectionString="<%$ ConnectionStrings:CadenaConexion %>" SelectCommand="SELECT par_cod_tab, par_cod_par, par_des_par FROM PAR WHERE (par_cod_tab = 4) ORDER BY PAR_COD_PAR ASC"></asp:SqlDataSource>

Answer

This can be a challenge – and the REASON why is that the one combo box in the list is NOT bound, and does NOT exist in the database. It is ONLY a filter for the cascaded combo that DOES exist in the database.

So, you can do it this way. We will assume a person, and they want to select a hotel, but we provide a “filter”/cascade combo. We allow the user to select a city, and THENN ONLY hotels from that city.

So, our markup can be like this. The one cbo is city filter, and then the actual data value (hotel_id) is part of the data – but the city cbo as noted is JUST a fiter.

So, this markup:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataKeyNames="ID" 
    CssClass="table"
    Width="30%"
    >
    <Columns>
        <asp:BoundField DataField="Firstname" HeaderText="Firstname" />
        <asp:BoundField DataField="LastName" HeaderText="LastName"  />
        <asp:BoundField DataField="City" HeaderText="City" />

        <asp:TemplateField HeaderText="Select Hotel City">
            <ItemTemplate>
                <asp:DropDownList ID="cboCity" runat="server" Width="120px"
                    DataTextField = "City"
                    DataValueField = "City"> 
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>

        <asp:TemplateField HeaderText="Select Hotel">
            <ItemTemplate>
                <asp:DropDownList ID="cboHotels" runat="server" Width="210px"
                    DataValueField ="ID"
                    DataTextField ="HotelName"
                    ></asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Ok, now our code to load the grid.

Dim rstCity As New DataTable

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadGrid
    End If


End Sub

Sub LoadGrid()

    ' load up City list for combo box
    rstCity = MyRst("SELECT City from City ORDER BY City")

    ' load up the grid
    GridView1.DataSource = MyRst("SELECT * from People ORDER BY FirstName")
    GridView1.DataBind()

End Sub

NOTE close I declare rstCity at the class level – it will persist during the data bind process (saves us having to re-pull city combo list over and over).

so far, so easy. But we now have to for each row do the following:

Load up the city cbo box
Load up the hotel select cbo box

but also set the city cbo box to the city that we filtered to.

So, this is a bit of code, but looks like this:

Protected Sub GridView1_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles GridView1.RowDataBound

    If e.Row.RowType = DataControlRowType.DataRow Then

        ' get the data bind row data
        Dim gData As DataRowView = e.Row.DataItem
        ' load the city combo box

        Dim cboCity As DropDownList = e.Row.FindControl("cboCity")
        cboCity.DataSource = rstCity
        cboCity.DataBind()
        ' add blank row for city
        cboCity.Items.Insert(0, New ListItem("", ""))

        ' now load Hotel combo box (if we have one!!)
        Dim cboHotels As DropDownList = e.Row.FindControl("cboHotels")

        If Not IsDBNull(gData("Hotel_id")) Then

            ' get the one hotel reocrd - we need the city from that hotel
            ' to set the city cbo - that city cbo is NOT in the People databse - only a filter

            Dim rstOneHotel As DataRow = MyRst("SELECT * From tblHotels where ID = " & gData("Hotel_id")).Rows(0)
            Dim strHotelCity As String = rstOneHotel("City")

            ' set the city filter cbo to correct city
            cboCity.SelectedValue = strHotelCity

            ' load hotel combo box only with current city list
            Dim strSQL As String = "Select ID,HotelName From tblHotels WHERE City = '" &
                                    strHotelCity & "' ORDER BY HotelName"
            cboHotels.DataSource = MyRst(strSQL)
            cboHotels.DataBind()
            ' set hotels combo to current selected
            cboHotels.SelectedValue = gData("Hotel_id")
        End If

    End If

End Sub

And now we see this:

Now, the above of course is based on each row “hotel_id”.

However, we do need to wire up the city filter and cascade to the hotel.

So, we need to add a a event to the cboCity.

So, we do this:

Since the cbo box is inside of the grid, we can’t use the property sheet, but as above shows, we simply start typing in markup. intel-sense will prompt that create new event – don’t look like anything occured, but when we flip back to code behind, the code stub will exist. We also of course had to set autopostback=true.

Ok, so this cascade code is much the same as what we did in the data bind routine. Of course, when a user selects a new city, we do have to blow out the existing value, since the filter is for a different city.

That bit of code is this:

Protected Sub cboCity_SelectedIndexChanged(sender As Object, e As EventArgs)

    Dim cboCity As DropDownList = sender

    ' get current grid row
    Dim gRow As GridViewRow = cboCity.Parent.Parent

    ' filter hotels to current city
    Dim strCity As String = cboCity.SelectedItem.Text

    Dim strSQL = "SELECT * from tblHotels WHERE CITY = '" & strCity & "' ORDER BY HotelName"
    Dim cboHotels As DropDownList = gRow.FindControl("cboHotels")
    cboHotels.DataSource = MyRst(strSQL)
    cboHotels.DataBind()
    cboHotels.Items.Insert(0, New ListItem("", ""))
    ' blow out the current hotel id
    Dim MyHotelID As HiddenField = gRow.FindControl("HotelID")
    MyHotelID.Value = ""

End Sub

Now we do have ONE spot where we concatenate the city input. Since it does come from a cbo box, then the risk of sql injection is very low. But if you really sticky on that issue, then we could parametrize that bit of code.

Also, I used a helper routine that returns a data table, and that code was this:

Public Function MyRst(strSQL As String) As DataTable

    Dim rstData As New DataTable

    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    Return rstData

End Function

Edit:

the poster has noted they are looking for a C# version. I am fluent in both – but the vb code is less effort, since vb does a LOT OF auto casting for you.

However, at the end of the day, the concpets here and HOW you approach this problem is really the value here.

However, for good measure, here is the code as C#

Code to load the grid:

    DataTable rstCity = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadGrid();
    }

    void LoadGrid ()
    {
        // load up City list for combo box
        rstCity = MyRst("SELECT City from City ORDER BY City");
        // load up the grid
        GridView1.DataSource = MyRst("SELECT * from People ORDER BY FirstName");
        GridView1.DataBind();
    }

And now the item data bound event:

   protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {


            // get the data bind row data
            DataRowView gData = (DataRowView)e.Row.DataItem;

            // load the city combo box

            DropDownList cboCity = (DropDownList)e.Row.FindControl("cboCity");
            cboCity.DataSource = rstCity;
            cboCity.DataBind();
            // add blank row for city
            cboCity.Items.Insert(0, new ListItem("", ""));

            // now load Hotel combo box (if we have one!!)
            DropDownList cboHotels = (DropDownList)e.Row.FindControl("cboHotels");

            if (!DBNull.Value.Equals(gData["Hotel_id"]))
            {
                // get the one hotel reocrd - we need the city from that hotel
                // to set the city cbo - that city cbo is NOT in the People databse - only a filter

                DataRow rstOneHotel = MyRst("SELECT * From tblHotels where ID = " + gData["Hotel_id"].ToString()).Rows[0];
                string strHotelCity = rstOneHotel["City"].ToString();

                // set the city filter cbo to correct city
                cboCity.SelectedValue = strHotelCity;

                // load hotel combo box only with current city list
                string strSQL = @"Select ID,HotelName From tblHotels WHERE City = '" +
                                        strHotelCity + "' ORDER BY HotelName";
                cboHotels.DataSource = MyRst(strSQL);
                cboHotels.DataBind();
                // set hotels combo to current selected
                cboHotels.SelectedValue = gData["Hotel_id"].ToString();
            }
        }
    }

And of course the “cascade” code for the grid. While above fills the gird correct, we still want the cbo’s to function. So this:

    protected void cboCity_SelectedIndexChanged(object sender, EventArgs e)
    {
        DropDownList cboCity = (DropDownList)sender;

        // get current grid row
        GridViewRow gRow = (GridViewRow)cboCity.Parent.Parent;

        // filter hotels to current city
        string strCity = cboCity.SelectedItem.Text;

        string strSQL = @"SELECT * from tblHotels WHERE CITY = '" + strCity + "' ORDER BY HotelName";
        DropDownList cboHotels = (DropDownList)gRow.FindControl("cboHotels");
        cboHotels.DataSource = MyRst(strSQL);
        cboHotels.DataBind();
        cboHotels.Items.Insert(0, new ListItem("", ""));
        // blow out the current hotel id
        HiddenField MyHotelID = (HiddenField)gRow.FindControl("HotelID");
        MyHotelID.Value = "";
    }

And of course my “helper” routine to return a table (gets too tiring to write that over and over!!!).

So this:

    public DataTable MyRst(string strSQL)
    {
        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }