M
Myron Marston
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
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