CODEWITHSUNDEEP
c#sqldatetimedatagridoledb
Big Al
Big Al

Reputation: 21

Populating a Datagrid with records between a date and time period - Using C# and OLE DB

I'm experiencing difficulties filtering a set of data between two DateTime values.

For example: Retrieve all records From: 24/04/2013 3:54 PM; To: 24/04/2013 4:30 PM.

I'm programming in C# and using OLE DB to pull data from a Access Database. The 'To' and 'From' DateTime values are retrieved from DateTimePicker controls on a GUI. I'm trying to query data in 'receiveDateTime' field of my data source - it is stored in DateTime format in Access.

My code appears as follows:

string SQLQuery = "SELECT EmailID, ServerName, receiveDateTime, Type, status, received, processed"
                + "FROM EmailTable, EmailTypesTable, ServerTable, StatusTable"
                + "WHERE EmailTypesTable.emailTypeID = EmailTypesTable.EmailType "
                + "AND ServerTable.ServerID = EmailTable.serverID "
                + "AND StatusTable.statusID = EmailTable.statusID "
                + "AND EmailTable.receiveDateTime BETWEEN " 
                + fromDateTime.Value.ToString("g") + "AND " + toDateTime.Value.ToString("g")";

loadDataGrid(SQLQuery);

Any solutions or advice would be much appreciate.

Thanks,

Allan.

Upvotes: 1

Views: 797

Answers (2)

Big Al
Big Al

Reputation: 21

For anyone that encounters this problem in future when comparing DateTime value, passing the C# DateTime as an OLE Automation date to the database works!

In order to access this value you use the ToOADate() method.

For example:

SqlConnection con = new SqlConnection(MyconnectionString);
con.Open();
string SQLQuery = "SELECT EmailID, receiveDateTime " 
                + "WHERE EmailTable.receiveDateTime " 
                + "BETWEEN @dateFrom AND @dateTo";

SqlCommand cmd = new SqlCommand(SQLQuery );
cmd.Parameters.AddWithValue("@dateFrom", fromDateTime.Value.ToOADate());
cmd.Parameters.AddWithValue("@dateTo", toDateTime.Value.ToOADate());

It is quite strange, because although the DateTime values appear in general DateTime Format in the DataGrid, the database must read them as such:

General DateTime Format: 26/04/2013 9:47 AM

OLE Automation Date: 41390.4082198032

Thanks for pointing me in the right direction noobob!

Upvotes: 1

noobob
noobob

Reputation: 532

1- It seems you forgot the single quotes between the date values:

string SQLQuery = "SELECT EmailID, ServerName, receiveDateTime, Type, status, received, processed"
                + "FROM EmailTable, EmailTypesTable, ServerTable, StatusTable"
                + "WHERE EmailTypesTable.emailTypeID = EmailTypesTable.EmailType "
                + "AND ServerTable.ServerID = EmailTable.serverID "
                + "AND StatusTable.statusID = EmailTable.statusID "
                + "AND EmailTable.receiveDateTime BETWEEN '" 
                + fromDateTime.Value.ToString("g") + "' AND '" + toDateTime.Value.ToString("g") +"' ";

2- It would be better if you use parameterized parameters too:

SqlConnection con = new SqlConnection(MyconnectionString);
con.Open();
string SQLQuery = "SELECT EmailID, ServerName, receiveDateTime, Type, status, received, processed"
            + "FROM EmailTable, EmailTypesTable, ServerTable, StatusTable"
            + "WHERE EmailTypesTable.emailTypeID = EmailTypesTable.EmailType "
            + "AND ServerTable.ServerID = EmailTable.serverID "
            + "AND StatusTable.statusID = EmailTable.statusID "
            + "AND EmailTable.receiveDateTime BETWEEN @dateFrom AND @dateTo";

SqlCommand cmd = new SqlCommand(SQLQuery );
cmd.Parameters.AddWithValue("@dateFrom", fromDateTime.Value.ToString("g"));
cmd.Parameters.AddWithValue("@dateTo", toDateTime.Value.ToString("g"));
SqlDataReader reader = cmd.ExecuteReader();
//...

You could have guessed the issue by trying to execute this query directly in your database

(I have used SQLConnection, SQLCommand... here, you will need to change that part based on the connection you are using.)

Upvotes: 2

Related Questions