Solved

How to format cell in gridview from codebehind

Posted on 2011-03-23
16
1,534 Views
Last Modified: 2012-05-11
Good Day,
I need an example or a link to an example that will format a cell in a gridview from date and time to just short date.  I need to do it in the codebehind as the autogenerate is set to true.  Also because I allow the user to choose their own columns from a dropdownlist to populate the report I need to check the columns selected (the selected values are the column names in the table)from the dropdown  to determine first if it is one of the date fields in the table before I do the formatting from date time to just date.  Can someone assist please.  Thanks.
0
Comment
Question by:thamilto0410
  • 8
  • 4
  • 4
16 Comments
 
LVL 17

Expert Comment

by:nepaluz
ID: 35201472
if you are sure of the intergrity of your data, set the DefaultCellStyle of the column in the designer to DataTime and choose your date mask from there.

to check whether the column is the date column, check for its index, eg if it is the first column, then the index will be 0 OR check for the name that you assign the column.
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 35201903
nepaluz:  How do I set set the DefaultCellStyle of the column in the designer to DataTime when my columns are dynamically generated from the codebehind?
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 35201964
wherever you generate your columns, you can set the cell style as follows

            Dim xColumn As New GridViewColumn
            Dim xStyle As New GridViewCellStyle
            xStyle.FormatProvider = GetType(DateTime)
            xColumn.DefaultCellStyle.ApplyStyle(xStyle)

Open in new window


I have instantiated the column in the code, but you would not need to do this since your code already does it. All you need to do is establish which column is the date column and set the cell style as illustrated.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:thamilto0410
ID: 35202226
nepaluz: Okay but I am autogenerating=true and then the columns selected drom the DB table as I said above is based on user selection from a dropdownlist.  So where do I put the above code in codebehind?  OnRowbound?
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 35202264
you can put that anywhere after generating your data for the gridview. have a test to find out whether the grid contains your said column and assign the style to it.

I have none of your code so I am unable to suggest where to place my suggestion, however, OnRowBound MAY be a good place, though I wouldn'thave chosen an event, rather I'd have placed it after the the call / code to populate returns.
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 35202413
nepaulz: Okay I know how to do onrowbound  can you tell me how to go through the cells and grab the column name so I can test as to whether that column is one of the date columns.  Thanks.
0
 
LVL 17

Assisted Solution

by:nepaluz
nepaluz earned 50 total points
ID: 35202836
onrowbound event will fire EVERYTIME a new row is bound, which is not very efficient since you only need to set this once.

from your routine to populate the gridview, find the header text or the name of the date column and from there set its cellstyle. If I had some code from you on how you populate the grid, then it would be easier, else thats all I can suggest. You should know what the column names / headers are though .........
0
 
LVL 1

Expert Comment

by:jesusaflores
ID: 35206079
Maybe this example you help...

Sample Code:


<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="OrderID"
            DataSourceID="Northwind">
            <Columns>
                <asp:BoundField DataField="OrderID" HeaderText="OrderID" InsertVisible="False" ReadOnly="True"
                    SortExpression="OrderID" />
                <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" SortExpression="CustomerID" />
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression="EmployeeID" />
                <asp:BoundField DataField="OrderDate" HeaderText="OrderDate" SortExpression="OrderDate" DataFormatString="{0:d}" HtmlEncode=false />
                <asp:BoundField DataField="RequiredDate" HeaderText="RequiredDate" SortExpression="RequiredDate" DataFormatString="{0:d}" HtmlEncode=false />
                <asp:BoundField DataField="ShippedDate" HeaderText="ShippedDate" SortExpression="ShippedDate" DataFormatString="{0:d}" HtmlEncode=false />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="Northwind" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="SELECT [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate] FROM [Orders]">
        </asp:SqlDataSource>

and if you are using template field try this

<%# Eval("Date","{0:dd-MM-yyyy}") %>, remenber change "Date"  for you filed..

I hope my answer helps you
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 35206372
jesusaflores: Sorry that does not help as according to my post I am autogenerating columns.  Thanks.

nepaluz:  I am adding below my code so maybe you can assist a little further:

aspx file with gridview looks like:
<asp:Gridview rowstyle-font-size="small" gridlines=both id="caseresults" runat="server">
<headerstyle borderwidth="1px" bordercolr="#bab8b4" />
</asp:Gridview>

codebehind to bind to grid is:

