I've discovered what seems to be a pretty significant bug in
System.Decimal. I'd like to report it to Microsoft, but I'm not sure
how, so I figure maybe someone from MS will see it if I post it here.
And I'd welcome any feedback, or course. Here's a summary of the bug:

When you have two decimals that are equal to each other (i.e. 1D and
1D), and subtract one from the other, you get a result that is "equal"
to zero, but not really. If you check Decimal.Zero.Equals(1D - 1D), it
will return true. However, if you look at the actual values stored in
the Decimal structure, they are different for Decimal.Zero and (1D -
1D):

?Decimal.GetBits(Decimal.Zero)
{Length=4}
(0): 0
(1): 0
(2): 0
(3): 0

?Decimal.GetBits(1D - 1D)
{Length=4}
(0): 0
(1): 0
(2): 0
(3): -2147483648

You'll notice that the contents of the last byte are different. The
first 3 are the hi, mid and lo portions of the decimal structure, and
determine the actual value; the last byte is a private member called
flags and seems to be used to determine the placement of the decimal,
whether the value is positive or negative, etc. So, what we get when
we subtract 1D from 1D is something like -0.00000000. Note that this
also occurs if you add 1D and -1D, as you would expect.

This wouldn't be such a big deal if that last byte was always ignored
when the first three bytes are zero; however, this is not always the
case. Specifically, that last byte is not ignored when the value is a
parameter of a SqlCommand. In that case, SQL server appears to try to
interpret that last byte and make sense of it. Sometimes nothing bad
happens (such as when you subtract 1D from 1D). However, when you set
up values with a precision out to 10 places going into a Decimal (28,
10) column, SQL server winds up inserting a weird undefined value.
It's somewhere between 0 and -.00000000000000000000000000000000000001
(the negative value that is the closest to zero SQL server allows). In
Query Analyzer, it shows up as -.0000000000.

Here is the code for a little test form that demonstrates the problem.

Imports System.Data.SqlClient
Imports System.IO
Imports System.Reflection
Imports System.Text

Public Class DecimalBug_UI
Inherits System.Windows.Forms.Form

