Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 192308 - Last Review: March 3, 2005 - Revision: 2.2
PRB: View Designer Misreads Date Criteria in ODBC Date Format
This article was previously published under Q192308
After saving and closing a remote view with a date criteria in the Open
Database Connectivity (ODBC) Date Format, the View Designer does not read
the criteria correctly. For example:
{d 'yyyy-mm-dd'}
Use the Create SQL View command to create and modify views. For an existing
view, the following code can be used to capture the existing SQL statement:
MyFile=CURDIR() + 'sqltxt.txt'
SET ALTERNATE TO &MyFile ADDITIVE
SET ALTERNATE ON
?DBGETPROP("Test1","View","SQL")
SET ALTERNATE TO
SET ALTERNATE OFF
MODI FILE &MyFile NOWAIT
One other option is to modify the view in the View Designer. This
necessitates entering the date criteria again before saving.
Microsoft is researching this problem and will post new information here in
the Microsoft Knowledge Base as it becomes available.
In Visual FoxPro 6.0, this problem is further exacerbated by the new
setting, SET STRICTDATE. The default setting of SET STRICTDATE causes the
following error when trying to open the view definition in the View
Designer:
Date/DateTime contains illegal characters.
To avoid this error and modify the view in the View Designer, issue the
following command from the Command window:
Please note that to save any changes in the View Designer to this remote
view, the date criteria must be re-entered. This is true for all versions
of Visual FoxPro listed in the "Applies to" section of this article.
Steps to Reproduce Behavior
- Create a data source to a SQL Server Pubs database where you have
permissions.
- Run the following code in a program file:
CREATE DATABASE test
CREATE SQL VIEW Test1 REMOTE CONNECTION MyPubsDSN AS ;
SELECT * FROM sales WHERE ord_date > {d '1993-12-30'}
USE Test1
BROWSE NOWAIT
*SET STRICTDATE TO 1 && Uncomment For Visual FoxPro 6.0.
MODIFY VIEW Test1
MESSAGEBOX(DBGETPROP("test1","view","sql"))
*SET STRICTDATE TO 0 && Uncomment For Visual FoxPro 6.0.
*MODIFY VIEW Test1 && Uncomment For Visual FoxPro 6.0.
{ / / }
It should be:
{d '1993-12-30'}
Note that the message box displays the correct criteria.
APPLIES TO
- Microsoft Visual FoxPro 3.0 Standard Edition
- Microsoft Visual FoxPro 3.0b Standard Edition
- Microsoft Visual FoxPro 5.0 Standard Edition
- Microsoft Visual FoxPro 5.0a
- Microsoft Visual FoxPro 6.0 Professional Edition
- Microsoft Data Access Components 2.5
| kbcode kbdesigner kbpending kbprb kbsqlprog KB192308 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate