How do I format a Date in an autogenerated GridView?

My gridview can have a variable # of columns, therefore I am autogenerating my gridview.  However, my datetime values for my 'Start Date' column are displayed in long format (date & time MM/DD/YYYY HH:MM:SS AM).  They come in this way from the DB and I do not want to format them there.  I only want the date (MM/DD/YYYY) to display.  I am trying to format the datetime value in the RowCreated event, but I can't overwrite the original value.  Where am I going wrong?
Protected Sub gvReviewApprove4_RowCreated(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvReviewApprove4.RowCreated

            ResizeColumns(e)

        End Sub

        Protected Sub ResizeColumns(ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)

            Dim td As TableCell
            Dim i As Integer = 0

            If e.Row.RowType = DataControlRowType.Header Then

                For Each td In e.Row.Cells
                    If (td.Text.Equals("Emission Sources") Or td.Text.Equals("Data Sources")) Then
                        td.Width = constLongColWidth
                        longCols.Add(i)
                        i += 1
                    ElseIf (td.Text.EndsWith("Date")) Then
                        td.Width = constRegColWidth
                        dateCols.Add(i)
                        i += 1
                    Else
                        td.Width = constRegColWidth
                        i += 1
                    End If
                Next
            ElseIf e.Row.RowType = DataControlRowType.DataRow Then
                Dim ci As CultureInfo
                ci = Thread.CurrentThread.CurrentCulture

                Dim longCol As Integer
                Dim dateCol As Integer
                Dim shortDate As DateTime

                'remove times from date - Not working
                If dateCols.Count > 0 Then
                    For i = 1 To dateCols.Count
                        dateCol = CInt(dateCols.Item(i))
                        shortDate = DateTime.Parse(e.Row.DataItem(dateCol))
                        e.Row.Cells(dateCol).Text = shortDate.ToShortDateString
                    Next
                End If

                'increase column widths for Emission Sources & Data Sources
                If longCols.Count > 0 Then
                    For i = 1 To longCols.Count
                        longCol = CInt(longCols.Item(i))
                        e.Row.Cells(longCol).Width = constLongColWidth
                    Next
                End If

            End If

        End Sub

Open in new window

joyness_33Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
e.Row.Cells(dateCol).Text = shortDate.ToString("MM/dd/yyyy")
0
Tommy11bCommented:
I think it's easier and more efficient to format it on the backend, even though you said that's not really what you want. The examples above where your looping thru and changing values per row may cause each rowstate to be changed to modified, meaning they will update back to the database when you call your update method.

An example of formatting the date in Sql Server is like this

Select Convert(VarChar(8), GetDate(), 1) From Table

Just replace GetDate() with your DateColumn name


0
CodeCruiserCommented:
Modify you sql query and use code 101 with convert function like this

Convert(Date, StartDate, 101) AS StartDate
0
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.

joyness_33Author Commented:
I'm sorry - that my lead developer said that changing the sql query is not an option.  The problem is that  e.Row.Cells(dateCol).Text is cating as if it is ReadOnly.   If I am handed a datatable, where can I change the value before it displays in the GridView?
0
CodeCruiserCommented:
Use the cell paint event to custom draw the string.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tommy11bCommented:
To rant for a second here; I would have to totally disagree with your lead developer then and wonder how he even holds the postion when he can not even format a date. To say a query can not be changed is incredibly questionable to me....

Yes the value can be changed within the dataset/datagridview but this is very ineffiecient to loop thru each row and change each value. Also you then run into the problem I previously stated which is a changed value changes the records rowstate which can cause unexpected results with your database. Third, this is a datatype of DateTime, hence it will show the time as long as the field remains this datatype. Formated values are strings, and again this would mean converting the field from a DateTime datatype to a String value.

What I woud do is hide this column from your datagridview altoghter and add a new column to it just for display purposes. This way the field values and datatype remain unchanged. Now with the newly added column, set the datatype as a string so it can accept a formatted value. Also you can then use the DataColumn.Expression method to set the values all at once rather then looping thru each record individually.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I do concur with Tommy11b
0
joyness_33Author Commented:
Thanks for your help, folks.  The rant was unnecessary, but I know that happens sometimes!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Applications

From novice to tech pro — start learning today.