CODEWITHSUNDEEP
asp.netsql-servergridviewdata-bindingado.net
Por Weiting
Por Weiting

Reputation: 167

can't retrieve the wanted data out from database

I have created an ASP.NET application in Visual Studio 2010. I am having trouble in retrieving the data i wanted from the two dates i selected from the date picker. once i select the 2 dates, it just keep displaying all the data out and not the dates that i have selected.

This is my code:

protected void Page_Load(object sender, EventArgs e)
{
    string startdate = (string)Session["startdate"];
    string enddate = (string)Session["enddate"];
    DateTime one = Convert.ToDateTime(startdate);
    DateTime two = Convert.ToDateTime(enddate);

    if (DateTime.Compare(two, one)>=0)
    {
        SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=ncpsdbb;Integrated Security=True");
        conn.Open();

        SqlCommand sqlcmd = new SqlCommand("SELECT * FROM StudentTransactions WHERE TimeDate BETWEEN '" + startdate + "' AND '" + enddate + "')", conn);

        SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
        DataSet ds = new DataSet();
        da.Fill(ds);

        GridView1.Visible = true;
        GridView1.DataBind();

        conn.Close();
    }
    else
    {
       GridView1.Visible = false;
       string strMsg = " Data not found for the choosen dates.";
       Response.Write("<script>alert('" + strMsg + "')</script>");
    }
}

Upvotes: 2

Views: 301

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1064114

My initial observation is that there is a ) in your SQL with no matching ( - I wonder if your command is simply throwing an exception...?

The first thing I would try is parameterizing it; in addition to being a really really good idea anyway, it could avoid formatting (dd/MM vs MM/dd) issues in the dates:

if(two >= one)
{
    DataSet ds = new DataSet();
    using(var conn = new SqlConnection("Data Source=localhost;Initial Catalog=ncpsdbb;Integrated Security=True"))
    using(var sqlcmd = new SqlCommand("SELECT * FROM StudentTransactions WHERE TimeDate BETWEEN @start AND @end", conn))
    using(var da = new SqlDataAdapter(sqlcmd))
    {
        sqlcmd.Parameters.AddWithValue("start", one);
        sqlcmd.Parameters.AddWithValue("end", two);
        conn.Open();
        da.Fill(ds);
    }
}

Upvotes: 2

SamiHuutoniemi
SamiHuutoniemi

Reputation: 1595

using(SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=ncpsdbb;Integrated Security=True"))
{
    conn.Open();

    SqlCommand sqlcmd = new SqlCommand("SELECT * FROM StudentTransactions WHERE TimeDate BETWEEN @startDate AND @endDate", conn);

    sqlcmd.Parameters.AddWithValue("startDate" <Your start date>);
    sqlcmd.Parameters.AddWithValue("endDate" <Your end date>);

    ...

Also notice the error with parantheses in your command string.

Upvotes: 1

Related Questions