VBA stored procedure SQL Server

Question

I'm trying to send data from excel spreadsheet to sql server. The code runs properly but the change of status is not done. Before those steps, I already added data with request numbers (or ids) in another process so I want to change info where the request number from excel is the same as the one in sql server. Does anybody sees something I could have missed ? Thanks a lot in advance

The VBA code:

    Dim mobjConn As ADODB.Connection
    Dim strConn As String
    Dim mobjCmd As ADODB.Command
    Dim stat As String

    Set mobjConn = New ADODB.Connection
        strConn = "mystrConn"
        mobjConn.Open strConn
    Set mobjCmd = New ADODB.Command
    
    stat = "DONE"
    
    'INSERT
    With mobjCmd
        .ActiveConnection = mobjConn
        .CommandText = "MySQL_Command"
        .CommandType = adCmdStoredProc
        .CommandTimeout = 0
    End With
    
    With mobjCmd
'
        .Parameters.Item("@Param1") = Date
        .Parameters.Item("@Maturity") = CStr(StartRange.Offset(nRow, 6).Value)
        .Parameters.Item("@Param3") = StartRange.Offset(nRow, 13).Value
        .Parameters.Item("@Status_Request") = stat
        .Parameters.Item("@RequestNumber") = StartRange.Offset(nRow, 16).Value
lineExecute:
        .Execute
            
    End With

The SQL stored procedure gets the parameters and tries to update the other parameters with the same number request:

ALTER PROCEDURE [dbo].[MySQL_Command]

    @Param1 AS date = '',
    @Maturity AS nvarchar(50) = '',
    @Param3 AS nvarchar(50) = '',
    @Status_Request AS nvarchar(10) = '',
    @RequestNumber AS int = ''
    
AS
BEGIN

    SELECT CONVERT(nvarchar(50),@Maturity)

    IF NOT EXISTS (SELECT * FROM myTable  WHERE RequestNumber = @RequestNumber)
        BEGIN
            INSERT INTO DCI_Requests_Table (Param1,
                                            Maturity,
                                            Param3,
                                            Status_Request,
                                            RequestNumber)

                    VALUES      (@Param1,
                                 @Maturity,
                                 @Param3,
                                 @Status_Request,
                                 @RequestNumber)
        
     END
    ELSE
        BEGIN
            UPDATE DCI_Requests_Table
                SET
                    Param1= ISNULL(@Param1,Param1),
                    Maturity= ISNULL(@Maturity,Maturity),
                    Param3= ISNULL(@Param3,Param3),
                    Status_Request= ISNULL(@Status_Request,Status_Request),
                    RequestNumber= ISNULL(@RequestNumber,RequestNumber)

                 WHERE RequestNumber = @RequestNumber
        END
END
GO

Answer 1

This is how I do this kind of thing.

Sub RunSProc()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String

Set cn = New ADODB.Connection

strConn = "Provider=SQLOLEDB;"
strConn = strConn & "Data Source=your_server_name;"
strConn = strConn & "Initial Catalog=Northwnd;"
strConn = strConn & "Integrated Security=SSPI;"

cn.Open strConn

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "MyOrders"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = ActiveSheet.Range("E1").Text
cmd.Parameters(2).Value = ActiveSheet.Range("E2").Text
Set rs = cmd.Execute()

If Not rs.EOF Then
    Worksheets("sheet2").Range("A5:D500").CopyFromRecordset rs
    rs.Close
End If

End Sub