Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple Parameter SQL query??? Any Suggestions

Author  Topic 

sydtek22
Starting Member

10 Posts
Posted - 2007-09-19 : 17:39:04
My end result will be a query with about six parameters total. But for right now I am trying to one by one for testing purposes. This is my first SQl project. All of my information goes into an Access DB. I am basically creating a search page with parameters for the employees to use to search for data. I need the query to work if all parameters, or combinations of parameters, or just one parameter is chosen. I started with one and got to three but now I'm stuck. My first two parameters are dates, so the data can be chosen by date range, and the second is location id. If I choose date range by itself it works, If I choose location id by itself it works, but If I choose both it doesn't work. Instead of filtering it gives me everything and I have a statement for it. Can someone please help me and tell me what am I not doing right. Here is my code;

SELECT *
FROM ASFdata
WHERE
(ASFdata.reportlocid = <cfqueryparam value="#Form.reportlocid#"> AND <cfqueryparam value="#Form.datefield#"> <> datefield AND <cfqueryparam value="#Form.datefield2#"> <> datefield) OR
(ASFdata.datefield BETWEEN <cfqueryparam value="#Form.datefield#"> AND <cfqueryparam value="#Form.datefield2#"> AND NOT <cfqueryparam value="#Form.reportlocid#"> = reportlocid ) OR
(ASFdata.datefield BETWEEN <cfqueryparam value="#Form.datefield#"> AND <cfqueryparam value="#Form.datefield2#"> AND ASFdata.reportlocid = <cfqueryparam value="#Form.reportlocid#">)

Kristen
Test

22859 Posts
Posted - 2007-09-20 : 01:20:36
You basically want something like:

WHERE     (@Parameter1 IS NULL OR MyColumn1 = @Parameter1)
      AND (@Parameter2 IS NULL OR MyColumn1 = @Parameter2)

where "@Parameter1" represents the first parameter the user enters, and its value will be NULL if they type nothing.

You can replace that concept using your access parameters - such as

<cfqueryparam value="#Form.reportlocid#">

and perhaps you will have to test them to see if they are blank, rather than NULL, I'm not sure how ColdFusion will treat them.

Alternatively construct a WHERE clause that ONLY includes the parts that are used, so something like

strSQL = "SELECT * FROM ASFdata WHERE 1=1"
IF #Form.reportlocid# <> "" THEN strSQL = strSQL & " AND ASFdata.reportlocid = <cfqueryparam value="#Form.reportlocid#">"
... and so on ...

Note that the way you are using the user's parameters is open to SQL Injection - a user could enter a value which will "break" the SQL command and allow them to hack into the database.

ColdFusion has some ways of working around this I believe, but you should probably read up on it so you know how to avoid it - it will take you a long time to fix your application later, rather than to start off preventing it!

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=SQL+Injection

Kristen
Go to Top of Page

sydtek22
Starting Member

10 Posts
Posted - 2007-09-20 : 11:33:40
quote:
Originally posted by Kristen

You basically want something like:

WHERE     (@Parameter1 IS NULL OR MyColumn1 = @Parameter1)
      AND (@Parameter2 IS NULL OR MyColumn1 = @Parameter2)

where "@Parameter1" represents the first parameter the user enters, and its value will be NULL if they type nothing.

You can replace that concept using your access parameters - such as

<cfqueryparam value="#Form.reportlocid#">

and perhaps you will have to test them to see if they are blank, rather than NULL, I'm not sure how ColdFusion will treat them.

Alternatively construct a WHERE clause that ONLY includes the parts that are used, so something like

strSQL = "SELECT * FROM ASFdata WHERE 1=1"
IF #Form.reportlocid# <> "" THEN strSQL = strSQL & " AND ASFdata.reportlocid = <cfqueryparam value="#Form.reportlocid#">"
... and so on ...

Note that the way you are using the user's parameters is open to SQL Injection - a user could enter a value which will "break" the SQL command and allow them to hack into the database.

ColdFusion has some ways of working around this I believe, but you should probably read up on it so you know how to avoid it - it will take you a long time to fix your application later, rather than to start off preventing it!

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=SQL+Injection

Kristen



Thank You so much for help, but the code is still not working. Your first set did what one my originals did and that didn't work near what it does now. Your second example I used and I get an error. Here is what I got;