Private mValue1 As String
Private mValue2 As String

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call
mValue1 = txtValue1.Text
mValue2 = txtValue2.Text
End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As
Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form
Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents btnRunTest As System.Windows.Forms.Button
Friend WithEvents txtConnectionString As
System.Windows.Forms.TextBox
Friend WithEvents btnCreateTable As System.Windows.Forms.Button
Friend WithEvents lblConnectionString As System.Windows.Forms.Label
Friend WithEvents btnQueryTable As System.Windows.Forms.Button
Friend WithEvents lblValue1 As System.Windows.Forms.Label
Friend WithEvents txtValue1 As System.Windows.Forms.TextBox
Friend WithEvents lblValue2 As System.Windows.Forms.Label
Friend WithEvents txtValue2 As System.Windows.Forms.TextBox
Friend WithEvents btnBadData As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.btnRunTest = New System.Windows.Forms.Button
Me.txtConnectionString = New System.Windows.Forms.TextBox
Me.btnCreateTable = New System.Windows.Forms.Button
Me.lblConnectionString = New System.Windows.Forms.Label
Me.btnQueryTable = New System.Windows.Forms.Button
Me.txtValue1 = New System.Windows.Forms.TextBox
Me.lblValue1 = New System.Windows.Forms.Label
Me.lblValue2 = New System.Windows.Forms.Label
Me.txtValue2 = New System.Windows.Forms.TextBox
Me.btnBadData = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'btnRunTest
'
Me.btnRunTest.Enabled = False
Me.btnRunTest.Location = New System.Drawing.Point(324, 92)
Me.btnRunTest.Name = "btnRunTest"
Me.btnRunTest.Size = New System.Drawing.Size(104, 23)
Me.btnRunTest.TabIndex = 0
Me.btnRunTest.Text = "Insert Data"
'
'txtConnectionString
'
Me.txtConnectionString.Location = New System.Drawing.Point(12,
28)
Me.txtConnectionString.Name = "txtConnectionString"
Me.txtConnectionString.Size = New System.Drawing.Size(600, 20)
Me.txtConnectionString.TabIndex = 1
Me.txtConnectionString.Text = ""
'
'btnCreateTable
'
Me.btnCreateTable.Location = New System.Drawing.Point(40, 92)
Me.btnCreateTable.Name = "btnCreateTable"
Me.btnCreateTable.Size = New System.Drawing.Size(120, 23)
Me.btnCreateTable.TabIndex = 2
Me.btnCreateTable.Text = "Create Test Table"
'
'lblConnectionString
'
Me.lblConnectionString.Location = New System.Drawing.Point(12,
8)
Me.lblConnectionString.Name = "lblConnectionString"
Me.lblConnectionString.Size = New System.Drawing.Size(600, 20)
Me.lblConnectionString.TabIndex = 3
Me.lblConnectionString.Text = "SQL Server Connection String:"
Me.lblConnectionString.TextAlign =
System.Drawing.ContentAlignment.MiddleLeft
'
'btnQueryTable
'
Me.btnQueryTable.Enabled = False
Me.btnQueryTable.Location = New System.Drawing.Point(460, 92)
Me.btnQueryTable.Name = "btnQueryTable"
Me.btnQueryTable.Size = New System.Drawing.Size(120, 23)
Me.btnQueryTable.TabIndex = 4
Me.btnQueryTable.Text = "List Table Contents"
'
'txtValue1
'
Me.txtValue1.Location = New System.Drawing.Point(168, 56)
Me.txtValue1.Name = "txtValue1"
Me.txtValue1.Size = New System.Drawing.Size(120, 20)
Me.txtValue1.TabIndex = 5
Me.txtValue1.Text = "1.0000000001"
'
'lblValue1
'
Me.lblValue1.Location = New System.Drawing.Point(88, 56)
Me.lblValue1.Name = "lblValue1"
Me.lblValue1.Size = New System.Drawing.Size(80, 20)
Me.lblValue1.TabIndex = 6
Me.lblValue1.Text = "Value1:"
Me.lblValue1.TextAlign =
System.Drawing.ContentAlignment.MiddleLeft
'
'lblValue2
'
Me.lblValue2.Location = New System.Drawing.Point(324, 56)
Me.lblValue2.Name = "lblValue2"
Me.lblValue2.Size = New System.Drawing.Size(80, 20)
Me.lblValue2.TabIndex = 8
Me.lblValue2.Text = "Value2:"
Me.lblValue2.TextAlign =
System.Drawing.ContentAlignment.MiddleLeft
'
'txtValue2
'
Me.txtValue2.Location = New System.Drawing.Point(404, 56)
Me.txtValue2.Name = "txtValue2"
Me.txtValue2.ReadOnly = True
Me.txtValue2.Size = New System.Drawing.Size(120, 20)
Me.txtValue2.TabIndex = 7
Me.txtValue2.Text = "-1.0000000001"
'
'btnBadData
'
Me.btnBadData.Location = New System.Drawing.Point(192, 92)
Me.btnBadData.Name = "btnBadData"
Me.btnBadData.Size = New System.Drawing.Size(96, 23)
Me.btnBadData.TabIndex = 9
Me.btnBadData.Text = "Reset Values"
'
'DecimalBug_UI
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(624, 121)
Me.Controls.Add(Me.btnBadData)
Me.Controls.Add(Me.lblValue2)
Me.Controls.Add(Me.txtValue2)
Me.Controls.Add(Me.lblValue1)
Me.Controls.Add(Me.txtValue1)
Me.Controls.Add(Me.btnQueryTable)
Me.Controls.Add(Me.lblConnectionString)
Me.Controls.Add(Me.btnCreateTable)
Me.Controls.Add(Me.txtConnectionString)
Me.Controls.Add(Me.btnRunTest)
Me.Name = "DecimalBug_UI"
Me.Text = "Decimal Bug Test"
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnRunTest.Click
Dim connection As New
SqlConnection(Me.txtConnectionString.Text)
connection.Open()
Dim cmd As New SqlCommand
Try
Dim msg As New StringBuilder
msg.Append("Dim value1 As Decimal = " & txtValue1.Text &
vbCrLf)
Dim value1 As Decimal = Convert.ToDecimal(txtValue1.Text)
msg.Append("Dim value2 As Decimal = " & txtValue2.Text &
vbCrLf)
Dim value2 As Decimal = Convert.ToDecimal(txtValue2.Text)
msg.Append("Dim finalValue As Decimal = value1 + value2" &
vbCrLf & vbCrLf)
Dim finalValue As Decimal = value1 + value2
msg.Append("finalValue.ToString = " & finalValue.ToString &
vbCrLf)
msg.Append("Decimal.GetBits(finalValue) = " &
GetIntegerArrayString(Decimal.GetBits(finalValue)) & vbCrLf)
msg.Append("Decimal.GetBits(Decimal.Zero) = " &
GetIntegerArrayString(Decimal.GetBits(Decimal.Zero)) & vbCrLf & vbCrLf)

