Visual Studio .NET - There must be an easier way to do this.

Asked By Byron King on 31-Mar-08 02:31 PM
Hi.  I have an ASP.NET page that displays a gridview based on search criteria.  The 5 items that determine the search criteria will be in 4 dropdown lists and 1 text box.   I'm staring to create queries for each condition of entry field, but there's got to be a better way to do this.  I'd rather not create a querystring URL with the items.  Thanks for the help.

Post is somewhat vague - Peter Bromberg replied to Byron King on 31-Mar-08 03:32 PM

If you have 5 items that need to be populated from dropdownlists and a textbox, then you should have a stored procedure that accepts the values of these 5 items as SqlParameters.  Is this what you are asking?

It's more than that. - Byron King replied to Peter Bromberg on 31-Mar-08 03:43 PM

The default search (default selections in the dropdown lists and nothing in the textbox) returns all records.  The user should be able to select some or all options in order to filter the results.  For instance, the user might select an option from one or more of the dropdowns and enter search text in the textbox. 

Don't understand why such a big deal... - Peter Bromberg replied to Byron King on 31-Mar-08 04:14 PM

So let the user select whatever they want and / or enter text in the textbox, and they can press the SEARCH button. On the postback, you would collect the items.

You can use a series of SQL CASE WHEN ... THEN ... ELSE... END

statements in your stored proc to handle situations where one or more of the parameters is NULL because they either didn't select an item from one or more dropdowns, or they didn't enter any text in the textbox.


Thanks. - Byron King replied to Peter Bromberg on 31-Mar-08 04:35 PM
I'll look into it.
Dynamically generating Query for Search - Danish Shaikh replied to Byron King on 01-Apr-08 05:08 AM

U must create Sp with input parameter which canm be null(optional).Now u can pass values for some parameter or no value at all(ie. it will pull all data)

check following example of SP:

CREATE PROCEDURE [dbo].[sp_SEARCH]
(
 @IN_PROJECT_NO varchar(50)=null,
 @IN_PROJECT_NAME varchar(250)=null, 
 @IN_DEPARTMENT int=0,
 @IN_STATUS int=0,
 @IN_FROM_DATE datetime=null,
 @IN_TO_DATE datetime=null,
 @IN_CER_SEARCH_CONDITION BIT=0 -- For identifying the Search Condition
)
AS
Declare @StrSQL nvarchar(2000)
Declare @StrParam nvarchar(2000)
Declare @SearchPrefix varchar(4)
Declare @StrDate nvarchar(2000)


-- Search Condition for Starting With & Containing
if @IN_CER_SEARCH_CONDITION = 0
BEGIN
 SET @SearchPrefix= '%'
END
ELSE
BEGIN
 SET @SearchPrefix=''
END

BEGIN

set @StrSQL = ''
Select @StrSQL = @StrSQL + ' Select ProjectDetails.Id, ProjectDetails.Revision_Version,'
Select @StrSQL = @StrSQL + ' CER_ProjectDetails.Project_Name as ProjectName,CER_ProjectDetails.Khalix_Id as ProjectNo, '
--Select @StrSQL = @StrSQL + ' CER_CERNO.Temp_Id, CER_CERNO.Revision_Version, '
Select @StrSQL = @StrSQL + ' CER_ProjectDetails.dept_id, cer_master.value as Department, '
Select @StrSQL = @StrSQL + ' CER_ProjectDetails.cer_status_id, cer_status_master.description as Status, '
Select @StrSQL = @StrSQL + ' Convert(varchar,CER_ProjectDetails.created_on,101) as Dates, '
Select @StrSQL = @StrSQL + ' CER_CostDetails.Capital_Cost_In_USD as Cost '
Select @StrSQL = @StrSQL + ', CER_CERNO.CER_No'
Select @StrSQL = @StrSQL + ',CER_ProjectDetails.Created_By'
Select @StrSQL = @StrSQL + ' from dbo.CER_ProjectDetails '
Select @StrSQL = @StrSQL + ' left join CER_CostDetails_Summarize_Capital_Items CER_CostDetails on (CER_ProjectDetails.Temp_Id = CER_CostDetails.Temp_Id and CER_ProjectDetails.Revision_Version = CER_CostDetails.Revision_Version) '
Select @StrSQL = @StrSQL + ' inner join cer_status_master on CER_ProjectDetails.cer_status_id = cer_status_master.status_id '
Select @StrSQL = @StrSQL + ' inner join cer_master on CER_ProjectDetails.dept_id = cer_master.id '
Select @StrSQL = @StrSQL + ''


if @IN_PROJECT_NO <> ''
   
 if @StrParam is not null
  select @StrParam = @StrParam + ' And CER_ProjectDetails.Khalix_Id like '+ char(39) + @searchPrefix + @IN_PROJECT_NO + '%' + char(39)
 else
  select @StrParam =  ' CER_ProjectDetails.Khalix_Id like '+ char(39) + @searchPrefix + @IN_PROJECT_NO + '%' + char(39)

  
  
if @IN_PROJECT_NAME<>''
 
 if @StrParam is not null
   Select @StrParam = @StrParam + ' And CER_ProjectDetails.Project_Name like '+ char(39) + @searchPrefix + @IN_PROJECT_NAME + '%' + char(39)
  else
  
   Select @StrParam =  ' CER_ProjectDetails.Project_Name like '+ char(39) + @searchPrefix + @IN_PROJECT_NAME + '%' + char(39)
  

if @IN_DEPARTMENT >0
 if @StrParam is not null
  Select @StrParam = @StrParam + 'AND CER_ProjectDetails.dept_id = '+  CONVERT(VARCHAR,@IN_DEPARTMENT)
  else
  Select @StrParam = '  CER_ProjectDetails.dept_id= '+  CONVERT(VARCHAR,@IN_DEPARTMENT)

if @IN_STATUS  > 0
 if @StrParam is not null
  Select @StrParam = @StrParam + ' And  CER_ProjectDetails.cer_status_id  = '+ CONVERT(VARCHAR,@IN_STATUS)
  else
  Select @StrParam = ' CER_ProjectDetails.cer_status_id  = '+  CONVERT(VARCHAR,@IN_STATUS)


--SELECT @IN_FROM_DATE, @IN_TO_DATE

if @IN_FROM_DATE IS NOT NULL  AND  @IN_TO_DATE IS NOT NULL
 begin
  
             set @StrDate= '( CER_ProjectDetails.Created_On >=' + CHAR(39) + convert(VARCHAR(30),dateadd(hh,00,dateadd(n,00,dateadd(ss,00,@IN_FROM_DATE)))) + CHAR(39)+' and CER_ProjectDetails.Created_On <='+CHAR(39)+convert(VARCHAR(30),dateadd(hh,23,dateadd(n,59,dateadd(ss,59,@IN_TO_DATE)))) + CHAR(39)+')'
  if @StrParam is not null
   Select @StrParam = @StrParam + 'AND'+ @StrDate
  else
   Select @StrParam =  @StrDate
 end


if @StrParam is not null
 begin
  select @StrSQL = (@StrSQL + ' Where ' + @StrParam)
 end