I want to use cfselect....

[djkhalif]

Hi All,

I want to use cfselect to act as a parameter for querying specific data. My questions are:

1. What the maximum keywords can I select?

2. How can I get that to my action page to query the multiple selections?

Ex.

<cfquery>

Select *

FROM tblname

WHERE dateprod = '#FORM.Defect_Title#' or dateProd LIKE '#FORM.Defect_Title#'

</cfquery>

Does this make sense? I get nothing when I attempt this when selecting multiples or when I select one I get double that one.

Thanks in Advance,

DJ Khalif   

[JR__Bob__Dobbs-qSBHQ2]

1. I don't think that there is a constraint on the number of items that can be selected in a CFSELECT if multiple="yes".  Someone please correct me if I am mistaken.

2. I believe that the selected items will be passed in a comma delimited string.  Assuming that Defect_Title is your CFSELECT tag name your query would look like the sample below.  The syntax may vary based on your database server version and the data type of the dateprod field.

If this doesn't help please post your code and some sample data along with a description of what you are trying to accomplish.

<cfquery>

Select *

FROM tblname

WHERE dateprod IN ( <cfqueryparam value"#FORM.Defect_Title#" cfsqltype="cf_sql_varchar" list="yes" /> )

</cfquery>

CFQUERYPARAM
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html#1102474

[djkhalif]

Bob,

Thanks. I think I found the answer:

WHERE
   tlkp_Defects.Defect_Title IN (#ListQualify(FORM.Defect_Title, "'")#).

I used this to return the results of a multy select. What I keep noticing is, my cfdump returns two of each result. I know there is something wrong with my query. I am going to work on that tonight.

Thanks,

DJ Khalif

[ilssac · Accepted solution]

It is really prefered that you use the <cfqueryparam...> tag.  Otherwise you are potentially exposing your database to SQL injection attacks.

P.S.

There is no limit on how may items can be in a select that of which I know.  But most DBMS do have a limit on how long an IN clause can be.  But I think most modern databases have quite a large list.  If you are not talking hundreds or thousands of items then you are probably o.k.

[Adam Cameron.]

There is no limit on how may items can be in a select that of which I know.

Not according to the HTML spec no.  However climbing out of the HTML box, one could put an onclick handler in place which tallies up how many items have been selected already, and disallow any more than n number of current selections.

But most DBMS do have a limit on how long an IN clause can be.  But I think most modern databases have quite a large list.  If you are not talking hundreds or thousands of items then you are probably o.k.

It's 1000 or 2000 or thereabouts, depending on the DB system.  One can have multiple IN statements though, so if one has 3000 items to check one can have one IN statement with the first 2000, and a second with the remaining 1000.

This, of course, is about as performant as swimming through treacle, so I don't recommend it.

--

Adam

[TLC-IT]

Absolutely you should be using "<cfqueryparam>" to provide the string to the SQL side.  Aside from the "SQL injection" issues, there may well be string-size limitations (e.g. "4K bytes") on the size of a single SQL statement ... where no such limitations exist on the size of a parameter-value.

You can certainly expect the computer to accept very-large strings.  ("It's a big boy now...")  Nevertheless, you do want to design your app in such a way that you stay well clear from any such pratfalls.  If you find yourself potentially having to send "hundreds of items at a time" in an HTTP POST stream ... even though the computer might not complain, your end-users have not a hope-in-Hades of accomplishing the arduous task that you have set out for them.