SQL Parameter Passing

[SqueakinSweep]

I am not over familiar with VBA, but recently I was tasked with pulling data from our SQL database into an Excel spreadsheet. I managed this fairly easily, but what I really want to do is to execute a SQL stored procedure, and be able to pass parameters into it. Executing the stored proc works fine in the code below, the error I get is when I try to include a parameter. When I include the line ocommand.Parameters.Append, I get a Run time error 424, and I have tried running this same code on Excel 2003 and 2010. Can anyone give me a few pointers as to what is wrong?

Sub Refresh()

Dim oConn As New ADODB.Connection
Dim oCommand As New ADODB.Command
Dim oRst As ADODB.Recordset
Dim oPM As ADODB.Parameter

Dim sConn As String
sConn = "Driver=SQL Server;Server=xxxx;Database=xxxx;User ID=xxxx;Password=xxxx"

oConn.Open (sConn)
oCommand.CommandText = "sp_Test1"
oCommand.CommandType = adCmdStoredProc
oCommand.ActiveConnection = oConn

Set oPM = oCommand.CreateParameter("@sName", adVarChar, adParamInput, , "A")
oCommand.Parameters.Append (oPM)

Set oRst = oCommand.Execute
Call Range("A2").CopyFromRecordset(oRst)

'Clean up
Set oRst = Nothing
Set oCommand = Nothing
Set oConn = Nothing

End Sub

Sweep
...if it works, you know the rest..
Always remember that Google is your friend

 

[kjv1611]

Looks like you're trying to pass a string/varchar as a numeric value, as far as the way SQL will see it.

Try changing this:

Set oPM = oCommand.CreateParameter("@sName", adVarChar, adParamInput, , "A")

To:

Set oPM = oCommand.CreateParameter("@sName", adVarChar, adParamInput, , "'A'")

If that doesn't fix it, then I'd suggest trying to run it w/o the parameter function in VBA. Instead, use the text for the SQL string to pass your parameter.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57