cmd.CommandText = "INSERT INTO TestTable (TEST_Value)
VALUES (@Value)"
msg.Append("INSERT INTO TestTable (TEST_Value) VALUES
(@Value); @Value = finalValue" & vbCrLf)
cmd.Connection = connection

cmd.Parameters.Add("@Value", finalValue)
cmd.ExecuteNonQuery()

MessageBox.Show(msg.ToString, "Here's what ran...",
MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show("Exception: " & ex.Message)
Finally
cmd.Dispose()
connection.Close()
End Try
End Sub

Private Sub btnCreateTable_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnCreateTable.Click
If CreateTable() Then
Me.btnQueryTable.Enabled = True
Me.btnRunTest.Enabled = True
MessageBox.Show("The TestTable has been created.")
End If
End Sub

Protected Overridable Function CreateTable() As Boolean
Dim connection As SqlConnection
Dim cmd As SqlCommand
Try
connection = New SqlConnection(Me.txtConnectionString.Text)
connection.Open()
cmd = New SqlCommand
cmd.Connection = connection

cmd.CommandText = "if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1) drop table [dbo].[TestTable]"
cmd.ExecuteNonQuery()

cmd.CommandText = "CREATE TABLE [dbo].[TestTable]
([TEST_RecordID] uniqueidentifier ROWGUIDCOL NOT NULL , [TEST_Value]
[decimal](28, 10) NOT NULL ) ON [PRIMARY]"
cmd.ExecuteNonQuery()

cmd.CommandText = "ALTER TABLE [dbo].[TestTable] WITH
NOCHECK ADD CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
([TEST_RecordID]) ON [PRIMARY]"
cmd.ExecuteNonQuery()

cmd.CommandText = "ALTER TABLE [dbo].[TestTable] ADD
CONSTRAINT [DF_TestTable_TEST_RecordID] DEFAULT (newid()) FOR
[TEST_RecordID]"
cmd.ExecuteNonQuery()

Return True
Catch ex As Exception
MessageBox.Show("Exception: " & ex.Message)
Return False
Finally
If Not cmd Is Nothing Then cmd.Dispose()
If Not connection Is Nothing AndAlso connection.State =
ConnectionState.Open Then connection.Close()
End Try
End Function

Protected Overridable Function GetIntegerArrayString(ByVal array As
Integer()) As String
Dim msg As New StringBuilder
For Each i As Integer In array
msg.Append(i.ToString & ", ")
Next
Return msg.ToString.Substring(0, msg.Length - 2)
End Function

Private Sub btnQueryTable_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnQueryTable.Click
Dim connection As SqlConnection
Dim cmd As SqlCommand
Dim result As Integer
Try
Dim msg As New StringBuilder
connection = New SqlConnection(Me.txtConnectionString.Text)
connection.Open()
cmd = New SqlCommand
cmd.Connection = connection

cmd.CommandText = "SELECT COUNT(*) FROM TestTable"
result = CInt(cmd.ExecuteScalar())
msg.Append("(" & cmd.CommandText & ") = " & result.ToString
& vbCrLf)

cmd.CommandText = "SELECT COUNT(*) FROM TestTable WHERE
TEST_Value < 0 AND TEST_Value >
-.00000000000000000000000000000000000001"
result = CInt(cmd.ExecuteScalar())
msg.Append("(" & cmd.CommandText & ") = " & result.ToString
& vbCrLf)

cmd.CommandText = "SELECT COUNT(*) FROM TestTable WHERE
TEST_Value = 0"
result = CInt(cmd.ExecuteScalar())
msg.Append("(" & cmd.CommandText & ") = " & result.ToString
& vbCrLf)

MessageBox.Show(msg.ToString, "TestTable Contents",
MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show("Exception: " & ex.Message)
Finally
If Not cmd Is Nothing Then cmd.Dispose()
If Not connection Is Nothing AndAlso connection.State =
ConnectionState.Open Then connection.Close()
End Try
End Sub

Private Sub txtValue1_Validating(ByVal sender As Object, ByVal e As
System.ComponentModel.CancelEventArgs) Handles txtValue1.Validating,
txtValue2.Validating
Try
Dim dec As Decimal = Convert.ToDecimal(DirectCast(sender,
TextBox).Text)
Catch ex As Exception
e.Cancel = True
MessageBox.Show("That is not a valid decimal.")
End Try
End Sub

Private Sub txtValue1_Validated(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtValue1.Validated
txtValue2.Text = (Convert.ToDecimal(DirectCast(sender,
TextBox).Text) * -1).ToString
End Sub

Private Sub btnBadData_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnBadData.Click
txtValue1.Text = mValue1
txtValue2.Text = mValue2
End Sub
End Class

quote

'However, if you look at the actual values stored in the Decimal structure'

Why would i look inside the data structure for a decimal, I don't care about
the INTERNAL workings on the class\struct as long as the exposed value is
correct which as you state is correct. You might want to look up the
definition of encapsulation - as in encapsulates the detail of how a decimal
is stored\manipulated away from the end user




"Myron Marston" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I've discovered what seems to be a pretty significant bug in
> System.Decimal. I'd like to report it to Microsoft, but I'm not sure
> how, so I figure maybe someone from MS will see it if I post it here.
> And I'd welcome any feedback, or course. Here's a summary of the bug:
>
> When you have two decimals that are equal to each other (i.e. 1D and
> 1D), and subtract one from the other, you get a result that is "equal"
> to zero, but not really. If you check Decimal.Zero.Equals(1D - 1D), it
> will return true. However, if you look at the actual values stored in
> the Decimal structure, they are different for Decimal.Zero and (1D -
> 1D):
>
> ?Decimal.GetBits(Decimal.Zero)
> {Length=4}
> (0): 0
> (1): 0
> (2): 0
> (3): 0
>
> ?Decimal.GetBits(1D - 1D)
> {Length=4}
> (0): 0
> (1): 0
> (2): 0
> (3): -2147483648
>
> You'll notice that the contents of the last byte are different. The
> first 3 are the hi, mid and lo portions of the decimal structure, and
> determine the actual value; the last byte is a private member called
> flags and seems to be used to determine the placement of the decimal,
> whether the value is positive or negative, etc. So, what we get when
> we subtract 1D from 1D is something like -0.00000000. Note that this
> also occurs if you add 1D and -1D, as you would expect.
>
> This wouldn't be such a big deal if that last byte was always ignored
> when the first three bytes are zero; however, this is not always the
> case. Specifically, that last byte is not ignored when the value is a
> parameter of a SqlCommand. In that case, SQL server appears to try to
> interpret that last byte and make sense of it. Sometimes nothing bad
> happens (such as when you subtract 1D from 1D). However, when you set
> up values with a precision out to 10 places going into a Decimal (28,
> 10) column, SQL server winds up inserting a weird undefined value.
> It's somewhere between 0 and -.00000000000000000000000000000000000001
> (the negative value that is the closest to zero SQL server allows). In
> Query Analyzer, it shows up as -.0000000000.
>
> Here is the code for a little test form that demonstrates the problem.
>
> Imports System.Data.SqlClient
> Imports System.IO
> Imports System.Reflection
> Imports System.Text
>
> Public Class DecimalBug_UI
> Inherits System.Windows.Forms.Form
>
> Private mValue1 As String
> Private mValue2 As String
>
> #Region " Windows Form Designer generated code "
>
> Public Sub New()
> MyBase.New()
>
> 'This call is required by the Windows Form Designer.
> InitializeComponent()
>
> 'Add any initialization after the InitializeComponent() call
> mValue1 = txtValue1.Text
> mValue2 = txtValue2.Text
> End Sub
>
> 'Form overrides dispose to clean up the component list.
> Protected Overloads Overrides Sub Dispose(ByVal disposing As
> Boolean)
> If disposing Then
> If Not (components Is Nothing) Then
> components.Dispose()
> End If
> End If
> MyBase.Dispose(disposing)
> End Sub
>
> 'Required by the Windows Form Designer
> Private components As System.ComponentModel.IContainer
>
> 'NOTE: The following procedure is required by the Windows Form
> Designer
> 'It can be modified using the Windows Form Designer.
> 'Do not modify it using the code editor.
> Friend WithEvents btnRunTest As System.Windows.Forms.Button
> Friend WithEvents txtConnectionString As
> System.Windows.Forms.TextBox
> Friend WithEvents btnCreateTable As System.Windows.Forms.Button
> Friend WithEvents lblConnectionString As System.Windows.Forms.Label
> Friend WithEvents btnQueryTable As System.Windows.Forms.Button
> Friend WithEvents lblValue1 As System.Windows.Forms.Label
> Friend WithEvents txtValue1 As System.Windows.Forms.TextBox
> Friend WithEvents lblValue2 As System.Windows.Forms.Label
> Friend WithEvents txtValue2 As System.Windows.Forms.TextBox
> Friend WithEvents btnBadData As System.Windows.Forms.Button
> <System.Diagnostics.DebuggerStepThrough()> Private Sub
> InitializeComponent()
> Me.btnRunTest = New System.Windows.Forms.Button
> Me.txtConnectionString = New System.Windows.Forms.TextBox
> Me.btnCreateTable = New System.Windows.Forms.Button
> Me.lblConnectionString = New System.Windows.Forms.Label
> Me.btnQueryTable = New System.Windows.Forms.Button
> Me.txtValue1 = New System.Windows.Forms.TextBox
> Me.lblValue1 = New System.Windows.Forms.Label
> Me.lblValue2 = New System.Windows.Forms.Label
> Me.txtValue2 = New System.Windows.Forms.TextBox
> Me.btnBadData = New System.Windows.Forms.Button
> Me.SuspendLayout()
> '
> 'btnRunTest
> '
> Me.btnRunTest.Enabled = False
> Me.btnRunTest.Location = New System.Drawing.Point(324, 92)
> Me.btnRunTest.Name = "btnRunTest"
> Me.btnRunTest.Size = New System.Drawing.Size(104, 23)
> Me.btnRunTest.TabIndex = 0
> Me.btnRunTest.Text = "Insert Data"
> '
> 'txtConnectionString
> '
> Me.txtConnectionString.Location = New System.Drawing.Point(12,
> 28)
> Me.txtConnectionString.Name = "txtConnectionString"
> Me.txtConnectionString.Size = New System.Drawing.Size(600, 20)
> Me.txtConnectionString.TabIndex = 1
> Me.txtConnectionString.Text = ""
> '
> 'btnCreateTable
> '
> Me.btnCreateTable.Location = New System.Drawing.Point(40, 92)
> Me.btnCreateTable.Name = "btnCreateTable"
> Me.btnCreateTable.Size = New System.Drawing.Size(120, 23)
> Me.btnCreateTable.TabIndex = 2
> Me.btnCreateTable.Text = "Create Test Table"
> '
> 'lblConnectionString
> '
> Me.lblConnectionString.Location = New System.Drawing.Point(12,
> 8)
> Me.lblConnectionString.Name = "lblConnectionString"
> Me.lblConnectionString.Size = New System.Drawing.Size(600, 20)
> Me.lblConnectionString.TabIndex = 3
> Me.lblConnectionString.Text = "SQL Server Connection String:"
> Me.lblConnectionString.TextAlign =
> System.Drawing.ContentAlignment.MiddleLeft
> '
> 'btnQueryTable
> '
> Me.btnQueryTable.Enabled = False
> Me.btnQueryTable.Location = New System.Drawing.Point(460, 92)
> Me.btnQueryTable.Name = "btnQueryTable"
> Me.btnQueryTable.Size = New System.Drawing.Size(120, 23)
> Me.btnQueryTable.TabIndex = 4
> Me.btnQueryTable.Text = "List Table Contents"
> '
> 'txtValue1
> '
> Me.txtValue1.Location = New System.Drawing.Point(168, 56)
> Me.txtValue1.Name = "txtValue1"
> Me.txtValue1.Size = New System.Drawing.Size(120, 20)
> Me.txtValue1.TabIndex = 5
> Me.txtValue1.Text = "1.0000000001"
> '
> 'lblValue1
> '
> Me.lblValue1.Location = New System.Drawing.Point(88, 56)
> Me.lblValue1.Name = "lblValue1"
> Me.lblValue1.Size = New System.Drawing.Size(80, 20)
> Me.lblValue1.TabIndex = 6
> Me.lblValue1.Text = "Value1:"
> Me.lblValue1.TextAlign =
> System.Drawing.ContentAlignment.MiddleLeft
> '
> 'lblValue2
> '
> Me.lblValue2.Location = New System.Drawing.Point(324, 56)
> Me.lblValue2.Name = "lblValue2"
> Me.lblValue2.Size = New System.Drawing.Size(80, 20)
> Me.lblValue2.TabIndex = 8
> Me.lblValue2.Text = "Value2:"
> Me.lblValue2.TextAlign =
> System.Drawing.ContentAlignment.MiddleLeft
> '
> 'txtValue2
> '
> Me.txtValue2.Location = New System.Drawing.Point(404, 56)
> Me.txtValue2.Name = "txtValue2"
> Me.txtValue2.ReadOnly = True
> Me.txtValue2.Size = New System.Drawing.Size(120, 20)
> Me.txtValue2.TabIndex = 7
> Me.txtValue2.Text = "-1.0000000001"
> '
> 'btnBadData
> '
> Me.btnBadData.Location = New System.Drawing.Point(192, 92)
> Me.btnBadData.Name = "btnBadData"
> Me.btnBadData.Size = New System.Drawing.Size(96, 23)
> Me.btnBadData.TabIndex = 9
> Me.btnBadData.Text = "Reset Values"
> '
> 'DecimalBug_UI
> '
> Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
> Me.ClientSize = New System.Drawing.Size(624, 121)
> Me.Controls.Add(Me.btnBadData)
> Me.Controls.Add(Me.lblValue2)
> Me.Controls.Add(Me.txtValue2)
> Me.Controls.Add(Me.lblValue1)
> Me.Controls.Add(Me.txtValue1)
> Me.Controls.Add(Me.btnQueryTable)
> Me.Controls.Add(Me.lblConnectionString)
> Me.Controls.Add(Me.btnCreateTable)
> Me.Controls.Add(Me.txtConnectionString)
> Me.Controls.Add(Me.btnRunTest)
> Me.Name = "DecimalBug_UI"
> Me.Text = "Decimal Bug Test"
> Me.ResumeLayout(False)
>
> End Sub
>
> #End Region
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnRunTest.Click
> Dim connection As New
> SqlConnection(Me.txtConnectionString.Text)
> connection.Open()
> Dim cmd As New SqlCommand
> Try
> Dim msg As New StringBuilder
> msg.Append("Dim value1 As Decimal = " & txtValue1.Text &
> vbCrLf)
> Dim value1 As Decimal = Convert.ToDecimal(txtValue1.Text)
> msg.Append("Dim value2 As Decimal = " & txtValue2.Text &
> vbCrLf)
> Dim value2 As Decimal = Convert.ToDecimal(txtValue2.Text)
> msg.Append("Dim finalValue As Decimal = value1 + value2" &
> vbCrLf & vbCrLf)
> Dim finalValue As Decimal = value1 + value2
> msg.Append("finalValue.ToString = " & finalValue.ToString &
> vbCrLf)
> msg.Append("Decimal.GetBits(finalValue) = " &
> GetIntegerArrayString(Decimal.GetBits(finalValue)) & vbCrLf)
> msg.Append("Decimal.GetBits(Decimal.Zero) = " &
> GetIntegerArrayString(Decimal.GetBits(Decimal.Zero)) & vbCrLf & vbCrLf)
>
> cmd.CommandText = "INSERT INTO TestTable (TEST_Value)
> VALUES (@Value)"
> msg.Append("INSERT INTO TestTable (TEST_Value) VALUES
> (@Value); @Value = finalValue" & vbCrLf)
> cmd.Connection = connection
>
> cmd.Parameters.Add("@Value", finalValue)
> cmd.ExecuteNonQuery()
>
> MessageBox.Show(msg.ToString, "Here's what ran...",
> MessageBoxButtons.OK, MessageBoxIcon.Information)
> Catch ex As Exception
> MessageBox.Show("Exception: " & ex.Message)
> Finally
> cmd.Dispose()
> connection.Close()
> End Try
> End Sub
>
> Private Sub btnCreateTable_Click(ByVal sender As System.Object,
> ByVal e As System.EventArgs) Handles btnCreateTable.Click
> If CreateTable() Then
> Me.btnQueryTable.Enabled = True
> Me.btnRunTest.Enabled = True
> MessageBox.Show("The TestTable has been created.")
> End If
> End Sub
>
> Protected Overridable Function CreateTable() As Boolean
> Dim connection As SqlConnection
> Dim cmd As SqlCommand
> Try
> connection = New SqlConnection(Me.txtConnectionString.Text)
> connection.Open()
> cmd = New SqlCommand
> cmd.Connection = connection
>
> cmd.CommandText = "if exists (select * from dbo.sysobjects
> where id = object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1) drop table [dbo].[TestTable]"
> cmd.ExecuteNonQuery()
>
> cmd.CommandText = "CREATE TABLE [dbo].[TestTable]
> ([TEST_RecordID] uniqueidentifier ROWGUIDCOL NOT NULL , [TEST_Value]
> [decimal](28, 10) NOT NULL ) ON [PRIMARY]"
> cmd.ExecuteNonQuery()
>
> cmd.CommandText = "ALTER TABLE [dbo].[TestTable] WITH
> NOCHECK ADD CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
> ([TEST_RecordID]) ON [PRIMARY]"
> cmd.ExecuteNonQuery()
>
> cmd.CommandText = "ALTER TABLE [dbo].[TestTable] ADD
> CONSTRAINT [DF_TestTable_TEST_RecordID] DEFAULT (newid()) FOR
> [TEST_RecordID]"
> cmd.ExecuteNonQuery()
>
> Return True
> Catch ex As Exception
> MessageBox.Show("Exception: " & ex.Message)
> Return False
> Finally
> If Not cmd Is Nothing Then cmd.Dispose()
> If Not connection Is Nothing AndAlso connection.State =
> ConnectionState.Open Then connection.Close()
> End Try
> End Function
>
> Protected Overridable Function GetIntegerArrayString(ByVal array As
> Integer()) As String
> Dim msg As New StringBuilder
> For Each i As Integer In array
> msg.Append(i.ToString & ", ")
> Next
> Return msg.ToString.Substring(0, msg.Length - 2)
> End Function
>
> Private Sub btnQueryTable_Click(ByVal sender As System.Object,
> ByVal e As System.EventArgs) Handles btnQueryTable.Click
> Dim connection As SqlConnection
> Dim cmd As SqlCommand
> Dim result As Integer
> Try
> Dim msg As New StringBuilder
> connection = New SqlConnection(Me.txtConnectionString.Text)
> connection.Open()
> cmd = New SqlCommand
> cmd.Connection = connection
>
> cmd.CommandText = "SELECT COUNT(*) FROM TestTable"
> result = CInt(cmd.ExecuteScalar())
> msg.Append("(" & cmd.CommandText & ") = " & result.ToString
> & vbCrLf)
>
> cmd.CommandText = "SELECT COUNT(*) FROM TestTable WHERE
> TEST_Value < 0 AND TEST_Value >
> -.00000000000000000000000000000000000001"
> result = CInt(cmd.ExecuteScalar())
> msg.Append("(" & cmd.CommandText & ") = " & result.ToString
> & vbCrLf)
>
> cmd.CommandText = "SELECT COUNT(*) FROM TestTable WHERE
> TEST_Value = 0"
> result = CInt(cmd.ExecuteScalar())
> msg.Append("(" & cmd.CommandText & ") = " & result.ToString
> & vbCrLf)
>
> MessageBox.Show(msg.ToString, "TestTable Contents",
> MessageBoxButtons.OK, MessageBoxIcon.Information)
> Catch ex As Exception
> MessageBox.Show("Exception: " & ex.Message)
> Finally
> If Not cmd Is Nothing Then cmd.Dispose()
> If Not connection Is Nothing AndAlso connection.State =
> ConnectionState.Open Then connection.Close()
> End Try
> End Sub
>
> Private Sub txtValue1_Validating(ByVal sender As Object, ByVal e As
> System.ComponentModel.CancelEventArgs) Handles txtValue1.Validating,
> txtValue2.Validating
> Try
> Dim dec As Decimal = Convert.ToDecimal(DirectCast(sender,
> TextBox).Text)
> Catch ex As Exception
> e.Cancel = True
> MessageBox.Show("That is not a valid decimal.")
> End Try
> End Sub
>
> Private Sub txtValue1_Validated(ByVal sender As Object, ByVal e As
> System.EventArgs) Handles txtValue1.Validated
> txtValue2.Text = (Convert.ToDecimal(DirectCast(sender,
> TextBox).Text) * -1).ToString
> End Sub
>
> Private Sub btnBadData_Click(ByVal sender As System.Object, ByVal e
> As System.EventArgs) Handles btnBadData.Click
> txtValue1.Text = mValue1
> txtValue2.Text = mValue2
> End Sub
> End Class
>

Ollie Riches wrote:
> quote
>
> 'However, if you look at the actual values stored in the Decimal structure'
>
> Why would i look inside the data structure for a decimal, I don't care about
> the INTERNAL workings on the class\struct as long as the exposed value is
> correct which as you state is correct. You might want to look up the
> definition of encapsulation - as in encapsulates the detail of how a decimal
> is stored\manipulated away from the end user

Yes, I was tempted to stop at that point, but if you actually read the
whole post you will see that sometimes these private details have an
effect at the public level, which is the bug the OP is highlighting:

> > This wouldn't be such a big deal if that last byte was always ignored
> > when the first three bytes are zero; however, this is not always the
> > case. Specifically, that last byte is not ignored when the value is a
> > parameter of a SqlCommand. In that case, SQL server appears to try to
> > interpret that last byte and make sense of it. Sometimes nothing bad
> > happens (such as when you subtract 1D from 1D). However, when you set
> > up values with a precision out to 10 places going into a Decimal (28,
> > 10) column, SQL server winds up inserting a weird undefined value.
> > It's somewhere between 0 and -.00000000000000000000000000000000000001
> > (the negative value that is the closest to zero SQL server allows). In
> > Query Analyzer, it shows up as -.0000000000.

Reading comprehension is fun!

--
Larry Lard
Replies to group please

> Yes, I was tempted to stop at that point, but if you actually read the
> whole post you will see that sometimes these private details have an
> effect at the public level, which is the bug the OP is highlighting:

Larry's correct--I could care less about what happens internally with
System.Decimal if it worked properly in every case. It does not, and
it has been wreaking havoc on my SQL server database due to the above
effects. The only reason I ever looked at the internals of
System.Decimal was to figure out what could be causing the bug, and
sure enough, I find the source.

I know the definition of encapsulation, but when bugs in the internal
logic of a type start to be manifested externally, sometimes looking at
the internals can reveal the problem (as it did in this case).

Anyone know if this bug has been previously reported, or if Microsoft
has done anything about it in VS2005/.NET 2.0?