Apostrophe Insert Problem

[autostadt]

Hi. I'm having a problem inserting data from a form that contains an apostrophe, such as a last name like O'Hara. I don't recieve any errors but everything after the O is blank or trimmed. I'm not sure what I am doing wrong. This is a MS SQL db. Here's the code if anyone can take a look and help:

<cfquery name="name" username="#application.datasource#" password="#application.datasource#" datasource="db">
insert into contactform
(firstname, lastname, address, city, state, zipcode, homePhone, workPhone, email,
financialAid, veteran, visa, list, programofinterest, howdidyouhear)
values
(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.firstname#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.lastname#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.address#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.city#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.state#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.zipCode#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.homePhone#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.workPhone#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.email#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.financialAid#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.veteran#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.visa#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.list#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.programofinterest#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.howdidyouhear#">
)
</cfquery>

[autostadt]

I figured out part of my problem. I am using hidden fields on the previous page and when changed to "text" it works. Any ideas on how to fix with hidden fields? Thanks.

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

Your query code looks OK to me. Is it possible that some other code is changing the values of your form.xxxx fields? Can you post all of your code related to this question? Have you tried using the SQL Server Profiler tool to see what data is being passed to SQL server by your query?

http://www.petefreitag.com/item/674.cfm

[autostadt]

The hidden field stuff is not the problem as stated by Dan. The previous page with the form has these values:

<input name="firstName" type="hidden" id="firstName" value='#form.firstName#'/>
<input name="lastName" type="hidden" id="lastName" value='#form.lastName#'/>
<input name="address" type="hidden" id="address" value="#form.address#"/>
<input name="city" type="hidden" id="city" value="#form.city#"/>
<input name="state" type="hidden" id="state" value="#form.state#"/>

<input name="zipCode" type="hidden" id="zipCode" value="#form.zipcode#"/>
<input name="workPhone" type="hidden" id="workPhone" value="#form.workPhone#"/>
<input name="homePhone" type="hidden" id="homePhone" value="#form.homePhone#"/>
<input name="email" type="hidden" id="email" value="#form.email#"/>

<input name="programOfInterest" type="hidden" id="programOfInterest" value="#form.programOfInterest#"/>
<input name="howDidYouHear" type="hidden" id="howDidYouHear" value="#form.howDidYouHear#"/>
<input name="financialAid" type="hidden" id="financialAid" value="#form.financialAid#"/>
<input name="veteran" type="hidden" id="veteran" value="#form.veteran#"/>
<input name="visa" type="hidden" id="visa" value="#form.visa#"/>
<input name="list" type="hidden" id="list" value="#form.list#"/>

I'll look into the other suggestions as well. Thanks.

[Dan_Bracuk]

quote:

---

Originally posted by: autostadt
I figured out part of my problem. I am using hidden fields on the previous page and when changed to "text" it works. Any ideas on how to fix with hidden fields? Thanks.

---

This does not make sense.

If it were happening to me, I would do the following.
1. Turn on debugging.
2. cfdump the form before the query.
3. look at the debugging info to what parameter got sent to the query.

[autostadt]

I had a misplaced cfoutput tag that caused the problem. Thanks to all for helping. Ray