none
How to Format a Datatable Column RRS feed

  • Question

  • I have a dataset containing a DataTable with 1 column having string Value like "12-Dec-2011 12:00:00" I am binding DataSet.Table with datagridview. Can any1 please suggest me how can I convert the string into "12-Dec-2011". I dont want to use

    1) for loop on rows...

    2) Conversion @ DB end is not an option...

    3) I am using datagridview

    Please suggest someother alternate............

    Tuesday, December 20, 2011 7:14 PM

Answers

  • > I used dsResponse.Tables(0).Column("XYZ").Expression = "CONVERT(XYZ, 'System.DateTime')" But it is giving me Circular Reference error.
     
     
    you can define additional 'virtual' column with expression. but the better approach is use DefaultCellStyle.Format in the DataGridView. an example is here.
     
     

    • Edited by Malobukv Wednesday, December 21, 2011 4:03 PM
    • Marked as answer by NikhilGupta Wednesday, December 21, 2011 4:56 PM
    Wednesday, December 21, 2011 4:02 PM

All replies

  • > a DataTable with 1 column having string Value like "12-Dec-2011 12:00:00" I am binding DataSet.Table with datagridview. Can any1 please suggest me how can I convert the string into "12-Dec-2011"
     
     
    see DataColumn.Expression Property, Functions: CONVERT, SUBSTRING
      
     

    Tuesday, December 20, 2011 7:46 PM
  • I used dsResponse.Tables(0).Column("XYZ").Expression = "CONVERT(XYZ, 'System.DateTime')"

    But it is giving me Circular Reference error.

    Wednesday, December 21, 2011 8:37 AM
  • > I used dsResponse.Tables(0).Column("XYZ").Expression = "CONVERT(XYZ, 'System.DateTime')" But it is giving me Circular Reference error.
     
     
    you can define additional 'virtual' column with expression. but the better approach is use DefaultCellStyle.Format in the DataGridView. an example is here.
     
     

    • Edited by Malobukv Wednesday, December 21, 2011 4:03 PM
    • Marked as answer by NikhilGupta Wednesday, December 21, 2011 4:56 PM
    Wednesday, December 21, 2011 4:02 PM
  • Thanks :)

    I think going with the Virtual Column will be a best solution.

    I could not directly use  DataGridView DefaultCellStyle.Format as column value in DataSet is in String Format, so Formatting is not applied untill column is of DateTime.

    I did something like this : 

            Dim dt As New DataTable
            Dim dtNew As New DataTable
            Dim dr As DataRow
    
            With dt.Columns
                .Add("Col1")
            End With
    
            dr = dt.NewRow
            dr("Col1") = "13-Dec-2011 00:00:00"
            dt.Rows.Add(dr)
    
            dt.Columns.Add("Col2", System.Type.GetType("System.DateTime")).Expression = "Convert(Col1, 'System.DateTime')"
    
            dtNew.Columns.Add("Col2", System.Type.GetType("System.DateTime"))
    
            dtNew = dt.DefaultView.ToTable(False, New String() {"Col2"})
    
            DataGridView1.DataSource = dtNew
    
            DataGridView1.Columns(1).DefaultCellStyle.Format = "dd-MMM-yyyy"


    • Edited by NikhilGupta Wednesday, December 21, 2011 5:13 PM
    Wednesday, December 21, 2011 4:56 PM