Advanced filtering for GridView control

[jeffshex]

Can anyone point me in the right direction here...>
I have a gridview that grabs CategoryID(int), VendorID(int), LoadOS(bit), and Addressable(bit) from my SQLDataSource.

I am trying to filter based off of those fields using seperate dropdown/checkbox controls called ddlCategory, ddlVendor, ckOS, and ckAddressable.

The issue that I'm fighting with is having data display when only one of the conditions is met.

For example, if the VendorID is 1, then I want to see all the rows where VendorID=1 regardless of the other 3 options (unless they are filled in or checked).

I have tried using the Where... feature and specifying the controls from the DataSource, but that seems to only work if all the conditions are satisfied.

I had to do this from Access once and I ended up having to build the SQL statement from code instead of stored procedures in this case.

Am I missing something or what?

 

[jmeckley]

drop the sqldatasource altogether and use a dynamic, parameterized query.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jeffshex]

Ok I will look into it.
Any good links to point me to?

Thanks!!!!!!

 

[jmeckley]

If I write my own data access layer I start with the DbProviderFactories and refactor from there. using this method I program to the interface (IDbConnection, IDbCommand). this allows me to easily change databases between local, testing and production databases.

most of the time I use an ORM tool like NHibernate, ActiveRecord or LLBL. right now NHibernate is my favorite. It requires xml configuration to map domain objects to database tables, but the power is incredible. ActiveRecord is built on top of NHibernate and uses class/member attributes in code so there are some compile time checks. NH is great for legacy databases and AR works well for normalized databases. NH and AR are also very easy to unit test. LLBL is quick and easy to configure becuase they provide a wizard which does 95% of the work for you. There approach to development is more inline with M$ RAD.

I would highly recommend utilizing a 3rd party ORM tool rather than rolling your own DAL.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

Ask in the SQL Programming froum if you are using SQL server. In your case there is no need for dynamic SQL, just a correctly structrued Where clause unless you are joing to multiple tables, then you will need dynamic SQL.

 

[jeffshex]

Don't know if it's the best, but it's a simple way that worked for me. I elimated the datasource and ran code to pass in parameters if need be to this SP:

ALTER PROCEDURE dbo.FilterProducts
	(
		@VendorID int = NULL,
		@CategoryID int = NULL,
		@Addressable bit = NULL,
		@LoadOS bit = NULL
	)
AS
SET NOCOUNT ON
SELECT * FROM PRO_ACTIVE
WHERE VendorID = COALESCE(@VendorID, VendorID)AND
	  CategoryID = COALESCE(@CategoryID, CategoryID)AND
	  Addressable = COALESCE(@Addressable, Addressable)AND
	  LoadOS = COALESCE(@LoadOS, LoadOS)