?
Solved

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

Posted on 2013-01-17
2
Medium Priority
?
310 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

741 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