SQL SELECT query not retrieving rows from a simple Access database

Question

The DayDate column in the database is of type DateTime & I'm passing a string formulated using a DateTimePicker in a form. The reader.HasRows always returns false!! I don't know what I'm doing wrong. Any help would be appreciated. The code that I used is below.

if (!this.con.IsConnected())
{
   this.con.Connect();
}

this.cmd = new OleDbCommand("SELECT DayNo FROM [Calendar] WHERE DayDate = " + date + "", this.con.conObj());

this.reader = cmd.ExecuteReader();
this.reader.Read();

int dayNo;
if (this.reader.HasRows)
{
   dayNo = int.Parse(reader[0].ToString());
}
else
{
   throw new InfoException("The system could not locate the date in the system");
}

Can you post the value for date that your passing into the query? Also can you state more clearly what it is your trying to achieve? — James
– James, Commented Dec 18, 2011 at 11:23
Also note that you are calling reader.Read() and then calling reader.HasRows. Either call reader.HasRows first in an enclosing if, or use if(reader.Read()) { //do something}. I personally prefer if(reader.Read()) { } as it's fewer lines of code. — dash
– dash, Commented Dec 18, 2011 at 11:26
Try if (reader.HasRows && reader.Read()) { reader[0] ETC... } — Lloyd
– Lloyd, Commented Dec 18, 2011 at 11:32
I'm actually passing a string for example, something like "12/18/2011". What I'm trying to do is, check if there are any related rows to this specific date. I can easily enter the date but when I try to retrieve the record with this date, it doesn't work. It's as if there are no records with this date. I tried if(reader.Read()) & if(reader.HasRows && reader.Read()) with no luck... — Ramila
– Ramila, Commented Dec 18, 2011 at 11:45
I solved it. I had to make some small changes in the query. I have to admit I don't actually understand why it didn't work. The working piece of code is this - "SELECT DayNo FROM [Calendar] WHERE DayDate LIKE '" + date + "'", this.con.conObj()" Thank you for your assistance, I greatly appreciate it. — Ramila
– Ramila, Commented Dec 18, 2011 at 12:01

James · Accepted Answer · 2011-12-18 12:15:25Z

1

The problem is your comparing a Date value to a DateTime so in essence you could possibly be comparing values like this:

DayDate = "2011-12-18 14:22:54"
Date = "2011-12-18 00:00:00"

You need to truncate the time part from your DB dates, try something like this:

"SELECT DayNo FROM [Calendar] WHERE dateadd(dd, 0, datediff(dd, 0, DayDate)) = " + date

Or if using SQL Server 2008 you can do:

"SELECT DayNo FROM [Calendar] WHERE cast(DayDate As Date) = " + date

answered Dec 18, 2011 at 12:15

James

82.4k19 gold badges172 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL SELECT query not retrieving rows from a simple Access database

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related