CODEWITHSUNDEEP
vbaexcel
GK1960
GK1960

Reputation: 121

Excel VBA stored procedure with parameters not working?

I am struggling bit here with a stored procedure with parameters in VBA. The code below without parameters working fine but with parameters not working.

My code:

Sub CopyDataFromDatabase()

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim Fields As ADODB.Field
Dim Cmd As ADODB.Command

Set Conn = New ADODB.Connection
Set Cmd = New ADODB.Command
Set Rs = New ADODB.Recordset
Conn.Open "My connection string here--------"

Cmd.CommandType = adCmdStoredProc

Cmd.Parameters.Append Cmd.CreateParameter("@Division", adVarChar, adParamInput, 40)
Cmd.Parameters("@Division").Value = "South"
Cmd.Parameters.Append Cmd.CreateParameter("@Area", adVarChar, adParamInput, 40)
Cmd.Parameters("@Area").Value = "IT"

Cmd.CommandText = "My SP here------"

Set Rs = Cmd.Execute

On Error GoTo CloseRecordset

Worksheets.Add

For Each Fields In Rs.Fields
ActiveCell.Value = Fields.Name
ActiveCell.Font.Bold = True
ActiveCell.Font.Underline = True
ActiveCell.HorizontalAlignment = xlCenter
ActiveCell.Interior.Color = RGB(0, 128, 255)
ActiveCell.Font.Color = RGB(255, 255, 255)
ActiveCell.Offset(0, 1).Select

Next Fields

Range("A1").Select
Range("A2").CopyFromRecordset Rs

CloseRecordset:
Rs.Close
Set Rs = Nothing
Set Cmd = Nothing

CloseConnection:
Conn.Close
Set Conn = Nothing

End Sub

When I run, its not giving any error, just showing like executing but no result

Can anybody suggest where I am doing wrong? Thanks

Upvotes: 0

Views: 1565

Answers (1)

Soulfire
Soulfire

Reputation: 4296

I have successfully declared a variant array and populated the parameters (in order!) into that array, then passed to the array into the execute method to execute a stored procedure.

Assuming your stored proc expects 'Division' then 'Area', something like this may do the trick:

Sub CopyDataFromDatabase()

    Dim Conn As ADODB.Connection
    Dim Rs As ADODB.Recordset
    Dim Fields As ADODB.Field
    Dim Cmd As ADODB.Command

    'New variable
    Dim v_Params(1 To 2) As Variant 'assuming you have 2 parameters

    Set Conn = New ADODB.Connection
    Set Cmd = New ADODB.Command
    Set Rs = New ADODB.Recordset

    Conn.Open "My connection string here--------"

    v_Params(1) = "South"
    v_Params(2) = "IT"

    With Cmd
        .ActiveConnection = Conn
        .CommandType = adCmdStoredProc
        .CommandText = "My SP here------"
        .CommandTimeout = 0
        Set rs = .Execute(, v_Params)
    End With

See if that works, as I am currently using this method successfully. I didn't see the need to modify the rest of your subroutine.

I used the With Cmd and End With to avoid fully qualifying the reference each time.

Updated

The issue, found by the author of the question, was that the SP was timing out when parameters were passed into it. The resolution was to set the CommandTimeout property to 0.

Upvotes: 1

Related Questions