Trey
|
| Posted: 02/21/2003, 8:19 PM |
|
I'm using CodeChargeStudio 1.0.7 programming in ASP accessing a Microsoft Access Database. I'm trying to do basic selections based on an entered range of dates. I have a number of schedules with start dates and I want to print out only the schedules that start within a given start and end dates.
The problem I'm having is CCS appears to be constructing the query to the Access database incorrectly. I have the text box setup as a date data type and and the date data type everywhere else it seems pertinent. For the data source of the table I used the Query Builder. The following is what I get when I look at the actual SQL statement that has been generated:
SELECT Schedules.*, LastName
FROM Schedules INNER JOIN Employees ON Schedules.EmployeeID = Employees.EmployeeID
WHERE (( StartDate <= #{s_StartDate}# ) AND ( Schedules.EmployeeID = {s_EmployeeID} ))
This appears right but it's not what is actually used for the query.
The actual error I'm getting is as follows:
Source: Grid Schedules
Error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. (Microsoft OLE DB Provider for ODBC Drivers)
SELECT Schedules.*, LastName FROM Schedules INNER JOIN Employees ON Schedules.EmployeeID = Employees.EmployeeID WHERE StartDate <= '1/1/2001'
As can be seen the value being compared against the StartDate is being treated as a string (with '') and not a date (with ##). It seems as though data types aren't being checked or something. If anybody has any thoughts please let me know. Thanks in advance.
Trey
|
|
|
 |
Edd
|
| Posted: 02/23/2003, 10:17 PM |
|
Validate your project settings and see what date format is set.
Also look at the parameters for the fetch in the Query Builder of the record set - it has a format option as well.
As a note - try to stick to the international 'YYYY-MM-DD' format for retrieving data - it will give you less grief.
Hope this helps.
Edd
|
|
|
|
Trey
|
| Posted: 02/27/2003, 7:06 AM |
|
I tried what you mentioned (changing the project settings) but that didn't really change anything. I think the problem is more that CCS is treating the date as a string instead of an Access date (which needs to be encapsulated by #). I tried some other things and found out that I can statically build the SQL statement but this necessitates that all my search fields (s_StartDate, s_EndDate, and s_EmployeeID) must not be null. Thoughts?
Trey
|
|
|
|
|
