Paged Search in GridView

I have a paged search that I want to return the values based on selected criteria.

I have a dropdown box which the user selects the field they want to search by then a textbox they type their criteria in.

Later on I will build more functionality, but for now I'll focus on these two fields...

I have it working where the user can type in the search field and return a result by having the column to search by hard coded:


<asp:AccessDataSource ID="AccessDataSource3" runat="server" DataFile="~/App_Data/wireless.mdb"
SelectCommand="SELECT * FROM [tblVZWQuotes] WHERE Quote_Number LIKE ?">
<SelectParameters><asp:ControlParameter ControlID="txtSearch" PropertyName="Text" />
</SelectParameters></asp:AccessDataSource>


then when the user clicks the search button I have this code:

GridView1.DataSourceID = "AccessDataSource3"
GridView1.DataBind()


Like I said that is all working fine... in the select statement above, how can I replaced the Quote_Number with the SelectedValued of the dropdown list...

I tried adding another control Parameter but I haven't had any luck with it.

Thanks!!!!!

I believe you'll have to update the SELECT Statement from the OnSelecting event. Or use a Stored Procedure, and pass in the column name as one of the options, and use a CASE statement or dynamic SQL within the SP.never mind on the second part. i missed that you were in Access.

So that means that I can only use two variables then? I wanted to use a field selector, a string input textbox, and searh by selector ( =, LIKE, BETWEEN, etc). And maybe another variable to search by textbox 1 then refine results further by a second criteria.

The onSelect event makes sense but how will I pass the value already accpeted by the textbox?

I think you can still use a ControlParameter even if you change the SQL Statement. Otherwise, just build the entire SQL Statement in OnSelecting, and don't use an "official" Parameter.

I'll give it a whirl... the million dollar question:

when you have your select statement, do I put ? for both

Select * From tblVZWQuotes WHERE ? LIKE ?

Something doesn't seem right to me there

Ok I have it working here it is:


Dim SQLString As String = "SELECT * FROM [tblVZWQuotes]"

If txtSearch.Text <> "" Then

If TheOperator.SelectedValue = " LIKE " _
Or TheOperator.SelectedValue = " Not LIKE " Then
'-- "Contains" comparison, e.g.,
'-- WHERE field LIKE 'value'
SQLString &= " WHERE " & drpDnCatSearch.SelectedValue & _
TheOperator.SelectedValue & "'&#37;" & txtSearch.Text & "%'"

Else

If drpDnCatSearch.SelectedValue <> "BookPrice" And _
drpDnCatSearch.SelectedValue <> "BookQty" Then
'-- Alphanumeric comparising, e.g.,
'-- WHERE field = 'value'
SQLString &= " WHERE " & drpDnCatSearch.SelectedValue & _
TheOperator.SelectedValue & "'" & txtSearch.Text & "'"
Else
'-- Numeric comparison, e.g.,
'-- WHERE field = value
SQLString &= " WHERE " & drpDnCatSearch.SelectedValue & _
TheOperator.SelectedValue & txtSearch.Text
End If

End If

End If
AccessDataSource3.SelectCommand = SQLString
GridView1.DataSourceID = "AccessDataSource3"
GridView1.DataBind()


Now my only problem... I have created a custom pager template... It displays First Previous (Previous 4 Pages) Current Page (Next 4 Pages) Next Last

When I retrieve the fields on the Button Onclick event (shown above) it returns my results but as soon as you click and of the customer pager template links it defaults back to my original AccessDataSource...

How can I fix this without placing the identical code against all of the link button onclick events in the pager template (or without having to create a function).


I really wanted to do this by using paramters as talked about earlier in this post but nothing i tried was working.



Edited:

I should add, I have this working by placing the same code shown above in the On_Click event for the Button and the DataBound event for the GridView.... I know there has to be a better way.

Like I said I really want to work it with the code from the AccessDataSource Select Statement using parameters, but haven't had the luck I was hoping for.

What happened was on postback, your grid was REPOPULATED with the default values. You are probably populating your grid on the Page_Load event.
You can check if there was a postback with the

If Not me.IsPostBack then
End If

construct.

You can also try to get the VIEWSTATE of the textbox and dropdownlist before the postback, and then use the values to replenish your grids data.

You mentioned you are using your own Custom Grid.

put something like this in the click even of your grids "Next" button

If DgControl.PageCount > 1 And DgControl.CurrentPageIndex < (DgControl.PageCount - 1) Then
DgControl.CurrentPageIndex = DgControl.CurrentPageIndex + 1
'Session("CurentPage") = DgControl.CurrentPageIndex
DgControl.DataBind()
Else
'btnNext.Enabled = False
End If

and then

DgControl.CurrentPageIndex = e.NewPageIndex
DgControl.DataBind()

in the grid's PageindexChanged event.

or you can also create a custom event for the grid like so:

Public Event PageChange(ByVal sender As Object, ByVal e As EventArgs, ByVal pintCurPage As Integer)


and then raise the event when needed like so:

RaiseEvent PageChange(Me, e, DgControl.CurrentPageIndex)

