Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CF with some sql 1

Status
Not open for further replies.

mochalova

MIS
Sep 20, 2007
15
US
Hello,

This might be for someone who knows CF as well as sql.


I have a list coming in from a text box called ID:
SOH01123, SOH01146,SOH01141, SOH0118


With these values I have to run a select statement:
Select * from my table
where (UserID IN ('#form.id#'))

When I do this, sql wont work because it shows in sql as:
SELECT * from my table
WHERE (UserID IN ('SOH01123, SOH01146,SOH01141, SOH01185 '))

Surely this didn't work because it should be something like this:
WHERE (UserID IN ('SOH01123','SOH01146','SOH01141','SOH01185'))

To achieve the right where statement, I have to do a replace in CF to insert the ' in the right places.

Which I do like this:
<cfset myList=ListChangeDelims(MyList,"','",',')>

That replace worked perfectly when I did a cfoutput of mylist.

However my sql statement is now a mess. It looks like
SELECT * from my table
WHERE (UserID IN ('SOH01123'','' SOH01146'',''SOH01141'','' SOH01185 '))

Any help would be great.

Thanks

 
Mocha,
You won't need the ListChangeDelims as that was not the problem.

Try:
Code:
SELECT * from my table
WHERE UserID IN (<cfqueryparam value="#Form.myTextBox#" list="true" cfsqltype="cf_sql_varchar">)


Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey
 
Ah, I see you were using the values in a SQL statement after all ;)

However my sql statement is now a mess. It looks like ...

Yes. The reason that happens is that ColdFusion automatically escapes the single quotes _within_ your values. ie Converts one (1) quote into two (2) quotes. That is done deliberately as a protection against sql injection.

The best option is to use cfqueryparam as Lyndon suggested. Then you do not need to worry about quotes at all. Plus, it is good to get into the habit of always using cfqueryparam anyway.

----------------------------------
 


Thank you both so very much.

Lyndon, you have saved the day one more time.

Thanks :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top