Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Excel VBA call to execute stored procedure. Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 7:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:54 AM
Points: 68, Visits: 265
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?
Post #1443241
Posted Thursday, April 18, 2013 1:59 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, June 13, 2013 10:02 AM
Points: 633, Visits: 808
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
Post #1443648
Posted Thursday, April 18, 2013 3:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:54 AM
Points: 68, Visits: 265
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, "'", "''")

Post #1443690
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse