PDA

View Full Version : Error 3001 when calling stored proc



HogHUnter
02-03-2009, 04:52 PM
I have a VBA module that I setup to call a stored proc on my SQL Server 2005 database. The proc works fine from the management interface. However, when I try to call it from VBA I get a 3001 error. I have tried both ways of loading the values into the parameters. TON creation of the parameter and after the parameter is created by assigning the value. Neither changes the result.


myEmp = CInt(DataSheet.tbEmployee.Value)
Set prmEmp = cmd.CreateParameter("@in_emp_id", adInteger, adParamInput, 4, myEmp)
'Append parameters
With cmd
.Parameters.Append prmEmp
End With



myEmp = CInt(DataSheet.tbEmployee.Value)
Set prmEmp = cmd.CreateParameter("@in_emp_id", adInteger, adParamInput)
'Append parameters
With cmd
.Parameters.Append prmEmp
End With
prmEmp.Value = myEmp


Since I have been pulling my hair out with this one, I am at a loss as to how I can get this data. It really does need to come from a proc because the data comes from two sources. I assume this is something I ought to be able to do. One choice I have considered is parsing the SQL and sending it in dynamically instead of calling the stored proc.

stanl
02-14-2009, 02:37 PM
I had a similar problem; I solved it by just opening a Connection then running the stored proc with the .execute() method. Stan