on 07-31-2008 3:15 PM
Details: ADO Error Code: 0x80004005
Source: Microsoft OLE DB Provider for Visual FoxPro
Description: SQL: Column 'DATETIME' is not found.
Native Error: 806 [Database Vendor Code: 806]
I get this error when trying to refresh a report. I cannot do any datetime record selection on this report or i get this error, i do not get the error if I don't use record selection for a datetime field. Please help. We are running Crystal XI and 2008 on different client computers with a Visual Foxpro backend through an OLE DB connection.
Edited by: Nick Cirksena on Jul 31, 2008 4:15 PM
Edited by: Nick Cirksena on Jul 31, 2008 4:16 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Was there ever a solution to this problem?
To recap, it seems that Visual FoxPro v.9 database files can only be accessed via OLE DB (ADO). Microsoft seems to have last supported this in 2008 with the OLE DB download cited as a replacement for the ODBC VFP driver.
So, yes, you can easily see VFP9 tables once you have configured OLE DB.
However, there continues to be a problem with OLE DB trying to filter VFP9 records in a datetime formatted field. I am curious if this is an OLE DB problem and has nothing to do with Crystal. Regardless, not being able to filter records based on date is quite limiting.
I think I saw another post where one defined the field type as DATE (only) and was able to get around this. Is this a possible fix? Could another fix be (I'm terrible at Crystal formulas): define a report variable called "new_cust.entrydate" and populate that with the extracted date-only valaue from cust.entrydate. Then have the filter formula filter on that.
(So {cust.entrydate} >= DateTime (2010, 01, 01, 00, 00, 00) would instead be written:
define a new report field
{new_cust.entrydate} = Date(cust.entrydate)
then filter by
new_cust.entrydate >= Date (2010,01,01)
My only current work-around has been to pull the report with all data (no filtering), then apply a filter and chose to NOT refresh the data.
Can anyone comment?
Thank you
John Howard
CI3 Partners
Dallas TX USA
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Please re-post if this is still an issue to the Data Connectivity - Crystal Reports Forum or purchase a case and have a dedicated support engineer work with you directly
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I am still facing the same issue while filtering over a set of available dates, the datatype in database is datetime. and the genetated crystal report query is:
{goodsrvc.dtcreated} = DateTime (2009, 09, 25, 12, 00, 05)
The datetime entry in the database is '9/25/2009 12:00:05 PM'
Error:
Failed to retrieve data from the database.
Details: ADO Error Code: 0x
Source: Microsoft OLE DB Provider for Visual FoxPro
Description: SQL: Column 'DATETIME' is not found.
Native Error: [Database Vendor Code: 806]
I am succesfully able to filter over the other datatypes, but fails for datetime.
Kindly leave your suggestions on the issue.
I am still facing the same issue while filtering over a set of available dates, the datatype in database is datetime. and the genetated crystal report query is:
{goodsrvc.dtcreated} = DateTime (2009, 09, 25, 12, 00, 05)
The datetime entry in the database is '9/25/2009 12:00:05 PM'
Error:
Failed to retrieve data from the database.
Details: ADO Error Code: 0x
Source: Microsoft OLE DB Provider for Visual FoxPro
Description: SQL: Column 'DATETIME' is not found.
Native Error: [Database Vendor Code: 806]
I am succesfully able to filter over the other datatypes, but fails for datetime.
Kindly leave your suggestions on the issue.
I have this same issue in the same environment (although I am using Crystal XI) and I have not been able to resolve it. I get this error when trying to refresh the report. I cannot do any date record selections on any report.
Details: ADO Error Code: 0x80004005
Source: Microsoft OLE DB Provider for Visual FoxPro
Description: SQL: Column 'DATETIME' is not found.
Native Error: 806 Database Vendor Code: 806
I am a new user - and not a developer. I need a simple, straightforward solution pls.
Thank you.
I had the exact same problem as described, same environment. I was able to work around the error. I had 2 parameter fields in the report set to field type Date. Even though the database really has a field type of date, changing it to Date Time resolved the issue. The data in the database has no time associated with it, but it still accepted it.
Were you able to resolve this and if so, what did you do?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Nick,
Can you please try to filter using the following in record selection and check still you are getting said error.
year({Orders.Order Date}) =2003 and month({Orders.Order Date}) = 12 and day({Orders.Order Date}) = 02
Thanks,
Sastry
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nick
You are using Visual Foxpro, and you have mentioned that you are getting this particular error since you have switched to OLE DB from ODBC.
Try creating new reports using the same OLE DB connection and see if it works.
If the new reports work with the same OLE DB connection, then please check if there have been any changes made to the database.
Hope it helps!!
Sourashree
Hi Nick,
Can you try this small step mentioned below?
Please write a SQL query and run it against your database which retrives the date time filed from your database. once that is done, copy the query and 'Add command' in crystal reports and paste that query. If even this does not work, then please please check the version of the OLE DB driver being used and upgrade it to the latest one.
Also, you have mentioned that the report worked fine with ODBC connection. Please check the date time field in that report and copy the "Show SQL Query" from there and paste it in the OLEDB Connection "Add command".
This should fix the issue.
Please let us know if this helps!
Regards,
Abhishek.
Also, you have mentioned that the report worked fine with ODBC connection. Please check the date time field in that report and copy the "Show SQL Query" from there and paste it in the OLEDB Connection "Add command".
This didn't work. Also, this is a Foxpro Database. How do I write a SQL query for VFP?
Hi Nick !!
If the issue is happening with all the versions of Crystal Reports and on all the machines, I would suggest you to check the exact data type of the field (that you are using for record selection) in the database first.
If you are using a record selection formula, then please make sure you are using the correct function and the data type matches the database.
Please let me know after you try this.
Regards
Sourashree Ghosh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.