Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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

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
earwig75
Asked:
earwig75
  • 2
1 Solution
 
_agx_Commented:
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
 
_agx_Commented:
<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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now