Invalid scale error on Command.Execute

[Joseph]

I'm building an Access Project that hits a SQL Server 2000 database. I'm
using a command object to return a stored proc's rows to a recordset:

Dim sProfSvcThresh As Single
Dim sInstSvcThresh As Single
Dim sProfSvcRate As Single
Dim sInstSvcRate As Single

'Populate the stop-loss recordset
'Prep the command object
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "spStopLossRecovery"
cmd.CommandType = adCmdStoredProc
Set parm = cmd.CreateParameter("@ProfSvcThresh", adNumeric,
adParamInput)
parm.NumericScale = 10
parm.Precision = 4
parm.Value = sProfSvcThresh
cmd.Parameters.Append parm
Set parm = cmd.CreateParameter("@InstSvcThresh", adNumeric,
adParamInput)
parm.NumericScale = 10
parm.Precision = 4
parm.Value = sInstSvcThresh
cmd.Parameters.Append parm
Set parm = cmd.CreateParameter("@ProfSvcRate", adNumeric, adParamInput)
parm.NumericScale = 5
parm.Precision = 4
parm.Value = sProfSvcRate
cmd.Parameters.Append parm
Set parm = cmd.CreateParameter("@InstSvcRate", adNumeric, adParamInput)
parm.NumericScale = 5
parm.Precision = 4
parm.Value = sInstSvcRate
cmd.Parameters.Append parm

Set rsStopLoss = cmd.Execute

The last line of code causes the following runtime error:
-2147467259 (80004005) The scale is invalid.

I've tried singles, doubles, and currencies for the variables in the VB
code. Anyone see what I'm doing wrong here?

The parameters in my stored proc are:
@ProfSvcThresh decimal (10,4),
@InstSvcThresh decimal (10,4),
@ProfSvcRate decimal (5,4),
@InstSvcRate decimal (5,4)

Thanks,

Joseph

[Stephen Howe]

> Set rsStopLoss = cmd.Execute
>
> The last line of code causes the following runtime error:
> -2147467259 (80004005) The scale is invalid.
>
> I've tried singles, doubles, and currencies for the variables in the VB
> code. Anyone see what I'm doing wrong here?

Not off hand. But why not try this technique:

1. Do a ParameterRefresh instead of constructing your Parameter collection
and then see if it works. It probably will.
2. Then dump the parameter collection to a text file or debug etc. Make sure
it is a complete dump of the properties. On dumping you will see what Scale
parameter should be set to for the parameter collection. Check also the
type.
3. Comment out the ParameterRefresh and set your Parameter collection to to
be the same. The only thing here is that ADO cannot always tell the
direction of Parameters so you may need to change adParameterInputOutput to
what it really is.

Stephen Howe

[Joseph]

Stephen,

Thanks. I looked at the parameter properties after refreshing, and
discovered that I had the definitions of "scale" and "precision" reversed in
my mind.

From the SQL Server Books Online:
"Precision is the number of digits in a number. Scale is the number of
digits to the right of the decimal point in a number. For example, the
number 123.45 has a precision of 5 and a scale of 2." My code would have
been setting the _scale_ to 5 and the _precision_ to 2.

In my mind, "precision" is about exactness (decimal places) and "scale" is
about size (order of magnitude), but who am I to question years of tradition
;) Lesson learned!

Joseph

"Stephen Howe" <SPAMstephe...@tnsofres.com> wrote in message
news:uPwZ%23qJFD...@TK2MSFTNGP12.phx.gbl...