Solved

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

Posted on 2013-01-17
2
309 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 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

Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

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…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 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