Allow single quote in query

**acoder** · Oct 7 '08, 05:56 PM

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.

**bonneylake** · Oct 7 '08, 06:04 PM

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.

Code:

<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>

Thank you,
Rach

**acoder** · Oct 7 '08, 08:21 PM

That error seems to be an SQL Server error most probably caused by an incorrect type. Instead of CF_SQL_DATE, try CF_SQL_TIMESTAM P.

**bonneylake** · Oct 8 '08, 01:10 PM

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_TIMESTAM P.

Hey Acoder,

I get the error

Code:

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification


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

Query Parameter Value(s) -

here is what i have

Code:

<!---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_TIMESTAMP">,
     <cfqueryparam value="#resvertified#" CFSQLType = "CF_SQL_VARCHAR">,
     <cfqueryparam value="#vertifidate#" CFSQLType = "CF_SQL_TIMESTAMP">,
     <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>

Thank you,
Rach

**acoder** · Oct 8 '08, 01:50 PM

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.

**bonneylake** · Oct 8 '08, 02:10 PM

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

**acoder** · Oct 8 '08, 03:39 PM

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.

**bonneylake** · Oct 8 '08, 03:50 PM

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

**acoder** · Oct 8 '08, 05:02 PM

Did you have it working before without the quotes?

If so, for a test, remove the cfsqltype for the two date files, e.g.

Code:

<cfqueryparam value="#resdate#">,

**bonneylake** · Oct 8 '08, 05:40 PM

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.

Code:

<cfqueryparam value="#resdate#">,

Hey Acoder,

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

Thank you again!
Rach

**acoder** · Oct 8 '08, 08:06 PM

Well, I never...

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

Anyway, glad it's now working.

**bonneylake** · Oct 8 '08, 08:38 PM

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

**acoder** · Oct 9 '08, 07:11 AM

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 CreateODBCDateT ime() to convert it?

Allow single quote in query

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment