Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Excel VBA call to execute stored procedure.


Excel VBA call to execute stored procedure.

Author
Message
Kelvin Phayre
Kelvin Phayre
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 267
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?
gofrancesc
gofrancesc
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 812
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
Kelvin Phayre
Kelvin Phayre
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 267
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, "'", "''")
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search