Article
Drilldown Datagrid Searching with ASP.NET
The Code
Here is the complete ASP.NET page code.
Cut and paste this into your favorite editor and let it rip!
<%@ Page Language="VB" Debug="False" Explicit="True" Buffer="True"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html><head><title>Drilldown Datagrid Searching Within
Results</title></head>
<script runat="server" language="VB">
Sub Page_Load(Source As Object, E As EventArgs)
Response.BufferOutput = "True"
If Len(Request("search")) > 0 Or Len(Request("sub")) > 0 Then
If Len(Request("search")) > 0 Then Session.Clear()
MyDataGrid.CurrentPageIndex = 0 ' resets the Datagrid to page 1
BindGrid()
subsrch.visible = "true"
Else
subsrch.visible = "false"
End If
End Sub
Sub BindGrid()
Dim Source As DataView
If Len(Request("search")) = 0 Then Source =
CType(Session("CachedGrid"), DataView)
If IsNothing(Source) Then
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
Dim RcdCount As integer
Dim DS As DataSet
Dim sqlQuery As String = "SELECT emp_id, fname, lname FROM
employee where (fname like '%" & Request("search") & "%'
or lname like '%" & Request("search") & "%') Order by emp_id asc"
Dim strConn As String = "server=(local);uid=sa;pwd=;database=pubs;"
MyConnection = New SqlConnection(strConn)
MyCommand = New SqlDataAdapter(sqlQuery, MyConnection)
DS = new DataSet()
MyCommand.Fill(DS, "MyDataGrid")
MyDataGrid.CurrentPageIndex = 0
Source = DS.Tables(0).DefaultView
' Insert Dataset into Session
Session("CachedGrid") = Source
End If
If Len(Request.Form("sub")) > 0 Then
Session ("Sub") &= " and (fname LIKE '%" & Request.Form("sub")
& "%' or lname LIKE '%" & Request.Form("sub") & "%') "
Source.RowFilter = Session ("Sub").SubString(5)
End If
If (Source.Count.ToString) >= 1 Then
MyDataGrid.visible = "true"
Else
msg.text = "<br><b>No records found</b>"
subsrch.visible = "false"
MyDataGrid.visible = "false"
End If
Try
'Get count from Dataview row count, same as Datagrid row
count
count.text = "<b>" & Source.Count.ToString & "
</b>results found<br>"
MyDataGrid.DataSource = Source
MyDataGrid.DataBind()
Catch e As Exception
MyDataGrid.CurrentPageIndex = 0 'reset the Datagrid back
to page 1 on any errors
End Try
End Sub
Sub MyDataGrid_Page(sender As Object, e As
DataGridPageChangedEventArgs)
MyDataGrid.CurrentPageIndex = e.NewPageIndex
BindGrid()
End Sub
</script>
<body bgcolor="#FFFFFF" topmargin="0" marginheight="0"
onLoad="document.forms[0].search.focus();">
<center>
<h3>Datagrid Drilldown Search Example</h3>
<form method="post">
<input type="text" name="search">
<input type="submit" Value="Go">
</form>
<form runat="server">
<span id="subsrch" runat="server">
Search Within results
<input type="text" name="sub">
<input type="submit" Value="Go">
</span>
<br><asp:label id="msg" runat="server" />
<br><asp:label id="count" runat="server" />
<ASP:DataGrid id="MyDataGrid" runat="server"
AllowPaging="True" PageSize="10" PagerStyle-Mode="NumericPages"
PagerStyle-HorizontalAlign="Right"
PagerStyle-NextPageText="Next" PagerStyle-PrevPageText="Prev"
OnPageIndexChanged="MyDataGrid_Page"
BorderColor="black" BorderWidth="1" GridLines="Both" CellPadding="3"
CellSpacing="0" Font-Name="Verdana"
Font-Size="8pt" HeaderStyle-BackColor="#FF8040"
AlternatingItemStyle-BackColor="#eeeeee" />
</form></center></body></html>
Conclusion
Well there you have it, a cool way to filter data instead of running a query over again with new criteria, especially when the actual query might not bring back the desired results. It's an easy way to allow your applications more functionality, and enhance your user's end results.