CODEWITHSUNDEEP
c#excelado.net
Aabid Ali
Aabid Ali

Reputation: 109

Compare date value obtained from TextBox in Windows Form, to Date value stored in Excel sheet

I am trying to make date comparisons between a textbox value accepting date and a date type in Excel. Problem is direct string comparisons dont seem to work.I need to select only those rows from excel file where date is lesser than date specified by Eindows forms textBox. Any help would be greatly appreciated. My code snippet:

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\empdetails.xls;Extended Properties=ImportMixedTypes=Text;Excel 8.0;HDR=Yes;IMEX=1;";

OleDbDataAdapter adap = new OleDbDataAdapter("Select * from [Sheet1$] where Date < "+ txtDate.Text + "", connStr);

DataTable viewemployees= new DataTable();

adap.Fill(viewemployees);

dataGridView1.DataSource=viewemployees;

Date is a named column accepting values of date type in Excel. Format: mm/dd/yyyy.

txtDate is a textbox accepting values of same date format.

How do I use formulas in a query against Excel? Not sure about it but I think that DateValue may be of some help here?

Upvotes: 0

Views: 1893

Answers (2)

basti
basti

Reputation: 2689

Had a problem similiar to yours, I've been able to solve it with these Data type mismatch in criteria expression | Access, OleDb, C# answers.

In short terms, you have just to do this:

String sql = "select * from [Tabelle1$] WHERE blahblah "' AND (StartDate <= @curDate AND EndDate >= @curDate)";
OleDbCommand olDbCmd = new OleDbCommand(sql, con);
OleDbParameter curDate = new OleDbParameter("curDate", DateTime.Now);
curDate.DbType = DbType.DateTime;
olDbCmd.Parameters.Add(curDate);
OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(olDbCmd);

This should solve the type-mismatches...

Upvotes: 1

scartag
scartag

Reputation: 17680

I reckon you need to use sql to compare both dates.

And i think you omitted the quote for the date you got from the textbox.

see a slightly modified version of your code below.

string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\sample.xls;Extended Properties=ImportMixedTypes=Text;Excel 8.0;HDR=Yes;IMEX=1;";

OleDbDataAdapter adap = new OleDbDataAdapter("Select * from [Sheet1$] where Date < '"+txtDate.Text+"'", connStr);

DataTable viewemployees= new DataTable();

adap.Fill(viewemployees);

dataGridView1.DataSource=viewemployees;

I hope this helps.

Upvotes: 0

Related Questions