[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 45
  • 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
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
AndyAinscowFreelance programmer / ConsultantCommented:
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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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