Microsoft KB Archive/317528

= BUG: The scale value for Oracle numeric parameter is ignored =

Article ID: 317528

Article Last Modified on 3/13/2006

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition

-



This article was previously published under Q317528



This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data
 * System.Data.OleDb



SYMPTOMS
When you set the scale for a parameter of type NUMBER, it does not affect the value of the parameter that is passed to the database.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section.



MORE INFORMATION
If you set the parameter scale to three and the parameter value is 2.1234, the last number of the parameter value (in this example, &quot;4&quot;) is not truncated and it is passed to the database. However, if the column that is going to be updated in the database also has a scale of three, the last number in the parameter value is truncated by the database.

Steps to reproduce the problem
 Create a new Visual Basic Windows Application project. Form1 is added to the project by default.  Add the following code to the top of the Code window: Imports System.Data.OleDb  Add a Button control to Form1. Double-click the Button control to bring up the Button1_Click event in the Code window.  Add the following code to the Button1_Click event of Form1: Dim connection1 As New OleDbConnection(&quot;Provider=MSDAORA;Data Source=myOracle;User ID=myUserID;Password=myPassword&quot;) connection1.Open Dim cmd As New OleDbCommand cmd.Connection = connection1 cmd.CommandText = &quot;create table Testscale(col_float_1 float PRIMARY KEY, col_number_4 numeric(28,4))&quot; cmd.ExecuteNonQuery

Dim command2 = New OleDbCommand(&quot;insert into Testscale(col_float_1, col_number_4) values(?,?)&quot;, connection1) command2.Parameters.Add(&quot;@col_float_1&quot;, OleDbType.Double).Value = 1.0

command2.Parameters.Add(&quot;@col_number_4&quot;, OleDbType.Numeric) command2.Parameters(&quot;@col_number_4&quot;).Precision = 28 command2.Parameters(&quot;@col_number_4&quot;).Scale = 3 command2.Parameters(&quot;@col_number_4&quot;).Value = New Decimal(25.50254)

Dim myx As Exception Try command2.ExecuteNonQuery Catch myx MessageBox.Show(myx.Message.ToString) End Try

Dim Reader2 As OleDbDataReader cmd.CommandText = &quot;select * from Testscale&quot; Try Reader2 = cmd.ExecuteReader

Dim x As Integer Do While Reader2.Read For x = 0 To Reader2.FieldCount - 1 MessageBox.Show(Reader2(x) & &quot; &quot;) Next Console.WriteLine Loop

Finally 'If Reader Is System.DBNull Then Reader2.Close 'End If       End Try  Modify the OleDbConnection string as appropriate for your environment.</li> Press the F5 key to compile and to run the application.</li> Click OK when you receive a dialog box that contains the value of the first field. (In this example, the value &quot;1&quot; appears.)</li> A dialog box that contains the second field will appear. Notice that the parameter value is &quot;25.50254&quot; with a scale of three, but the parameter value &quot;25.5025&quot; was inserted into the database. Even with a scale of three for the parameter, 4 digits were passed to the database. The last digit of the parameter value was truncated by the database because the underlying column has a scale of four.</li></ol>

Keywords: kbbug kbnofix kbsystemdata kbvs2002sp1sweep KB317528

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.