how to search data with data grid in asp.net using c#

[rohitmanhas_12]

cud any one tell me how to search particular data from database using data grid.....

i had three text boxes for firstname,lastname and age.. and a search button...
when i enter anything in any of the first two text boxes i.e either firstname or lastname or both, and enter search button it will show the particular record associated with it....
also if i enter particular age with any of the two textboxes then it will show the name with respective age in data grid.....
plz help me ....

[kvprajapati]

>cud any one tell me how to search particular data from database.

Type SELECT statement with WHERE clause.

SELECT * FROM tablename WHERE firstname=@firstname or lastname=@lastname

[Ramesh S]

Create a stored procedure as below and pass the first name, last name and age values as parameters to the stored procedure. The query will be constructed dynamically based on the values of the parameteres. You may need to check the syntax of the query as I typed in notepad.

CREATE PROCEDURE 
(
  @First_Name VARCHAR(50),
  @Last_Name VARCHAR(50),
  @AGE_Name int
)

AS

BEGIN
 DECLARE @Condition VARCHAR(200)
 DECLARE @SELECTSTMT VARCHAR(500)

 SET @Condition = ''
 @SELECTSTMT = 'SELECT First_Name, Last_Name, AGE FROM Your_Table '

 IF (@First_Name IS NOT NULL oR @First_Name <> '')
 BEGIN
   SET @Condition = 'FIRST_NAME = ''' + @First_Name + ''''
 END

 IF (@Last_Name IS NOT NULL OR @Last_Name <> '')
 BEGIN
   SET @Condition = @Condition + CASE WHEN @Condition <> '' THEN ' AND ' ELSE '' END 
   SET @Condition = @Condition  + 'LAST_NAME = ''' + @Last_Name + ''''
 END

 IF (@AGE NOT NULL OR @AGE <> 0)
 BEGIN
    SET @Condition = @Condition + CASE WHEN @Condition <> '' THEN ' AND ' ELSE ' ' END 
    SET @Condition = @Condition + 'AGE = ' + @AGE
 END

 IF @Condition <> '' 
 BEGIN
  SET @SELECTSTMT = @SELECTSTMT + ' WHERE ' + @Condition 
 END
  EXECUTE @SELECTSTMT 
END

[rohitmanhas_12]

thanx but i want to search the result by using data grid....
plz if u help me with this.....

[Ramesh S]

If you are binding the DataGrid with database, You can call the above stored procedure to search in the DataGrid.

[rohitmanhas_12]

plz help me with this...
iam not able to do it.....
don't understand how to do it....

or cud u plz provide the c# code for that...

Plz...

[rohitmanhas_12]

also when i create a stored procedure and write the above code and execute it i cum across these errors..how to remove these errors.....
....one more thing can we do the search option using c#....if yes then plz give me the code......
anyways the errors that i get while executing the stores procedure is.....................................

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@SELECTSTMT'.
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@FirstName".
Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@FirstName".
Msg 137, Level 15, State 2, Line 21
Must declare the scalar variable "@LastName".
Msg 137, Level 15, State 2, Line 24
Must declare the scalar variable "@LastName".
Msg 137, Level 15, State 2, Line 29
Must declare the scalar variable "@Age".
Msg 137, Level 15, State 2, Line 32
Must declare the scalar variable "@Age".
...........................................

[Ramesh S]

I already said that I wrote the query in notepad and didn't check the syntax. However I re-write the query and tested it.

CREATE PROCEDURE Searching
(
  @First_Name VARCHAR(50),
  @Last_Name VARCHAR(50),
  @AGE int
)

AS

BEGIN
 DECLARE @Condition VARCHAR(200)
 DECLARE @SELECTSTMT VARCHAR(500)
 
 SET @Condition = ''
 SET @SELECTSTMT = 'SELECT First_Name, Last_Name, AGE FROM Your_Table '

 IF (@First_Name IS NOT NULL OR @First_Name <> '')
 BEGIN
   SET @Condition = 'FIRST_NAME = ''' + @First_Name + ''''
 END

 IF (@Last_Name IS NOT NULL OR @Last_Name <> '')
 BEGIN
   SET @Condition = @Condition + CASE WHEN @Condition <> '' THEN ' AND ' ELSE '' END 
   SET @Condition = @Condition  + 'LAST_NAME = ''' + @Last_Name + ''''
 END
 PRINT @Condition
 IF (@AGE IS NOT NULL OR @AGE <> 0)
 BEGIN
    SET @Condition = @Condition + CASE WHEN @Condition <> '' THEN ' AND ' ELSE ' ' END 
    SET @Condition = @Condition + 'AGE = ' + CAST(@AGE AS VARCHAR(10))
 END

 IF @Condition <> '' 
 BEGIN
  SET @SELECTSTMT = @SELECTSTMT + ' WHERE ' + @Condition 
 END
 EXECUTE @SELECTSTMT 
 --PRINT @SELECTSTMT
END

You can call this query in C# and bind the resulting datatable to DataGrid.

Otherwise if need to search within DataGrid using the DataTable which you bind to it, you can use DataView to filter the DataTable as below

DataView dv = new DataView(dtCustomer); //dtCustomer is a DataTable
   dv.RowFilter = "First_Name = '" + txtFirstName.Text + "'";
   DataGrid1. DataSource= dv;
   DataGrid1.DataBind();

[rohitmanhas_12]

Thanx ,,nw iam able to create the stored procedure but how to call this query in C# and bind the resulting datatable to DataGrid.

one more thing can i have to create a new page to display the result and write the c# code there or the c# code can be written on main page...
plz reply...

[rohitmanhas_12]

Thanx nw iam able to search the data ....
sorry for replying late.....