Excel VBA ADODB SQL Stored Proc Parameters

[cardiac]

I am having trouble passing parameters via excel vba to a stored proceudure in MS SQL server 2005.
The code is as follows: -

Sub Get_RowData(TeamName As String, sheetname As String, rownum As Integer)
Dim SQLcmd As String
Dim fromDate As String
Dim toDate As String
Dim strTeam As String
Dim rsCommand As New ADODB.Command
Dim prm As ADODB.Parameter
Dim strConn As String

dbase = "FootballResults"
Set cnDBase = New ADODB.Connection
strConn = "Provider=SQLNCLI;Server=LAPTOP\SQLEXPRESS;Database=" & dbase & "; Trusted_Connection=yes;"

'Now open the connection.
cnDBase.Open strConn
' Create a recordset object.
Set rsDBase = New ADODB.Recordset
Sheets(sheetname).Range("C3").Offset(rownum, 0) = Sheets(sheetname).Range("C30").Offset(rownum, 0)
strTeam = TeamName
fromDate = Format(Sheets("General").Range("FromDate").Value, "yyyy/mm/dd")
toDate = Format(Sheets("General").Range("ToDate").Value, "yyyy/mm/dd")
Set rsCommand = New ADODB.Command
SQLcmd = "sp_CalcTable"
With rsCommand
.CommandType = adCmdStoredProc
.ActiveConnection = cnDBase
.CommandText = SQLcmd
rsCommand.Parameters("@teamname").Value = TeamName
rsCommand.Parameters("@fromDate").Value = fromDate
rsCommand.Parameters("@toDate").Value = toDate
End With
Set rsDBase = cnDBase.Execute(SQLcmd)

Sheets(sheetname).Range("D3").Offset(rownum, 0).CopyFromRecordset rsDBase

Call Close_Connection
End Sub

I get an error message 'Procedure or function expects parameter "@teamname", which was not suppled'.

I would appreciate any help.

 

[xlbo]

Not sure exactly but I have always had success using this method - reference parameters by order rather than name and make sure you refresh them 1st:

Dim cmdl As ADODB.Command

Set strCon = New ADODB.Connection

strCon.Open "ConnectionStringHere"

'Set CONNECTION timeout property
strCon.CommandTimeout = 0

'Create a new command object to process the stored proc
Set cmdl = New ADODB.Command
    
With cmdl
    .ActiveConnection = strCon
    'set COMMAND timeout property - query can time out on either the connection OR the command
    .CommandTimeout = 0
    .CommandText = "StoredProcName"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    'Add parameters to stored proc in order in which they are passed
    .Parameters(1).Value = ParamOneValue
    .Parameters(2).Value = ParamTwoValue
    Set rsRecSet = .Execute()
End With

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cardiac]

Thanks but when I tried this I still get exactly the same error message.

 

[cardiac]

Found the following info while trawling through forums for answers:
Try adding SET NOCOUNT ON as the first statement after AS in your procedure
script. Without that, either ADO or DAO will see the rowcount returned from the
first select, and think the procedure has completed before a result is ready.
You should also add SET NOCOUNT ON to a procedure that executes an update query
of some kind and returns no rows, or you won't be able to trap error information
properly at the front-end.
I added SET NOCOUNT ON as suggested and all is well!!!