Sub GetTheCase_Onclick(ByVal source As Object, ByVal E As EventArgs) Handles getReport.Click

        Dim thevalues As String = ""
        Dim i As Integer
        For i = 0 To thecolumns.Items.Count - 1
            If thecolumns.Items(i).Selected Then
                thevalues += thecolumns.Items(i).Text & ","
            End If
        Next
        sqltext = "SELECT " & Left(thevalues, Len(thevalues) - 1) & " FROM  " & Session("strEvalTable") & " e "
        sqltext = sqltext & " where e.ID=" & Session("unique")
        Try

            cnRDPSWeb = New OracleConnection(connstring)
            cnRDPSWeb.Open()
            Dim adRDPSWeb = New OracleDataAdapter(sqltext, connstring)
            Dim dsRDPSWeb As New DataSet
            adRDPSWeb.Fill(dsRDPSWeb, Session("strEvalTable"))
            If dsRDPSWeb.Tables(0).Rows.Count > 0 Then
                caseresults.DataSource = dsRDPSWeb
                norecords.Text = "Please find below data related to the closed record chosen."
                'caseresults.DataKeyField = "ID"
                caseresults.DataBind()
                getcolumns.Visible = False
                pnlnorecords.Visible = False
                thecase.Visible = True
            Else
                thecase.Visible = False
                norecords.Text = "There are no records to display"
                pnlnorecords.Visible = True
            End If
        Catch ex As Exception
            errhandler.WriteError("filename.aspx and procedure is getthecase", ex.Message)
            Response.Redirect("error.aspx")

        Finally
            If cnRDPSWeb.State = ConnectionState.Open Then
                cnRDPSWeb.Close()
                cnRDPSWeb.Dispose()
            End If
        End Try
    End Sub

Now when the grid binds the report looks like the below as an example

NAME        ID            DATE ASSIGNED                 SUBJECT
myname    myID        9/1/2010  12:00:00 AM          MYSUBJECT

My goal is to remove the timestamp from the date column above but the report is autogenerated and the user could choose from 27 different columns to build the report and about 8 of them are date fields.  Does this better help you?

0
 
LVL 1

Author Comment

by:thamilto0410
ID: 35206397
Also upping the points based on time already invested in my request.
0
 
LVL 1

Expert Comment

by:jesusaflores
ID: 35206795
Can you set the format for the cell in mode Design of the page(if is WebPage), how?

you edit columns in teh GridView  and the field "date" you set value in the BoundField Properties the propertie "DataFormatString" with value {0:d}, attached image for example.

I  hope my answer helps you
Format-DateShort-in-GridView.bmp
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 35208016
jesusaflores: The columns do not exist at design time.  They are built at run time.
0
 
LVL 1

Accepted Solution

by:
jesusaflores earned 350 total points
ID: 35208599
So, in statement SQL have  modify format value Date, example:

Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATE format. For example,
SELECT TO_CHAR(b, 'YYYY/MM/DD') AS b
FROM x;
returns the result:
B
--------
1998/04/01

another possibility would be to fill the Dataset: adRDPSWeb.Fill (dsRDPSWeb, Session ("strEvalTable")) in Table zero (0), can change the date column to take the format , but this is necesary use  DateTimeMode
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 35209655
jesusaflores:  Thank you.  You get the majority of the points as the sql way you mentioned works great.  The only thing is the to_char is in the header of the grid.  How do I get it out?
0
 
LVL 1

Assisted Solution

by:jesusaflores
jesusaflores earned 350 total points
ID: 35210011
I think what the problem is what the column "date" is selected from combobox(or the control what contains the columns for you report), right?
 
If thre answer is yes, so this can help(see attached)

I  hope my answer helps you

For i = 0 To thecolumns.Items.Count - 1
            If thecolumns.Items(i).Selected Then
                If thecolumns.Items(i).Text ="the field o column what contains the Date" then
                     thevalues +=  " to_char(" &  thecolumns.Items(i).Text & ", 'YYYY/MM/DD') as ColumnX,"

'  ColumnX  should be the same column name  of combobox or filed in you table, example:
' if the name of the columns in the combobox(or the control what contains the columns for you report) is "DateRecord", so ColumnX should be  the name of "DateRecord"
                 else
                   thevalues += thecolumns.Items(i).Text & ","
                End If
            End If
        Next
        sqltext = "SELECT " & Left(thevalues, Len(thevalues) - 1) & " FROM  " & Session("strEvalTable") & " e "
        sqltext = sqltext & " where e.ID=" & Session("unique")

Open in new window

0
 
LVL 1

Author Closing Comment

by:thamilto0410
ID: 35210496
nepaluz: Thank you.  Points awarded for attempting to help.
jesusflores:  Exactly what I needed both the sql and the change to fix the column header.  Thanks so much.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question