Datagrid culture and money formatting issue

[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.N umberFormat.Cur rencyDecimalDig its = 2

'note the use of Ken Tucker's alignment class here
Dim tbcSaleAmount As New HeaderAndDataAl ignColumn
tbcSaleAmount.M appingName = "TotalSaleAmoun t"
tbcSaleAmount.A lignment = HorizontalAlign ment.Center
tbcSaleAmount.D ataAlignment = HorizontalAlign ment.Right
tbcSaleAmount.H eaderText = "Sale Amount"
tbcSaleAmount.W idth = 70
tbcSaleAmount.N ullText = ""
tbcSaleAmount.F ormatInfo = USCultureInfo
tbcSaleAmount.F ormat = "c"

[Ken Tucker [MVP]]

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(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
Dim strConn As String
Dim strSQL As String
Dim daEmployees As OleDbDataAdapte r
Dim conn As OleDbConnection
Dim ds As New DataSet

strConn = "Provider = Microsoft.Jet.O LEDB.4.0;"
strConn &= "Data Source =C:\Northwind.m db;"

conn = New OleDbConnection (strConn)

daEmployees = New OleDbDataAdapte r("Select * From Products", conn)
daEmployees.Fil l(ds, "Products")

DataGrid1.DataS ource = ds.Tables("Prod ucts")

Dim ts As New DataGridTableSt yle
ts.MappingName = "Products"

ts.SelectionBac kColor = Color.Yellow
ts.SelectionFor eColor = Color.Red
ts.PreferredRow Height = 25

Dim colName As New DataGridTextBox Column
With colName
.MappingName = "ProductNam e"
.HeaderText = "Name"
.Width = 300
.ReadOnly = True
End With

Dim colPrice As New HeaderAndDataAl ignColumn
With colPrice
.MappingName = "UnitPrice"
.HeaderText = "Price"
.Width = 75
.Format = "c"
.Alignment = HorizontalAlign ment.Center
.DataAlignment = HorizontalAlign ment.Right
End With

ts.GridColumnSt yles.Add(colNam e)
ts.GridColumnSt yles.Add(colPri ce)

DataGrid1.Table Styles.Add(ts)
ts = Nothing
colPrice = Nothing
colName = Nothing

DataGrid1.DataS ource = ds.Tables("Prod ucts")
ds.Tables("Prod ucts").DefaultV iew.AllowNew = False
End Sub
The changed class

Public Class HeaderAndDataAl ignColumn
Inherits DataGridTextBox Column

Private mTxtAlign As HorizontalAlign ment = HorizontalAlign ment.Left
Private mDrawTxt As New StringFormat

Protected Overloads Overrides Sub Edit(ByVal source As
System.Windows. Forms.CurrencyM anager, ByVal rowNum As Integer, ByVal bounds
As System.Drawing. Rectangle, ByVal [readOnly] As Boolean, ByVal instantText
As String, ByVal cellIsVisible As Boolean)
MyBase.Edit(sou rce, 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.CurrencyM anager, 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.GetColumnVal ueAtRow([source], rowNum) Is Decimal Then
s = CDec(Me.GetColu mnValueAtRow([source],
rowNum)).ToStri ng(Me.Format)
Else
s = Me.GetColumnVal ueAtRow([source], rowNum).ToStrin g
End If
Dim r As Rectangle = bounds
r.Inflate(0, -1)
g.DrawString(s, MyBase.TextBox. Font, foreBrush,
RectangleF.op_I mplicit(r), _
mDrawTxt)
End Sub

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

End Class
Ken
------------------------
"Earl" <br******@newsg roups.nospam> wrote in message
news:Oq******** ******@TK2MSFTN GP15.phx.gbl...
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.N umberFormat.Cur rencyDecimalDig its = 2

'note the use of Ken Tucker's alignment class here
Dim tbcSaleAmount As New HeaderAndDataAl ignColumn
tbcSaleAmount.M appingName = "TotalSaleAmoun t"
tbcSaleAmount.A lignment = HorizontalAlign ment.Center
tbcSaleAmount.D ataAlignment = HorizontalAlign ment.Right
tbcSaleAmount.H eaderText = "Sale Amount"
tbcSaleAmount.W idth = 70
tbcSaleAmount.N ullText = ""
tbcSaleAmount.F ormatInfo = USCultureInfo
tbcSaleAmount.F ormat = "c"

[Earl]

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 [MVP]" <vb***@bellsout h.net> wrote in message
news:uX******** *****@TK2MSFTNG P10.phx.gbl...

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(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
Dim strConn As String
Dim strSQL As String
Dim daEmployees As OleDbDataAdapte r
Dim conn As OleDbConnection
Dim ds As New DataSet

strConn = "Provider = Microsoft.Jet.O LEDB.4.0;"
strConn &= "Data Source =C:\Northwind.m db;"

conn = New OleDbConnection (strConn)

daEmployees = New OleDbDataAdapte r("Select * From Products", conn)
daEmployees.Fil l(ds, "Products")

DataGrid1.DataS ource = ds.Tables("Prod ucts")

Dim ts As New DataGridTableSt yle
ts.MappingName = "Products"

ts.SelectionBac kColor = Color.Yellow
ts.SelectionFor eColor = Color.Red
ts.PreferredRow Height = 25

Dim colName As New DataGridTextBox Column
With colName
.MappingName = "ProductNam e"
.HeaderText = "Name"
.Width = 300
.ReadOnly = True
End With

Dim colPrice As New HeaderAndDataAl ignColumn
With colPrice
.MappingName = "UnitPrice"
.HeaderText = "Price"
.Width = 75
.Format = "c"
.Alignment = HorizontalAlign ment.Center
.DataAlignment = HorizontalAlign ment.Right
End With

ts.GridColumnSt yles.Add(colNam e)
ts.GridColumnSt yles.Add(colPri ce)

DataGrid1.Table Styles.Add(ts)
ts = Nothing
colPrice = Nothing
colName = Nothing

DataGrid1.DataS ource = ds.Tables("Prod ucts")
ds.Tables("Prod ucts").DefaultV iew.AllowNew = False
End Sub
The changed class

Public Class HeaderAndDataAl ignColumn
Inherits DataGridTextBox Column

Private mTxtAlign As HorizontalAlign ment = HorizontalAlign ment.Left
Private mDrawTxt As New StringFormat

Protected Overloads Overrides Sub Edit(ByVal source As
System.Windows. Forms.CurrencyM anager, ByVal rowNum As Integer, ByVal
bounds
As System.Drawing. Rectangle, ByVal [readOnly] As Boolean, ByVal
instantText
As String, ByVal cellIsVisible As Boolean)
MyBase.Edit(sou rce, 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.CurrencyM anager, 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.GetColumnVal ueAtRow([source], rowNum) Is Decimal Then
s = CDec(Me.GetColu mnValueAtRow([source],
rowNum)).ToStri ng(Me.Format)
Else
s = Me.GetColumnVal ueAtRow([source], rowNum).ToStrin g
End If
Dim r As Rectangle = bounds
r.Inflate(0, -1)
g.DrawString(s, MyBase.TextBox. Font, foreBrush,
RectangleF.op_I mplicit(r), _
mDrawTxt)
End Sub

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

End Class
Ken
------------------------
"Earl" <br******@newsg roups.nospam> wrote in message
news:Oq******** ******@TK2MSFTN GP15.phx.gbl...
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.N umberFormat.Cur rencyDecimalDig its = 2

'note the use of Ken Tucker's alignment class here
Dim tbcSaleAmount As New HeaderAndDataAl ignColumn
tbcSaleAmount.M appingName = "TotalSaleAmoun t"
tbcSaleAmount.A lignment = HorizontalAlign ment.Center
tbcSaleAmount.D ataAlignment = HorizontalAlign ment.Right
tbcSaleAmount.H eaderText = "Sale Amount"
tbcSaleAmount.W idth = 70
tbcSaleAmount.N ullText = ""
tbcSaleAmount.F ormatInfo = USCultureInfo
tbcSaleAmount.F ormat = "c"