Allow single quote in query

[bonneylake]

I got one last question. Its more related to my question about the serials and would post it in that forum but we already closed it an i think its a simple question, if it needs a new forum let me know :).

When i was testing out insertinga description and a resolution (both textarea fields).When i go to submit i noticed a problem.if you put any words like for example: here's in the field it will not submit it. The comma is what it is having trouble on. An i have a textarea field like these 2 an it has no trouble with it an they have everything the same except how it inserts. This is what i have for when it inserts for both resolution and description

<CFIF REQUEST_METHOD EQ "POST">
<CFSET machineListLen = listLen(Form.serialcount)>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">
 <CFSET serialnum       = "Form.serialnum_" & machineCount>
 <CFSET modelno         = "Form.modelno_" & machineCount>
 <CFSET producttype     = "Form.producttype_" & machineCount>
 <CFSET softhardware    = "Form.softhardware_" & machineCount>
 <CFSET resolution      = "Form.resolution_" & machineCount>
 <CFSET resdate         = "Form.resdate_" & machineCount>
 <CFSET resvertified    = "Form.resvertified_" & machineCount>
 <CFSET vertifidate     = "Form.vertifidate_" & machineCount>
 <CFSET deptvendor      = "Form.deptvendor_" & machinecount>
 <CFSET hardwarefailure = "Form.hardwarefailure_" & machineCount>
 <CFSET rma             = "Form.rma_" & machineCount>
 <CFSET thedescription  = "Form.thedescription_" & machineCount>

<cfquery name="serial" datasource="CustomerSupport">
   exec usp_CS_Insertserial 
     '#evaluate(serialnum)#','#Form.ID#','#evaluate(modelno)#','#evaluate(producttype)#',
     '#evaluate(softhardware)#','#evaluate(resolution)#','#evaluate(resdate)#',
     '#evaluate(resvertified)#','#evaluate(vertifidate)#','#evaluate(deptvendor)#',
     '#evaluate(hardwarefailure)#','#evaluate(rma)#'
   </CFQUERY>
   
<!---Inserts information into notes_descr table.--->
<cfquery name="description" datasource="CustomerSupport">
    exec usp_CS_Insertdescription
   '#evaluate(serialnum)#','#Form.ID#','#evaluate(thedescription)#','#Form.fk_addedBy#'
</cfquery>

</CFLOOP>
</CFIF>

I thought/think its the listLen causing the trouble an i tried this listLen(Form.se rialcount," ' ") to make it so it would accept commas, but
it didn't work. But i am basically wondering if there is a way to make it accept commas or if the way i am doing it will just not accept commas?

Thank you,
Rach

[acoder]

You need to escape the single quotes. If you use cfqueryparam (read up on it), it will do it for you automatically.

You can also avoid the evaluate statements by using the form struct, e.g.

<CFSET serialnum       = Form["serialnum_" & machineCount]>

[bonneylake]

Hey Acoder,

But i am confused on the cfqueryparam. Basically from the examples online
it looks like you put the cfqueryparam inside the cfquery correct? an since i am using 2 different cfquerys would i put the cfqueryparam in both cfquerys?and then what would the value be for both? here is the example i am looking at

http://www.cimmyt.org/CFDOCS/CFML_Reference/Tags79.html

Thank you again :),
Rach

[acoder]

Yes, you should be using cfqueryparam for all user input into a cfquery and you would use it for each field.

Instead of the link that you've looked at, get the latest docs from the Adobe website and for your version of Coldfusion. Tags can sometimes change from version to version.

[bonneylake]

Hey Acoder,

Well it looks like what i have is for the correct version of mine. But i must admit i am still baffled. Are you saying to do something like

<cfquery name="serial" datasource="CustomerSupport">
   exec usp_CS_Insertserial 
'#evaluate(serialnum)#','#Form.ID#','#evaluate(modelno)#','#evaluate(producttype)#',
     '#evaluate(softhardware)#','#evaluate(resolution)#','#evaluate(resdate)#',
     '#evaluate(resvertified)#','#evaluate(vertifidate)#','#evaluate(deptvendor)#',
     '#evaluate(hardwarefailure)#','#evaluate(rma)#'
<cfqueryPARAM value =#evaluate(serialnum)#>
   </CFQUERY>
   
<!---Inserts information into notes_descr table.--->
<cfquery name="description" datasource="CustomerSupport">
    exec usp_CS_Insertdescription
  '#evaluate(serialnum)#','#Form.ID#','#evaluate(thedescription)#',
'#Form.fk_addedBy#'
<cfqueryPARAM value =#evaluate(serialnum)#>
</cfquery>

i am just confused on what the value should be.

Thank you,
Rach

[acoder]

Yes, but not just for one value, but all values. As I mentioned earlier, you can avoid using evaluate() by using the form struct. For some fields, you should also use the CFSQLTYPE attribute of the cfqueryparam tag.

