Passing parameters to stored procedure

[Newbie]

Hi,

I am using Access 2002 with SQL Server 2000

I have a stored procedure that returns records based on the followung 6
parameters (primary key) however when it runs it comes back with the error:

-2147467259 (80004005) The precision is invalid

My table structure that the procedure runs on is:

PONo char 6
Supplier char 7
Jnl decimal 5
JnlEnt decimal 5
GRNSource char 1
GRN char 9

My code does the following (the variable ares public variants):

params.Append cmd.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmd.CreateParameter("@PONo", adVarChar, adParamInput, 6)
params.Append cmd.CreateParameter("@Jnl", adDecimal, adParamInput, 5)
params.Append cmd.CreateParameter("@JnlEnt", adDecimal, adParamInput, 5)
params.Append cmd.CreateParameter("@GRN", adChar, adParamInput, 9)
params.Append cmd.CreateParameter("@Supplier", adChar, adParamInput, 7)
params.Append cmd.CreateParameter("@GRNSource", adChar, adParamInput, 1)

params("@PONo") = mPONo
params("@Jnl") = mJnl
params("@JnlEnt") = mJnlEnt
params("@GRN") = mGRN
params("@Supplier") = mSupplier
params("@GRNSource") = mGRNSource

Set rs = cmd.Execute

what am I doing wrong

A

 

[Newbie]

Where exactly do I put this?

The Decimal type requires two parts to its length definition Precision and
scale. After creating the Decimal parameter you need to define the
precision and scale by using params.Precision = ## and params.NumericScale =
##.

 

[Newbie]

Thanks for that but where do I put this - it comes with up with object not
found so I have changed it to parameter.Precision = ## and now it says
Variable not defined

Ali

 

[Newbie]

Thanks for that I now have the error Type Mismatch :-(

Any ideas?

Here is my code:
params.Append cmd.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmd.CreateParameter("@PONo", adChar, adParamInput, 6)
params.Append cmd.CreateParameter("@GRNSource", adChar, adParamInput, 1)
params.Append cmd.CreateParameter("@GRN", adChar, adParamInput, 9)
params.Append cmd.CreateParameter("@Supplier", adChar, adParamInput, 7)

Set param = cmd.CreateParameter("@Jnl", adDecimal, adParamInput, 5)
param.Precision = 5
param.NumericScale = 0
cmd.Parameters.Append param

Set param2 = cmd.CreateParameter("JnlEnt", adDecimal, adParamInput, 5)
param2.Precision = 5
param2.NumericScale = 0
cmd.Parameters.Append param2

' Specify input parameter values
params("@PONo") = mPONo
params("@GRNSource") = mGRNSource
param2 = mJnlEnt
param("@Jnl") = mJnl - this is the line where it debugs to
params("@GRN") = mGRN
params("@Supplier") = mSupplier

' Execute the command
Set rs = cmd.Execute

Where exactly do I put this?

5)

You can't use the all-in-one syntax for this type of parameter. You have to
do something like this:

Dim prm As ADODB.Parameter

[...]
Set prm = cmd.CreateParameter("@Jnl", adDecimal, adParamInput, 5)
prm.Precision = xxx
prm.NumericScale = yyy
cmd.Parameters.Append prm