Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > coldfusion > answers

Allow single quote in query

  #1  
Old October 2nd, 2008, 07:22 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769
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

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2. <CFSET machineListLen = listLen(Form.serialcount)>
  3. <CFLOOP from="1" to="#machineListLen#" index="machineCount">
  4.  <CFSET serialnum       = "Form.serialnum_" & machineCount>
  5.  <CFSET modelno         = "Form.modelno_" & machineCount>
  6.  <CFSET producttype     = "Form.producttype_" & machineCount>
  7.  <CFSET softhardware    = "Form.softhardware_" & machineCount>
  8.  <CFSET resolution      = "Form.resolution_" & machineCount>
  9.  <CFSET resdate         = "Form.resdate_" & machineCount>
  10.  <CFSET resvertified    = "Form.resvertified_" & machineCount>
  11.  <CFSET vertifidate     = "Form.vertifidate_" & machineCount>
  12.  <CFSET deptvendor      = "Form.deptvendor_" & machinecount>
  13.  <CFSET hardwarefailure = "Form.hardwarefailure_" & machineCount>
  14.  <CFSET rma             = "Form.rma_" & machineCount>
  15.  <CFSET thedescription  = "Form.thedescription_" & machineCount>
  16.  
  17. <cfquery name="serial" datasource="CustomerSupport">
  18.    exec usp_CS_Insertserial 
  19.      '#evaluate(serialnum)#','#Form.ID#','#evaluate(modelno)#','#evaluate(producttype)#',
  20.      '#evaluate(softhardware)#','#evaluate(resolution)#','#evaluate(resdate)#',
  21.      '#evaluate(resvertified)#','#evaluate(vertifidate)#','#evaluate(deptvendor)#',
  22.      '#evaluate(hardwarefailure)#','#evaluate(rma)#'
  23.    </CFQUERY>
  24.  
  25. <!---Inserts information into notes_descr table.--->
  26. <cfquery name="description" datasource="CustomerSupport">
  27.     exec usp_CS_Insertdescription
  28.    '#evaluate(serialnum)#','#Form.ID#','#evaluate(thedescription)#','#Form.fk_addedBy#'
  29. </cfquery>
  30.  
  31. </CFLOOP>
  32. </CFIF>
I thought/think its the listLen causing the trouble an i tried this listLen(Form.serialcount," ' ") 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
Last edited by acoder; October 3rd, 2008 at 02:47 PM. Reason: Removed irrelevant text
  #2  
Old October 2nd, 2008, 08:14 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

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.
Expand|Select|Wrap|Line Numbers
  1. <CFSET serialnum       = Form["serialnum_" & machineCount]>
Last edited by acoder; October 3rd, 2008 at 02:48 PM. Reason: Removed text
  #3  
Old October 2nd, 2008, 08:38 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

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
Last edited by acoder; October 3rd, 2008 at 02:48 PM. Reason: Removed text
  #4  
Old October 2nd, 2008, 10:41 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

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.
  #5  
Old October 3rd, 2008, 02:13 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

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

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="serial" datasource="CustomerSupport">
  2.    exec usp_CS_Insertserial 
  3. '#evaluate(serialnum)#','#Form.ID#','#evaluate(modelno)#','#evaluate(producttype)#',
  4.      '#evaluate(softhardware)#','#evaluate(resolution)#','#evaluate(resdate)#',
  5.      '#evaluate(resvertified)#','#evaluate(vertifidate)#','#evaluate(deptvendor)#',
  6.      '#evaluate(hardwarefailure)#','#evaluate(rma)#'
  7. <cfqueryPARAM value =#evaluate(serialnum)#>
  8.    </CFQUERY>
  9.  
  10. <!---Inserts information into notes_descr table.--->
  11. <cfquery name="description" datasource="CustomerSupport">
  12.     exec usp_CS_Insertdescription
  13.   '#evaluate(serialnum)#','#Form.ID#','#evaluate(thedescription)#',
  14. '#Form.fk_addedBy#'
  15. <cfqueryPARAM value =#evaluate(serialnum)#>
  16. </cfquery>
  17.  
i am just confused on what the value should be.

