Search button to filter a Dataset

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.DataGridPageChangedEventHandler(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

EltonW
Sun Mar 05 11:18:26 CST 2006

Use DataView as data source:

DataView dv = ds.Tables[0].DefaultView;
dv.RowFilter = "FirstName like '%John%'";
datagrid.DataSource = dv;
datagrid.DataBind();

HTH

Elton Wang

"thebison" wrote:

> 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.DataGridPageChangedEventHandler(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
Sun Mar 05 13:10:09 CST 2006

Hi,
Thanks for replying so quickly.
Is it possible to change this code so that it does the search based on
whatever text the user has entered into the search textbox...?

I imagine something like this:

dv.RowFilter = "FirstName like '%'+txtFirstname.text+'%'";

This doesn't seem to work though?

Thanks again for your help!

Al

EltonW
Sun Mar 05 14:12:27 CST 2006

dv.RowFilter = "FirstName like '%'+txtFirstname.text+'%'";

basically is correct. What I 'm not sure is FirstName. The
dataview.RowFilter property works like sql query condition (without WHERE). I
just assume your column name is FirstName. It should be what ever it is.

HTH

"thebison" wrote:

> Hi,
> Thanks for replying so quickly.
> Is it possible to change this code so that it does the search based on
> whatever text the user has entered into the search textbox...?
>
> I imagine something like this:
>
> dv.RowFilter = "FirstName like '%'+txtFirstname.text+'%'";
>
> This doesn't seem to work though?
>
> Thanks again for your help!
>
> Al
>
>

Cor
Mon Mar 06 00:11:57 CST 2006

Thebison,

Just simple
>
> dv.RowFilter = "FirstName like '%'+txtFirstname.text+'%'";
>
dv.RowFilter = "FirstName = '" + txtFirstname.text

See yourself what the like command does in this, or even if it exist. The
like is everywhere else implemented, it is showed with the wildcards which
are *

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp

I hope this helps,

Cor

thebison
Mon Mar 06 08:01:27 CST 2006

Hi again,
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!

Cor
Mon Mar 06 10:39:39 CST 2006

TheBison,


> 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?

Probably. The databind is that what you do as the last thing in your
program.
Why did you not just tried?

Cor