Note: split from URL Method for 3 tables?.

[bonneylake]

Hey Acoder,

I decided to try your other way with the avoid the evaluate and it won't work :(.

here is what i have, it starts having a problem after exec usp_cs_insertse rial. do i need to change the names in the cfquery to something else?

<!---Inserts information into serial table.--->
<CFIF REQUEST_METHOD EQ "POST">
<CFSET machineListLen = listLen(Form.serialcount, " ' ")>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">
 <CFSET serialnum       = Form["serialnum_" & machineCount]>
 <CFSET modelno         = Form["modelno_" & machineCount]>
 <CFSET producttype     = Form["producttype_" & machineCount]>
 <CFSET softhardware    = Form["softhardware_" & machineCount]>
 <CFSET resolution      = Form["resolution_" & machineCount]>
 <CFSET resdate         = Form["resdate_" & machineCount]>
 <CFSET resvertified    = Form["resvertified_" & machineCount]>
 <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
 <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
 <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
 <CFSET rma             = Form["rma_" & machineCount]>
 <CFSET thedescription  = Form["thedescription_" & machineCount]>

<cfquery name="serial" datasource="CustomerSupport">
   exec usp_CS_Insertserial 
     '#serialnum#','#Form.ID#','#modelno#','#producttype#',
     '#softhardware#','#resolution#','#resdate#',
     '#resvertified#','#vertifidate#','#deptvendor#',
     '#hardwarefailure#','#rma#'
   </CFQUERY>
   
<!---Inserts information into notes_descr table.--->
<cfquery name="description" datasource="CustomerSupport">
    exec usp_CS_Insertdescription
   '#serialnum#','#Form.ID#','#thedescription#','#Form.fk_addedBy#'
</cfquery>

</CFLOOP>
</CFIF>

Thank you,
Rach

[acoder]

When you say it doesn't work, do you mean with a single quote ' or just in general? Check the values of the variables.

Together with avoiding evaluate (which was just a best practice/performance issue), you still need to use cfqueryparam.

[bonneylake]

When you say it doesn't work, do you mean with a single quote ' or just in general? Check the values of the variables.

Together with avoiding evaluate (which was just a best practice/performance issue), you still need to use cfqueryparam.

Hey Acoder,

I am meaning the whole thing does not work right now. But where does the cfqueryparam need to go? never done one so i am just confused on how to go about that. But here is what i have

<!---Inserts information into serial table.--->
<CFIF REQUEST_METHOD EQ "POST">
<CFSET machineListLen = listLen(Form.serialcount, " ' ")>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">
 <CFSET serialnum       = Form["serialnum_" & machineCount]>
 <CFSET modelno         = Form["modelno_" & machineCount]>
 <CFSET producttype     = Form["producttype_" & machineCount]>
 <CFSET softhardware    = Form["softhardware_" & machineCount]>
 <CFSET resolution      = Form["resolution_" & machineCount]>
 <CFSET resdate         = Form["resdate_" & machineCount]>
 <CFSET resvertified    = Form["resvertified_" & machineCount]>
 <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
 <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
 <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
 <CFSET rma             = Form["rma_" & machineCount]>
 <CFSET thedescription  = Form["thedescription_" & machineCount]>

<cfquery name="serial" datasource="CustomerSupport">
   exec usp_CS_Insertserial 
     '#serialnum#','#Form.ID#','#modelno#','#producttype#',
     '#softhardware#','#resolution#','#resdate#',
     '#resvertified#','#vertifidate#','#deptvendor#',
     '#hardwarefailure#','#rma#'
   </CFQUERY>

<!---Inserts information into notes_descr table.--->
<cfquery name="description" datasource="CustomerSupport">
    exec usp_CS_Insertdescription
   '#serialnum#','#Form.ID#','#thedescription#','#Form.fk_addedBy#'
</cfquery>

</CFLOOP>
</CFIF>

Thank you,
Rach

[acoder]

For each of the inputs, so #serialnum# would be replaced be <cfqueryparam ...> and the same for the rest.

[bonneylake]

For each of the inputs, so #serialnum# would be replaced be <cfqueryparam ...> and the same for the rest.

hey acoder,

so something like this,except apply it to every field?

<cfquery name="serial" datasource="CustomerSupport">
exec usp_CS_Insertserial 
'<cfqueryparam value="#serialnum#"></cfqueryparam>','#Form.ID#','#modelno#',
'#producttype#','#softhardware#','#resolution#','#resdate#',
'#resvertified#','#vertifidate#','#deptvendor#',
'#hardwarefailure#','#rma#'
</CFQUERY>

Thank you,
Rach

[acoder]

Also set the cfsqltype attribute too to the correct type. Optionally, it might be an idea to set maxlength and scale where appropriate. Note that there's no closing tag. See the documentation.

[bonneylake]

