Expects Parameters When It Should Not

Hi,
I have a stored proc that has one output parameter. When I call
this sp from my client program (an MS Access VBA) it has always worked fine,
but just recently started to give me a message saying that the stored
procedure is expecting a parameter named @.TPRD but did not receive it,
program fails. @.TPRD is the output parameter and is declared as such. I
have not changed the sp recently so there is no code change in the sp. I
do not administrate my SQL Server, but I did request my administrator to
grant me EXEC access to sp_debug so that I could run the stored procedure
debugger. My sp stopped working at about the same time. Do you think
these two events could be related? How to solve? The code for my
stored procedure is below:
ALTER PROCEDURE [ad\jmuseck].[SELECT_TPRD]
@.TPRD VARCHAR(6) OUTPUT
AS
SELECT @.TPRD = TPRD
FROM tblCurrentMonth
RETURN @.TPRD
----
The code in my client MS Access VBA program looks like this:
Function SelectTprd(Optional newval As Variant) As String
Dim cmdCommand As ADODB.Command
Dim prmTPRD As ADODB.Parameter
Set cmdCommand = New ADODB.Command
cmdCommand.ActiveConnection = CurrentProject.AccessConnection
cmdCommand.CommandType = adCmdStoredProc
cmdCommand.CommandText = "SELECT_TPRD"
cmdCommand.Parameters.Refresh
<--STARTED FAILING ON THIS LINE !!
Set prmTPRD = cmdCommand.CreateParameter("@.TPRD", adChar, adParamOutput)
cmdCommand.Execute
'Return the time period to the calling program.'
SelectTprd = cmdCommand.Parameters(1).Value
Set cmdCommand = Nothing 'Free memory
End FunctionHello, Joe
This problem is related to MS Access, more specifically to the OLEDB
provider that Access uses internally. Use one of the following
workarounds:
1. Do not use Parameters.Refresh; instead of this, add each of the
parameters of the stored procedure (even if they have default values)
to the Parameters collection, *in the same order that they are
defined*, using something like this:
cmd.Parameters.Append cmd.CreateParameter("@.ParameterName",...)
2. Use another connection, based on the SQL Server OLEDB provider
(instead of the internal Access OLEDB provider), i.e. instead of the
line:
cmdCommand.ActiveConnection = CurrentProject.AccessConnection
use the following:
cmdCommand.ActiveConnection = CurrentProject.BaseConnectionString
3. If you use at least ADO 2.6, set the NamedParameters property to
True (before setting the ActiveConnection property) and:
a) use Parameters.Refresh (after setting the ActiveConnection) and fill
only the input parameters you need with something like this:
cmd.Parameters("@.ParameterName")=Value
after cmd.Execute, you can get the value of the output parameter by
reffering to it the same way:
Variable=cmd.Parameters("@.ParameterName")
or:
b) do not use Parameters.Refresh, but add the input parameters that you
need (in any order) (it's not necessary to add the input parameters
that have default values) , and also the output parameters (before
setting the ActiveConnection), with something like this:
cmd.Parameters.Append cmd.CreateParameter("@.ParameterName",...)
after cmd.Execute, you can get the value of the output parameter by
reffering to it the same way as above:
Variable=cmd.Parameters("@.ParameterName")
For more informations about the last method, see:
http://msdn.microsoft.com/library/e...dparameters.asp
The easiest method would be the second one (using the
BaseConnectionString to open another connection).
Note: in this message, I wrote about the case when the procedure has
more input parameters, some of them having default values, in addition
to the output parameters. In your case, if you have just one (output)
parameter, some of my remarks do not apply (i.e. adding the parameters
in the same order that they are defined, etc).
Razvan