How to execute stored procedure which insert/update records using VBA?

Question

I created many spreadsheets which use stored procedures. I have a custom VBA code which works fine when I try to get data from SQL. However, today I wanted to execute parametrized stored procedure which inserts and updates data on a database table. When I run macro no errors show up, however there's no insert/update action on database. I have no idea why. I established SQL connection in my workbook (myConn) as I do everytime I need to connect with SQL so it's correct for sure. This is my standard VBA code:

Sub SaveData()

    Dim myValue As Double   
    myValue = Sheets("XYZ").Range("valueToSave").Value

    With ActiveWorkbook.Connections("myConn").OLEDBConnection
        .CommandText = Array( _
         "EXEC DB.[dbo].[myProc] '" & myValue & "'")
    End With

    ActiveWorkbook.Connections("myConn").Refresh
  
End Sub

I need to insert data into column of decimal(6,4) type (in SQL table). myProc does it perfectly when I run it manually via SSMS but not here using VBA code. valueToSave is an Excel range which stores one decimal value (for example: 23,56, 11,21 etc.). When I run macro nothing happens. When I run macro and go to 'Connection Properties' > 'Definitions' > 'Command Text' then I can see there's a procedure with parameter (EXEC DB.[dbo].[myProc] '11,23'). So my acode above seems working but not executing stored procedure.

Has it something to do with data type? Honestly, I tried with other VBA types: String, Variant, Integer but it's not working. I also changed data type of that column in SQL table (to varchar, int etc.) but it also doesn't work. The most interesting thing is that the code above works fine when I withdraw data from db, it doesn't work when need to insert/update data.

PS. I guess I added all required refrences:

WARNING: Your code is dangerous; it is wide open to SQL injection attacks. Fix your code, and parametrise. — Thom A
– Thom A ♦, Commented Oct 14, 2021 at 16:42
I use parameters in stored procedure. Anyways, code is used internally, in my company. I just wanted to know why insert/update is not working here — mustafa00
– mustafa00, Commented Oct 14, 2021 at 17:01
"EXEC DB.[dbo].[myProc] '" & myValue & "'" is not using parameters... That is injection... — Thom A
– Thom A ♦, Commented Oct 14, 2021 at 17:03
"Anyways, code is used internally, in my company" That's like saying "I only park my car outside my residence and Office, so it's safe to leave it unlocked." — Thom A
– Thom A ♦, Commented Oct 14, 2021 at 17:05
The stored procedure may have parameters, but the SQL code you are submitting does not. Consider Alice entering something like the following into your Excel cell: '; drop database DB; --. Time to read up on OleDbCommand Parameters and translate that code to VBA. — AlwaysLearning
– AlwaysLearning, Commented Oct 15, 2021 at 3:00

CDP1802 · Accepted Answer · 2021-10-15 16:18:01Z

Using ADODB

Option Explicit

Sub SaveData()

    Const PROC_NAME = "DB.dbo.myproc"

    Dim wb As Workbook
    Dim ado As ADODB.Connection, cmd As ADODB.Command
    Dim sCon As String, v As Single

    Set wb = ThisWorkbook
    v = wb.Sheets("XYZ").Range("valueToSave").Value
    
    ' get connection string
    sCon = wb.Connections("myConn").OLEDBConnection.Connection
    sCon = Replace(sCon, "OLEDB;", "")

    ' open connection
    Set ado = New ADODB.Connection
    ado.Open sCon

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = ado
        .CommandType = adCmdStoredProc
        .CommandText = PROC_NAME
        .Parameters.Append .CreateParameter("P1", adDecimal, adParamInput)
        With .Parameters(0)
            .NumericScale = 2
            .Precision = 18
            .Value = v
        End With
        .Execute
    End With
    ado.Close

    MsgBox PROC_NAME & " " & v, vbInformation, "Done"

End Sub

Collectives™ on Stack Overflow

How to execute stored procedure which insert/update records using VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related