0

I am vb.net newbie and working on a program which will be used as a daily-worklog. As backend I use MS ACCESS. I store the "datetime.utc.now" time in a field (type:date/time) of MS Access.

This is shown in the database like: dd.mm.yyyy hh:mm:ss

I want to see all entered items of the last 12 hours. I used many different ways, but I´m not able to fix it.

My preferred / logical (for me :-)) way to do this was:

  1. add the parameter:

    mycommand.SelectCommand.Parameters.AddWithValue("date12",OleDbType.DBTimeStamp).Value = DateTime.UtcNow.AddHours(-12)

  2. Query the database

    select * from complaints where entrydate >= @date12;

But I can't figure out why it doesn't work.

Failure message = "data types in criteria expression incompatible"

I assume the problem is the different time formats, but I'm not sure and I have no clue how I could fix it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aeronaut
  • 1
  • 1

2 Answers2

1

MS Access does not use named parameters. You use a ? for all of the placeholders in the SQL statement. You still can and should give the parameter object a name, but the value is matched to the placeholder based on position in the Parameters collection and query string rather than name.

SQL:

"select * from complaints where entrydate >= ?;"

VB:

mycommand.SelectCommand.Parameters.Add("date12",OleDbType.Date).Value = DateTime.UtcNow.AddHours(-12)

Do NOT settle for hard-coding the parameter value. What you can do is use SQL expressions to determine the date value, and avoid both parameters and string concatentation:

 select * from complaints where entrydate >= DATEADD('h', -12, Now())
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Ok, thanks for all of your tips. But as far as i unterstood your comment correctly that i should use expressions I have to have one command for each. E.g. if I wanna see the last 12 hours and 3,6,9 months, I need 4 commands. I thought if I use parameters, then I only change the parameter value and could use only 1 command. As I mentioned above I'm a newbie and the www. gives way to much different ways of how to do a certain step and I thought the parameterized one is the most elegant and safest one (injection). I appreciate any help u give!!! Thanks a lot – Aeronaut Jan 18 '18 at 21:26
  • Using parameters is fine. Showing the DATEADD() version is just there to show the other alternative to concatenating data into the string. – Joel Coehoorn Jan 18 '18 at 21:29
  • i used "mycommand.SelectCommand.Parameters.AddWithValue("date12", OleDbType.Date).Value = DateTime.UtcNow.Date.AddDays(-0.5)" but it Returns not exactly the last 12 hours. it Returns up to 12:00:00 of the previous day from now. it´s blowing my mind – Aeronaut Jan 19 '18 at 07:19
-1

You can change the type to OleDbType.Date or hardcode the query:

select * from complaints where entrydate >= ( Now() - 0.5 )

Trouble inserting DateTime into Access with OleDb

As a side note, most value types are generally safe to hardcode:

"select * from complaints where entrydate >= " & DateTime.UtcNow.AddHours(-12).ToOADate()
Slai
  • 22,144
  • 5
  • 45
  • 53
  • THX for your answer: does 0.5 mean 12 hours i guess, but what if i wanna make the date flexible!?!? E.g.: 12 hours, 3,6,9 months!? – Aeronaut Jan 17 '18 at 22:50
  • @Aeronaut Yes, DateTime in Office is generally stored in number of days since 1/0/1900. You can try the `OleDbType.Date` parameter type for dynamic date. – Slai Jan 17 '18 at 23:08
  • when i use: mycommand.SelectCommand.Parameters.AddWithValue("date12", OleDbType.DBDate).Value = DateTime.UtcNow.Date.AddDays(-0.5) then it Returns Dates up to 17.01.2018 11:45:00 ???? has anyody an idea how i can fix this issue!?!?! – Aeronaut Jan 18 '18 at 20:00
  • `.Date` not `.DBDate` – Slai Jan 18 '18 at 20:02