access between query

Question

User-320330690 posted

i am trying to count between a date range using following query , i am using access database and asp.net 2.0

 

 

"SELECT COUNT(USERID) FROM Userinfo Where ( JOININGDATE Between '9/3/2010' and '1/8/2011')"

here is my error

Data type mismatch in criteria expression. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

Source Error: 


Line 249:
Line 250:        cn.CommandText = "SELECT COUNT(USERID) FROM Userinfo Where ( JOININGDATE BETWEEN '9/3/2010' and '1/8/2011')";
Line 251:        string temp4 = cn.ExecuteScalar().ToString();

 




Stack Trace: 


[OleDbException (0x80040e07): Data type mismatch in criteria expression.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1006560
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +255
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +188
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +161
   System.Data.OleDb.OleDbCommand.ExecuteScalar() +116
   Admin_pay_incentives.check_silver_Button1_Click(Object sender, EventArgs e) in d:\Projects\Web Based\Current\OSN\MLM\Admin\pay incentives.aspx.cs:251
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

 

 

Answer 1

User-1757793930 posted

What it is saying is the types dont match, type char and type datetime.

Try the following

"SELECT COUNT(USERID) FROM Userinfo Where ( convert(date,JOININGDATE) Between convert(date,'9/3/2010') and convert(date,'1/8/2011'))"  

In this instance you will surely know that they are all of the same data type.

Cheers

Answer 2

User-1199946673 posted

instead of single quotes, with Access you need to use #. And to avoid problems better use the format yyyy/M/d

#2011/1/9#

Or use parameterized queries:

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access
http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET