CODEWITHSUNDEEP
c#sqlms-access
mkh
mkh

Reputation: 1

The selection sql statement is not responding to date value

I am trying to select items from a database where their date equals the date in the Session. It says there is no row at position [0][2], but there is actually a row there. This is my code:

DateTime b = Convert.ToDateTime(Session["Dat"]);
int D = b.Day;
int M = b.Month;
int Y = b.Year;
string dat = M + "/" + D + "/" + Y ;

DataTable m;
m = DAL.SelectFromTable(
   "SELECT * FROM [ToDoList] where [UserID]='" + k + "' and  [D]="+ dat);
if (m.Rows.Count > 0)
{
    Response.Write("</br></br></br><center>" + m.Rows[0][2].ToString() + "</center>");
}

Upvotes: 0

Views: 56

Answers (3)

Gustav
Gustav

Reputation: 55906

There is no reason to reinvent the wheel, just use method ToString:

DateTime b = Convert.ToDateTime(Session["Dat"]);
DataTable m;
m = DAL.SelectFromTable("select * from [ToDoList] where [UserID] = '" + k + "' and [D] = #" + dat.ToString("yyyy'/'MM'/'dd") + "#");

Upvotes: 1

Crowcoder
Crowcoder

Reputation: 11514

Access requires dates to be surrounded by #.

Assuming DAL is not written by you, and you don't really have the option to correctly and securely query the database, you would have to do something like:

...and [D] = #" + dat + "#"

However, @thisextendsthat has good point that this will probably return no results because you would have to have the time portion of the date exactly as the data is in the database and you are only using month, day and year to build the date.

You could also get around the time portion by selecting a range:

... and [D] BETWEEN #" + dat + "# AND #" + // one day greater than your date at midnight + "#"

But if you do that you have to be careful not to create an impossible date like May 32, for instance.

Be sure to thank your teacher for continuing to train students to code insecurely, keeping SQL injection vulnerabilities right at the top of the OWASP top 10, right where it belongs.

Upvotes: 1

thisextendsthat
thisextendsthat

Reputation: 1345

As Crowcoder says, which DB are you using? That will help determine how you ought to be sending dates into your queries.

If the underlying field is a datetime then you may need to explicitly trim the time part from the value in order to compare to a date. In Sql server:

...where CAST([D] as DATE) = [The date passed in from from C#]

Otherwise you might be comparing today at some arbitrary time to today at midnight, which won't give you what you want.

Also, please think think about paramterising your Sql queries - building up a string literal like this is bad practice and leaves your app vulnerable to Sql injection attacks.

Upvotes: 0

Related Questions