Pass Select query to SqldataSource SelectCommand?

[nell1]

Hi,

I am in the process of building a small application that uses MS Access as the back end. One of my pages is a "search" page which has a few server cotrols that allows the user to enter data to search the DB.

At he moment the data is accessed via the 'SqlDataSource' control and displayed via the 'DataGrid' control. I have a SELECT query already in the 'SelectComand' parameter however I am finding that as my search parameters grow I need to build the SELECT query dynamically and pass it to the 'SelectCommand' of the 'SqlDataSource' control.

Is this possible or am I going about it the wrong way?

Many thanks,

Nell1

 

[JoeZim]

I've had a similar situation and coded a dynamic SQL statment like the following (this is a SQL Server sp):

SET @strSQL1 = 
	'(SELECT OBLIGOR, NAME
	FROM AFS_OBLG_SEARCH
	WHERE NAME LIKE ''%' + UPPER(RTRIM(LTRIM(@filter1))) + '%'' 
	)'

SET @Union = 
	' 
	UNION

	'

SET @strSQL2 = 
	'(SELECT OBLIGOR, NAME
	FROM AFS_OBLG_SEARCH
	WHERE NAME LIKE ''%' + UPPER(RTRIM(LTRIM(@filter2))) + '%'' 
	)'

SET @strSQL3 = 
	'(SELECT OBLIGOR, NAME
	FROM AFS_OBLG_SEARCH
	WHERE NAME LIKE ''%' + UPPER(RTRIM(LTRIM(@filter3))) + '%'' 
	)'

SET @strSQL4 = 
	'(SELECT OBLIGOR, NAME
	FROM AFS_OBLG_SEARCH
	WHERE NAME LIKE ''%' + UPPER(RTRIM(LTRIM(@filter4))) + '%'' 
	)'

SET @strSQL5 = 
	'(SELECT OBLIGOR, NAME
	FROM AFS_OBLG_SEARCH
	WHERE NAME LIKE ''%' + UPPER(RTRIM(LTRIM(@filter5))) + '%'' 
	)'

SET @order = 
	'
	ORDER BY NAME

	'

If @filtersearch1 <> 'do not use this parameter'
	BEGIN
		Set @strSQL = @strSQL1
 	END

If @filtersearch2 <> 'do not use this parameter'
	BEGIN
		Set @strSQL = @strSQL + @Union + @strSQL2
	END

If @filtersearch3 <> 'do not use this parameter'
	BEGIN
		Set @strSQL = @strSQL + @Union + @strSQL3
	END

If @filtersearch4 <> 'do not use this parameter'
	BEGIN
		Set @strSQL = @strSQL + @Union + @strSQL4
	END

If @filtersearch5 <> 'do not use this parameter'
	BEGIN
		Set @strSQL = @strSQL + @Union + @strSQL5
	END

Set @strSQL = @strSQL + @Order

EXEC(@strSQL)

Basically, I evaluate the parameters and only include those that are actually needed. It still runs a bit slower when they enter something in each parameter field, but it definitely executes faster on the normal 1 or 2 parameter queries.

Again, this is part of a sql stored procedure that receives parameters from the asp page. if a paramter field is empty on the page, I send 'do not use this parameter' as the parameter value so the sp knows not to use it.

Not sure if this is the best way to go or if this will help you, but I had a similar need to build a select statement dynamically.

 

[SQLDenis]

What's wrong with OR??????

SET @strSQL1 = 
    '(SELECT OBLIGOR, NAME
    FROM AFS_OBLG_SEARCH
    WHERE NAME LIKE ''%' + UPPER(RTRIM(LTRIM(@filter1))) + '%'' 
    )'
    

SET @strSQL2' = OR NAME LIKE ''%' + UPPER(RTRIM(LTRIM(@filter2))) + '%'' 
    )'


SET @strSQL3' = OR NAME LIKE ''%' + UPPER(RTRIM(LTRIM(@filter3))) + '%'' 
    )'

ETC ETC ETC

However I would have passed the parm as a delimited string then inserted then into a temp table joined the temp table on the real table and voila

You could have also create a user defined table funtion that returns a table and joined the UDF on the table



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/