• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

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?
0
stylelyf
Asked:
stylelyf
  • 4
  • 2
1 Solution
 
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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