Solved

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now