Need Help With Single Quote in a Dynamic Form 1

[LyndonOHRC]

I have a dynamically generated form and one of the fields requires the ability to type an apostrophe as it contains proper names; horses names often have apostrophes as a part of their official registered name.

If the user enters bob's baby for example in the FoalName field, I am getting the following error in my query code:

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression  '[COLOR=red]'bob's baby'[color], 'R34812', 'Quarterhorse' , Val(50.00), 'Racestock', Now(), 'lpatton' )'.

My CF Query Tag:

<cfquery name="InsertRacestock" datasource="Registry">
			Insert Into 
				ReceiptDetails(
					[COLOR=red]Name[color],
					WorkOrder,
					Breed,
					Amount,
					HorseType,
					WorkorderDate,
					user)
				Values(
					[COLOR=red]'#Form["FoalName#i#"]#'[color],
					'#Session.WorkOrderReceipt.wo#',
					<cfif Form["FoalBreed#i#"] eq '1'>
						'Thoroughbred'
					</cfif>
					<cfif Form["FoalBreed#i#"] eq '2'>
						'Quarterhorse'
					</cfif>
					<cfif Form["FoalBreed#i#"] eq '3'>
						'Appaloosa'
					</cfif>
					<cfif Form["FoalBreed#i#"] eq '4'>
						'Paint'
					</cfif>
					<cfif Form["FoalBreed#i#"] eq '5'>
						'OTHER'
					</cfif>,
					Val(#Form["FoalAmount#i#"]#),
					'Racestock',
					Now(),
					'#SESSION.Auth.userlogin#'
					)
		</cfquery>

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[cfStarlight]

One option is to use cfqueryparam. It should automatically handle any single quoting issues.

Not tested

Insert Into ReceiptDetails (Name, ...other columns) 
Values(
  <cfqueryparam 
        value="#Form['FoalName#i#']#"      
        cfsqltype="cf_sql_varchar">
   ,
   ... other values 
)

 

[LyndonOHRC]

Thanks,

This is very old code. I hadn't yet discovered cfqueryparam
at that time.

I use it in all my code now; I should have thought of that!

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey