Home Forums SQL Server 2005 T-SQL (SS2K5) Excel VBA call to execute stored procedure. RE: Excel VBA call to execute stored procedure.

  • April 18, 2013 at 1:59 am

    #1607567

    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