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

Question

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: My SqlDataSource's: Answer This can be a

Accepted 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: Ok, now our code to load the grid.Dim rstCity As New DataTableProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then LoadGrid End IfEnd SubSub 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 SubNOTE 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 boxLoad up the hotel select cbo boxbut 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 IfEnd SubAnd 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 SubNow 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 rstDataEnd FunctionEdit: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; }

Advertisement

Answer

Edit: