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

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:

CodePudding user response：

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