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

cdemott33Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

robastaCommented:
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.
0
cdemott33Author Commented:
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

0
cdemott33Author Commented:
Sorry, I forgot to attach my code behind and class function.  see attached
codesnippet.txt
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

tovvenkiCommented:
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
0
rajeeshmcaCommented:
hI CDEMOTT33,

tRY THE FOLLOWING QUERY

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 COALESCE(@PARAM_FROM, SHOWDATE) AND COALESCE(@PARAM_TO, SHOWDATE)
            AND
            CUSTOMER LIKE '%' + @PARAM_CUSTOMER + '%'
            AND
            SHOWNAME LIKE '%' +  @PARAM_EVENTNAME + '%'
            AND
            JOB LIKE '%' + @PARAM_JOB + '%'

Pass DBNull.Value as parameter if u are not going to pass any value for the Date. for the Like Operators u can pass empty value ''
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
13598Commented:
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();
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.