strSQL = "SELECT * FROM ASFdata WHERE 1 = 1"
IF #Form.datefield# <> "" AND #Form.datefield2# <> "" THEN strSQL = strSQL & " AND ASFdata.datefield BETWEEN <cfqueryparam value="#Form.datefield#"> AND <cfqueryparam value="#Form.datefield2#">"
AND
strSQL = "SELECT * FROM ASFdata WHERE 1 = 1"
IF #Form.reportlocid# <> "" THEN strSQL = strSQL & " AND ASFdata.reportlocid = <cfqueryparam value="#Form.reportlocid#">"

It said [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

It said it was the second to last line???

I really do appreciate your input and time
Go to Top of Page
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-20 : 19:10:19
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

Trying displaying the strSQL statement, rather than executing it, to see what it says. I expect the string manipulation has caused a space to be omitted or something like that.

Kristen
Go to Top of Page

sydtek22
Starting Member

10 Posts
Posted - 2007-09-21 : 15:14:27
quote:
Originally posted by Kristen

"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

Trying displaying the strSQL statement, rather than executing it, to see what it says. I expect the string manipulation has caused a space to be omitted or something like that.

Kristen


I don't understand what you mean by displaying the strSQL statement, I was thinking about a stored procedure, since this is all new to me, I'm just exploring every option as I learn about it, your thoughts?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-22 : 02:16:31
"I don't understand what you mean by displaying the strSQL statement"

My example shows creating a SQL statement in a variable called strSQL in ColdFusion.

You then need to "output" strSQL, in your HTML, so that you can see what it says.

You can cut & paste it into your SQL tools, try running it, see what error you get, fix the error, get it working until it retrieves the correct data and THEN copy those changes back in to your ColdFusion program so that it will work.

Otherwise its like working through a letter box with a long rod with a tiny piece of string on the end!

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2007-09-24 : 02:05:30
http://www.sommarskog.se/dyn-search.html
Also, why dont you use stored procedures with input parameters and avoid having concatenated sql?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-24 : 02:51:46
"Also, why dont you use stored procedures with input parameters and avoid having concatenated sql?"

IIRC that's very hard to achieve with ColdFusion

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2007-09-24 : 03:55:12
quote:
Originally posted by Kristen

"Also, why dont you use stored procedures with input parameters and avoid having concatenated sql?"

IIRC that's very hard to achieve with ColdFusion



I see.

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sydtek22
Starting Member

10 Posts
Posted - 2007-09-24 : 11:24:52
Thank You so much for the link, i will review and see what I can do. I also noticed with my code that I tried an SQL statement for every combination of options to retrieve data, but they only work by choosing a single option and not multiple, for instance I can look things up by date, or division, or Damages, but I can't combine and get correct data. What would you recommend besides ColdFusion to use these stroed procedures?

quote:
Originally posted by madhivanan

quote:
Originally posted by Kristen

"Also, why dont you use stored procedures with input parameters and avoid having concatenated sql?"

IIRC that's very hard to achieve with ColdFusion



I see.

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-24 : 11:42:11
"but I can't combine and get correct data"

Display the actual SQL you are building, in your page, and try it directly on SQL Server, modify until it works, and then migrate those changes back to the ColdFusion page.

Post any examples here that don't work and I can help with those. most likely there is a conflict between the WHERE clause criteria you are generating - e.g.

WHERE MyDate = ''

or somesuch, that won;t actually do anything useful in SQL Server

Kristen
Go to Top of Page

sydtek22
Starting Member

10 Posts
Posted - 2007-10-03 : 16:54:38
I found another method thats works, i was on track when i thought about it but I just couldn't picture it, but here it goes

SELECT *
FROM ASFdata
WHERE

<cfif #form.datefield# is not "">
ASFdata.datefield BETWEEN #form.datefield# AND #form.datefield2# AND
</cfif>

<cfif #form.datefield# is not "">
ASFdata.report = #form.report# AND
</cfif>
0=0

The 0=0 keeps the form from displaying errors when nothing is submitted and just shows everything, and now I have a dynamic search engine.It works AMAZING!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-03 : 17:57:09
Yup, that looks good. it would be much better if you used sp_ExecuteSQL instead, because that would increase the ability for the various forms of the query to be cached.

EXEC sp_ExecuteSQL
N'SELECT *
FROM ASFdata
WHERE 1=1
<cfif #form.datefield# is not "">
AND ASFdata.datefield BETWEEN @datefield AND @datefield2
</cfif>
<cfif #form.datefield# is not "">
AND ASFdata.report = @report
</cfif>
', N'@datefield datetime, @datefield2 datetime, @report int',
@datefield = #form.datefield#, @datefield2 = #form.datefield2#, @report = #form.report#

Kristen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site