none
Data Grid View Column shown in wrong format RRS feed

  • Question

  • Hi Good People

    I have an SQL Database, with four columns called (FaresID, PickupAddress, DropAddress, InsertFare) In the SQL Database the FaresID (Primary key) PickupAddress & DropAddress Columns are  VARCHAR's and the InsertFare Column is money.

    When My form Loads the SQL Database Values are populated into the Data grid view, But the InsertFare Column Values Display as 7.7000 instead of 7.70 Is there a way I can get Insert fare Value to display Currency Value or Decimal value.  

    Hopefully the picture below will show what I mean...

     

    I hope You can Help me.

    Public Sub RefreshGrid()
            'Run Query
            SQL.ExecQuery("SELECT FareID, PickupAddress, DropAddress, InsertFare FROM Fares")
            If NoErrors(True) = False Then Exit Sub
            CBXFares.Items.Clear()
    
    
            dgvFares.DataSource = SQL.SQLDT
    
            For Each R As DataRow In SQL.SQLDT.Rows
                CBXFares.Items.Add(R("PickupAddress"))
            Next
            If SQL.RecordCount > 0 Then CBXFares.SelectedIndex = 0
        End Sub

    the Above code refreshes the Datagridview on form load...

    Kind Regards

    Gary


    Gary Simpson

    Thursday, October 5, 2017 4:09 PM

Answers

  • Hi

    Without doing anything with the database values,perhaps just formatting the display - something like this:

     ' decimal currency value
     Dim n As Decimal = 7.7D
    
    ' some dummyn values
     dgvFares.Rows.Add("AAAA", "BBB", n, "DDD")
    
     'force display to $nnn.nn
      dgvFares.Columns("InsertFare").DefaultCellStyle.Format = "$#.00"
    
    
    


    Regards Les, Livingston, Scotland

    • Marked as answer by Gary Simpson Thursday, October 5, 2017 10:10 PM
    Thursday, October 5, 2017 4:32 PM
  • Try the next minor change too:

    SQL.ExecQuery("SELECT FareID, PickupAddress, DropAddress, CAST(InsertFare AS decimal(18,2)) FROM Fares")


    Thursday, October 5, 2017 6:00 PM

All replies

  • Hi

    Without doing anything with the database values,perhaps just formatting the display - something like this:

     ' decimal currency value
     Dim n As Decimal = 7.7D
    
    ' some dummyn values
     dgvFares.Rows.Add("AAAA", "BBB", n, "DDD")
    
     'force display to $nnn.nn
      dgvFares.Columns("InsertFare").DefaultCellStyle.Format = "$#.00"
    
    
    


    Regards Les, Livingston, Scotland

    • Marked as answer by Gary Simpson Thursday, October 5, 2017 10:10 PM
    Thursday, October 5, 2017 4:32 PM
  • Try the next minor change too:

    SQL.ExecQuery("SELECT FareID, PickupAddress, DropAddress, CAST(InsertFare AS decimal(18,2)) FROM Fares")


    Thursday, October 5, 2017 6:00 PM
  • Try the next minor change too:

    SQL.ExecQuery("SELECT FareID, PickupAddress, DropAddress, CAST(InsertFare AS decimal(18,2)) FROM Fares")


    Hi Viorel

    I have changed my code to what you and leshay has suggested But I get an error Pictured Blow...

    Regards Gary


    Gary Simpson

    Thursday, October 5, 2017 9:36 PM
  • Hi

    Without doing anything with the database values,perhaps just formatting the display - something like this:

     ' decimal currency value
     Dim n As Decimal = 7.7D
    
    ' some dummyn values
     dgvFares.Rows.Add("AAAA", "BBB", n, "DDD")
    
     'force display to $nnn.nn
      dgvFares.Columns("InsertFare").DefaultCellStyle.Format = "$#.00"
    
    


    Regards Les, Livingston, Scotland

    Hi LesHay Thank you for getting back to me so quickly,

    I have tried your code only to get another problem saying the InsertFare Column does not exist...


    Gary Simpson

    Thursday, October 5, 2017 9:42 PM
  • Hi Everyone

    I have changed my column data types from money to varchar, and now i am getting the result i want now.

    A big thank you to Les Livingston and Viorel for their input much appreciated 

    kind regards

    Gary


    Gary Simpson

    Thursday, October 5, 2017 10:10 PM
  • Hi

    Glad you have a solution.

    Regarding the DataGridView column display settings. The DataGridView does need the columns to be created already before they can be referenced by any code. Whether this is done in code or in the designer, the columns need to exist before you can set their properties.

    In your case, I would imagine, after you have set the DataSource for your DataGridView would be where you would set any of the properties for the cells, rows or columns etc.

    You could easily set up a sub that runs through all the columns and sets particular properties for them. 


    Regards Les, Livingston, Scotland

    Thursday, October 5, 2017 11:27 PM