Formatting certain columns in the datagrid to date and currency

[panadoll]

Hi all,

I desperately need help here.

Currently, I have a dynamic datagrid which actually is used for search function. So the columns and the the format is actually dynamic depending on which search the user clicked on...

However, I am trying to format certain columns in my grid to date and currency format. But after trying for several hours and surfing thru the websites, i am still unable to format the columns..

The vb.net application is actually linked to mssql2000. the date are stored as decimal in mssql as yyyymmdd.

Please do help me out... I really have a deadline to meet and until now, i am still stuck here. Thanks alot in advance.

The following are my codes.
=======================================================
Private Sub LoadGrid()
Dim mGridTableStyle As New DataGridTableStyle
Dim col_counter As Integer
Dim CleanMapName As String

With mGridTableStyle
.MappingName = TableField
.PreferredRowHeight = 18
.ReadOnly = True
With .GridColumnStyles
For col_counter = 0 To SelectFields.Length - 1
.Add(New DataGridTextBoxColumn)
With .Item(col_counter)
CleanMapName = Replace(SelectFields(col_counter), "[", "", , , CompareMethod.Binary)
CleanMapName = Replace(CleanMapName, "]", "", , , CompareMethod.Binary)

.MappingName = CleanMapName
.HeaderText = SelectCaptionFields(col_counter)
If Alignment(col_counter) = "Left" Then
.Alignment = HorizontalAlignment.Left
ElseIf Alignment(col_counter) = "Center" Then
.Alignment = HorizontalAlignment.Center
ElseIf Alignment(col_counter) = "Right" Then
.Alignment = HorizontalAlignment.Right
End If
.Width = SelectWidth(col_counter)
.NullText = String.Empty
.ReadOnly = True

If FormatFields(col_counter) = "Currency" Then
Dim tbc As DataGridTextBoxColumn
tbc = CType(mGridTableStyle.GridColumnStyles(CleanMapName), DataGridTextBoxColumn)
Dim CulInfo As New CultureInfo("en-US", False)
tbc.FormatInfo = CulInfo
tbc.Format = "c"
ElseIf FormatFields(col_counter) = "Date" Then
Dim tbc As DataGridTextBoxColumn
tbc = CType(mGridTableStyle.GridColumnStyles(CleanMapName), DataGridTextBoxColumn)
Dim CulInfo As New CultureInfo("en-US", False)
tbc.FormatInfo = CulInfo
tbc.Format = "d"
End If
End With
Next col_counter
End With
End With

DGSearch.TableStyles.Clear()
DGSearch.TableStyles.Add(mGridTableStyle)
DGSearch.Refresh()
End Sub

 

[SqueakinSweep]

For date fields try tbc.format = "MM/dd/yy"
for currency fields tbc.format = "$##,##0.00"


Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.

 

[panadoll]

thanks for your reply...

I tried that out already.. but it still does not take that into effect.

Any other ways?

 

[SqueakinSweep]

It should work, and I can only think the reason it doesnt is somehow down to the way you are adding your DatagridTextBoxcolumns.

Better practice is to create your new column, set all the properties you need and then finally add it to your TableStyle, rather than adding the GridColumn, and then adjusting its properties.

Sample Code...as to how I build up a datagrid, formatting works fine.

Dim dgcol As DataGridTextBoxColumn

Dim iloop As Integer = 0
For Each s As String In Me.sColumns.Split(","c)

   'Define and apply the column settings
    dgcol = New DataGridTextBoxColumn
    dgcol.ReadOnly = True
    dgcol.MappingName = s
 
   'Column Header
    If iloop < Me.sHeaders.Length And CInt(Me.sWidths(iloop)) > 0 Then
         dgcol.HeaderText = CStr(Me.sHeaders(iloop))
    End If

    'Column Width
    If iloop < Me.sWidths.Length Then
         dgcol.Width = CInt(Me.sWidths(iloop))
    End If

   'Column AlignMent
    If iloop < Me.sAlignments.Length Then
         Dim sAlign As String = CStr(Me.sAlignments(iloop))
         Select Case sAlign
         Case "L"
            dgcol.Alignment = HorizontalAlignment.Left
         Case "C"
            dgcol.Alignment = HorizontalAlignment.Center
         Case "R"
            dgcol.Alignment = HorizontalAlignment.Right
         End Select
    End If

    'Any Custom Formatting
    Dim sFormat As String = Me.zGetFormat(s.ToLower)
    If sFormat <> String.Empty Then dgcol.Format = sFormat

    iTotalWidth += dgcol.Width + 1
    iloop += 1
 
   'Adjust last Grid Column width (to fill the Grid)
    If iloop = iTotalCols Then
       Dim iFiller As Integer = iGridWidth - iTotalWidth
       If iFiller > dgcol.Width Then dgcol.Width = iFiller
    End If

    'Add to TableStyle
    ts.GridColumnStyles.Add(dgcol)

Next

'Finally add the Tablestyle to the Grid itself!
With Me
     .TableStyles.Add(ts)
     .TableStyles(Me.sMapping).ReadOnly = True
     .TableStyles(Me.sMapping).AllowSorting = True
End With

Me.Select(0)

Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.

 

[panadoll]

Hi sweep,

thanks for replying.. I amended my codes to the way you suggested. However, it just does not take in the formating. The alignment, headertext all works fine. It is just the formatting of the date and the currency that is goin haywire....

Can you please advice me what is wrong with the amended codes? I really appreciate your help.

Dim mGridTableStyle As New DataGridTableStyle
Dim dgcol As DataGridTextBoxColumn
Dim col_counter As Integer
Dim CleanMapName As String

mGridTableStyle.MappingName = TableField
mGridTableStyle.PreferredRowHeight = 18
mGridTableStyle.ReadOnly = True

For col_counter = 0 To SelectFields.Length - 1
CleanMapName = Replace(SelectFields(col_counter), "[", "", , , CompareMethod.Binary)
CleanMapName = Replace(CleanMapName, "]", "", , , CompareMethod.Binary)

dgcol = New DataGridTextBoxColumn
dgcol.ReadOnly = True
dgcol.MappingName = CleanMapName

dgcol.HeaderText = SelectCaptionFields(col_counter)

dgcol.Width = SelectWidth(col_counter)

If Alignment(col_counter) = "Left" Then
dgcol.Alignment = HorizontalAlignment.Left
ElseIf Alignment(col_counter) = "Center" Then
dgcol.Alignment = HorizontalAlignment.Center
ElseIf Alignment(col_counter) = "Right" Then
dgcol.Alignment = HorizontalAlignment.Right
End If

If FormatFields(col_counter) = "Currency" Then
dgcol.Format = FormatCurrency
ElseIf FormatFields(col_counter) = "Date" Then
dgcol.Format = FormatDate
End If

mGridTableStyle.GridColumnStyles.Add(dgcol)
Next col_counter

DGSearch.TableStyles.Clear()
DGSearch.TableStyles.Add(mGridTableStyle)

 

[SqueakinSweep]

And you've stepped through the code to ensure the format lines of code are executing??. Also what are the values of FormatDate amd FormatCurrency

Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.

 

[panadoll]

Yap.. I stepped thru the codes and it is executing in the correct order.

The value of the FormatDate is "dd/mm/yyyy", FormatCurrency is #,###,###,###0.00

 

[SqueakinSweep]

Formatdate should be dd/MM/yy ...Note the capital M. This is because likkle m is used for minutes.

Im also assuming that the date field you are trying to apply the format to is a date field and not a string field from your Datatable?



Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.