Solved

How to send multiple choices to a query where statement in ColdFusion

Posted on 2013-01-17
2
308 Views
Last Modified: 2013-01-17
In my form I am using a select box to allow multiple choices and would like to send those choices to my WHERE statement. My select box and where statement are below. Can someone assist with how to send multiple "usernames" to my sql statement?
Select and SQL below:
I tried changing part of the WHERE to: b.username in <cfqueryparam  value="#form.username#" list="true" cfsqltype="CF_SQL_VARCHAR">) but I get an error: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '@P3'.


<cfselect name="username" 
	queryposition="below"
	size="5"
	query="usernames" 
	value="username" 
	display="UserName" 
	required="Yes" 
	multiple="Yes">
</cfselect>
where (user_date BETWEEN <cfqueryparam  value="#form.stDate#"  cfsqltype="CF_SQL_DATE"> AND <cfqueryparam  value="#form.EdDate# 23:59:59"  cfsqltype="CF_SQL_DATE"> and b.username = <cfqueryparam  value="#form.username#" cfsqltype="CF_SQL_VARCHAR">)

Open in new window


Thank you.
0
Comment
Question by:earwig75
  • 2
2 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 38788235
You're missing an open parenthesis. The correct syntax is:

        WHERE  ColumnName IN
                     (
                            <cfqueryparam  value="#form.username#"
                                       list="true" cfsqltype="CF_SQL_VARCHAR">
                     )

          ... etc...
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38788263
<cfqueryparam  value="#form.EdDate# 23:59:59"  cfsqltype="CF_SQL_DATE">

Btw, that won't work correctly.  cf_sql_date drops the time so you're actually excluding all records on #form.EdDate#  *except* #form.EdDate# at midnight.  A better way is:

WHERE user_date >= <cfqueryparam  value="#form.stDate#" cfsqltype="CF_SQL_DATE">
AND   user_date < <cfqueryparam  value="#dateAdd('d', 1, form.EdDate)#"  cfsqltype="CF_SQL_DATE">
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Handling onsession end function in Application.cfc 5 68
REGEX HELP 11 57
decryping the string data from visa checkout 5 43
Cold Fusion form reset button issues. 11 32
This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question