By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,645 Members | 1,047 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,645 IT Pros & Developers. It's quick & easy.

Format Data Column

P: n/a
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?

--
Hutty
Nov 18 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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...matstrings.asp

Hope this helps
Martin
"Hutty" <Hu***@discussions.microsoft.com> wrote in message
news:A0**********************************@microsof t.com...
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?

--
Hutty

Nov 18 '05 #2

P: n/a
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" wrote:
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...matstrings.asp

Hope this helps
Martin
"Hutty" <Hu***@discussions.microsoft.com> wrote in message
news:A0**********************************@microsof t.com...
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?

--
Hutty


Nov 18 '05 #3

P: n/a
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
"Hutty" <Hu***@discussions.microsoft.com> wrote in message
news:D5**********************************@microsof t.com...
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" wrote:
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...matstrings.asp
Hope this helps
Martin
"Hutty" <Hu***@discussions.microsoft.com> wrote in message
news:A0**********************************@microsof t.com...
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?

--
Hutty


Nov 18 '05 #4

P: n/a
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
"Martin Dechev" wrote:
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
"Hutty" <Hu***@discussions.microsoft.com> wrote in message
news:D5**********************************@microsof t.com...
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" wrote:
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...matstrings.asp
Hope this helps
Martin
"Hutty" <Hu***@discussions.microsoft.com> wrote in message
news:A0**********************************@microsof t.com...
> 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?
>
> --
> Hutty


Nov 18 '05 #5

P: n/a
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.

"Hutty" wrote:
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
"Martin Dechev" wrote:
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
"Hutty" <Hu***@discussions.microsoft.com> wrote in message
news:D5**********************************@microsof t.com...
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" wrote:

> 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...matstrings.asp
>
> Hope this helps
> Martin
> "Hutty" <Hu***@discussions.microsoft.com> wrote in message
> news:A0**********************************@microsof t.com...
> > 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?
> >
> > --
> > Hutty
>
>
>


Nov 18 '05 #6

P: n/a
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)

"mr killer" wrote:
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.

"Hutty" wrote:
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
"Martin Dechev" wrote:
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
"Hutty" <Hu***@discussions.microsoft.com> wrote in message
news:D5**********************************@microsof t.com...
> 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" wrote:
>
> > 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...matstrings.asp
> >
> > Hope this helps
> > Martin
> > "Hutty" <Hu***@discussions.microsoft.com> wrote in message
> > news:A0**********************************@microsof t.com...
> > > 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?
> > >
> > > --
> > > Hutty
> >
> >
> >

Nov 18 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.