Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

Format column on datatable

Hello,
How can I  format this code on datasource

  dg(int).Columns("Total").DefaultCellStyle.Format = "N2"

Open in new window


dg is my datagridview and int is the number

instead on dg(int) how can I use datasource

Cheers
0
RIAS
Asked:
RIAS
  • 3
  • 2
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Are you getting this from the database?

Do you want to do this in datatable? , You can do that in the query itself.
0
 
AndyAinscowCommented:
Basically - you can't. You can format strings and store these in the databable but numbers you do that when you display them.
0
 
RIASAuthor Commented:
The problem is that it is a money field.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
AndyAinscowCommented:
You format the value when you display it.  (Separating data and UI in designing the app)
0
 
Pawan KumarDatabase ExpertCommented:
Use formatted from DB itself rather the in UI.

Try one of these approaches...

1. SELECT * , FORMAT(a,'0.00') FormattedColumn FROM CTE
2. SELECT * , CASE WHEN CHARINDEX('.',a,0) > 0 THEN SUBSTRING(CAST(a AS VARCHAR(10)),0,CHARINDEX('.',a,0)+2) ELSE a END FormattedColumn FROM CTE

FirstOne

;WITH CTE AS
(
	
	SELECT CAST(1.5 AS MONEY) a UNION ALL
	SELECT CAST(2.AS MONEY) UNION ALL
	SELECT CAST(3.2456AS MONEY) UNION ALL
	SELECT CAST(4.82AS MONEY) UNION ALL
	SELECT CAST(5AS MONEY) UNION ALL
	SELECT CAST(0.09 AS MONEY)
)
SELECT * , CASE WHEN CHARINDEX('.',a,0) > 0 THEN SUBSTRING(CAST(a AS VARCHAR(10)),0,CHARINDEX('.',a,0)+2) ELSE a END FormattedColumn FROM CTE

Open in new window

Second


;WITH CTE AS
(
	
	SELECT CAST(1.5 AS MONEY) a UNION ALL
	SELECT CAST(2.AS MONEY) UNION ALL
	SELECT CAST(3.2456AS MONEY) UNION ALL
	SELECT CAST(4.82AS MONEY) UNION ALL
	SELECT CAST(5AS MONEY) UNION ALL
	SELECT CAST(0.09 AS MONEY)
)
SELECT * , FORMAT(a,'0.00') FormattedColumn FROM CTE

Open in new window



Output


a                     FormattedColumn
--------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.50                  1.50
2.00                  2.00
3.2456                3.25
4.82                  4.82
5.00                  5.00
0.09                  0.09

(6 row(s) affected)

Open in new window


Hope it helps!!
0
 
RIASAuthor Commented:
Cheers Pawan!
0
 
Pawan KumarDatabase ExpertCommented:
Cheers Rias !!

Just one update..

<<SELECT * , CASE WHEN CHARINDEX('.',a,0) > 0 THEN SUBSTRING(CAST(a AS VARCHAR(10)),0,CHARINDEX('.',a,0)+3) ELSE a END FormattedColumn FROM CTE>>
1

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now