asked on

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?

ASKER CERTIFIED SOLUTION

Ramachandar N

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stylelyf

ASKER

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

Ramachandar N

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?

stylelyf

ASKER

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?

Daniel Van Der Werken

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

stylelyf

ASKER

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?

stylelyf

ASKER

This is correct. I just added a check for each field's datatype, if its money, then format it.

Thanks.