CODEWITHSUNDEEP
c#asp.netsqlpostgresqlgridview
user2939293
user2939293

Reputation: 813

Ugly time format in GridView ASP.NET C#

In my ASP.NET project I have a GridView to display times (among other things). I'm using Visual Studio 2012 and PostgreSQL and pgAdmin.

In the database times are stored in tbl_tid as:

enter image description here

This is also how they are displayed if I run the SQL in the database or print the times to a listbox.

BUT when I display the times in my GridView they come out like this: enter image description here

So, how do I get rid of **0001-01-01 ** in each row (and where does it come from?).

In my code behind:

            string sql = "SELECT tid FROM tbl_tid";
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);  
            ds.Reset();                 
            da.Fill(ds);                
            dt = ds.Tables[0];          
            GridView1.DataSource = dt;  
            GridView1.DataBind();

In my asp.net

                    <asp:GridView ID="GridView1" runat="server" OnRowDataBound="OnRowDataBound" Height="118px" Width="390px">
                    <Columns>
                    <asp:TemplateField >
                         <HeaderTemplate>
                             <input id="chkAll" onclick="SelectAllCheckboxes(this);" runat="server" type="checkbox" />                                        
                         </HeaderTemplate>            
                         <ItemTemplate>
                             <asp:CheckBox ID="myCheckBox" runat="server" /> 
                         </ItemTemplate>
                    </asp:TemplateField>
                    </Columns>
                    </asp:GridView>

Script:

<script src='http://code.jquery.com/jquery-latest.min.js' type='text/javascript'> </script>
<script>      
function SelectAllCheckboxes(chk) {
     $('#<%=GridView1.ClientID%>').find("input:checkbox").each(function () {
         if (this != chk) {
             this.checked = chk.checked;
         }
     });
}
</script>

Upvotes: 0

Views: 1424

Answers (3)

Mike Atkisson
Mike Atkisson

Reputation: 601

Why does time appear with "0001-01-01"?

The .NET framework is converting the PostGreSql time field into a Standard DateTime. Since there's no actual date involved, .NET is assuming the date associated with the time is DateTime.MinValue which is January 1, 0001.

How can you get rid of the date portion in your ASP.NET Grid?

You can format the DateTime so that the date portion is not displayed. .NET DateTime values are fairly easy to format, but you'll need to tell ASP.NET how to do that explicitly. One technique is to use a boundfield in your Grid like this:

<asp:boundfield datafield="TheNameOfYourDateColumn" 
                dataformatstring="{0:H:mm:ss}" 
                htmlencode="false" />

You could also use a TemplateField like you did for your checkboxes. There are also plenty of Format Strings for DateTimes which you should be able to take advantage of.

Upvotes: 5

user2316116
user2316116

Reputation: 6824

If you want to select time as a string, use to_char()

string sql = "SELECT to_char(tid, 'HH12:MI:SS') AS tid FROM tbl_tid";

Demo: http://sqlfiddle.com/#!15/e3bcb/5

Upvotes: 1

user2939293
user2939293

Reputation: 813

I solved it using this code:

   protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            int columnIndex = 0;
            foreach (DataControlFieldCell cell in e.Row.Cells)
            {
                if (cell.ContainingField is BoundField)
                    if (((BoundField)cell.ContainingField).DataField.Equals("tid"))
                        break;
                columnIndex++;
            }
            string columnValue = e.Row.Cells[columnIndex].Text;              
            string[] splitString = columnValue.Split(' ');
            string tid = splitString[1];
            e.Row.Cells[columnIndex].Text = tid.Remove(5,3);
        }          
    }

Upvotes: 1

Related Questions