Microsoft KB Archive/192308

= PRB: View Designer Misreads Date Criteria in ODBC Date Format =

Article ID: 192308

Article Last Modified on 3/3/2005

-

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

-



This article was previously published under Q192308



SYMPTOMS
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'}



RESOLUTION
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.



STATUS
Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.



MORE INFORMATION
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: SET STRICTDATE TO 0 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. Notice that the date criteria displayed in the View Designer is:

{ / / }

It should be:

{d '1993-12-30'}



Note that the message box displays the correct criteria.

Keywords: kbsqlprog kbdesigner kbprb kbpending kbcode KB192308

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.