0

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

DavidPostill
  • 7,734
  • 9
  • 41
  • 60
GK1960
  • 111
  • 2
  • 4
  • 15

1 Answers1

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.

Soulfire
  • 4,218
  • 23
  • 33
  • Hi @Joshua Ross, I just modified your code but still same problem, shwing like executing but no data displaying. where I am doing wrong? – GK1960 Apr 08 '15 at 14:49
  • 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 – Soulfire Apr 08 '15 at 15:10
  • Hi @ Joshua Ross, Yes SP is running fine and got the result set with required parameters values. Thanks – GK1960 Apr 08 '15 at 15:12
  • when I remove parameters in SP and VBA, I am getting data to excel fine, means connection string is fine I think. – GK1960 Apr 08 '15 at 15:15
  • 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. – Soulfire Apr 08 '15 at 15:24
  • Hi @Joshua Ross, I just got the answer. The problem with SP executing Timeout , I just added CommandTimeout = 0, it returned values. Thanks for your suggestions and time. – GK1960 Apr 08 '15 at 15:30
  • I was just about to mention that that would be my next check, I'm glad you found the answer! – Soulfire Apr 08 '15 at 15:31
  • Thanks @ Joshua Ross, If you are good at VBA can you please also suggest answer this link [link](http://stackoverflow.com/questions/29481649/vba-code-to-filter-data-automatically-by-windows-log-in-user-id) , which I asked yesterday. Thanks. – GK1960 Apr 08 '15 at 15:34
  • Hi @Joshua Ross, can you please help with this link also [link](http://stackoverflow.com/questions/29525727/how-to-pass-multiple-values-to-stored-procedure-parameter-that-running-from-vba). thanks in advance – GK1960 Apr 08 '15 at 22:09