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.

Go to Top of Page

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
Go to Top of Page
   

- Advertisement -