none
How to format datagridview single column to date

    Pregunta

  • I am filling datagridview with data from SQL server 2008 R2. one column is date which is stored in the data base as yyyy-mm-dd and when i retrieve it displays in the same format. I want to change the display to dd-MMM-yyyy. need help to implement the same please find the code i am using.

    Try
                Connect_Design_Document_Record()
                Dim cmdDrawing As New OleDbCommand("SELECT DRAWING_NO, DRAWING_DESCRIPTION, DRAWN_DATE, REVISION_NO" & _
                    " FROM " & strDRAWING_TABLE & " WHERE PROJECT_NO='" & Me.ProjectNo_ComboBox.Text & "'" & _
                    " ORDER BY DRAWING_NO", Con_Design)
                Con_Design.Open()
    
                Dim dtDrawing As New DataTable
                dtDrawing.Load(cmdDrawing.ExecuteReader)
    
                With Me.DataGridView1
                    .DataSource = dtDrawing
                    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
                    .ReadOnly = True
                    .Columns.Item(0).HeaderText = "DRAWING NO"
                    .Columns.Item(1).HeaderText = "DISCRIPTION"
                    .Columns.Item(2).HeaderText = "DRAWN DATE"
                    .Columns.Item(3).HeaderText = "REV. NO."
                    .Columns.Item(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                    .Columns.Item(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                    .Columns.Item(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                    .Columns.Item(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                    .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                End With
    
                dtDrawing.Dispose()
                cmdDrawing.Dispose()
                Con_Design.Close()
    
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

    sábado, 01 de octubre de 2011 3:28

Respuestas

  • Hi Saseendran,

    You should be able to do the formatting on the SQL Server end.  Just change your OleDbCommand to this:

     

    Dim cmdDrawing As New OleDbCommand("SELECT DRAWING_NO, DRAWING_DESCRIPTION, DRAWN_DATE = convert(varchar(10),DRAWN_DATE,105), REVISION_NO" & _
                    " FROM " & strDRAWING_TABLE & " WHERE PROJECT_NO='" & Me.ProjectNo_ComboBox.Text & "'" & _
                    " ORDER BY DRAWING_NO", Con_Design)
    


     


    Tom Overton
    sábado, 01 de octubre de 2011 9:24

Todas las respuestas

  • Hi Saseendran,

    You should be able to do the formatting on the SQL Server end.  Just change your OleDbCommand to this:

     

    Dim cmdDrawing As New OleDbCommand("SELECT DRAWING_NO, DRAWING_DESCRIPTION, DRAWN_DATE = convert(varchar(10),DRAWN_DATE,105), REVISION_NO" & _
                    " FROM " & strDRAWING_TABLE & " WHERE PROJECT_NO='" & Me.ProjectNo_ComboBox.Text & "'" & _
                    " ORDER BY DRAWING_NO", Con_Design)
    


     


    Tom Overton
    sábado, 01 de octubre de 2011 9:24
  • Or you can specify the format for particualt column of DGV:

    dataGridView1.Columns(0).DefaultCellStyle.Format = "dd-MM-yyyy"
    

    Instead of 0 specify the column index (or column name - inside double quotes, like : ("ColumnName")


    Mitja
    • Propuesto como respuesta Armin Zingler sábado, 01 de octubre de 2011 11:22
    sábado, 01 de octubre de 2011 9:56
  • Thanks ! it's working good
    sábado, 01 de octubre de 2011 15:29