OLEDB SELECT Statement

[Magus]

How do I calculate all fields for column X and display the difference in column Y for each row?

I have a column (date) named DateEntered. I want to take that and use DATEDIFF() for todays date and display how old each one is.

I'm currently using: dAdapter = new OleDbDataAdapter("SELECT JobName, Location, JobType, DateEntered, DaysOld FROM pIndex", con);

I've tried using this:
"SELECT JobName, Location, JobType, DateEntered, DATEDIFF('day',DateEntered,Date()) AS DaysOld FROM pIndex"

That doesn't work either. I'm new to databases and probably going about this entirely wrong. Hopefully someone can help point me in the right direction.

Oh. I'm using an MS Access database (mdb). My connection string is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb;";

Here is the error I receive when I try to fill my dataset using DATEDIFF:

"The provider could not determine the Int32 value. For example, the row was just created, the default for the Int32 column was not available, and the consumer had not yet set a new Int32 value."

Any help would be greatly appreciated.

 

[Jeff Johnson]

I've tried using this:
"SELECT JobName, Location, JobType, DateEntered,
DATEDIFF('day',DateEntered,Date()) AS DaysOld FROM pIndex"

It looks like you're trying to use the SQL Server syntax, but I think you
need to use the VB syntax instead since you're going through the Jet driver.
In this case I think you only need to change "day" to "d":

SELECT JobName, Location, JobType, DateEntered,
DateDiff('d',DateEntered,Date()) AS DaysOld FROM pIndex

Ulimately, though, this isn't really a C# question, and you might be better
off asking in a group dedicated to ADO.NET or Jet or something like that.

 

[Arne Vajhøj]

How do I calculate all fields for column X and display the difference in column Y for each row?

I have a column (date) named DateEntered. I want to take that and use DATEDIFF() for todays date and display how old each one is.

I'm currently using: dAdapter = new OleDbDataAdapter("SELECT JobName, Location, JobType, DateEntered, DaysOld FROM pIndex", con);

I've tried using this:
"SELECT JobName, Location, JobType, DateEntered, DATEDIFF('day',DateEntered,Date()) AS DaysOld FROM pIndex"

That doesn't work either. I'm new to databases and probably going about this entirely wrong. Hopefully someone can help point me in the right direction.

Oh. I'm using an MS Access database (mdb). My connection string is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb;";

Here is the error I receive when I try to fill my dataset using DATEDIFF:

"The provider could not determine the Int32 value. For example, the row was just created, the default for the Int32 column was not available, and the consumer had not yet set a new Int32 value."

Jeff has solved your problem.

But you could consider to do that calculation on the .NET side
instead of the DB side.

It would make the code less database dependent.

Arne