I hope this helps.

Thanks, that helps... because I have so many controls in the pager template, I think the only thing that will make sense will be to work with the custom event for the page change for the GridView.

I don't know if this will be more work then what I have now but I am definitely going to weigh the options.

Using the GridViews Databound event and the OnClick event I have this working.

I really thought there would have to be a way to parameter in all of the different search variables into the AccessDataSource Select Statment, then paging would not be an option.

Here is my GridView_DataBound code to show you how this works... the If txtSearch.Text code is repeated in the on_click event then it is binded to AccessDataSource3 vs the AccessDataSource1 which is binded on_load


Dim SQLString As String = "SELECT * FROM [tblVZWQuotes]"

If txtSearch.Text <> "" Then

If TheOperator.SelectedValue = " LIKE " _
Or TheOperator.SelectedValue = " Not LIKE " Then
'-- "Contains" comparison, e.g.,
'-- WHERE field LIKE 'value'
SQLString &= " WHERE " & drpDnCatSearch.SelectedValue & _
TheOperator.SelectedValue & "'&#37;" & txtSearch.Text & "%'"

Else

If drpDnCatSearch.SelectedValue <> "BookPrice" And _
drpDnCatSearch.SelectedValue <> "BookQty" Then
'-- Alphanumeric comparising, e.g.,
'-- WHERE field = 'value'
SQLString &= " WHERE " & drpDnCatSearch.SelectedValue & _
TheOperator.SelectedValue & "'" & txtSearch.Text & "'"
Else
'-- Numeric comparison, e.g.,
'-- WHERE field = value
SQLString &= " WHERE " & drpDnCatSearch.SelectedValue & _
TheOperator.SelectedValue & txtSearch.Text
End If

End If

End If
AccessDataSource3.SelectCommand = SQLString
'GridView1.DataSourceID = "AccessDataSource3"
'GridView1.DataBind()
lblRecord.Text = GridView1.PageSize
lblPageCurrent.Text = GridView1.PageIndex + 1
lblPageCount.Text = GridView1.PageCount

If GridView1.PageIndex >= 4 Then
GridView1.BottomPagerRow.FindControl("Neg4").Visible = True
End If
If GridView1.PageIndex >= 3 Then
GridView1.BottomPagerRow.FindControl("Neg3").Visible = True
End If
'Creates the First Button
If GridView1.PageIndex >= 2 Then
GridView1.BottomPagerRow.FindControl("FirstButton").Visible = True
GridView1.BottomPagerRow.FindControl("Neg2").Visible = True
End If
'Creates the Previous Button
If GridView1.PageIndex >= 1 Then
GridView1.BottomPagerRow.FindControl("PreviousButton").Visible = True
GridView1.BottomPagerRow.FindControl("Neg1").Visible = True
End If
'Creates the Next Button
If GridView1.PageIndex + 1 < GridView1.PageCount Then
GridView1.BottomPagerRow.FindControl("NextButton").Visible = True
GridView1.BottomPagerRow.FindControl("Plus1").Visible = True
End If
'Creates the Last Button
If GridView1.PageIndex + 1 < GridView1.PageCount - 1 Then
GridView1.BottomPagerRow.FindControl("LastButton").Visible = True
GridView1.BottomPagerRow.FindControl("Plus2").Visible = True
End If
If GridView1.PageIndex + 1 < GridView1.PageCount - 2 Then
GridView1.BottomPagerRow.FindControl("Plus3").Visible = True
End If
If GridView1.PageIndex + 1 < GridView1.PageCount - 3 Then
GridView1.BottomPagerRow.FindControl("Plus4").Visible = True
End If

The problem with using the DataBound event is that it runs after the data is pulled from the database. So basically, you're telling it to get the data, before you tell it what data you want.

I still think the OnSelecting event is the way to go. I'll see if I can get an example working later today. no promises, but i'll try.

OK. This is a very basic page, but I think it does what you're looking for. The paging will keep the filter. Either the button starts the filtering, or it will take effect with the next click. If I was really setting this up, I would have used CodeBehind, but for posting it here, this was easier.
<&#37;@ Page Language="VB" AutoEventWireup="false" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
GridView1.DataBind()
End Sub

Protected Sub BuildSQL(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
If TextBox1.Text <> "" Then
e.Command.CommandText = "Select * From dbo.tblTestFilter where " & DropDownList1.SelectedValue & " like '%" & TextBox1.Text & "%'"
Else
e.Command.CommandText = "Select * From dbo.tblTestFilter"
End If
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Filter Grid</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:waynetestConnectionString %>"
SelectCommand="SELECT * FROM dbo.tblTestFilter" OnSelecting="BuildSQL"></asp:SqlDataSource>
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem>Col1</asp:ListItem>
<asp:ListItem>Col2</asp:ListItem>
<asp:ListItem>Col3</asp:ListItem>
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="True"
DataSourceID="SqlDataSource1" PageSize="5">
</asp:GridView>
</div>
</form>
</body>
</html>

That is great! Thanks!

Helps a lot!!!

Thanks for taking the time... your way is definitely more efficient