Visual Studio 2k5 Database tools problem with Fox pro 9.0 sp1 ole db provider

I have MicrosoftR Visual FoxProR OLE DB Provider 9.0.0.3504 installed. In the visual database tool, when I run "SELECT DATE() FROM MYTABLE", visual studio will give me the follow error:

Error in SELECT clause: expression near ')'.
Error in SELECT clause: expression near 'FROM'.
Missing FROM clause.
Unable to parse query text.

However in visual studio 2003 on another machine, everything works fine.

I tried to reinstall visual studio 2005 and the fox pro ole db provider many times. I still get the errors. Also, I remember it works on my friend's visual studio 2005.

Can someone give me some help to fix this

Thanks.

Also, "Verify SQL Syntax" returns "This command is not supported by this provider".

Visual FoxPro1

It looks like an SQL server error message rather than VFP. Are you sure you used VFPOLEDB on that connection What's your code

PS: When it works it'd return today's date on all rows.

I agree with Cetin - this is not a VFP Error message, it is a SQL Server error message. Looks like you are connecting to SQL Server, not VFP!

I get a "Query Definitions Differ" Error dialog box. If i press the continue button, the query will run ok and return the right result. It is just the Error dialog box is a bit annoying everytime I start a new query and the Date() field won't show up in the "Criteria Pane" when i build my query. I think the problem lies to the VS 2005 in supporting other OLE DB providers.

"Criteria Tab" Are you talking about VS database tools that lets you modify views using query builder If so it's only for MSSQL server as documented. Though you can use that to test a query against a VFP database it IS for SQL server and naturally parses syntax against MSSQL (and unlike MSSQL it doesn't create a view,SP etc - read documentation, MSSQL only).

It errors because there is no date() function in MSSQL (and designer assumes you're reading the documentation and using it against an MSSQL database).

And I don't understand why should it list date() there. Does it list getdate() when you use it with an MSSQL database