0
SELECT DateDiff("n", LastDate, Now()) as datediffs FROM UserLog WHERE ID = 1

When I execute this query from Query Design View (MS Access). This give me different in minutes well like this:

datediffs
---------
2826

But when I execute the same query (including static value in where condition ID = 1) from servlet like this:

strSql = "SELECT DateDiff(\"n\", LastDate, Now()) as datediffs FROM UserLog WHERE ID = 1";
recset=stmt.executeQuery(strSql);

It gives me the following error:

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

So what wrong with me?

Himanshu
  • 31,810
  • 31
  • 111
  • 133
  • Have you checked this? http://support.microsoft.com/kb/216425 - referring to a column that does not exist... – eis Aug 20 '12 at 10:13
  • @eis Yes. I checked it. But here I am using only one column `LastDate` which is available in `UserLog` table. – Himanshu Aug 20 '12 at 10:15
  • You are using two columns, LastDate and ID (`ID = 1`) – Fionnuala Aug 20 '12 at 11:32
  • @Remou But both `LastDate` and `ID` are in my table. – Himanshu Aug 20 '12 at 11:35
  • So does `select top 5 id, lastdate from UserLog` work? – Fionnuala Aug 20 '12 at 11:36
  • @Remou Yes it is executed from servlet. – Himanshu Aug 20 '12 at 11:40
  • I wonder is Now() the problem? Can you try something instead of now()? A real date or Date(). Occasionally, Now can cause a problem if the database has lost references. – Fionnuala Aug 20 '12 at 11:43
  • @Remou I executed `SELECT DateDiff(\"n\", LastDate, '20-08-2012 5:15:10 PM')` Which did not executed. When I tried this one `SELECT LastDate, NOW() FROM ...` it was executed successfully. So I suspect the problem is with `Datediff()` function. – Himanshu Aug 20 '12 at 11:49
  • 1
    That is odd, because datediff works outside of MS Access. Aha http://bugs.sun.com/bugdatabase/view_bug.do;jsessionid=385ad421bcdae65da641072ba3da6?bug_id=4253498 – Fionnuala Aug 20 '12 at 11:52
  • @Remou Ohh.. So I can not use `Datediff()` function of Access from Java. I have to get datediff in minutes from Java. – Himanshu Aug 20 '12 at 11:58
  • Possibly. I notice one guy/ette said it was okay. If you are using a Jet connection, you might like to try ACE. – Fionnuala Aug 20 '12 at 12:02
  • @Remou can you please turn these comments into an answer with some links for [this comment](http://stackoverflow.com/questions/12035536/too-few-parameters-expected-1-error-but-i-am-not-using-any-parameter#comment16068029_12035536) so I can accept it? – Himanshu Aug 20 '12 at 12:07

2 Answers2

1

Write-up of comments from above.

If

SELECT TOP 5 id, lastdate FROM UserLog

runs, then you need to look at the various other parts of your statement. For example, Now() can cause a problem if the database has lost references.

However, you report

I executed SELECT DateDiff(\"n\", LastDate, '20-08-2012 5:15:10 PM') Which did not executed. When I tried this one SELECT LastDate, NOW() FROM ... it was executed successfully. So I suspect the problem is with Datediff() function. (–- @hims056, OP)

Which is odd, because DateDiff notmally works outside of MS Access. However, I notice that this problem is reported in Oracle Bugs. In the comments, one person mentions that in his or her case, the problem was a misnamed column, so it may be possible to use ACE, rather than Jet for the connection. Alternatively, as you say, "get datediff in minutes from Java."

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
0

Try using a PreparedStatement instead:

strSql = "SELECT DateDiff(\"n\", LastDate, Now()) as datediffs FROM UserLog WHERE ID = ?";
PreparedStatement ps = conn.prepareStatement(strSql);
ps.setInt(1, 1);
recset = ps.executeQuery();
Sai Ye Yan Naing Aye
  • 6,622
  • 12
  • 47
  • 65