Thank you,
Rach
  #6  
Old October 3rd, 2008, 02:51 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

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?.
  #7  
Old October 3rd, 2008, 03:18 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

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_insertserial. do i need to change the names in the cfquery to something else?

Expand|Select|Wrap|Line Numbers
  1. <!---Inserts information into serial table.--->
  2. <CFIF REQUEST_METHOD EQ "POST">
  3. <CFSET machineListLen = listLen(Form.serialcount, " ' ")>
  4. <CFLOOP from="1" to="#machineListLen#" index="machineCount">
  5.  <CFSET serialnum       = Form["serialnum_" & machineCount]>
  6.  <CFSET modelno         = Form["modelno_" & machineCount]>
  7.  <CFSET producttype     = Form["producttype_" & machineCount]>
  8.  <CFSET softhardware    = Form["softhardware_" & machineCount]>
  9.  <CFSET resolution      = Form["resolution_" & machineCount]>
  10.  <CFSET resdate         = Form["resdate_" & machineCount]>
  11.  <CFSET resvertified    = Form["resvertified_" & machineCount]>
  12.  <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  13.  <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  14.  <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  15.  <CFSET rma             = Form["rma_" & machineCount]>
  16.  <CFSET thedescription  = Form["thedescription_" & machineCount]>
  17.  
  18. <cfquery name="serial" datasource="CustomerSupport">
  19.    exec usp_CS_Insertserial 
  20.      '#serialnum#','#Form.ID#','#modelno#','#producttype#',
  21.      '#softhardware#','#resolution#','#resdate#',
  22.      '#resvertified#','#vertifidate#','#deptvendor#',
  23.      '#hardwarefailure#','#rma#'
  24.    </CFQUERY>
  25.  
  26. <!---Inserts information into notes_descr table.--->
  27. <cfquery name="description" datasource="CustomerSupport">
  28.     exec usp_CS_Insertdescription
  29.    '#serialnum#','#Form.ID#','#thedescription#','#Form.fk_addedBy#'
  30. </cfquery>
  31.  
  32. </CFLOOP>
  33. </CFIF>
Thank you,
Rach
  #8  
Old October 3rd, 2008, 10:05 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

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.
  #9  
Old October 7th, 2008, 02:20 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

Quote:
Originally Posted by 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.
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

Expand|Select|Wrap|Line Numbers
  1. <!---Inserts information into serial table.--->
  2. <CFIF REQUEST_METHOD EQ "POST">
  3. <CFSET machineListLen = listLen(Form.serialcount, " ' ")>
  4. <CFLOOP from="1" to="#machineListLen#" index="machineCount">
  5.  <CFSET serialnum       = Form["serialnum_" & machineCount]>
  6.  <CFSET modelno         = Form["modelno_" & machineCount]>
  7.  <CFSET producttype     = Form["producttype_" & machineCount]>
  8.  <CFSET softhardware    = Form["softhardware_" & machineCount]>
  9.  <CFSET resolution      = Form["resolution_" & machineCount]>
  10.  <CFSET resdate         = Form["resdate_" & machineCount]>
  11.  <CFSET resvertified    = Form["resvertified_" & machineCount]>
  12.  <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  13.  <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  14.  <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  15.  <CFSET rma             = Form["rma_" & machineCount]>
  16.  <CFSET thedescription  = Form["thedescription_" & machineCount]>
  17.  
  18. <cfquery name="serial" datasource="CustomerSupport">
  19.    exec usp_CS_Insertserial 
  20.      '#serialnum#','#Form.ID#','#modelno#','#producttype#',
  21.      '#softhardware#','#resolution#','#resdate#',
  22.      '#resvertified#','#vertifidate#','#deptvendor#',
  23.      '#hardwarefailure#','#rma#'
  24.    </CFQUERY>
  25.  
  26. <!---Inserts information into notes_descr table.--->
  27. <cfquery name="description" datasource="CustomerSupport">
  28.     exec usp_CS_Insertdescription
  29.    '#serialnum#','#Form.ID#','#thedescription#','#Form.fk_addedBy#'
  30. </cfquery>
  31.  
  32. </CFLOOP>
  33. </CFIF>
