Invalid Scale Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Invalid Scale Value

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-10 : 08:53:10

John writes "Hello,
I've coded the parameter list in VB for a procedure created in SQL Server. As I debug the code, it gets through all of the parameters, connection, text, text, etc and then bombs once it gets to the execute statement. I get:

Run-time error '-2147217887 (80040e21)':
[Microsoft][ODBC SQL Server Driver]:Invalid scale value

Thanks in advance. Here's the procedure set up:

With cmd_tblIntersections_insert
.CommandText = "sp_tblIntersections_insert"
.CommandType = adCmdStoredProc
.ActiveConnection = cn_SQLServer
.Parameters.Append .CreateParameter("ItersectionUniqueID", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, frmCreateIntersection.cboNS.Text)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, frmCreateIntersection.cboEW.Text)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 12, Null)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 25, Null)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 10, Null)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 10, Null)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 15, Null)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 15, Null)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 15, Null)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, Null)
.Parameters.Append .CreateParameter(, adInteger, adParamInput, 4, Null)
.Parameters.Append .CreateParameter(, adInteger, adParamInput, 4, Null)
.Parameters.Append .CreateParameter(, adNumeric, adParamInput, 9, Null)
.Parameters.Append .CreateParameter(, adNumeric, adParamInput, 9, Null)
End With
cmd_tblIntersections_insert.Execute

Here's the procedure:

CREATE procedure sp_tblIntersections_insert(@NSStreet nvarchar(50), @EWStreet nvarchar(50),
@intAsBuiltDate nvarchar(12), @intControllerModel nvarchar(25), @intCabinetType nvarchar(10),
@intModemModel nvarchar(10), @intModemOperation nvarchar(15), @intModemConnection nvarchar(15),
@intTrueTimeUnit nvarchar(15), @intControllerNotes nvarchar(50), @intTotalADA int,
@intTotalOther int,@intXCOORD numeric(9), @intYCOORD numeric(9))
as
insert into tblIntersections (NSStreet, EWStreet,intAsBuiltDate,intControllerModel, intCabinetType,
intModemModel, intModemOperation, intModemConnection, intTrueTimeUnit, intControllerNotes,
intTotalADA, intTotalOther, intXCOORD,intYCOORD) values(@NSStreet,@EWStreet,@intAsBuiltDate,
@intControllerModel, @intCabinetType,@intModemModel,@intModemOperation, @intModemConnection,
@intTrueTimeUnit,@intControllerNotes, @intTotalADA,@intTotalOther,@intXCOORD,@intYCOORD)

return @@Identity

Any help would be greatly appreciated.
-John"

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-04-10 : 09:06:54

You don't specify the scale for your numerics. Change your sproc like this

@intTotalOther int,@intXCOORD numeric(9,0), @intYCOORD numeric(9,0))

Replace the zero with whatever value is appropriate for numbers past the decimal point.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-10 : 09:11:09

Found this little snippet in the ADO documentation, under the "CreateParameter" method:

If you specify a numeric data type (adNumeric or adDecimal) in the Type argument, then you must also set the NumericScale and Precision properties.

You'd need to change your code a little bit:

.Parameters.Append .CreateParameter("@intXCOORD", adNumeric, adParamInput, 9, Null)
.Parameters.Append .CreateParameter("@intYCOORD", adNumeric, adParamInput, 9, Null)
.Parameters("@intXCOORD").NumericScale=2
.Parameters("@intYCOORD").NumericScale=2

Also, in your SQL stored procedure, you declared:

@intXCOORD numeric(9), @intYCOORD numeric(9)

Numeric and decimal datatypes must have a precision (# of digits) and scale (# of decimals) when you declare them, so something like this:

@intXCOORD numeric(9,2), @intYCOORD numeric(9,2)

...would be needed.

SNIPED AGAIN! Must......type......faster.....

Edited by - robvolk on 04/10/2002 09:16:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources