Datagrid culture and money formatting issue

  • Thread starter Thread starter Earl
  • Start date Start date
E

Earl

Still trying to clean up some datagrid formatting issues from the past.

When I bring in money values from a stored procedure, I'm getting 4 decimal
places in the grid ( which of course I only want 2).

Soooo ... I tried to create the cultureinfo object, set the format decimal
digits, then assign that to formatinfo on the column. Anyone see where I
went astray?

Dim USCultureInfo As CultureInfo = New CultureInfo("en-us")
USCultureInfo.NumberFormat.CurrencyDecimalDigits = 2

'note the use of Ken Tucker's alignment class here
Dim tbcSaleAmount As New HeaderAndDataAlignColumn
tbcSaleAmount.MappingName = "TotalSaleAmount"
tbcSaleAmount.Alignment = HorizontalAlignment.Center
tbcSaleAmount.DataAlignment = HorizontalAlignment.Right
tbcSaleAmount.HeaderText = "Sale Amount"
tbcSaleAmount.Width = 70
tbcSaleAmount.NullText = ""
tbcSaleAmount.FormatInfo = USCultureInfo
tbcSaleAmount.Format = "c"
 
Hi,

In my orginal class I never thought about the format parameter.
Here is some code that will work for you. When I finish updating the sample
to work with the format and formatinfo I will update the sample on my
website.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strConn As String
Dim strSQL As String
Dim daEmployees As OleDbDataAdapter
Dim conn As OleDbConnection
Dim ds As New DataSet

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
strConn &= "Data Source =C:\Northwind.mdb;"

conn = New OleDbConnection(strConn)

daEmployees = New OleDbDataAdapter("Select * From Products", conn)
daEmployees.Fill(ds, "Products")

DataGrid1.DataSource = ds.Tables("Products")

Dim ts As New DataGridTableStyle
ts.MappingName = "Products"

ts.SelectionBackColor = Color.Yellow
ts.SelectionForeColor = Color.Red
ts.PreferredRowHeight = 25

Dim colName As New DataGridTextBoxColumn
With colName
.MappingName = "ProductName"
.HeaderText = "Name"
.Width = 300
.ReadOnly = True
End With

Dim colPrice As New HeaderAndDataAlignColumn
With colPrice
.MappingName = "UnitPrice"
.HeaderText = "Price"
.Width = 75
.Format = "c"
.Alignment = HorizontalAlignment.Center
.DataAlignment = HorizontalAlignment.Right
End With

ts.GridColumnStyles.Add(colName)
ts.GridColumnStyles.Add(colPrice)

DataGrid1.TableStyles.Add(ts)
ts = Nothing
colPrice = Nothing
colName = Nothing

DataGrid1.DataSource = ds.Tables("Products")
ds.Tables("Products").DefaultView.AllowNew = False
End Sub


The changed class

Public Class HeaderAndDataAlignColumn
Inherits DataGridTextBoxColumn

Private mTxtAlign As HorizontalAlignment = HorizontalAlignment.Left
Private mDrawTxt As New StringFormat

Protected Overloads Overrides Sub Edit(ByVal source As
System.Windows.Forms.CurrencyManager, ByVal rowNum As Integer, ByVal bounds
As System.Drawing.Rectangle, ByVal [readOnly] As Boolean, ByVal instantText
As String, ByVal cellIsVisible As Boolean)
MyBase.Edit(source, rowNum, bounds, [readOnly], instantText,
cellIsVisible)
MyBase.TextBox.TextAlign = mTxtAlign
MyBase.TextBox.CharacterCasing = CharacterCasing.Upper
End Sub

Protected Overloads Overrides Sub Paint(ByVal g As
System.Drawing.Graphics, ByVal bounds As System.Drawing.Rectangle, ByVal
source As System.Windows.Forms.CurrencyManager, ByVal rowNum As Integer,
ByVal backBrush As System.Drawing.Brush, ByVal foreBrush As
System.Drawing.Brush, ByVal alignToRight As Boolean)
'clear the cell
g.FillRectangle(backBrush, bounds)

'draw the value
Dim s As String
If TypeOf Me.GetColumnValueAtRow([source], rowNum) Is Decimal Then
s = CDec(Me.GetColumnValueAtRow([source],
rowNum)).ToString(Me.Format)
Else
s = Me.GetColumnValueAtRow([source], rowNum).ToString
End If
Dim r As Rectangle = bounds
r.Inflate(0, -1)
g.DrawString(s, MyBase.TextBox.Font, foreBrush,
RectangleF.op_Implicit(r), _
mDrawTxt)
End Sub

Public Property DataAlignment() As HorizontalAlignment
Get
Return mTxtAlign
End Get
Set(ByVal Value As HorizontalAlignment)
mTxtAlign = Value
If mTxtAlign = HorizontalAlignment.Center Then
mDrawTxt.Alignment = StringAlignment.Center
ElseIf mTxtAlign = HorizontalAlignment.Right Then
mDrawTxt.Alignment = StringAlignment.Far
Else
mDrawTxt.Alignment = StringAlignment.Near
End If
End Set
End Property

End Class


Ken
------------------------
Still trying to clean up some datagrid formatting issues from the past.

When I bring in money values from a stored procedure, I'm getting 4 decimal
places in the grid ( which of course I only want 2).

Soooo ... I tried to create the cultureinfo object, set the format decimal
digits, then assign that to formatinfo on the column. Anyone see where I
went astray?

Dim USCultureInfo As CultureInfo = New CultureInfo("en-us")
USCultureInfo.NumberFormat.CurrencyDecimalDigits = 2

'note the use of Ken Tucker's alignment class here
Dim tbcSaleAmount As New HeaderAndDataAlignColumn
tbcSaleAmount.MappingName = "TotalSaleAmount"
tbcSaleAmount.Alignment = HorizontalAlignment.Center
tbcSaleAmount.DataAlignment = HorizontalAlignment.Right
tbcSaleAmount.HeaderText = "Sale Amount"
tbcSaleAmount.Width = 70
tbcSaleAmount.NullText = ""
tbcSaleAmount.FormatInfo = USCultureInfo
tbcSaleAmount.Format = "c"
 
Thanks Ken. I did some more experimenting with that after I posted and
realized that the format parameter would not work with your class. The
tradeoff I took (in this particular form) was to simply change the sproc to
convert all my values to varchar (with a $ sign of course!). But I have
other forms that I wish to handle without changing the sprocs, so your
changes will come in handy. Thanks again!

Ken Tucker said:
Hi,

In my orginal class I never thought about the format parameter.
Here is some code that will work for you. When I finish updating the
sample
to work with the format and formatinfo I will update the sample on my
website.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strConn As String
Dim strSQL As String
Dim daEmployees As OleDbDataAdapter
Dim conn As OleDbConnection
Dim ds As New DataSet

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
strConn &= "Data Source =C:\Northwind.mdb;"

conn = New OleDbConnection(strConn)

daEmployees = New OleDbDataAdapter("Select * From Products", conn)
daEmployees.Fill(ds, "Products")

DataGrid1.DataSource = ds.Tables("Products")

Dim ts As New DataGridTableStyle
ts.MappingName = "Products"

ts.SelectionBackColor = Color.Yellow
ts.SelectionForeColor = Color.Red
ts.PreferredRowHeight = 25

Dim colName As New DataGridTextBoxColumn
With colName
.MappingName = "ProductName"
.HeaderText = "Name"
.Width = 300
.ReadOnly = True
End With

Dim colPrice As New HeaderAndDataAlignColumn
With colPrice
.MappingName = "UnitPrice"
.HeaderText = "Price"
.Width = 75
.Format = "c"
.Alignment = HorizontalAlignment.Center
.DataAlignment = HorizontalAlignment.Right
End With

