Excel VBA stored procedure with parameters not working?

Question

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

Show us the sanatized conn string and SQL query

Jeremy Thompson
– Jeremy Thompson

2015-04-08 13:54:39 +00:00
Commented Apr 8, 2015 at 13:54 — Jeremy Thompson
– Jeremy Thompson, Commented Apr 8, 2015 at 13:54
Where's your store procedure with parameters it self ?

Eko Junaidi Salam
– Eko Junaidi Salam

2015-04-08 14:00:22 +00:00
Commented Apr 8, 2015 at 14:00 — Eko Junaidi Salam
– Eko Junaidi Salam, Commented Apr 8, 2015 at 14:00

Soulfire · Accepted Answer · 2015-04-08 15:33:01Z

1

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.

edited Apr 8, 2015 at 15:33

answered Apr 8, 2015 at 14:04

Soulfire

4,3161 gold badge25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

GK1960 Over a year ago

Hi @Joshua Ross, I just modified your code but still same problem, shwing like executing but no data displaying. where I am doing wrong?

Soulfire Over a year ago

If you execute the stored procedure on it's own, in SQL Server Management Studio, with the parameters South and IT does it run? Unfortunately without seeing the content of the stored procedure, connection string, etc. it is difficult to say

GK1960 Over a year ago

Hi @ Joshua Ross, Yes SP is running fine and got the result set with required parameters values. Thanks

GK1960 Over a year ago

when I remove parameters in SP and VBA, I am getting data to excel fine, means connection string is fine I think.

Soulfire Over a year ago

This is intriguing to me. I will think a bit more on it and hopefully come back with an updated answer. Seems to me the connection string is fine, as you said, if you can get data in without parameters.

|

Collectives™ on Stack Overflow

Excel VBA stored procedure with parameters not working?

1 Answer 1

Updated

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Updated

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related