Thank you,
Rach
  #10  
Old October 7th, 2008, 02:58 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

For each of the inputs, so #serialnum# would be replaced be <cfqueryparam ...> and the same for the rest.
  #11  
Old October 7th, 2008, 04:05 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

Quote:
Originally Posted by acoder
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?

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="serial" datasource="CustomerSupport">
  2. exec usp_CS_Insertserial 
  3. '<cfqueryparam value="#serialnum#"></cfqueryparam>','#Form.ID#','#modelno#',
  4. '#producttype#','#softhardware#','#resolution#','#resdate#',
  5. '#resvertified#','#vertifidate#','#deptvendor#',
  6. '#hardwarefailure#','#rma#'
  7. </CFQUERY>
  8.  
Thank you,
Rach
  #12  
Old October 7th, 2008, 04:29 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

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.
  #13  
Old October 7th, 2008, 05:03 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

Quote:
Originally Posted by 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.
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

Expand|Select|Wrap|Line Numbers
  1. Microsoft][ODBC SQL Server Driver]Optional feature not implemented
  2.  
  3.  
  4. SQL = "exec usp_CS_Insertserial '?', '123', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?'"
  5.  
  6. Query Parameter Value(s) - 
  7.  
  8. Parameter #1 = 3242 
  9.  
  10. Parameter #2 = 340TWIN 
  11.  
  12. Parameter #3 = Labtop 
  13.  
  14. Parameter #4 = Processor 
  15.  
  16. Parameter #5 = test's 
  17.  
  18. Parameter #6 = {d '2008-10-07'} 
  19.  
  20. Parameter #7 = Greg Cason 
  21.  
  22. Parameter #8 = {d '2008-10-07'} 
  23.  
  24. Parameter #9 = Vendor Software 
  25.  
  26. Parameter #10 = OutOfWarranty 
  27.  
  28. Parameter #11 = rest's 
  29.  
  30. Data Source = "CUSTOMERSUPPORT"
here is what i have in there right now

Expand|Select|Wrap|Line Numbers
  1. <!---Inserts information into serial table.--->
  2. <CFIF REQUEST_METHOD EQ "POST">
  3. <CFSET machineListLen = listLen(Form.serialcount, " ' ")>
  4. <CFLOOP from="1" to="#machineListLen#" index="machineCount">
  5.  <CFSET serialnum       = Form["serialnum_" & machineCount]>
  6.  <CFSET modelno         = Form["modelno_" & machineCount]>
  7.  <CFSET producttype     = Form["producttype_" & machineCount]>
  8.  <CFSET softhardware    = Form["softhardware_" & machineCount]>
  9.  <CFSET resolution      = Form["resolution_" & machineCount]>
  10.  <CFSET resdate         = Form["resdate_" & machineCount]>
  11.  <CFSET resvertified    = Form["resvertified_" & machineCount]>
  12.  <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  13.  <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  14.  <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  15.  <CFSET rma             = Form["rma_" & machineCount]>
  16.  <CFSET thedescription  = Form["thedescription_" & machineCount]>
  17.  
  18. <cfquery name="serial" datasource="CustomerSupport">
  19.    exec usp_CS_Insertserial 
  20.      '<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_NUMERIC">',
  21.      '#Form.ID#',
  22.      '<cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">',
  23.      '<cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">',
  24.      '<cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">',
  25.      '<cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">',
  26.      '<cfqueryparam value="#resdate#" CFSQLType = "CF_SQL_DATE">',
  27.      '<cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">',
  28.      '<cfqueryparam value="#vertifidate#" CFSQLType = "CF_SQL_DATE">',
  29.      '<cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">',
  30.      '<cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">',
  31.      '<cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">'
  32.    </CFQUERY>
  33.  
  34. <!---Inserts information into notes_descr table.--->
  35. <cfquery name="description" datasource="CustomerSupport">
  36.     exec usp_CS_Insertdescription
  37.    '<cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">',
  38.    '#Form.ID#',
  39.    '<cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">',
  40.    '#Form.fk_addedBy#'
  41. </cfquery>
  42.  
  43. </CFLOOP>
  44. </CFIF>

