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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy