Dropdown list to filter gridview

Question

I would like my gridview to be filtered by the dropdown list I have. It is pulling specific information from the database, so when you choose a value from the dropdown list, it should search through all the records and find only records with the ddl value in them.

The code that I am using in the codebehind for the SelectedIndexChanged is not right though. I get an error message saying 'Value' is not a member of 'Integer'. This is on the line dsCompanyFilter.SelectParameters.Add

It probably has something to do with the gridview not tying to the dropdown list properly, but I am not sure how to fix that code. Please help!

  <asp:Content ID="Content2" ContentPlaceHolderID="body" Runat="Server"><br /><br /><br />
<asp:linkbutton id="btnAll" runat="server" text="ALL" onclick="btnAll_Click" />
<asp:repeater id="rptLetters" runat="server" datasourceid="dsLetters">
<headertemplate>
 |
</headertemplate>
<itemtemplate>
 <asp:linkbutton id="btnLetter" runat="server" onclick="btnLetter_Click" 
 text='<%#Eval("Letter")%>' />
 </itemtemplate>

<separatortemplate>
 |
</separatortemplate>
</asp:repeater>

<asp:sqldatasource id="dsLetters" runat="server" connectionstring="<%$ 
 ConnectionStrings:ProductsConnectionString %>"
 selectcommand="SELECT DISTINCT LEFT(ProductName, 1) AS [Letter] FROM [Product]">
</asp:sqldatasource>

Filter By Company:<asp:DropDownList ID="ddlCompany" runat="server" 
DataSourceID="dsCompanyFilter"  DataTextField="CompanyName" DataValueField="CompanyID">
</asp:DropDownList>
<asp:gridview id="gvProducts" runat="server" AutoGenerateColumns="False" 
datakeynames="ProductID" datasourceid="dsProductLookup" 
style="margin-top: 12px;">
<Columns>
     <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
     SortExpression="ProductName" />
</Columns>

</asp:gridview>

<asp:sqldatasource id="dsProductLookup" runat="server" connectionstring="<%$  
ConnectionStrings:ProductsConnectionString %>"
Selectcommand="SELECT ProductID, ProductName FROM [Product] ORDER BY [ProductName]">
 </asp:sqldatasource>

<asp:SqlDataSource ID="dsCompanyFilter" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ProductsConnectionString %>" 
    SelectCommand="SELECT [CompanyName], [CompanyID] FROM [Company]">
</asp:SqlDataSource>

</asp:Content>

This code filters the results in the gridview by Letter and the Dropdown. The problem is with the dropdown list filtering the gridview.

 Protected Sub btnLetter_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim btnLetter As LinkButton = TryCast(sender, LinkButton)
    If btnLetter Is Nothing Then
        Return
    End If
    dsProductLookup.SelectCommand = [String].Format("SELECT ProductID, ProductName 
                                                   FROM [Product] 
                                                   WHERE ([ProductName] LIKE '{0}%') 
                                                   ORDER BY [ProductName]", btnLetter.Text)
 End Sub

This is the part that has the problem. I now get the error, must declare the scalar variable @CompanyID

Protected Sub ddlCompany_SelectedIndexChanged(ByVal sender As Object, ByVal e As 
System.EventArgs) Handles ddlCompany.SelectedIndexChanged
    dsProductLookup.SelectCommand = "SELECT ProductName, CompanyID, CompanyName 
                                     FROM Product, Company 
                                     WHERE CompanyID = @CompanyID 
                                     ORDER BY ProductName"
    dsProductLookup.SelectParameters.Add("@CompanyID", DbType.Int32, 
    ddlCompany.SelectedValue)
End Sub

Are you fully populating the grid first and then filtering, not populating until a filter is selected or are you populating on a default filter first? — Yatrix
The gridview is already populated when I pull up the page. Then a user will be able to click on the dropdown list and choose a value to re-populate the gridview — Jamie
I would look into filtering your data object instead of requerying the database. If it's just to view and not edit there's no point to talk to the database again unless it's to check for new records. Linq is awesome to query data objects (collections, datasets, etc) to filter by a particular value. Querying the database just to filter results you already have will be a lot slower than populating from the data you grabbed initially. You can always update the database from the data object after. — Yatrix
That makes sense. All the products are already listed in the gridview. I've never used Linq before but I hear good things. — Jamie
It's not very difficult to do something like that. Check it out. — Yatrix

Kelsey Kelsey · Accepted Answer · 2011-10-21T16:22:44

Deleted my previous answer.

Actually try the following :

dsProductLookup.SelectParameters.Add("@ProductID",
    DbType.Int32, ddlCompany.SelectedValue)

No assignment to value as it is part of the Add. Noticed this when I tried to compile a test of my previous answer which didn't work.

EDIT: I tried the above code and it worked (did it in C#) but changed it to use DbType.Int32 for second param.

Dropdown list to filter gridview

3 Answers