The Question
I need to build a searched SQL query and a datagrid to display
the results of the search.
So an example-- I search for say 4000 and the SQL query returns all the records
+ and - 10 around that so my datagrid shows records from 3990 to 4010. e.g.
3992, 3995, 3998, 4000 and 4005 what I would like if the datagrid shows an exact
match ie 4000 for this entry to be highlighted. It can also search on text too
and that returns records normally - I would then like to not highlight them
all! I guess that's just a bol val.
Either bold the entry or highlight the row with say another background or similar
idea. Being used to ASP I could work this with a "If rs.fields("name") = strSearch
then bg = "silver" or similar as I loop the records - but .NET is keeping this
from me.
The caveat is that if there is no exact match then I just want the record highlight
ignored.
The Answer
The DataGrid has two events which can help us with the task at hand.
The ItemCreated and the
ItemDataBound events allow us to access the data items (which give us the actual data)
within a DataGrid by creating an event handler for the events. Before diving into how to setup our
event handler lets look at these two events briefly.
The ItemCreated event is raised when an item in the DataGrid
control is created, both during round-trips and at the time data is bound to the
control.
The ItemDataBound event is raised after an item is
data bound to the DataGrid control. The ItemDataBound
event is only raised at the time data is bound to the control, not on round trips.
For this example we only need to access the data item at the time it is being
bound to the control so we'll be working with the ItemDataBound event.
To assign an event handler to the ItemDataBound event we
identify the method that will handler the event in the OnItemDataBound property of the datagrid.
| <asp:DataGrid id="myDataGrid" OnItemDataBound="myDataGrid_OnItemDataBound" runat="server">...</asp:datagrid> |
Then within our .aspx.vb code behind page we can create the event handler:
Protected Sub myDataGrid_OnItemDataBound(ByVal sender As System.Object, ByVal e As DataGridItemEventArgs) Handles myDataGrid.ItemCreated
' OnItemDataBound Event Handler Code here...
End Sub
|
The declaration of the event handler must have the same parameters as the
DataGridItemEventHandler delegate declaration.
<Serializable>
Public Delegate Sub DataGridItemEventHandler( _
ByVal sender As Object, _
ByVal e As DataGridItemEventArgs _
)
|
Now that we have an understanding of how we can assign an event handler to the
ItemDataBound event of a DataGrid lets
look at how we can use the second parameter of our event handler to compare values in
the DataGrid to our search term(s).
Before we dive into the code let's create a form for submitting
searches and displaying results. On our form we place a <asp:RadioButtonList>
control to allow the user to select the type of search, a <asp:TextBox>
control to allow the user to enter their search terms, a <asp:Button>
control to submit the search, and a <asp:DataGrid>
for displaying our search results.
Our final form looks like this:
[WebForm1.aspx]
<%@ Page Language="vb" AutoEventWireup="true" Codebehind="WebForm1.aspx.vb" Inherits="DotNetJunkies.WebForm1"%>
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
<title>WebForm1</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body ms_positioning="GridLayout">
<form id="Form1" method="post" runat="server">
Search Type:
<asp:radiobuttonlist id="SearchType" repeatdirection="Horizontal" runat="server">
<asp:listitem value="Numeric">Numeric</asp:listitem>
<asp:listitem value="Text">Text</asp:listitem>
</asp:radiobuttonlist>
<asp:requiredfieldvalidator id="ValSearchType" controltovalidate="SearchType" runat="server" errormessage="Select the search type." />
<br>
Search Term:
<asp:textbox id="SearchText" runat="server" />
<asp:button id="Search" runat="server" text="Search" />
<br>
<br>
<asp:datagrid id="SearchResultGrid" runat="server" onitemdatabound="SearchResultsGrid_OnItemDataBound" autogeneratecolumns="False">
<columns>
<asp:boundcolumn datafield="NumericID" headertext="Numeric ID" />
<asp:boundcolumn datafield="Text" headertext="Text" />
</columns>
</asp:datagrid>
</form>
</body>
</html>
|
Now that we have our form for submitting our searches and displaying our results lets
look at the logic we can place in our event handlers to accomplish the task at hand.
First we need to create an event handler for the Search button's click event.
Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
mySearchString = SearchText.Text
' This method will bind our data to the datagrid
' For this HowTo we create a DataTable that
' contains a NumericID and Text column.
' The DataTable is filled with several DataRow's
' to simulate search results
BindData()
End Sub
|
As you can see we are storing the text the user is searching for within a public
variable called mySearchString. We'll use this variable in our
ItemDataBound event handler to check and see if the row should be
highlighted. The actual binding of data to the DataGrid is done in the BindData method. In this method
we would run our query against our database but for this example we create a DataTable and fill it with some dummy data
and bind it to our DataGrid.
The final peice of the puzzle is the creation of our ItemDataBound event handler.
Protected Sub SearchResultsGrid_OnItemDataBound(ByVal sender As System.Object, ByVal e As DataGridItemEventArgs) Handles SearchResultGrid.ItemDataBound
If SearchType.SelectedItem.Value.ToString() = "Numeric" Then
If e.Item.Cells(0).Text = mySearchString Then
e.Item.BackColor = System.Drawing.Color.Red
e.Item.Font.Bold = True
End If
End If
End Sub
|
The ItemDataBound event handler first checks to see
if we are performing a numeric search since we don't need to worry about highlighting any rows if
we're not. After making sure the user has selected to
perform a numeric search we use the second parameter of our event handler to check our search term against the data
that is being bound to the DataGrid.
The second parameter is defined as a DataGridItemEventArgs Class. This
class provides data for the ItemCreated and the
ItemDataBound events of the DataGrid control. We use
this class to get at the numeric value that is being bound to the DataGrid by using the
e.Item.Cells(0).Text property. If the value of the data being bound to the DataGrid
matches mySearchString then we have a match so we hightlight the
item by setting the background color to red and bolding the font. We could have also set the css style of the item by using the
e.Item.CssClass property.