PRB: Decimal Values Passed to a Stored Procedure Get Truncated

MORE INFORMATION

Steps to Reproduce Behavior

1. Use the following sample SQL Server Scripts to generate the stored procedures and table. The first stored procedure illustrates the problem. The second stored procedure demonstrates how to define input parameters with numeric scale:

      if exists (select * from sysobjects where id =
      object_id('dbo.numericscale') and sysstat & 0xf = 3
         drop table dbo.numericscale
      GO
   
      CREATE TABLE dbo.numericscale (
         mynum numeric(5, 2) NULL ,
         mydec decimal(5, 2) NULL
      )
      GO
   
      if exists (select * from sysobjects where id =
      object_id('dbo.SimpleSQL1') and sysstat & 0xf = 4)
         drop procedure dbo.SimpleSQL1
      GO
   
      CREATE PROCEDURE SimpleSQL1 @input1 numeric, @input2 decimal
      AS
      INSERT into numericscale values (@input1, @input2)
      GO
   
      if exists (select * from sysobjects where id =
      object_id('dbo.SimpleSQL2') and sysstat & 0xf = 4)
         drop procedure dbo.SimpleSQL2
      GO
   
      CREATE PROCEDURE SimpleSQL2 @input1 numeric(5,2), @input2
       decimal(5,2)
      AS
      INSERT into numericscale values (@input1, @input2)
      GO
   					

1. Add a reference to Microsoft ActiveX Data Objects in the project.
2. Add the following ADO code to the default form's Load method:
   
   Note You must change User UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

      Dim cn As ADODB.Connection
      Dim cm1 As ADODB.Command
      Dim cm2 As ADODB.Command
      Dim pm1 As ADODB.Parameter
      Dim pm2 As ADODB.Parameter
      Dim rs as ADODB.Recordset
   
      Set cn = New ADODB.Connection
      Set cm1 = New ADODB.Command
      Set cm2 = New ADODB.Command
      Set pm1 = New ADODB.Parameter
      Set pm2 = New ADODB.Parameter
      Set rs = New ADODB.Recordset
   
      With cn
         .ConnectionString = "DRIVER={SQL SERVER};" & _
                          "SERVER=<server_name>;" & _
                          "UID=<username>;PWD=<strong password>"
         .Open
         .DefaultDatabase = "pubs"
      End With
   
      With pm1
         .Direction = adParamInput
         .Name = "param1"
         .NumericScale = 2  ' this corresponds to the second sp's numeric
                            ' scale.
         .Precision = 10
         .Size = 19
         .Type = adNumeric
         .Value = 3.2
      End With
   
      With pm2
         .Direction = adParamInput
         .Name = "param2"
         .NumericScale = 2
         .Precision = 10
         .Size = 19
         .Type = adNumeric
         .Value = 3.2
      End With
   
      With cm1
      Set .ActiveConnection = cn
         .CommandType = adCmdStoredProc
         .CommandText = "simplesql1"
         .Parameters.Append pm1
         .Parameters.Append pm2
         .Parameters(0).Value = 3.25
         .Parameters(1).Value = 4.26
      End With
      cm1.Execute
   
      Set cm1.ActiveConnection = nothing
   
      With cm2
      Set .ActiveConnection = cn
         .CommandType = adCmdStoredProc
         .CommandText = "simplesql2"
         .Parameters.Append pm1
         .Parameters.Append pm2
         .Parameters(0).Value = 5.35
         .Parameters(1).Value = 6.46
      End With
   
      cm2.Execute
      Set cm1 = Nothing
      Set cm2 = Nothing
   
      Set rs = cn.Execute("select * from numericscale")
   
      While Not rs.EOF
         Debug.Print rs(0).Name & ": " & rs(0)
         Debug.Print rs(1).Name & ": " & rs(1)
         rs.MoveNext
      Wend
   
      rs.Close
      Set rs = Nothing
      cn.Close
      Set cn = Nothing
   
      Unload Me
   					

3. Run the form.

1. Create a new Visual Basic (VB) Standard EXE project.
2. Add a Project reference to Microsoft Remote Data Objects.
3. Add the following code to the default form's Load method:
   
   Note You must change User UID =<username> and PWD =<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

      Dim en As rdoEnvironment
      Dim cn As rdoConnection
      Dim rs As rdoResultset
      Dim rq1 As rdoQuery
      Dim rq2 As rdoQuery
      Dim val1 As Double
      Dim val2 As Double
   
      val1 = 8.2
      val2 = 9.2
   
      Set en = rdoEngine.rdoEnvironments(0)
      en.CursorDriver = rdUseOdbc
   
      Set cn = en.OpenConnection("",rdDriverNoPrompt,," & _
                                 "DRIVER={SQL Server};" & _
                                 "Server=matthofa;" & _
                                 "UID=<username>;PWD=<strong password>;" & _
                                 "DATABASE=pubs")
   
      Set rq1 = cn.CreateQuery("", "{Call simplesql1(?,?) }")
      rq1.rdoParameters(0).Direction = rdParamInput
      rq1.rdoParameters(0).Value = val1
      rq1.rdoParameters(1).Direction = rdParamInput
      rq1.rdoParameters(1).Value = val2
   
      Set rq2 = cn.CreateQuery("", "{Call simplesql2(?,?) }")
      rq2.rdoParameters(0).Direction = rdParamInput
      rq2.rdoParameters(0).Value = val1
      rq2.rdoParameters(1).Direction = rdParamInput
      rq2.rdoParameters(1).Value = val2
   
      rq1.Execute
      rq2.Execute
      rq1.Close
      rq2.Close
   
      Set rs = cn.OpenResultset("select * from numericscale")
      While Not rs.EOF
         Debug.Print rs(0).Name & ": " & rs(0)
         Debug.Print rs(1).Name & ": " & rs(1)
         rs.MoveNext
      Wend
   
      cn.Close
      en.Close
      Unload Me
   					

4. Run the application.