ASP.NET Searching Values in Datagrid

This article describes the simple method to filter the records of datagrid as per your searching crieteria regarding Name, City, State, Country etc fields. But this code is only for varchar datatypes.

Searching Records in DataGrid / Gridview

This article describes the method and the coding for searching some value in Gridview or DataGrid. Searching in Gridview/DataGrid is a basic requirement of many programmers in Asp.Net. The seaching depends upon FirstName, LastName, City, State, Country etc. So the Gridview/DataGrid shows the filtered records which matches the value to be searched.

Make one simple page with the following controls

1) One DataGrid or Gridview
2) One Dropdown List
3) One TextBox
4) One Button.

First you have to bind the datagrid normally on page_load event as we always do.

Now for searching crieteria, you have to select a value from Dropdownlist i.e. Firstname, Lastname, City, State etc.. Then you have to write the text value to be searched from the database column which you have selected from the dropdownlist. And the click the button to perform the final task.

Here is the Button's click event.

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgbtnGo.Click

            If ddSearch.SelectedIndex <> 0 And txtSearch.Text <> "" Then
                        txtSearch.Text = txtSearch.Text.Replace("'", "''")
                        Call BindGrid()
                        txtSearch.Text = txtSearch.Text.Replace("''", "'")
            Else
                        Messagebox.show("No values provided to Search")
            End If
End Sub

The BindGrid method is called from the click event of the search button.

The BindGrid Method ::

Sub BindGrid()
Dim ds As New DataSet
Dim cmd As New SqlCommand
Dim con As New SqlConnection
Dim DataGrid1 As New DataGrid
Dim ddSearch As New DropDownList
Dim txtSearch As New TextBox

Try

      cmd.CommandText = "Proc_Employees"
      cmd.CommandType = CommandType.StoredProcedure
      cmd.Parameters.Add(New SqlParameter("@mode", "SELECT"))      
      cmd.Parameters.Add(New SqlParameter("@searchfield", ddSearch.SelectedValue.ToString))
      cmd.Parameters.Add(New SqlParameter("@searchvalue", txtSearch.Text.ToString))

      Dim adp As New SqlDataAdapter
      cmd.Connection = con
      con.Open()
      adp = New SqlDataAdapter(cmd)
      adp.Fill(ds)
      DataGrid1.DataSource = ds
      DataGrid1.DataBind()

Catch Ex As Exception
      Throw Ex
Finally
   con.Close()
End Try

End Sub

And following is the Stored Procedure for retrieving the searched records from the database table. The parameters @searchfield and @searchvalue are passed to the stored procedure and then making the whole query with parameters and where clause. The resultant query will return the searched records from the database table.

The Stored Procedure ::

ALTER PROCEDURE [dbo].[PROC_EMPLOYEES]
(
@mode VARCHAR(20)='select',
@searchfield VARCHAR(20)='',
@searchvalue VARCHAR(20)=''
)
AS
BEGIN
DECLARE @STR VARCHAR(2000)
IF @mode='select'
BEGIN
   SET @STR = 'select * from Employees'
   
   IF @searchfield <> '' AND @searchvalue <> ''
   BEGIN 
         @STR = @STR + ' Where ' + @searchfield + ' like ' + '''' + @searchvalue + '%'+ '''' 
   END 
   PRINT(@STR)
   EXEC(@STR)
END
End

Note :: This will work only for Varchar DataType.

This is a simple practice example which has no concerns with paging and other stuffs. This is a normal coding to search the fields value from the datagrid.

Hope this helps.