How do I format money field in my datatable in winforms?

I have a function like this below that gets the data:

Private Function GetData(ByVal selectCommand As String)

		connection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("myconnectionstring").ConnectionString)
		If connection.State = ConnectionState.Open Then
			connection.Close()
		End If

		connection.Open()

		command = New SqlCommand(_spLoader, connection)
		command.CommandType = CommandType.StoredProcedure
		command.CommandTimeout = 0
		adapter = New SqlDataAdapter(command)
		Dim cb As New SqlCommandBuilder(adapter)
		adapter.UpdateCommand = cb.GetUpdateCommand(True)
		adapter.DeleteCommand = cb.GetDeleteCommand(True)
		adapter.InsertCommand = cb.GetInsertCommand(True)

		datatable = New DataTable()
		adapter.Fill(datatable)

		' Refresh grid.
               ' I breakpoint here
		BindingSource1.DataSource = datatable
		DataGridView.DataSource = BindingSource1.DataSource
		Try
			DataGridView.Columns("recid").Visible = False
		Catch ex As Exception
			'
		End Try

	End Function

Open in new window


I breakpoint (commented on code above) to see the data in my dataset visualizer.
But I notice my money field in datatable has 4 decimals

How do I format it to have 2 decimals only?
stylelyfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ramachandar NCommented:
Change the CellStyle format to "N2" in the DataGridView properties in the form.

or

Do it in the code
dataSource.Columns["ColumnName"].DefaultCellStyle.Format = "N2"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stylelyfAuthor Commented:
The form is a generic form. This means I will not be able to know the names of the column beforehand. It is up to the caller who will instantiate the form to pass the name of the stored procedure that will populate the data for the grid.

Eg: The form constructor will be passed a parameter named spLoader (stored proc that will provide the data and using the code above in my first post)

Public Sub New(ByVal spLoader As String)

Open in new window

0
Ramachandar NCommented:
So, the column order and type of the Stored Procedure's result will always be the same?

If yes, what's the position of Money field in the result?
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

stylelyfAuthor Commented:
Oops, I'm sorry on the code above, it should be this

Private Function GetData()

		connection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("mainconnectionstring").ConnectionString)
		If connection.State = ConnectionState.Open Then
			connection.Close()
		End If

		connection.Open()

		command = New SqlCommand(_spLoader, connection)
		command.CommandType = CommandType.StoredProcedure
		command.CommandTimeout = 0
		adapter = New SqlDataAdapter(command)
		Dim cb As New SqlCommandBuilder(adapter)
		adapter.UpdateCommand = cb.GetUpdateCommand(True)
		adapter.DeleteCommand = cb.GetDeleteCommand(True)
		adapter.InsertCommand = cb.GetInsertCommand(True)

		datatable = New DataTable()
		adapter.Fill(datatable)


		' Refresh grid.
		BindingSource1.DataSource = datatable
		DataGridView.DataSource = BindingSource1.DataSource
		Try
			DataGridView.Columns("recid").Visible = False
		Catch ex As Exception
			'
		End Try

	
	End Function

Open in new window



The stored procedure (_spLoader variable) is a local variable and is passed by the caller and it can be any stored procedure. So I will not know how many fields or what are the fields. Now when the command is populated by the adapter using the _sploader stored procedure, the datatable as seen in my visualizer is 1.0000. I want it to be 1.00.

So how do I do formatting considering  I do not know the fields beforehand?
0
Daniel Van Der WerkenIndependent ConsultantCommented:
Does using DECIMAL(5,2) -- or DECIMAL(Anything,2) work when creating the column in the database?

IF OBJECT_ID('tempdb.dbo.#MyMoney') IS NOT NULL
	DROP TABLE #MyMoney
CREATE TABLE #MyMoney(ID INT, Dollars DECIMAL(5,2))
INSERT INTO #MyMoney (ID, Dollars) VALUES (1, 1.00000)
INSERT INTO #MyMoney (ID, Dollars) VALUES (2, 2.67700)
INSERT INTO #MyMoney (ID, Dollars) VALUES (3, 2.50050)
INSERT INTO #MyMoney (ID, Dollars) VALUES (4, 3.00044)
INSERT INTO #MyMoney (ID, Dollars) VALUES (5, 4.44333)
INSERT INTO #MyMoney (ID, Dollars) VALUES (6, 8.99090)

SELECT * FROM #MyMoney

Open in new window

0
stylelyfAuthor Commented:
This is working. But this will cause me to change a lot of money datatypes to decimal in the database side. I have a hundred tables with not fewer than 20 columns with money datatypes. And I still do not know the side effect of this, and I'm thinking I might break something. If I do the change in the database, I will have to tell the callers "hey change all of the money types to decimal in your database".

Are there any other way? Wherein I can manipulate the formatting display in my side?
0
stylelyfAuthor Commented:
This is correct. I just added a check for each field's datatype, if its money, then format it.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.