ts.GridColumnStyles.Add(colName)
ts.GridColumnStyles.Add(colPrice)

DataGrid1.TableStyles.Add(ts)
ts = Nothing
colPrice = Nothing
colName = Nothing

DataGrid1.DataSource = ds.Tables("Products")
ds.Tables("Products").DefaultView.AllowNew = False
End Sub


The changed class

Public Class HeaderAndDataAlignColumn
Inherits DataGridTextBoxColumn

Private mTxtAlign As HorizontalAlignment = HorizontalAlignment.Left
Private mDrawTxt As New StringFormat

Protected Overloads Overrides Sub Edit(ByVal source As
System.Windows.Forms.CurrencyManager, ByVal rowNum As Integer, ByVal
bounds
As System.Drawing.Rectangle, ByVal [readOnly] As Boolean, ByVal
instantText
As String, ByVal cellIsVisible As Boolean)
MyBase.Edit(source, rowNum, bounds, [readOnly], instantText,
cellIsVisible)
MyBase.TextBox.TextAlign = mTxtAlign
MyBase.TextBox.CharacterCasing = CharacterCasing.Upper
End Sub

Protected Overloads Overrides Sub Paint(ByVal g As
System.Drawing.Graphics, ByVal bounds As System.Drawing.Rectangle, ByVal
source As System.Windows.Forms.CurrencyManager, ByVal rowNum As Integer,
ByVal backBrush As System.Drawing.Brush, ByVal foreBrush As
System.Drawing.Brush, ByVal alignToRight As Boolean)
'clear the cell
g.FillRectangle(backBrush, bounds)

'draw the value
Dim s As String
If TypeOf Me.GetColumnValueAtRow([source], rowNum) Is Decimal Then
s = CDec(Me.GetColumnValueAtRow([source],
rowNum)).ToString(Me.Format)
Else
s = Me.GetColumnValueAtRow([source], rowNum).ToString
End If
Dim r As Rectangle = bounds
r.Inflate(0, -1)
g.DrawString(s, MyBase.TextBox.Font, foreBrush,
RectangleF.op_Implicit(r), _
mDrawTxt)
End Sub

Public Property DataAlignment() As HorizontalAlignment
Get
Return mTxtAlign
End Get
Set(ByVal Value As HorizontalAlignment)
mTxtAlign = Value
If mTxtAlign = HorizontalAlignment.Center Then
mDrawTxt.Alignment = StringAlignment.Center
ElseIf mTxtAlign = HorizontalAlignment.Right Then
mDrawTxt.Alignment = StringAlignment.Far
Else
mDrawTxt.Alignment = StringAlignment.Near
End If
End Set
End Property

End Class


Ken
------------------------
Still trying to clean up some datagrid formatting issues from the past.

When I bring in money values from a stored procedure, I'm getting 4
decimal
places in the grid ( which of course I only want 2).

Soooo ... I tried to create the cultureinfo object, set the format decimal
digits, then assign that to formatinfo on the column. Anyone see where I
went astray?

Dim USCultureInfo As CultureInfo = New CultureInfo("en-us")
USCultureInfo.NumberFormat.CurrencyDecimalDigits = 2

'note the use of Ken Tucker's alignment class here
Dim tbcSaleAmount As New HeaderAndDataAlignColumn
tbcSaleAmount.MappingName = "TotalSaleAmount"
tbcSaleAmount.Alignment = HorizontalAlignment.Center
tbcSaleAmount.DataAlignment = HorizontalAlignment.Right
tbcSaleAmount.HeaderText = "Sale Amount"
tbcSaleAmount.Width = 70
tbcSaleAmount.NullText = ""
tbcSaleAmount.FormatInfo = USCultureInfo
tbcSaleAmount.Format = "c"
Click to expand...
 
Back
Top