Precision Error

[Richard6]

Hello,

I have the following code to update a SQL Server 2005 table via a Stored Procedure called spUpdate. One of the parameters is bring back a PRECISION error and I assume it is the field set to a DECIMAL type on the server (Payment). The table is set to DECIMAL with a scale of 2 and the data always has 2 or less decimal places. The code errors on execute. I don't really understand the PRECISION error so any help no matter how small would be very welcome:

Private Sub cmdselect_Click()
Dim Cmd As New ADODB.Command
Dim Param As New ADODB.Parameter
Cmd.CommandText = "spUpdate"
Cmd.CommandType = adCmdStoredProc

Cmd.ActiveConnection = MyConn

Cmd.Parameters.Refresh

Set Param = Cmd.CreateParameter("Postcode", adChar, adParamInput, 8, Postcode)
Cmd.Parameters.Append Param
Set Param = Cmd.CreateParameter("ID", adChar, adParamInput, 255, ID)
Cmd.Parameters.Append Param
Set Param = Cmd.CreateParameter("StartDate", adDate, adParamInput, StartDate)
Cmd.Parameters.Append Param
Set Param = Cmd.CreateParameter("Code", adChar, adParamInput, 3, Code)
Cmd.Parameters.Append Param
Set Param = Cmd.CreateParameter("Payment", adDecimal, adParamInput, Payment)
Cmd.Parameters.Append Param
Set Param = Cmd.CreateParameter("AID", adInteger, adParamInput, AID)
Cmd.Parameters.Append Param

' Execute once and display...
Cmd.Execute
MyConn.Close

Set Cmd = Nothing
Set Param = Nothing


End Sub

 

[ErikSnoek]

Perhaps using a Round on Payment could help?

Set Param = Cmd.CreateParameter("Payment", adDecimal, adParamInput, Round(Payment,2))

 

[Richard6]

Thanks. I've added that to the code but am still getting "the Precision is invalid". I am assuming that it is to do with the DECIMAL field because there are only int fields apart from this (relating to numbers). I have also tried changing the PRECISION value on the SQL server to 4, 2 and 0 but none of this has worked either.

 

[ErikSnoek]

Alright, I think this should help:

Set Param = Cmd.CreateParameter("Payment", adDecimal, adParamInput, Payment)
Param.NumericScale = 3 'just guessing here
Param.Precision = 2
Cmd.Parameters.Append Param

 

[Richard6]

Thanks, that did the trick. Now the data is all getting passed into the parameters properly but I am getting another error on execution:

"Invalid Character Value for Cast Specification."

Can anyone give me any help on this one?