CODEWITHSUNDEEP
c#asp.netgridviewdate-format
Weichafe
Weichafe

Reputation: 97

Date Format to a GridView populated with SQL in ASP.net - NOT boundfield

This is the code that creates the GridView everything else is happening with C#, I mean that is populated with the C# code:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="STANDBY_JOURNALS Email.aspx.cs" Inherits="STANDBY_JOURNALS_Email.STANDBY_JOURNALS_Email" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        <asp:Button ID="Button1" runat="server" Text="SQL" OnClick="Button1_Click" />
        <asp:Button ID="Button2" runat="server" Text="SEND" OnClick="Button2_Click" />
        <br />
        <asp:GridView ID="GridView1" runat="server" Font-Size="Smaller">
        </asp:GridView>


    </div>
    </form>
</body>
</html>

I need to add Date format to this GridView, but if I try to use BOUNDFIELD, what it does, it creates a column at the beginning of the table, like this:

<asp:GridView ID="GridView1" runat="server" Font-Size="Smaller">
    <Columns>
        <asp:BoundField DataField="Journal Date" DataFormatString="{0:MMMM d, yyyy}" />
    </Columns>
</asp:GridView>

enter image description here

This is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Net;
using System.Net.Mail;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace STANDBY_JOURNALS_Email
{
    public partial class STANDBY_JOURNALS_Email : System.Web.UI.Page
    {

        SqlConnection vid = new SqlConnection("Data Source=xxxxxxxx; Initial Catalog=xxxxxxxxxxx;  User id=xxxxxx; Password=xxxxxxx");

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string sqlquery1 = "SELECT * FROM DBO.STANDBY_JOURNALS_DQMS WHERE [Ledger Grp] IN ('ACTUALS','GRP_FXRATE','CONSEGRP') AND [Source] = 'CON' OR [Source] LIKE 'Q%' ORDER BY [Source], [SystemDateTime],[SystemTime] ASC";

            String str = sqlquery1;
            SqlCommand xp = new SqlCommand(str, vid);
            vid.Open();
            xp.ExecuteNonQuery();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = xp;
            DataSet ds = new DataSet();
            da.Fill(ds, "Name");
            GridView1.DataSource = ds;
            GridView1.DataBind();
            vid.Close();
        }
        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Verifies that the control is rendered */
        }
        protected void Button2_Click(object sender, EventArgs e)
        {
           using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter hw = new HtmlTextWriter(sw))
                {


                    String EmailFrom = "[email protected]";
                    String EmailTo = "[email protected]";
                    String EmailSubject = "this is a subject";
                    //String EmailBody = "Cuerpo del Mensaje";



                    GridView1.RenderControl(hw);
                    StringReader sr = new StringReader(sw.ToString());
                    MailMessage mm = new MailMessage(EmailFrom, EmailTo);
                    mm.Subject = EmailSubject;
                    mm.Body = "Report:<hr />" + sw.ToString(); ;
                    mm.IsBodyHtml = true;
                    SmtpClient smtp = new SmtpClient();
                    smtp.Host = "XXXXXXXXXXXXXX";
                    smtp.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
                    smtp.Port = 25;
                    smtp.Timeout = 20000;
                    smtp.Send(mm);
                }
            }
        }
    }
}

Anyone can help in how to affect the columns in the table created with the GridView?

Upvotes: 1

Views: 1412

Answers (4)

Preet
Preet

Reputation: 994

Set AutoGenerateColumns property of GridView to false (I think this is the only solution you looking for) and add BoundFields for all required columns.

Or if you are using TemplateFields then there are options to set date format for your TemplateField. No need to add BoundField like:

<asp:TemplateField ItemStyle-Width = "100px"  HeaderText = "date" 
      SortExpression="date" >
   <ItemTemplate>
         <asp:Label ID="lblreceived_at" runat="server"
               Text='<%# Eval("date","{0:d}")%>'></asp:Label>
   </ItemTemplate>
</asp:TemplateField>

For all options refer: http://www.codedigest.com/Articles/ASPNET/137_How_to_format_DateTime_in_GridView_BoundColumn_and_TemplateColumn.aspx

Upvotes: 2

Muhammad Aamir Iqbal
Muhammad Aamir Iqbal

Reputation: 76

If you add the following Convert function in your SQL query, your purpose will be achieved. For example:

Select CONVERT(varchar,YOUR_DATE_COLUMN,107) as JournalDate, * From YOUR_TABLE_NAME

Upvotes: 1

Rox
Rox

Reputation: 361

The easiest way is to just use SQL MID() in the query,

SELECT MID(Journal_Date,1,8) AS JD FROM DBO.STANDBY_JOURNALS_DQMS

It will give you

01/06/17

Upvotes: -1

user1773603
user1773603

Reputation:

In BoudField, Try this format:

DataFormatString="{0:dd/MM/yyyy}"

Output:

01/06/2017 00:00:00

Upvotes: 2

Related Questions