Any suggestion on what i am doing wrong?

Thank you,
Rach
  #14  
Old October 7th, 2008, 05:41 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

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.
  #15  
Old October 7th, 2008, 06:36 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

Quote:
Originally Posted by 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.
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?here is what i have


Expand|Select|Wrap|Line Numbers
  1. <!---Inserts information into serial table.--->
  2. <CFIF REQUEST_METHOD EQ "POST">
  3. <CFSET machineListLen = listLen(Form.serialcount)>
  4. <CFLOOP from="1" to="#machineListLen#" index="machineCount">
  5.  <CFSET serialnum       = Form["serialnum_" & machineCount]>
  6.  <CFSET modelno         = Form["modelno_" & machineCount]>
  7.  <CFSET producttype     = Form["producttype_" & machineCount]>
  8.  <CFSET softhardware    = Form["softhardware_" & machineCount]>
  9.  <CFSET resolution      = Form["resolution_" & machineCount]>
  10.  <CFSET resdate         = Form["resdate_" & machineCount]>
  11.  <CFSET resvertified    = Form["resvertified_" & machineCount]>
  12.  <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  13.  <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  14.  <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  15.  <CFSET rma             = Form["rma_" & machineCount]>
  16.  <CFSET thedescription  = Form["thedescription_" & machineCount]>
  17.  
  18. <cfquery name="serial" datasource="CustomerSupport">
  19.    exec usp_CS_Insertserial 
  20.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_NUMERIC">,
  21.      #Form.ID#,
  22.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  23.      <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  24.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  25.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  26.      <cfqueryparam value="#resdate#" CFSQLType = "CF_SQL_DATE">,
  27.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  28.      <cfqueryparam value="#vertifidate#" CFSQLType = "CF_SQL_DATE">,
  29.      <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  30.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">,
  31.      <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">
  32.    </CFQUERY>
  33.  
<!---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>


Thank you,
Rach
  #16  
Old October 7th, 2008, 06:56 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

No, what you now have is almost there. If #form.id# is a string, then keep the quotes around that field, because you're not using cfqueryparam.
  #17  
Old October 7th, 2008, 07:04 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

Quote:
Originally Posted by acoder
No, what you now have is almost there. If #form.id# is a string, then keep the quotes around that field, because you're not using cfqueryparam.
Hey Acoder,

Here is what i have but i am still having the same trouble.

Expand|Select|Wrap|Line Numbers
  1. <CFIF REQUEST_METHOD EQ "POST">
  2. <CFSET machineListLen = listLen(Form.serialcount)>
  3. <CFLOOP from="1" to="#machineListLen#" index="machineCount">
  4.  <CFSET serialnum       = Form["serialnum_" & machineCount]>
  5.  <CFSET modelno         = Form["modelno_" & machineCount]>
  6.  <CFSET producttype     = Form["producttype_" & machineCount]>
  7.  <CFSET softhardware    = Form["softhardware_" & machineCount]>
  8.  <CFSET resolution      = Form["resolution_" & machineCount]>
  9.  <CFSET resdate         = Form["resdate_" & machineCount]>
  10.  <CFSET resvertified    = Form["resvertified_" & machineCount]>
  11.  <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  12.  <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  13.  <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  14.  <CFSET rma             = Form["rma_" & machineCount]>
  15.  <CFSET thedescription  = Form["thedescription_" & machineCount]>
  16.  
  17. <cfquery name="serial" datasource="CustomerSupport">
  18.    exec usp_CS_Insertserial 
  19.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_NUMERIC">,
  20.      '#Form.ID#',
  21.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  22.      <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  23.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  24.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  25.      <cfqueryparam value="#resdate#" CFSQLType = "CF_SQL_DATE">,
  26.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  27.      <cfqueryparam value="#vertifidate#" CFSQLType = "CF_SQL_DATE">,
  28.      <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  29.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">,
  30.      <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">
  31.    </CFQUERY>
  32.  
  33. <!---Inserts information into notes_descr table.--->
  34. <cfquery name="description" datasource="CustomerSupport">
  35.     exec usp_CS_Insertdescription
  36.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  37.    '#Form.ID#',
  38.    <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  39.    '#Form.fk_addedBy#'
  40. </cfquery>
  41.  
  42. </CFLOOP>
  43. </CFIF>
