Retriving data from Ms access table in between two dates by using C#.net

vishnuvardhan diyyala

15y15.9k.NET

Dear all,
Here what i actually need is i want to display the data in between two dates in MS Access table by using OLEDB connection through datagridview.here table name in tokendetails and column name in software_1 createddate.Please help me to write code for this problem.

i wrote code as bellow.

string

da.Fill(ds,
dataGridView1.DataSource = ds.Tables[
st = "select * from tokendetails where software_1createddate between " + DateTime.Parse(DateTime.Today.AddDays(-10ToShortDateString()) + " and " + DateTime.Parse(DateTime.Today.AddDays(-1).ToShortDateString()) + "";MessageBox.Show(st);OleDbDataAdapter da = new OleDbDataAdapter(st, con);DataSet ds = new DataSet();"tokendetails");"tokendetails"];

In between two dates data available in Table.

but i got error as

Syntax error (missing operator) in query expression 'software_1createddate between 01/07/2011 00:00:00 and 16/07/2011 00:00:00'.

for u r referece i send my application and database file in attachment.pls check at u r end and please suggest me the code.
thanks in advance.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

jose alexander Molano12y ago
muchas gracias... it was solution to my problem!!!

SELECT * FROM tokendetails WHERE software_1createddate BETWEEN #" + DateTime.Today.AddDays(-10).ToShortDateString() + "# AND #" + DateTime.Today.AddDays(-1).ToShortDateString() + "#"
0
Satish Bhat15y ago
Hi Vishnu,

Checked your code, The problem is with the Culture, either change your computer Regional Settings or change the culture of your application.

To change the Regional Settings ref: http://msdn.microsoft.com/en-us/goglobal/bb688124.aspx

To change the application culture use the following code in form initialization code

System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

In this case you will have to format the display of the date field to DD/MM/YYYY.
Open your Columns property of DataGrid go to DefaultCellStyle and put dd/MM/yyyy in Format field.

Also change the datatype of software_1createddate & software_2createddate in db1DataSet.xsd to System.DateTime from System.String

0
vishnuvardhan diyyala15y ago
Dear.,
I checked like that also.but i didn't got correct output.now i send you my application.if u r free pls check ones and suggest me correct code.that wil contain database file and application.
thanks in advance.
0
Satish Bhat15y ago
Try the below query

SELECT * FROM tokendetails WHERE software_1createddate BETWEEN #" + DateTime.Today.AddDays(-10).ToShortDateString() + "# AND #" + DateTime.Today.AddDays(-1).ToShortDateString() + "#"

Since MS Access can't do auto conversion from char/text into date or date time in query, use #
e.g. for 8/5/2011 00:00:00 PM use #8/5/2011 00:00:00#

Also check the date format in MS Access. i.e. DD/MM/YYYY HH:NN:SS or MM/DD/YYYY HH:NN:SS or YYYY-MM-DD HH:NN:SS etc. and pass the parameter accordingly.
+1
Jiteendra Sampathirao15y ago
run a simple query and see if there is an error
0
vishnuvardhan diyyala15y ago
Dear jitendra,
i checked like dat also.that time i got error as "Data type mismatch in criteria expression."Please check onses at u end for this code.in table those column as data/time formate.i need as date time formate.
please check and suggest.Thanks in advance.
0
Jiteendra Sampathirao15y ago
use this query:

select * from tokendetails where software_1createddate between '" + DateTime.Parse(DateTime.Today.AddDays(-10ToShortDateString()) + "' and '" + DateTime.Parse(DateTime.Today.AddDays(-1).ToShortDateString()) + "'

you should use single quotes before and after double quotes as i mentioned.....
+1