asked on

Filtering a Gridview (Multiple Columns)

I'm a bit of a .NET newbie so I'm looking for some general guidance.

I have a GridView that I need to create optional filters for. Basically the Gridview will load all the data on page load. The page will also have an area above the GridView where the user could enter 1 or more of the following filter options.

- My events only (boolean)
- Dates to and from (2 datetime fields)
- Customer name (string value)
- Event Name (string value)
- Event ID (integer)

How would you handle something like this? Some of my users might enter an Event Name only, leaving all other field blank. Some might enter the From and To Dates aloing with a customer name and leave the other fields blank.

I started creating my Stored Procedure SELECT statement and then realized that it is not going to work because of the wide varity of options the user has.

I've hunted the web and found plenty of examples on filtering a single column but nothing like what I'm describing, where some filter options could be blank and some have values.

How would you solve this dilemma?

(I've included my Stored Procedure SELECT statement, though I know it doesn't work)

ALTER PROCEDURE dbo.GetFilteredResultsForGrid
	(
	@PARAM_FILTERTYPE varchar(10),
	@PARAM_FROM datetime,
	@PARAM_TO datetime,
	@PARAM_CUSTOMER varchar(50),
	@PARAM_EVENTNAME varchar(150),
	@PARAM_JOB int,
	)
AS

	SELECT DISTINCT
		[PROPOSAL], [DATE], [CUSTOMER], [SHOWNAME], [SHOWDATE], [JOB], [SALE_DATE] 
	FROM 
		PROPOSALS T
	WHERE 
		T.REV = (SELECT MAX(REV) FROM PROPOSALS O WHERE T.PROPOSAL = O.PROPOSAL)
	AND
		SHOWDATE BETWEEN @PARAM_FROM AND @PARAM_TO
	AND 
		CUSTOMER LIKE '% @PARAM_CUSTOMER %'
	AND 
		SHOWNAME LIKE '% @PARAM_EVENTNAME %'
	AND 
		JOB LIKE '% @PARAM_JOB %'			
	ORDER BY 
		PROPOSAL DESC

Open in new window

robasta

You need to generate a dynamic query.

1. You can do this via a stored Procedure (More Here: http://www.codeproject.com/KB/database/Building_Dynamic_SQL.aspx)
2. Or you can use LINQ to SQL (More Here: http://www.rocksthoughts.com/blog/archive/2008/01/24/linq-to-sql-dynamic-queries.aspx)

Option 2 is easier, but if you need to use a storedproc, then use option1.

cdemott33

ASKER

Thanks for the infomation.

I've read through the first link regarding Dynamic SQL in a Store Procedure and rewrote my Stored Procedure code, however, upon opening my page in Internet Explorer no records are loaded into my GridView. The browser dose not show any error and the page loads all the design elements however there is no gridview displaying my records???

Interestingly enough I executed the store procedure in Visual Studio and it did, in fact, return results based on the values I entered in for my paramaters?

I attached my entire STORED PROCEDURE for your review. Maybe I'm missing something in order for it to get the records back to the Gridview. I'm not sure.

Can you take a look and let me know what I did wrong? Thanks!

ALTER PROCEDURE dbo.OMS_PROPOSALS_GetFilteredProposalForGrid
	(
	@PARAM_FROM datetime,
	@PARAM_TO datetime,
	@PARAM_CUSTOMER varchar(50)
	)
AS

	Declare @SQLQuery As NVarchar(4000)
	Declare @ParamDefinition As NVarchar(2000)
	
	Set @SQLQuery = '
	SELECT DISTINCT 
		[PROPOSAL], [DATE], [CUSTOMER], [SHOWNAME], [SHOWDATE], [JOB], [PROPLOCK], [SALE_DATE] 
	FROM 
		PROPOSALS T
	WHERE 
		T.REV = (SELECT MAX(REV) FROM PROPOSALS O WHERE T.PROPOSAL = O.PROPOSAL)'
	
    IF (@PARAM_FROM Is Not Null) AND (@PARAM_TO Is Not Null)
        Set @SQLQuery = @SQLQuery + ' AND (SHOWDATE BETWEEN @PARAM_FROM AND @PARAM_TO)'
	IF @PARAM_CUSTOMER Is Not Null
		Set @SQLQuery = @SQLQuery + ' AND (CUSTOMER = @PARAM_CUSTOMER)'		
		
	Set @ParamDefinition = '
				@PARAM_FROM datetime,
				@PARAM_TO datetime,
				@PARAM_CUSTOMER varchar(50)'
				
	Execute sp_Executesql @SQLQuery,
		@ParamDefinition,
		@PARAM_FROM,
		@PARAM_TO,
		@PARAM_CUSTOMER

Open in new window

cdemott33

ASKER

Sorry, I forgot to attach my code behind and class function. see attached
codesnippet.txt

tovvenki

Hi,
can you place a breakpoint in the line "MyGridView.DataSource = myFilteredResults" and see what data is returned in the datareader.

Also using SQL profiler see what data is being sent to SQL Server from the application

Thanks and regards,
Venki

ASKER CERTIFIED SOLUTION

rajeeshmca

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

13598

Why don't you just use dataviews to filter your existing data, i.e..
if (cmbConditionSelection.SelectedValue != "MyEventsOnly")
{
condition = "EventsField='" + me + "'";
}
etc......

DataView.Rowfilter = condition

then bind the dataview to your gridview
GridView1.DataSource = dvDataView
GridView1.DataBind();