Search button to filter a DataGrid

[thebison]

Hi all,

I hope someone can help with this relatively simple problem.
I am building a timesheet application using ASP.NET C# with Visual
Studio 2003.As it is only a protoype application, my database has been
made in MSDE.

I have a 'View Resources' page, the purpose of which is to show all
employees, taken from that table in the database. This works fine, up
to a point. I have a DataGrid, which is done with a DataSet and a
DataAdapter. I now want the user to be able to 'filter' the DataGrid,
using a text box for input, and a 'search' button. I have tried
different ways of doing this but can't get it to work. I imagine that
it is achieved by running some kind of different SQL statement (with a
% LIKE % operator) in the btnSearch_Click method, and re-binding the
Data Grid after the filter has finished.

So for example the user could enter 'John' in the 'First Name' text box
and click 'search', and the Data Grid would refresh accordingly with
all records LIKE 'John'.

My Page_Load code looks like this currently
-------------------------------------------------------------------------
this.sqlDataAdapter1.Fill(this.dsResources1);
DataGrid1.DataBind();
sqlConnection2.Close();

this.DataGrid1.PageIndexChanged += new
System.Web.UI.WebControls.DataGridPageChangedEvent Handler(this.GridPageIndexChanged);
--------------------------------------------------------------------------------------------

Hope someone can help, I'm sure the btnSearch_Click method is where I
need to put the code.

Thanks in advance,

Al

[Terry Burns]

DataSet11.Employees.DefaultView.RowFilter = " Firstname like '%" &
TextBox1.Text & "%' "
--
Terry Burns
http://TrainingOn.net
"thebison" <al************@btinternet.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

Hi all,

I hope someone can help with this relatively simple problem.
I am building a timesheet application using ASP.NET C# with Visual
Studio 2003.As it is only a protoype application, my database has been
made in MSDE.

I have a 'View Resources' page, the purpose of which is to show all
employees, taken from that table in the database. This works fine, up
to a point. I have a DataGrid, which is done with a DataSet and a
DataAdapter. I now want the user to be able to 'filter' the DataGrid,
using a text box for input, and a 'search' button. I have tried
different ways of doing this but can't get it to work. I imagine that
it is achieved by running some kind of different SQL statement (with a
% LIKE % operator) in the btnSearch_Click method, and re-binding the
Data Grid after the filter has finished.

So for example the user could enter 'John' in the 'First Name' text box
and click 'search', and the Data Grid would refresh accordingly with
all records LIKE 'John'.

My Page_Load code looks like this currently
-------------------------------------------------------------------------
this.sqlDataAdapter1.Fill(this.dsResources1);
DataGrid1.DataBind();
sqlConnection2.Close();

this.DataGrid1.PageIndexChanged += new
System.Web.UI.WebControls.DataGridPageChangedEvent Handler(this.GridPageIndexChanged);
--------------------------------------------------------------------------------------------

Hope someone can help, I'm sure the btnSearch_Click method is where I
need to put the code.

Thanks in advance,

Al

[thebison]

Hi Terry,
Thanks for your quick reply. I have tried that code, with some
variations but still cannot quite get the search button to have any
effect on my Data Grid when pressed? In the code below, I have
simplified the requirement to simply return all records with a DeptID
less than 2. Unfortunately it still returns all records in the table?
(Note I am running the Search on the 'Dept' table now, not 'Resource')
-------------------------------------------------------------------
private void btnSearch_Click(object sender, System.EventArgs e)
{
DataView dv = dsDept1.Tables[0].DefaultView;
dv.RowFilter = "DeptID <2";
DataGrid1.DataSource = dv;
DataGrid1.DataBind();
}
----------------------------------------------------------------------------

Any suggestions? I'm sure I am close to getting it to work! Is it
something to do with the fact I have another DataGrid1.DataBind(); in
my Page_Load section of the code?

Thanks again!

[carl]

Al,

Try this... place a DataView on your page designer, set the Table to
the appropriate table in your DataSet and use the DataView as the
Grid's datasource (you need to add the tables to your DataSet, if you
haven't already).

Then you can use this code like this in your Click event:

newDataView.RowFilter = "DeptID <2";
DataGrid1.DataBind();

I didn't look into the problem you're having, but I suspect the problem
is related to binding to the DataSet in the PageLoad and changing it to
the DataView in the click method. I think you'll find a DataView
created at design makes RowFilters and sorting expressions easier to
use anyway.

Hope this helps,
Carl

[thebison]

Carl,

That works just right! Thanks!

Would you know how I can manipulate the RowFilter to return the rows
based on a user-input textbox?

I've tried something like

dataView1.RowFilter = "DeptName LIKE '%'+txtDeptName.text+'%'";

But this does not work.

Any suggestions?

Thanks for your help

Al

[thebison]

Extra info - the error message for the code above is

Exception Details: System.Data.EvaluateException: Cannot find column
[txtDeptName.text].

This occurs when I click on the 'Search' Button

Thanks!

[Terry Burns]

I showed you how to do this, in my first post, which by the way is a live
working example.

--
Terry Burns
http://TrainingOn.net
"thebison" <al************@btinternet.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...

Extra info - the error message for the code above is

Exception Details: System.Data.EvaluateException: Cannot find column
[txtDeptName.text].

This occurs when I click on the 'Search' Button

Thanks!

[thebison]

Terry,

Sorry, I confused myself with this! I tried the code you posted, and it
does exactly what I wanted! Thanks a lot!

The next step for me is to apply this principle to make a search on
another form, holding lists of Time Periods with Start and End Dates.
With this search, I need to to possibly search by values from TWO user
text boxes. So in theory the user could search for values between Start
Date 11/01/05 and End Date 30/01/05 OR just search based on ONE of the
two input text boxes. So if the textbox is blank the search will just
ignore it. Does that make sense?

I've googled it, but can't quite find what I'm after

Any help much appreciated.

Al

[carl]

I think you accidently wrapped txtDeptName.Text in double quotes, so
the literal value is being passed rather than the Text property. Try
this:

dataView1.RowFilter = "DeptName LIKE '%'" + txtDeptName.text + "'%'";

or...

dataView1.RowFilter = string.Format("DeptName LIKE '%{0}%'",
txtDeptName.text);

Glad this worked for you,
Carl

[Terry Burns]

Yes, the SQL syntax is

SELECT column_name FROM table_name
WHERE column_name
BETWEEN value1 AND value2

I have not tried using this in a table, so I would be interested to see if
it works.

= "column_name BETWEEN 01/01/2005 AND 01/01/2006"

let me know

--
Terry Burns
http://TrainingOn.net
"thebison" <al************@btinternet.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...

Terry,

Sorry, I confused myself with this! I tried the code you posted, and it
does exactly what I wanted! Thanks a lot!

The next step for me is to apply this principle to make a search on
another form, holding lists of Time Periods with Start and End Dates.
With this search, I need to to possibly search by values from TWO user
text boxes. So in theory the user could search for values between Start
Date 11/01/05 and End Date 30/01/05 OR just search based on ONE of the
two input text boxes. So if the textbox is blank the search will just
ignore it. Does that make sense?

I've googled it, but can't quite find what I'm after

Any help much appreciated.

Al