dropdownlist in GridView Data Bound not behaving correctly ASP.NET C#

Question

I have the following SQL

SELECT Equipment.* ,cast(EquipmentId as varchar(100)) + ' ' + EquipmentName as Name
FROM Equipment 
WHERE Equipment.EquipmentCategoryID = @EquipmentCategoryID 
AND (Equipment.EquipmentSubCategoryID = @EquipmentSubCategoryID 
OR (@EquipmentSubCategoryID is null OR @EquipmentSubCategoryID = '' ))

In SQL Server it is behaving as expected. When @EquipmentCategoryId is 12 and @EquipmentSubCategoryID is null It returns all the values I want. And when @EquipmentCategoryId is 12 and @EquipmentSubCategoryID is another number it returns the smaller amount of rows which is also what I want.

In an ASP.NET dropdownlist in a GridView it is not behaving as expected, returning all the rows for each dropdownlist in the GridView, even though the @EquipmentSubCategoryID has different numbers, that work in SQL Server. The dropdownlist is bound like this.

protected void ServiceFormsGV_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {                
         HiddenField EquipmentCategoryIDHF = (HiddenField) e.Row.FindControl("EquipmentCategoryIDHF");
         HiddenField EquipmentSubCategoryIDHF = (HiddenField) e.Row.FindControl("EquipmentSubCategoryIDHF");
         DropDownList EquipmentDD = (DropDownList) e.Row.FindControl("EquipmentDD");
         EquipmentDS.SelectParameters["EquipmentCategoryID"].DefaultValue = EquipmentCategoryIDHF.Value;              
         EquipmentDS.SelectParameters["EquipmentSubCategoryID"].DefaultValue = EquipmentSubCategoryIDHF.Value; 
         EquipmentDD.Items.Clear();
         EquipmentDD.Items.Add(new ListItem());
         EquipmentDD.DataBind();  
    }
}

I can confirm that each time through the method that the HiddenFields are displaying the correct values but the ServiceFormsGV GridView has all the rows in each DropDownList. The GridView is as follows.

And in the SQLDataSource I have

CancelSelectOnNullParameter="False"

which is necessary.

I have tested the query in Configure Data Source and it behaves as expected.

Chetan · Accepted Answer

The reason behind this behavior is SqlDataSource being used as DataSource for the dropdownlists inside the GridView.

During DataGridView_RowDataBound event you are changing default values of the parameters of SqlDataSource and rebinding the dropdownlist of the row. When you change the Default values of parameters the outcome of the query execution by SqlDataSource impacts all the dropdownlists so eventually all the dropdownlists will have the query result generated by the values passed by the last row of the gridview. That is why you get the same result in all the dropdownlists of the grid.

You need to run the query everytime and retrieve the result and bind it separately to each dropdownlist without using any common datasource among them.

While the solution you found is works with no issues, it would require a SQL connection to be opened and run query against the db for each Row of DataGridView.

The approach I suggest is to retrieve the all the rows from the Equipment table and store them as a collection of entities at page level and then during rowDataBound event filter the collection for the current Category and SubCategory and bind the filtered result to the dropdown list.

Following is the entity Equipment

public class Equipment
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string CategoryId { get; set; }

    public string SubCategoryId { get; set; }

    public string FullName
    {
        get
        {
            return string.Format("{0} {1}", Id, Name);
        }
    }
}

In the Page_load populate the collection of Equipment by retrieving all the rows from Equipment table in the database.

public partial class _Default : Page
{
    private List equipments;

    protected void Page_Load(object sender, EventArgs e)
    {
        equipments = new List();

        var commandText = "SELECT EquipmentId, EquipmentName, CategoryId, SubCategoryId FROM Equipment";

        using (var connection = new SqlConnection("Data Source=.;Initial Catalog=LocalDevDb;User id=sa;Password=Password1"))
        {
            using (var command = new SqlCommand(commandText, connection))
            {
                command.CommandType = System.Data.CommandType.Text;

                connection.Open();
                using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        var equipment = new Equipment
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            CategoryId = reader.GetString(2),
                            SubCategoryId = reader.GetString(3),
                        };

                        equipments.Add(equipment);
                    }
                }
            }
        }
    }
}

Filter this collection based on the CategoryId and SubCategoryId and bind the result to the DropDown.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        DropDownList EquipmentDD = (DropDownList)e.Row.FindControl("dropDownList");

        HiddenField EquipmentCategoryIDHF = (HiddenField) e.Row.FindControl("EquipmentCategoryIDHF");
        HiddenField EquipmentSubCategoryIDHF = (HiddenField) e.Row.FindControl("EquipmentSubCategoryIDHF");

        var categoryId = EquipmentCategoryIDHF.Value;
        var subCategoryId = EquipmentSubCategoryIDHF.Value;

        Func criteria;

        if(!string.IsNullOrEmpty(subCategoryId))
        {
            criteria = criteria = equip => equip.CategoryId == categoryId && equip.SubCategoryId == subCategoryId;
        }
        else
        {
            criteria = equip => equip.CategoryId == categoryId;
        }

        var list = equipments.Where(criteria).ToList();

        EquipmentDD.DataSource = list;
        EquipmentDD.DataBind();
        EquipmentDD.Items.Insert(0, new ListItem());

    }
}

Here in my example, CategoryId and SubCategoryId columns are created with String datatype. You need to change them to appropriate if they are different than columns of your table.

This should help you solve your issue.

dropdownlist in GridView Data Bound not behaving correctly ASP.NET C#

Answers (2)

Related Questions