PDA

View Full Version : Calling a Stored Proc from VBA



deyken
01-17-2012, 02:57 AM
Hi All,

I am attempting to call a simply SP from VBA Code, passing in 4 parameters. The SP need not return any results. It just does a little job using the given parameters on the server side. I get the error: Parameter improperly defined...

Please help? Here is my VBA code:

' Call Stored Procedure for updating the GL_USED Column for this Item Record
Dim DB As New ADODB.Connection
DB.Open "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=PROJP002D830\SQLEXPRESS;Database=Allowables"
Dim Cmd1 As ADODB.Command
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = DB
Cmd1.CommandText = "UpdateAllowableUsed"
Cmd1.CommandType = adCmdStoredProc

Set prm = Cmd1.CreateParameter("ProjectCode", adString, adParamInput)
Cmd1.Parameters.Append prm
Cmd1.Parameters("ProjectCode").Value = ProjectCode
Set prm = Cmd1.CreateParameter("GLCode", adString, adParamInput)
Cmd1.Parameters.Append prm
Cmd1.Parameters("GLCode").Value = GLCode
Set prm = Cmd1.CreateParameter("ProdDescr", adString, adParamInput)
Cmd1.Parameters.Append prm
Cmd1.Parameters("ProdDescr").Value = ItemDescr
Set prm = Cmd1.CreateParameter("Value", adString, adParamInput)
Cmd1.Parameters.Append prm
Cmd1.Parameters("Value").Value = TotalPrice

Dim AllowUpdated As New ADODB.Recordset
Set AllowUpdated = Cmd1.Execute
AllowUpdated.Open Cmd1

Bob Phillips
01-17-2012, 03:24 AM
Is the error on the last line of the code shown? I am just wondering why you use Execute and Open. A quick scan of my code that runs SPs to get data just seems to do an Open.

deyken
01-17-2012, 03:42 AM
Hi XLD,

I get the error on the first Parameter constructor, saying that the parameter is improperly defined. Is my syntax incorrect?

Bob Phillips
01-17-2012, 04:30 AM
I believe that you will need to assign the value before appending the parameter. Looking at my code, I also pass the length to the parameter.



Set prm = Cmd1.CreateParameter("ProjectCode", adString, adParamInput, Len(ProjectCode))
prm.Value = ProjectCode
Cmd1.Parameters.Append prm

Aflatoon
01-17-2012, 06:54 AM
In case it helps, you can often just use something like:


' Call Stored Procedure for updating the GL_USED Column for this Item Record
Dim DB As New ADODB.Connection
DB.Open "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=PROJP002D830\SQLEXPRESS;Database=Allowables"
DB.UpdateAllowableUsed ProjectCode, GLCode, ItemDescr, TotalPrice

and ADO will make a best guess at the parameter types.