Also set the cfsqltype attribute too to the correct type. Optionally, it might be an idea to set maxlength and scale where appropriate. Note that there's no closing tag. See the documentation.

Hey Acoder,

Well i tried it an it gave me the error of optional feature not implemented.Got that error after i put date for 2 of the fields

Microsoft][ODBC SQL Server Driver]Optional feature not implemented


SQL = "exec usp_CS_Insertserial '?', '123', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?'"

Query Parameter Value(s) - 

Parameter #1 = 3242 

Parameter #2 = 340TWIN 

Parameter #3 = Labtop 

Parameter #4 = Processor 

Parameter #5 = test's 

Parameter #6 = {d '2008-10-07'} 

Parameter #7 = Greg Cason 

Parameter #8 = {d '2008-10-07'} 

Parameter #9 = Vendor Software 

Parameter #10 = OutOfWarranty 

Parameter #11 = rest's 

Data Source = "CUSTOMERSUPPORT"

here is what i have in there right now

<!---Inserts information into serial table.--->
<CFIF REQUEST_METHOD EQ "POST">
<CFSET machineListLen = listLen(Form.serialcount, " ' ")>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">
 <CFSET serialnum       = Form["serialnum_" & machineCount]>
 <CFSET modelno         = Form["modelno_" & machineCount]>
 <CFSET producttype     = Form["producttype_" & machineCount]>
 <CFSET softhardware    = Form["softhardware_" & machineCount]>
 <CFSET resolution      = Form["resolution_" & machineCount]>
 <CFSET resdate         = Form["resdate_" & machineCount]>
 <CFSET resvertified    = Form["resvertified_" & machineCount]>
 <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
 <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
 <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
 <CFSET rma             = Form["rma_" & machineCount]>
 <CFSET thedescription  = Form["thedescription_" & machineCount]>

<cfquery name="serial" datasource="CustomerSupport">
   exec usp_CS_Insertserial 
     '<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_NUMERIC">',
     '#Form.ID#',
     '<cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">',
     '<cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">',
     '<cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">',
     '<cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">',
     '<cfqueryparam value="#resdate#" CFSQLType = "CF_SQL_DATE">',
     '<cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">',
     '<cfqueryparam value="#vertifidate#" CFSQLType = "CF_SQL_DATE">',
     '<cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">',
     '<cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">',
     '<cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">'
   </CFQUERY>
   
<!---Inserts information into notes_descr table.--->
<cfquery name="description" datasource="CustomerSupport">
    exec usp_CS_Insertdescription
   '<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">',
   '#Form.ID#',
   '<cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">',
   '#Form.fk_addedBy#'
</cfquery>

</CFLOOP>
</CFIF>

Any suggestion on what i am doing wrong?

Thank you,
Rach

[acoder]

When using cfqueryparam, you don't need the single quotes around the tag. Remove them. Also make sure that the SQL types match those in the database.

[bonneylake]

When using cfqueryparam, you don't need the single quotes around the tag. Remove them. Also make sure that the SQL types match those in the database.

Hey Acoder,

I got rid of the single quotes. But i am not sure what you mean by the sql types. Like are you saying instead of using serialnum in my cfquery to use what the name is in the table which is pka_serialNo?he re is what i have

<!---Inserts information into serial table.--->
<CFIF REQUEST_METHOD EQ "POST">
<CFSET machineListLen = listLen(Form.serialcount)>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">
 <CFSET serialnum       = Form["serialnum_" & machineCount]>
 <CFSET modelno         = Form["modelno_" & machineCount]>
 <CFSET producttype     = Form["producttype_" & machineCount]>
 <CFSET softhardware    = Form["softhardware_" & machineCount]>
 <CFSET resolution      = Form["resolution_" & machineCount]>
 <CFSET resdate         = Form["resdate_" & machineCount]>
 <CFSET resvertified    = Form["resvertified_" & machineCount]>
 <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
 <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
 <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
 <CFSET rma             = Form["rma_" & machineCount]>
 <CFSET thedescription  = Form["thedescription_" & machineCount]>

<cfquery name="serial" datasource="CustomerSupport">
   exec usp_CS_Insertserial 
     <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_NUMERIC">,
     #Form.ID#,
     <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#resdate#" CFSQLType = "CF_SQL_DATE">,
     <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#vertifidate#" CFSQLType = "CF_SQL_DATE">,
     <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">
   </CFQUERY>

<!---Inserts information into notes_descr table.--->
<cfquery name="descripti on" datasource="Cus tomerSupport">
exec usp_CS_Insertde scription
<cfqueryparam value="#serialn um#" CFSQLType = "CF_SQL_VARCHAR ">,
#Form.ID#,
<cfqueryparam value="#thedesc ription#" CFSQLType = "CF_SQL_VARCHAR ">,
#Form.fk_addedB y#'
</cfquery>

</CFLOOP>
</CFIF>


Thank you,
Rach