How to get the ListItem based on the column name in ASP.net?

Question

I have a drop down list box like following. Under some condition, in the databound event I want to remove the items whose bitActive is set to 0 (inactive). I did not put a WHERE bitAcive!=0 in the selectCommand, because I only want to remove them under some conditions. Is there any way I could iterate the items and check the value of bitActive?

<tr>
            <td width="30%" align="right">Location<span class="littlefont">*</span></td>
            <td width="70%" align="left">
                <asp:DropDownList ID="ddlLocation" runat="server" 
                    DataSourceID="SqlDSLocation" DataTextField="txtRefLocation_Name" 
                    DataValueField="intRefLocation_ID" ondatabound="ddlLocation_DataBound">
                </asp:DropDownList>
                <asp:SqlDataSource ID="SqlDSLocation" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:SPRConnectionString %>" 
                    SelectCommand="SELECT DISTINCT [intRefLocation_ID], [txtRefLocation_Name], [location], [bitActive] FROM [tblRefLocation] ORDER BY [intRefLocation_ID]">
                </asp:SqlDataSource>
            </td>
        </tr>

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

In codebehind you can call the SQLDataSource.Select() method:

System.Data.DataView dv = (System.Data.DataView)SqlDSLocation.Select(DataSourceSelectArguments.Empty);

And then iterate through the rows returned, finding the "bitActive" rows who are set to zero and removing them from your DropDownList (code sort of hacked from the example linked above):

foreach(System.Data.DataRow row in dv.Table.Rows)
{
    // This is approximate logic, tailor this to fit your actual data
    if (row["bitActive"].ToString() == "False")
    {
        ddlLocation.Items.Remove(row["intRefLocation_ID"].ToString());
    }
}

Note that this is not removing these rows from your SQL table. Make sure you don't databind your DropDownList again after this - otherwise all the stuff you just removed will return.

EDIT: For a more efficient and elegant solution, see James Johnson's answer.

edited May 23 '17 at 12:20

Community

1
1

answered Apr 02 '12 at 16:39

Josh Darnell

11,304
9
38
66

Not to criticize the poster's answer, but this is neither an efficient or elegant way to solve the problem... – James Johnson Apr 02 '12 at 18:31
1

@JamesJohnson No, that doesn't sound like criticism *at all* =) +1 to your answer, it *is* very elegant! – Josh Darnell Apr 02 '12 at 18:40
1

I really wasn't criticizing you, because you stuck with the implementation OP was already using. I was just pointing out that there were more efficient ways. I hope you didn't take it as a slight on you, because I've seen you post some solid code. – James Johnson Apr 02 '12 at 18:50
@JamesJohnson I appreciate the compliment! Sorry I guess I did take your comment a bit personally, but it makes more sense now. That'll teach me to make assumptions =) – Josh Darnell Apr 02 '12 at 19:00

score 1 · Answer 2 · answered Apr 02 '12 at 16:53

Instead of removing items in the ItemDataBound event, why not just filter the datasource before binding it?:

var table = new DataTable("MyTable"); //assume it's populated
if (table.Rows.Count > 0)
{
    var results = table.AsEnumerable().Where(r => r.bitActive).AsDataView().ToTable();
    if (!results.HasErrors)
    {
        DropDownList1.DataSource = results;
        DropDownList1.DataBind();
    }        
}

How to get the ListItem based on the column name in ASP.net?

2 Answers2