GridView and SqlDataSoruce SORTING issue

Question

I have got very basic Product data table, I set up my grid view data source to sqlDataSoruce with paging and sorting options true.

But I also have a DropDownList and if a user chooses a certain product group ( Vegetables, Fruits, Frozen etc), grid view only shows that product group items.

String sqlSearchByProduct = " SELECT ID, pID,pGroupID, pName, pPrice, Status, Stock FROM productsTable  WHERE (pGroupID= ProductGroupIDDropDownList.SelectedItem.Value)";
    sqlDataSource.SelectCommand = sqlSearchByProduct ;

The grid shows all products from groupID selected from dropdownlist but the issue is when I want to sort it by name or price, grid view shows all products again.

I will be very grateful if you know how to fix this issue. Thank you.

Alex Kudryashev · Accepted Answer · 2017-11-05 23:54:23Z

1

You need to keep SelectedValue of the dropdown and the easiest way to do so is to bind all data declaratively. Something like this:

<asp:DropDownList ID="ddCategory" runat="server" DataTextField="categoryname"
    DataValueField="categoryid"
    AppendDataBoundItems="true" DataSourceID="sqlCategory" AutoPostBack="true">
    <asp:ListItem Value="0" Text="- Select -"></asp:ListItem>
</asp:DropDownList>

<asp:SqlDataSource ID="sqlCategory" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindCnn %>"
    SelectCommand="select categoryid,categoryname from dbo.categories">
</asp:SqlDataSource>

<asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="False" AllowPaging="True" PageSize="5" DataKeyNames="ProductID" DataSourceID="sqlNWind">
    <Columns>
        <asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False" ReadOnly="True" SortExpression="ProductID" />
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
    </Columns>
</asp:GridView>

<asp:SqlDataSource ID="sqlNWind" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindCnn %>"
    SelectCommand="SELECT Products.ProductID, Products.ProductName FROM Products where (@categoryid=0 or CategoryID=@categoryId)">
    <SelectParameters>
        <asp:ControlParameter Name="categoryid" ControlID="ddCategory" Type="Int32" PropertyName="SelectedValue" />
    </SelectParameters>
</asp:SqlDataSource>

No code behind required.

Side note: never build sql query string like this

" ... WHERE (pGroupID= ProductGroupIDDropDownList.SelectedItem.Value)"

Such code is prone to SQL Injection attack. Always use parameters.

answered Nov 5, 2017 at 23:54

Alex Kudryashev

9,5103 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AliAzra Over a year ago

Thank you, Alex, for help, but what will I do if I have got more than one DropDownList. " SELECT Products.ProductID, Products.ProductName FROM Products where (CategoryID=@categoryId) or (Price < @price) or (inStock = @stockStatusTrue" etc.. How about if user only choose only two option.

Alex Kudryashev Over a year ago

@Adil All the same. Add parameters to the select query, add dropdownlists and set all of them AutoPostBack="true". They work independently and filter select as a sum of filters. Wrap GridView into UpdatePanel for better performance.

Alex Kudryashev Over a year ago

"How about if user only choose only two option". See how where clause is built in my answer. where (@categoryid=0 /*nothing selected*/ or CategoryID=@categoryId)

Trent Milton · Accepted Answer · 2017-11-05 22:33:58Z

0

Have you rebound your data to the gridview, e.g. below

GridView1.DataBind();

answered Nov 5, 2017 at 22:33

Trent Milton

111 bronze badge

1 Comment

AliAzra Over a year ago

Yes, I did and nothing changes. Same issue

Collectives™ on Stack Overflow

GridView and SqlDataSoruce SORTING issue

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related