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?
 
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
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
cdemott33Author Commented:
Sorry, I forgot to attach my code behind and class function.  see attached
codesnippet.txt
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.