469,323 Members | 1,601 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Allow single quote in query

769 512MB
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
Oct 2 '08 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. <CFSET serialnum       = Form["serialnum_" & machineCount]>

27 5667
acoder
16,027 Expert Mod 8TB
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]>
Oct 2 '08 #2
bonneylake
769 512MB
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
Oct 2 '08 #3
acoder
16,027 Expert Mod 8TB
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.
Oct 2 '08 #4
bonneylake
769 512MB
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
Oct 3 '08 #5
acoder
16,027 Expert Mod 8TB
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?.
Oct 3 '08 #6
bonneylake
769 512MB
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
Oct 3 '08 #7
acoder
16,027 Expert Mod 8TB
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.
Oct 3 '08 #8
bonneylake
769 512MB
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
Oct 7 '08 #9
acoder
16,027 Expert Mod 8TB
For each of the inputs, so #serialnum# would be replaced be <cfqueryparam ...> and the same for the rest.
Oct 7 '08 #10
bonneylake
769 512MB
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
Oct 7 '08 #11
acoder
16,027 Expert Mod 8TB
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.
Oct 7 '08 #12
bonneylake
769 512MB
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
Oct 7 '08 #13
acoder
16,027 Expert Mod 8TB
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.
Oct 7 '08 #14
bonneylake
769 512MB
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
Oct 7 '08 #15
acoder
16,027 Expert Mod 8TB
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.
Oct 7 '08 #16
bonneylake
769 512MB
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
Oct 7 '08 #17
acoder
16,027 Expert Mod 8TB
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.
Oct 7 '08 #18
bonneylake
769 512MB
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
Oct 8 '08 #19
acoder
16,027 Expert Mod 8TB
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.
Oct 8 '08 #20
bonneylake
769 512MB
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
Oct 8 '08 #21
acoder
16,027 Expert Mod 8TB
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.
Oct 8 '08 #22
bonneylake
769 512MB
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
Oct 8 '08 #23
acoder
16,027 Expert Mod 8TB
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#">,
Oct 8 '08 #24
bonneylake
769 512MB
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
Oct 8 '08 #25
acoder
16,027 Expert Mod 8TB
Well, I never...

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

Anyway, glad it's now working.
Oct 8 '08 #26
bonneylake
769 512MB
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
Oct 8 '08 #27
acoder
16,027 Expert Mod 8TB
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?
Oct 9 '08 #28

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by thecoolone | last post: by
7 posts views Thread by =?Utf-8?B?TG9zdEluTUQ=?= | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.