April 17, 2013 at 7:06 am
How do you surround a stored procedures parameters so that any with a single quote don’t cause a crash.
Excel VBA code
fld1 = Range("b" & TShtRow.Row).Value
fld2 = Range("c" & TShtRow.Row).Value
fld3 = Range("d" & TShtRow.Row).Value
fld4 = Range("f" & TShtRow.Row).Value
sExec = "EXECUTE [dbo].[LOXKanbanDevMaint] '" + fld1 + "'" + ",'" + fld2 + "'" + ",'" + fld3 + "'" + ",'" + fld4 + "'"
adoCN.Execute sExec
EXECUTE [dbo].[LOXKanbanDevMaint] 'ZL03222009','LABEL 7/8' WHT S/A L','SF6','TW'
Unfortunately in fld2 is LABEL 7/8' WHT S/A L so the string sExec is corrupted. How do I fix this?
April 18, 2013 at 1:59 am
Kelvin Phayre (4/17/2013)
How do you surround a stored procedures parameters so that any with a single quote don’t cause a crash.Excel VBA code
fld1 = Range("b" & TShtRow.Row).Value
fld2 = Range("c" & TShtRow.Row).Value
fld3 = Range("d" & TShtRow.Row).Value
fld4 = Range("f" & TShtRow.Row).Value
sExec = "EXECUTE [dbo].[LOXKanbanDevMaint] '" + fld1 + "'" + ",'" + fld2 + "'" + ",'" + fld3 + "'" + ",'" + fld4 + "'"
adoCN.Execute sExec
EXECUTE [dbo].[LOXKanbanDevMaint] 'ZL03222009','LABEL 7/8' WHT S/A L','SF6','TW'
Unfortunately in fld2 is LABEL 7/8' WHT S/A L so the string sExec is corrupted. How do I fix this?
Hello,
you should double any single quote to fix this issue, coding like
sExec = "EXECUTE [dbo].[LOXKanbanDevMaint] '" + _
replace(fld1, "'", "''") + "', '" + _
replace(fld2, "'", "''") + "', '" + _
replace(fld3, "'", "''") + "', '" + _
replace(fld4, "'", "''") + "'"
adoCN.Execute sExec
Regards,
Francesc
April 18, 2013 at 3:05 am
Many thanks.
I was nearly there as I had replaced ' with a space. Just didnt think about replacing it with a double ''.
New code:-
fld2 = Replace(Range("c" & TShtRow.Row).Value, "'", "''")
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply