Wrong date format C#, Oracle

I'm trying to get my date in the correct format(dd/mm/yyyy). At the moment its in this format: MM-DD-YYYY HH24:MI:SS When I change it to dd/mm/yyyy, it works in the database(Oracle). As soon as I run it in my app I get exception: IndexOutOfRange at :

this.InfoList9.Add(dr["start_rcv_datetime"].ToString());

Please see my code below.

public List<String> InfoList = new List<String>();

private void populatelblDate()
{
    conn.Open();
    string query;
    query = "select to_char(dg.start_rcv_datetime,'dd/mm/yyyy') from dc_pallet dp, dc_pallet_stock dps , dc_grv dg , sku s ,prod_size ps,colour c ,purch_order_carton_sku pocs , dc_crane_instruc dci where dps.pallet_id_no = '" + palletId.ToString() + "' and dp.pallet_id_no = dps.pallet_id_no and dg.dc_grv_id_no = dps.dc_grv_id_no and dg.order_no = dps.order_no and dg.company_id_no = dps.company_id_no and s.company_id_no = dps.company_id_no and s.company_id_no = dg.company_id_no and dps.company_id_no = c.company_id_no and dps.company_id_no = ps.company_id_no and s.prod_size_id_no = ps.prod_size_id_no and s.colour_id_no = c.colour_id_no and dps.company_id_no = ps.company_id_no and pocs.order_no = dps.order_no and pocs.carton_code = dps.carton_code and pocs.company_id_no = dps.company_id_no and pocs.sku_id_no = s.sku_id_no and dci.pallet_id_no(+) = dp.pallet_id_no";

    OracleCommand cmd = new OracleCommand(query, conn);
    OracleDataReader dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        this.InfoList.Add(dr["start_rcv_datetime"].ToString());
    }
    dr.Close();
    conn.Close();
}

private void frmInfo_Load(object sender, EventArgs e)
{ 
    populatelblDate();
    lbl1.Text = this.InfoList[0];
}

Then I have a prev and next button as well...

---

Your IndexOutOfRange exception suggests that the immediate problem is that the result set doesn't contain a column of start_rcv_datetime - presumably because of the to_char conversion.

Don't deal with strings at the database side at all. Fetch the value as a DateTime , and then format it at the client in whatever you want to.

Use dr.GetDateTime to fetch the value, having removed the to_char part from your query:

query = "select dg.start_rcv_datetime from ...";

using (OracleCommand cmd = new OracleCommand(query, conn))
{
    using (OracleDataReader dr = cmd.ExecuteReader())
    {
        int dateColumn = dr.GetOrdinal("start_rcv_datetime");
        while (dr.Read())
        {
            DateTime date = dr.GetDateTime(0);
            // Or whatever - consider cultural implications
            string text = date.ToString("dd/MM/yyyy");
            InfoList.Add(text);
        }
    }
}

(Note the using statements - you should always make sure you clean up your database-related resources.)

---

Your first column doesn't have a name andso you cannot retrieve it with "start_rcv_datetime"

The immediate solution would be to change the sql to read

query = "select to_char(dg.start_rcv_datetime,'dd/mm/yyyy') as start_rcv_datetime from dc_pallet dp, dc_pallet_stock dps , dc_grv dg , sku s ,prod_size ps,colour c ,purch_order_carton_sku pocs , dc_crane_instruc dci where dps.pallet_id_no = '" + palletId.ToString() + "' and dp.pallet_id_no = dps.pallet_id_no and dg.dc_grv_id_no = dps.dc_grv_id_no and dg.order_no = dps.order_no and dg.company_id_no = dps.company_id_no and s.company_id_no = dps.company_id_no and s.company_id_no = dg.company_id_no and dps.company_id_no = c.company_id_no and dps.company_id_no = ps.company_id_no and s.prod_size_id_no = ps.prod_size_id_no and s.colour_id_no = c.colour_id_no and dps.company_id_no = ps.company_id_no and pocs.order_no = dps.order_no and pocs.carton_code = dps.carton_code and pocs.company_id_no = dps.company_id_no and pocs.sku_id_no = s.sku_id_no and dci.pallet_id_no(+) = dp.pallet_id_no";

However you could simply return the date as a datetime from the database and then use string.Format on the result

eg.

this.InfoList.Add(string.Format("{0:dd/MM/yyyy}", dr["start_rcv_datetime"]));

链接地址: http://www.djcxy.com/p/36912.html

上一篇: Oracle：包含时区的日期/时间的时代milleseconds

下一篇: 错误日期格式C＃，Oracle