ASP Calendar in not working properly

Question

I have created one webpage in ASP.net C# front end and Oracle 11g back end. Webpage consist of one ASP calendar, two DropDownList and one GridView. As per the selection of date and DropDownList, data will show in GridView. But when i select the any combination data is not showing in grid view. However, when i write same query in database through sqldeveloper i.e. SELECT PALLET_NO, DATA_STS, MERGE, PLANT_CD, SHIFT, RACK_NO FROM WI_PALLET WHERE PROD_CD = 'PET' AND INPUT_DT LIKE '24-06-15%'; data retrieve from database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OracleClient;
using System.Data;
using System.Configuration;
using System.Drawing;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
DataSet ds = new DataSet();
OracleConnection con = new OracleConnection("Data Source=10.31.41.103/ORCL;User ID=RL_PET;Password=RL_PET;Unicode=True");

protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click1(object sender, EventArgs e)
{
    Label1.Visible = false;

    if (DropDownList1.Text == "Store In" && DropDownList2.Text == "ALL")
    {

        con.Open();
        OracleDataAdapter a = new OracleDataAdapter("SELECT PALLET_NO, DATA_STS, MERGE, PLANT_CD, SHIFT, RACK_NO FROM WI_PALLET WHERE PROD_CD = 'PET' AND INPUT_DT LIKE '"+ Calendar1.SelectedDate.Date+"%' ORDER BY PALLET_NO ASC", con);
        a.Fill(ds);
        int count = ds.Tables[0].Rows.Count;
        Label1.Text = count.ToString();
        Label1.Visible = true;
        GridView1.DataSource = ds;
        GridView1.DataBind();
        GridView1.Visible = true;
        con.Close();
    }
    else if (DropDownList1.Text == "Store In")
    {
        con.Open();
        OracleDataAdapter a = new OracleDataAdapter("SELECT PALLET_NO, DATA_STS, MERGE, PLANT_CD, SHIFT, RACK_NO FROM WI_PALLET WHERE PROD_CD = 'PET' AND  INPUT_DT LIKE '" + Calendar1.SelectedDate.Date+"%' AND SHIFT = '" + DropDownList2.Text + "' ORDER BY PALLET_NO ASC", con);
        a.Fill(ds);
        int count = ds.Tables[0].Rows.Count;
        Label1.Text = count.ToString(); 
        Label1.Visible = true;
        GridView1.DataSource = ds;
        GridView1.DataBind();
        GridView1.Visible = true;
        con.Close();

    }
 }
 }

When you run this query in your oracle database manager, it works there? If not, what error message you get? — Soner Gönül, Jun 24 '15 at 07:25
Isn't it possible that in your SQL developer different culture was used (so different format of dates) and that caused the difference? — Gábor Bakos, Jun 24 '15 at 07:44
@SonerGönül when i fire this query SELECT PALLET_NO, DATA_STS, MERGE, PLANT_CD, SHIFT, RACK_NO FROM WI_PALLET WHERE PROD_CD = 'PET' AND INPUT_DT LIKE '24-06-15%'; in sqldeveloper data is retrieving from database — Jay Desai, Jun 24 '15 at 08:56
@GáborBakos in SQL developer i can set the date format by Go to Tools> Preferences > Database > NLS and set the Date Format — Jay Desai, Jun 24 '15 at 08:58
@user2374666 As I understand the problem is with your code. Probably the `ToString()` of ` Calendar1.SelectedDate.Date` is not in the proper format. — Gábor Bakos, Jun 24 '15 at 09:02
@user2374666 [`SqlCommand.Prepare`](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare(v=vs.110).aspx). Sorry, I am not familiar with `OracleDataAdapter`. — Gábor Bakos, Jun 24 '15 at 09:12

score 0 · Answer 1 · answered Jun 24 '15 at 07:42

0

I am using the calender control like this

//front end aspx page
    <asp:Calendar ID="Calendar1" runat="server"  
               SelectionMode="Day" 
               ShowGridLines="True">
//aspx.cs file
    Label1.Text = "The selected date is " + Calendar1.SelectedDate.ToShortDateString();

check if it helps you.

answered Jun 24 '15 at 07:42

shreesha

1,811
2
21
30

getting date on label but not fetching data from database as per date. – Jay Desai Jun 25 '15 at 04:54
Better check the query before passing it to OracleDataAdapter.Assign the query to a string variable and then pass it to adapter. – shreesha Jun 26 '15 at 14:43
i have checked all combination of date...but still not fetching the data...want to clear one more thing INPUT_DT field data type is date and data stored like this **18-07-14 19:48:51 ** that means with date and time in same cell...i have selected this format from sqldevloper...I don't know how date time data stored in oracle by default... – Jay Desai Jun 27 '15 at 05:30
1

A DATE in Oracle always has a day component and a time component.You have to extract date part from datetime.check the links (http://stackoverflow.com/questions/13135552/oracle-sql-how-to-remove-time-from-date) [link](http://stackoverflow.com/questions/5542080/how-to-extract-only-date-value-from-date-field-in-oracle) – shreesha Jun 29 '15 at 10:12

Jay Desai · Accepted Answer · 2015-07-01T07:53:39.107

0

Lable1.Text = Calendar1.Selected.ToString("dd-MMMM-yyyy");
OracleDataAdapter a = new OracleDataAdapter("SELECT PALLET_NO, DATA_STS, MERGE, PLANT_CD, SHIFT, RACK_NO FROM WI_PALLET WHERE PROD_CD = 'PET' AND trunc(TO_DATE(INPUT_DT)) = '"+ Lable1.Text+"' ORDER BY PALLET_NO ASC", con);

This is worked for me. Thanks @shreesha for the link

edited Jul 01 '15 at 07:53

answered Jul 01 '15 at 03:36

Jay Desai

821
3
15
42

ASP Calendar in not working properly

2 Answers2