Format and display date in dd-mm-yyyy in SQL Server database

prachi

SOLVED CLOSED ACTIVE ANSWERED
User: prachi
Posted: on Jan 14, 2015 02:30 AM
Forum: SQL Server
Answer: 1
Views: 2984

Data is binded firsttime and getting error

public void Populate1()
    {
        ddlRepDate.Items.Clear();
        ddlRepDate.Items.Add(new ListItem("--Select Reporting Date--", ""));
        ddlRepDate.AppendDataBoundItems = true;
        conn.Open();
         
        string strQuery = "SELECT reviewtodate,RIGHT ('0' + RTRIM(DAY(reviewtodate)), 2) + '-' + RIGHT ('0' + RTRIM(MONTH(reviewtodate)), 2) +'-' + RTRIM(YEAR(reviewtodate)) AS mydate from drp2 where deptcode=" + lblDeptNo.Text + " and deptfinalizationstatus!='F'";
        SqlCommand cmd = new SqlCommand(strQuery, conn);
        string firstdate = "1";
        string monthyear;
        string reviewtodate = "";
        //DateTime dt = DateTime.Parse(txtFromDate.Text).Date;
        SqlDataReader reader = cmd.ExecuteReader();
        
        while (reader.Read())
        {
            reviewtodate =reader["mydate"].ToString();
            //reviewtodate=reader["reviewtodate"].ToString();
            Response.Write(string.Format("{0}", reviewtodate));

        }
        reader.Close();
        monthyear = reviewtodate.Substring(3, 7);
        string lastdate = reviewtodate.Substring(0, 2);
        string mydate = "";
        cmd.CommandType = CommandType.Text;
        ddlRepDate.DataSource = cmd.ExecuteReader();
        for (int i = Convert.ToInt32(firstdate); i <= Convert.ToInt32(lastdate); i++)
        {
            
            mydate=(i.ToString().PadLeft(2,'0'))+"-"+monthyear;

            ddlRepDate.DataTextField = mydate;
            ddlRepDate.DataValueField = mydate;
            ddlRepDate.DataBind();
            if (ddlRepDate.Items.Count>1)
            {
                ddlRepDate.Enabled = true;
            }
            else
            {
                ddlRepDate.Enabled = false;

            }
            
        }
            
        
        
        conn.Close();

    }

Azim

0 4

Reply
ANSWER
User: Azim
Replied: on Jan 15, 2015 02:14 AM
Report

You need to bind the DropDownList this way if you are using SqlDataReader.

Namespaces

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        PopulateDropDownLIst();
    }
}

public void PopulateDropDownLIst()
{
    ddlRepDate.Items.Clear();
    ddlRepDate.Items.Add(new ListItem("--Select Reporting Date--", ""));
    ddlRepDate.AppendDataBoundItems = true;
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        string strQuery = "SELECT OrderDate,RIGHT ('0' + RTRIM(DAY(OrderDate)), 2) + '-' + RIGHT ('0' + RTRIM(MONTH(OrderDate)), 2) +'-' + RTRIM(YEAR(OrderDate)) AS mydate from Orders ";
        using (SqlCommand cmd = new SqlCommand(strQuery, conn))
        {
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            ddlRepDate.DataTextField = "mydate";
            ddlRepDate.DataValueField = "OrderDate";
            ddlRepDate.DataSource = reader;
            ddlRepDate.DataBind();
            conn.Close();
        }
    }
}

Format and display date in dd-mm-yyyy in SQL Server database

Company

Explore

Follow us

Contact Us