Thank you,
Rach
  #18  
Old October 7th, 2008, 09:21 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

That error seems to be an SQL Server error most probably caused by an incorrect type. Instead of CF_SQL_DATE, try CF_SQL_TIMESTAMP.
  #19  
Old October 8th, 2008, 02:10 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

Quote:
Originally Posted by acoder
That error seems to be an SQL Server error most probably caused by an incorrect type. Instead of CF_SQL_DATE, try CF_SQL_TIMESTAMP.
Hey Acoder,

I get the error

Expand|Select|Wrap|Line Numbers
  1. [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
  2.  
  3.  
  4. SQL = "exec usp_CS_Insertserial ?, '144', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?"
  5.  
  6. Query Parameter Value(s) - 

here is what i have
Expand|Select|Wrap|Line Numbers
  1. <!---Inserts information into serial table.--->
  2. <CFIF REQUEST_METHOD EQ "POST">
  3. <CFSET machineListLen = listLen(Form.serialcount)>
  4. <CFLOOP from="1" to="#machineListLen#" index="machineCount">
  5.  <CFSET serialnum       = Form["serialnum_" & machineCount]>
  6.  <CFSET modelno         = Form["modelno_" & machineCount]>
  7.  <CFSET producttype     = Form["producttype_" & machineCount]>
  8.  <CFSET softhardware    = Form["softhardware_" & machineCount]>
  9.  <CFSET resolution      = Form["resolution_" & machineCount]>
  10.  <CFSET resdate         = Form["resdate_" & machineCount]>
  11.  <CFSET resvertified    = Form["resvertified_" & machineCount]>
  12.  <CFSET vertifidate     = Form["vertifidate_" & machineCount]>
  13.  <CFSET deptvendor      = Form["deptvendor_" & machinecount]>
  14.  <CFSET hardwarefailure = Form["hardwarefailure_" & machineCount]>
  15.  <CFSET rma             = Form["rma_" & machineCount]>
  16.  <CFSET thedescription  = Form["thedescription_" & machineCount]>
  17.  
  18. <cfquery name="serial" datasource="CustomerSupport">
  19.    exec usp_CS_Insertserial 
  20.      <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_NUMERIC">,
  21.      '#Form.ID#',
  22.      <cfqueryparam value="#modelno#" CFSQLType = "CF_SQL_VARCHAR">,
  23.      <cfqueryparam value="#producttype#" CFSQLType = "CF_SQL_VARCHAR">,
  24.      <cfqueryparam value="#softhardware#" CFSQLType = "CF_SQL_VARCHAR">,
  25.      <cfqueryparam value="#resolution#" CFSQLType = "CF_SQL_VARCHAR">,
  26.      <cfqueryparam value="#resdate#" CFSQLType = "CF_SQL_TIMESTAMP">,
  27.      <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
  28.      <cfqueryparam value="#vertifidate#" CFSQLType = "CF_SQL_TIMESTAMP">,
  29.      <cfqueryparam value="#deptvendor#" CFSQLType = "CF_SQL_VARCHAR">,
  30.      <cfqueryparam value="#hardwarefailure#" CFSQLType = "CF_SQL_VARCHAR">,
  31.      <cfqueryparam value="#rma#" CFSQLType = "CF_SQL_VARCHAR">
  32.    </CFQUERY>
  33.  
  34. <!---Inserts information into notes_descr table.--->
  35. <cfquery name="description" datasource="CustomerSupport">
  36.     exec usp_CS_Insertdescription
  37.    <cfqueryparam value="#serialnum#" CFSQLType = "CF_SQL_VARCHAR">,
  38.    '#Form.ID#',
  39.    <cfqueryparam value="#thedescription#" CFSQLType = "CF_SQL_VARCHAR">,
  40.    '#Form.fk_addedBy#'
  41. </cfquery>
  42.  
  43. </CFLOOP>
  44. </CFIF>
Thank you,
Rach
  #20  
Old October 8th, 2008, 02:50 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

What type are the two date fields and what are some example input values?

Also check the database to see how the values are stored.

PS. If you remember, I mentioned in an earlier thread about using cfstoredproc/cfprocparam instead of cfquery/cfqueryparam for stored procedures.
  #21  
Old October 8th, 2008, 03:10 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

Quote:
Originally Posted by acoder
What type are the two date fields and what are some example input values?

Also check the database to see how the values are stored.

PS. If you remember, I mentioned in an earlier thread about using cfstoredproc/cfprocparam instead of cfquery/cfqueryparam for stored procedures.
Hey Acoder,

Here is how it displays the date

2008-09-23 00:00:00.

Do i need to do the cfstoredproc/cfprocparam instead?

Thank you,
Rach
  #22  
Old October 8th, 2008, 04:39 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

How is it stored in the database? Do you know the exact type?

This may actually be a part-SQL Server question that you might want to ask in the SQL Server forum if the experts there have some tips.

Changing to cfstoredproc may not solve the problem, but it would make sense to anyway.
  #23  
Old October 8th, 2008, 04:50 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

Quote:
Originally Posted by acoder
How is it stored in the database? Do you know the exact type?

This may actually be a part-SQL Server question that you might want to ask in the SQL Server forum if the experts there have some tips.

Changing to cfstoredproc may not solve the problem, but it would make sense to anyway.
Hey Acoder,

This is how it is stored in the database

1/1/1900 12:00:00 AM

the exact type is datetime.

Thank you,
Rach
  #24  
Old October 8th, 2008, 06:02 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

Did you have it working before without the quotes?

If so, for a test, remove the cfsqltype for the two date files, e.g.
Expand|Select|Wrap|Line Numbers
  1. <cfqueryparam value="#resdate#">,
  #25  
Old October 8th, 2008, 06:40 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

Quote:
Originally Posted by acoder
Did you have it working before without the quotes?

If so, for a test, remove the cfsqltype for the two date files, e.g.
Expand|Select|Wrap|Line Numbers
  1. <cfqueryparam value="#resdate#">,
Hey Acoder,

That was it, It works! Thank you so much for all the help, your awesome!!!

Thank you again!
Rach
  #26  
Old October 8th, 2008, 09:06 PM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

Well, I never...

If you aren't doing already, you should validate the input going into the database.

Anyway, glad it's now working.
  #27  
Old October 8th, 2008, 09:38 PM
Site Addict
  
Join Date: Aug 2008
Location: United States
Posts: 769

re: Allow single quote in query

Quote:
Originally Posted by acoder
Well, I never...

If you aren't doing already, you should validate the input going into the database.

Anyway, glad it's now working.
Hey Acoder,

Yes it is definitely weird. I even read up on it an according to them (adobe) the date one should of worked. The only thing i can think of is when users submit a date they submit it like this 10/08/2008. But when it goes into the database its 10/08/2008 12:00:00am.So that could of been it. But thank you again for all the help :),

Rach
  #28  
Old October 9th, 2008, 08:11 AM
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,528
Provided Answers: 12

re: Allow single quote in query

If you're using the timestamp type, I think that format wouldn't work. You'd probably need 10-08-2008 or something. Consult the SQL Server documentation for that. Are you using CreateODBCDateTime() to convert it?
Reply

« previous question | next question »

Similar Threads
Thread Thread Starter Forum Replies Last Post
DataGridView: How to allow single row to span multiple lines? =?Utf-8?B?TG9zdEluTUQ=?= answers 7 May 14th, 2007 04:45 PM
Accessors in Python (getters and setters) mystilleef answers 112 July 21st, 2006 03:55 PM
Storing array in a row guitarromantic@gmail.com answers 22 May 13th, 2006 05:15 PM
Invalid procedure call in query - why!??! Martin Lacoste answers 6 November 13th, 2005 02:55 AM
Applying an SQL query through a for...while loop (!!) Steve Macleod answers 3 July 17th, 2005 03:58 AM

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 225,720 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved