Need help with ADO in Excel

[Paul W]

Trying to run a stored proc in excel via ADO. My code returns no
errors on this, but I get no data back in my recordset. Running the
code manually on SQL or via MS Query with the same parameters returns
data. Can anyone see or think of something that I might be missing?

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Dim intRowIndex As Integer
Dim intDataIndex As Integer

Set cn = New ADODB.Connection
cn.Open "Driver={SQL
Server};Server=MyServer;Trusted_Connection=yes;Database=MyDb"
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "SOGoodChannel"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = strRunType
cmd.Parameters(2).Value = CInt(frmDates.cbxMonth.Text)
cmd.Parameters(3).Value = CInt(frmDates.cbxYear.Text)
Set rst = cmd.Execute

--

CREATE PROCEDURE dbo.SOGoodChannel @strType Char(1),
@intMonth Integer,
@intYear Integer
AS
BEGIN

IF @strType = 'I'
BEGIN
Run Query 1
END
ELSE
BEGIN
IF @strType = 'C'
BEGIN
Run Query 2
END
ELSE
BEGIN
Run Query 3
END
END
END
GO

Thanks,
Paul W

 

[Robin Hammond]

Paul,

One thing I found while trying to do this, although I am not sure that this
is the solution for you, is to have a Set NoCount On statement at the start
of the stored proc, and Set No Count Off at the end.

e.g.

Create Proc spWhatever as
BEGIN
SET NOCOUNT ON
'proc code here
SET NOCOUNT OFF
END
GO

Have a look here for where this was applicable and why (thanks to Craig
Kelly in the SQL group)
http://www.google.com.hk/groups?hl=...ps?num=100&hl=en&lr=&safe=off&q=robin+hammond

I've also had a look at how I get data back, and the only difference is that
I am using the Open command rather than execute on the recordset. ie.
instead of

Set rst = cmd.Execute

you could try

With rst
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
'execute the SP returning the result into a recordset
.Open Cmd
End With

Robin Hammond
www.enhanceddatasystems.com

 

[Paul W]

Thanks, I'll give that a shot and let you know a few days.

Paul W