Sorry for the cryptic title, not sure how best to describe it.
I have a page used to search records in a table with about 4000 records. The query for the search uses 10 optional parameters provided via textboxes, dropdowns and datepickers. When the page loads, the grid should start empty. The user selects as many paramas suit their search needs and clck a button. i.e. dept='finance' and role='csr' and startdate > 'somedate'. all parameters are optional and ANDed together using something like
Example SQL
ALTER PROC test (
@Dept varchar(50)=null,
@Role varchar(50)=null,
@Startdate datetime=null ) AS
SELECT some, bunch, of, fields
FROM sometable
WHERE
(Dept = @Dept or isNull(@Dept,'')='')
AND (Role = @Role or isNull(@Role,'')='')
AND (startdate > @somedate or isNull(@somedate,0)=0)
* Grid must start with zero records on page load
* If the user select no parameters then ALL records will be returned.
As it stands now when the page loads ALL 4000 records are returned because I have CancelSelectonNullParameter=false in the SQL DataSource. This can usually causes a timeout.
I know I can do some of this via custom paging but thats not really what I want. I want the page to load with zero records in the grid but if the user clicks search with no parameters set then yes all recods can be returned. at which point normal paging will occur.
Thanks
Kelly
I have a page used to search records in a table with about 4000 records. The query for the search uses 10 optional parameters provided via textboxes, dropdowns and datepickers. When the page loads, the grid should start empty. The user selects as many paramas suit their search needs and clck a button. i.e. dept='finance' and role='csr' and startdate > 'somedate'. all parameters are optional and ANDed together using something like
Example SQL
ALTER PROC test (
@Dept varchar(50)=null,
@Role varchar(50)=null,
@Startdate datetime=null ) AS
SELECT some, bunch, of, fields
FROM sometable
WHERE
(Dept = @Dept or isNull(@Dept,'')='')
AND (Role = @Role or isNull(@Role,'')='')
AND (startdate > @somedate or isNull(@somedate,0)=0)
* Grid must start with zero records on page load
* If the user select no parameters then ALL records will be returned.
As it stands now when the page loads ALL 4000 records are returned because I have CancelSelectonNullParameter=false in the SQL DataSource. This can usually causes a timeout.
I know I can do some of this via custom paging but thats not really what I want. I want the page to load with zero records in the grid but if the user clicks search with no parameters set then yes all recods can be returned. at which point normal paging will occur.
Thanks
Kelly