How to search records with GridView in ASP.NET

Introduction

In this article, i will discuss with you guys,how to implement search records with gridview in asp.net.For example  i'm going to use simple method to filter the records of GridView as per your searching crieteria regarding Name, City, State, Country etc fields. But this code is only for varchar datatypes.

Implementation

Lets create a simple page with following controls,

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

Now 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.

Now click button and navigate to button click event and write code to filter based on your selection from the dropdown list,

VB.NET Code

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

I have compose a method to Bind data to GridView call as BindGrid,

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

Note:I have written data access code directly in page level, for the best practice always write data access code separate.

Now i have to create stored procedure,The 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.

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 GridView.Hope this helps.