GridView Sorting/Paging

[Esoteric]

Okay I have a SIMPLE stupid issue I can't seem to get.

here it is, I have a gridview1 that starts with some data. I have a search box with a drop down box on what field to search. I execute it

Protected Sub SearchButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SearchButton.Click
        If SearchBox1.Text <> "" Then
            SqlDataSourceTires.SelectCommand = "SELECT CNAMEKEY AS [NAME KEY], CNAME AS [Customer Name], CADDR1 AS Address, CCITY AS City, CSTATE AS State, CZIP AS ZIP, CPHONE AS Phone FROM dbo.Customers WHERE " & SearchDropDown.Text & " LIKE '" & SearchBox1.Text & "%' ORDER BY CNAME"
            GridView1.DataBind()
        End If
    End Sub

The grid updates perfectly, however when I sort or page, it reverts to its originating Select statement.

Here is the origination code.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
                                    BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px"
                                    CellPadding="3" DataSourceID="SqlDataSourceTires" GridLines="Horizontal" PageSize="25" Font-Names="Verdana" Font-Size="Smaller" Width="100%">
                                    <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
                                    <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
                                    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
                                    <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
                                    <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
                                    <AlternatingRowStyle BackColor="#F7F7F7" />
                                </asp:GridView>
                                <asp:SqlDataSource ID="SqlDataSourceTires" runat="server" ConnectionString="<%$ ConnectionStrings:TIRESConnectionString %>"
                                    SelectCommand="SELECT CNAMEKEY AS [NAME KEY], CNAME AS [Customer Name], CADDR1 AS Address, CCITY AS City, CSTATE AS State, CZIP AS ZIP, CPHONE AS Phone FROM dbo.Customers WHERE CNAME = 'ZZZZ' ORDER BY CNAME">
                                </asp:SqlDataSource>

Thanks,

[ Ésôtêrîç ]

 

[jmeckley]

the biggesting problem I currently see is a sql injection attack. replace injected values of the sql string with command parameters.

your loosing your where clause because the clause is only contained within the search button event. when you sort the grid is bound using the sql in the sqldatasource (no filter).

suggested changes:
1. add sqlcommandparameters to the sqldatasource which link to the specified controls.
2. add a sqldatasource selectevent (or whatever the name is). within this event customize parameters as necessary. remove them if the filter is blank.
3. search button event should fire grid.databind(). nothing else.

this should fix your problems.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Esoteric]

Yeah, I was reading more and figured out that I need parameters. Thanks.

Thanks,

[ Ésôtêrîç ]