Format Data Column

[Guest]

I've looked around and have yet to find anything that would answer my
question regarding formating a column in a datagrid. My grid looks like
this as far as data"

AMHQCON|51300.01|-3147

The first two columns are pretty much text column, but subsequent columns
1-12 are numerical. I'm trying to get the thousand separator to work. Any
ideas?

 

[Martin Dechev]

Hi,

It is not clear what you need to show, but the following format string:

#,#.00

will produce the following:

51300.01 -> 51,300.01
123123123 -> 123,123,123.00
-3147 -> -3,147.00

If you don't need to show zeros after the decimal point and you know that
there are at most 5 digits after the decimal point use the following:

#,#.#####

Read more about custom numeric format strings:

http://msdn.microsoft.com/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp

Hope this helps
Martin

 

[Guest]

I've read the article referenced above along with others, but can't get the
correct coding to format the column in the datagrid. Here's my latest
attempt in trying to format column 3 of the datagrid.

DataGrid1.Columns(3).ToString.Format("#,##0,")

 

[Martin Dechev]

Hi,

If the grid is with AutoGenerateColumns=true then you are in trouble - you
will have to format the DataSource. e.g: If it is a DataTable you will need
to create a new DataTable with the same number of columns all of type
string. Then loop through the rows and columns of the original one and store
the result of ToString(format) on each value in the corresponding cell of
the new DataTable. Then bind the grid with the new datatable.

Otherwise:
For BoundColumn's use the property DataFormatString
For HyperlinkColumn's and ButtonColumn's use the property
DataTextFormatString
For TemplateColumn's - inside the ItemTemplate use the overload of
DataBinder.Eval that takes the format as parameter

