Decimal parameter for stored procedure

[ddub]

Hi,

I need to insert a new record with a decimal value into a table after a record is created. When my event code runs, I get an error message that says "The precision is invalid." I am using an Access 2000 ADP project front-end to connect to my SQL Server 2000 back-end. The Priority field is defined as decimal with a precision of 9 and a scale of 2. As an example, the Priority field in the form has a value of 2.25.

Any help would be greatly appreciated,
ddub

Here's the code in my form.

Private Sub Form_AfterInsert()

    Dim cmd1 As ADODB.Command
    Dim prm1 As ADODB.Parameter
   
    
    Set cmd1 = New ADODB.Command
    With cmd1
        .ActiveConnection = CurrentProject.Connection
        .CommandType = adCmdStoredProc
        .CommandText = "spCreateCIPShellRec"
    End With
 
    Set prm1 = cmd1.CreateParameter("@Priority", adDecimal, adParamInput)
    
    
    cmd1.Parameters.Append prm10
    prm10.Value = Me.Priority
    
    cmd1.Execute
End Sub

Here's the stored procedure.

Alter Procedure spCreateCIPShellRec
	
	@Priority decimal	
As
	INSERT INTO tblCIPAdj (Priority) VALUES (@Priority)
			
return

 

[kraxmo]

You might try embedding prm10 within a CDec(prm10) or by multiplying it by 1.0

BTW, what is the value of prm10? Jim Kraxberger
Developing Access solutions since 1995

 

[ddub]

Hi Jim,

I tried both of your suggestions, unfortunately, still got the same error message. The value of prm10 was 2.25. Here's what I changed the code to:

prm10.Value = CDec(Me.Priority)

If you can think of any other fix, please let me know.

Thanks,
ddub
BTW, in the SET and DIM statements, prm1 should be prm10.