How to search records with GridView in ASP.NET

No.of Views2956
Bookmarked0 times
Downloads 
Votes0
By  Sanjay Verma   On  02 Dec 2010 09:12:30
Tag : ASP.NET , Grid Controls
In this article, i will discuss with you guys,how to implement search records with gridview in asp.net
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

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.

 

 
Sign Up to vote for this article
 
About Author
 
Sanjay Verma
Occupation-Not Provided
Company-Not Provided
Member Type-Fresh
Location-India
Joined date-12 Nov 2010
Home Page-Not Provided
Blog Page-Not Provided
 
 
Other popularSectionarticles
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top
</