e.g:
[C#]
(grid.Columns[0] as BoundColumn).DataFormatString = "{0:#,#.00}";
[VB]
DirectCast(grid.Columns(0), BoundColumn).DataFormatString = "{0:#,#.00}"

Just make sure you set the formats before the call to DataBind() on the
grid.

Greetings
Martin

 

[Guest]

I'm sort of new to the coding. I do have the AutoGenerate set to TRUE. The
datagrid is assigned datasource at runtime. I inserted the DirectCast line
below and got an error stating index is out of range. Here's my code that
populates the grid.


Private Sub cmdOK_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdOK.Click
Dim DS As New System.Data.DataSet
Dim DT As New System.Data.DataTable
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Inetpub\wwwroot\intelis.MDB")

TextBox1.Text = DropDownList1.SelectedItem.ToString
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from " & ListBox1.SelectedValue.ToString & " where
Entity = '" & DropDownList1.SelectedValue.ToString & "'", MyConnection)

DS = New System.Data.DataSet
DS.Clear()

MyCommand.Fill(DS)

DT = DS.Tables(0)
' DataGrid1.DataMember =
DataGrid1.DataSource = DT

DirectCast(DataGrid1.Columns(3), BoundColumn).DataFormatString
"{0:#,#.00}"

DataGrid1.DataBind()


End Sub

Hi,

If the grid is with AutoGenerateColumns=true then you are in trouble - you
will have to format the DataSource. e.g: If it is a DataTable you will need
to create a new DataTable with the same number of columns all of type
string. Then loop through the rows and columns of the original one and store
the result of ToString(format) on each value in the corresponding cell of
the new DataTable. Then bind the grid with the new datatable.

Otherwise:
For BoundColumn's use the property DataFormatString
For HyperlinkColumn's and ButtonColumn's use the property
DataTextFormatString
For TemplateColumn's - inside the ItemTemplate use the overload of
DataBinder.Eval that takes the format as parameter

e.g:
[C#]
(grid.Columns[0] as BoundColumn).DataFormatString = "{0:#,#.00}";
[VB]
DirectCast(grid.Columns(0), BoundColumn).DataFormatString = "{0:#,#.00}"

Just make sure you set the formats before the call to DataBind() on the
grid.

Greetings
Martin

I've read the article referenced above along with others, but can't get the
correct coding to format the column in the datagrid. Here's my latest
attempt in trying to format column 3 of the datagrid.

DataGrid1.Columns(3).ToString.Format("#,##0,")

 

[Guest]

Hutty - Simply format the data as you're populating the DataTable, and then
bind it to your DataGrid. The DataGrid column will then display the
formatted data instead of attempting to format the DataGrid column itself.

I'm sort of new to the coding. I do have the AutoGenerate set to TRUE. The
datagrid is assigned datasource at runtime. I inserted the DirectCast line
below and got an error stating index is out of range. Here's my code that
populates the grid.


Private Sub cmdOK_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdOK.Click
Dim DS As New System.Data.DataSet
Dim DT As New System.Data.DataTable
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Inetpub\wwwroot\intelis.MDB")

TextBox1.Text = DropDownList1.SelectedItem.ToString
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from " & ListBox1.SelectedValue.ToString & " where
Entity = '" & DropDownList1.SelectedValue.ToString & "'", MyConnection)

DS = New System.Data.DataSet
DS.Clear()

MyCommand.Fill(DS)

DT = DS.Tables(0)
' DataGrid1.DataMember =
DataGrid1.DataSource = DT

DirectCast(DataGrid1.Columns(3), BoundColumn).DataFormatString
"{0:#,#.00}"

DataGrid1.DataBind()


End Sub

Hi,

If the grid is with AutoGenerateColumns=true then you are in trouble - you
will have to format the DataSource. e.g: If it is a DataTable you will need
to create a new DataTable with the same number of columns all of type
string. Then loop through the rows and columns of the original one and store
the result of ToString(format) on each value in the corresponding cell of
the new DataTable. Then bind the grid with the new datatable.

Otherwise:
For BoundColumn's use the property DataFormatString
For HyperlinkColumn's and ButtonColumn's use the property
DataTextFormatString
For TemplateColumn's - inside the ItemTemplate use the overload of
DataBinder.Eval that takes the format as parameter

e.g:
[C#]
(grid.Columns[0] as BoundColumn).DataFormatString = "{0:#,#.00}";
[VB]
DirectCast(grid.Columns(0), BoundColumn).DataFormatString = "{0:#,#.00}"

Just make sure you set the formats before the call to DataBind() on the
grid.

Greetings
Martin

I've read the article referenced above along with others, but can't get the
correct coding to format the column in the datagrid. Here's my latest
attempt in trying to format column 3 of the datagrid.

DataGrid1.Columns(3).ToString.Format("#,##0,")

:

Hi,

It is not clear what you need to show, but the following format string:

#,#.00

will produce the following:

51300.01 -> 51,300.01
123123123 -> 123,123,123.00
-3147 -> -3,147.00

If you don't need to show zeros after the decimal point and you know that
there are at most 5 digits after the decimal point use the following:

#,#.#####

Read more about custom numeric format strings:

http://msdn.microsoft.com/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp

Hope this helps
Martin
I've looked around and have yet to find anything that would answer my
question regarding formating a column in a datagrid. My grid looks like
this as far as data"

AMHQCON|51300.01|-3147

The first two columns are pretty much text column, but subsequent columns
1-12 are numerical. I'm trying to get the thousand separator to work.
Any
ideas?

 

[Guest]

I hear what you're saying, but can't seemed to get the code correct.
However, I'm able to get it to work using the following code. But don't
think it's efficient for insert 4-5 lines for each of the 14 columns. There
has to be a more efficient way of doing it.

DataGrid1.AutoGenerateColumns = False
DataGrid1.DataSource = Me.DataSet31
DataGrid1.DataMember = "Jan 04"
DataGrid1.DataKeyField = "Jan 04"

Dim dgc_col As New BoundColumn
dgc_col.DataField = "Jan 04"
dgc_col.HeaderText = "Jan 04"
dgc_col.DataFormatString = "{0:N0}"
DataGrid1.Columns.Add(dgc_col)

Hutty - Simply format the data as you're populating the DataTable, and then
bind it to your DataGrid. The DataGrid column will then display the
formatted data instead of attempting to format the DataGrid column itself.

I'm sort of new to the coding. I do have the AutoGenerate set to TRUE. The
datagrid is assigned datasource at runtime. I inserted the DirectCast line
below and got an error stating index is out of range. Here's my code that
populates the grid.


Private Sub cmdOK_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdOK.Click
Dim DS As New System.Data.DataSet
Dim DT As New System.Data.DataTable
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Inetpub\wwwroot\intelis.MDB")

TextBox1.Text = DropDownList1.SelectedItem.ToString
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from " & ListBox1.SelectedValue.ToString & " where
Entity = '" & DropDownList1.SelectedValue.ToString & "'", MyConnection)

DS = New System.Data.DataSet
DS.Clear()

MyCommand.Fill(DS)

DT = DS.Tables(0)
' DataGrid1.DataMember =
DataGrid1.DataSource = DT

DirectCast(DataGrid1.Columns(3), BoundColumn).DataFormatString
"{0:#,#.00}"

DataGrid1.DataBind()


End Sub

Hi,

If the grid is with AutoGenerateColumns=true then you are in trouble - you
will have to format the DataSource. e.g: If it is a DataTable you will need
to create a new DataTable with the same number of columns all of type
string. Then loop through the rows and columns of the original one and store
the result of ToString(format) on each value in the corresponding cell of
the new DataTable. Then bind the grid with the new datatable.

Otherwise:
For BoundColumn's use the property DataFormatString
For HyperlinkColumn's and ButtonColumn's use the property
DataTextFormatString
For TemplateColumn's - inside the ItemTemplate use the overload of
DataBinder.Eval that takes the format as parameter

e.g:
[C#]
(grid.Columns[0] as BoundColumn).DataFormatString = "{0:#,#.00}";
[VB]
DirectCast(grid.Columns(0), BoundColumn).DataFormatString = "{0:#,#.00}"

Just make sure you set the formats before the call to DataBind() on the
grid.

Greetings
Martin
I've read the article referenced above along with others, but can't get
the
correct coding to format the column in the datagrid. Here's my latest
attempt in trying to format column 3 of the datagrid.

DataGrid1.Columns(3).ToString.Format("#,##0,")

:

Hi,

It is not clear what you need to show, but the following format string:

#,#.00

will produce the following:

51300.01 -> 51,300.01
123123123 -> 123,123,123.00
-3147 -> -3,147.00

If you don't need to show zeros after the decimal point and you know
that
there are at most 5 digits after the decimal point use the following:

#,#.#####

Read more about custom numeric format strings:


http://msdn.microsoft.com/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp

Hope this helps
Martin
I've looked around and have yet to find anything that would answer my
question regarding formating a column in a datagrid. My grid looks
like
this as far as data"

AMHQCON|51300.01|-3147

The first two columns are pretty much text column, but subsequent
columns
1-12 are numerical. I'm trying to get the thousand separator to work.
Any
ideas?