DOCUMENT:Q259192 01-MAY-2001 [foxpro] TITLE :PRB: Updating a Table with DTOC() in an Index Using ODBC PRODUCT :Microsoft FoxPro PROD/VER:WINDOWS:6.0 OPER/SYS: KEYWORDS:kbODBC kbvfp600 kbvfp600bug KbDBFDBC kbGrpDSFox kbDSupport kbCodeSnippet ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft ODBC Driver for Visual FoxPro (Build 6.00.8281.00), version 6.0 ------------------------------------------------------------------------------- SYMPTOMS ======== If you have a Visual FoxPro table containing an index with the DTOC() function in the index key expression and the index was built with CENTURY turned OFF and/or the DATE format was not AMERICAN, adding records or updating the table with the Visual FoxPro ODBC drivers may cause problems. If the index key expression is built using the DTOC() function and the ",1" parameter, your indexes work as expected. CAUSE ===== The DTOC() function returns a character string corresponding to a Date or DateTime expression. The date format of this character string is determined by the SET CENTURY and SET DATE commands. The values placed into the index may vary depending on these environment settings at the time a record is added or updated. The default settings for the Visual FoxPro ODBC driver are DATE is AMERICAN and CENTURY is ON. If your application uses a different date format, it is not able to find those records using the index built with the DTOC() function. RESOLUTION ========== Try one of the following to resolve this issue: - Build your indexes with the DTOS() or DTOC(,1) functions. - Make sure DATE is AMERICAN and CENTURY is ON when building and updating the index in Visual FoxPro. - Re-index the table after updating through ODBC. - Replace the DTOC() function call with the following expression (format may be changed to fit your needs: if your DATE is USA, use "-" instead of "/"): For a 2 digit year: PADL(MONTH(),2,"0") + "/" + PADL(DAY(),2,"0") + "/" + RIGHT(STR(YEAR(),4,0),2) For a 4 digit year: PADL(MONTH(),2,"0") + "/" + PADL(DAY(),2,"0") + "/" + STR(YEAR(),4,0) - Change the DATE and CENTURY settings for the Visual FoxPro ODBC driver using SQL Passthrough commands (this code should be executed AFTER you open the remote view): * to do this in Visual FoxPro * get connection handle to table lnHandle=CursorGetProp("ConnectHandle") * if the DATE format was set to USA when the index was built =SQLExec(lnHandle,"SET DATE USA") * if CENTURY was OFF when the index was built =SQLExec(lnHandle,"SET CENTURY OFF") * perform table updates, index values will be created properly STATUS ====== This behavior is by design. MORE INFORMATION ================ Steps to Reproduce Behavior --------------------------- 1. Create a program and paste the following code into it: *Start of Code Clear Set Century Off ? "Creating table" Create Table ODBCDTOC (Name C(20), Date D) ? "Creating index Name" Index On Name Tag Name ? "Creating index NameDate" Index On Name+DtoC(Date) Tag NameDate ? "Adding record 1" Insert Into ODBCDTOC (Name,Date) Values ("Apple",{^1962-07-14}) ? "Adding record 2" Insert Into ODBCDTOC (Name,Date) Values ("Banana",{^1960-04-01}) Set Order To Tag NameDate ? "Seeking record 1: " ?? Seek(PadR("Apple" ,20)+DtoC({^1962-07-14})), "this should be .T. - as expected" ? "Seeking record 2: " ?? Seek(PadR("Banana",20)+DtoC({^1960-04-01})), "this should be .T. - as expected" Close All ? "Creating DBC" Create Database ODBCDTOC ? "Creating Connection" Create Connection Connect1 ConnString "DSN=Visual FoxPro Tables;UID=;PWD=;SourceDB=" + SYS(5) + SYS(2003) DBSetProp('CONNECT1', 'Connection', 'Asynchronous', .F.) DBSetProp('CONNECT1', 'Connection', 'BatchMode', .T.) DBSetProp('CONNECT1', 'Connection', 'Comment', '') DBSetProp('CONNECT1', 'Connection', 'DispLogin', 1) DBSetProp('CONNECT1', 'Connection', 'ConnectTimeOut', 15) DBSetProp('CONNECT1', 'Connection', 'DispWarnings', .F.) DBSetProp('CONNECT1', 'Connection', 'IdleTimeOut', 0) DBSetProp('CONNECT1', 'Connection', 'QueryTimeOut', 0) DBSetProp('CONNECT1', 'Connection', 'Transactions', 1) DBSetProp('CONNECT1', 'Connection', 'Database', '') ? "Creating Remote View" Create SQL View "View1" Remote Connect "Connect1" As Select * From ODBCDTOC DBSetProp('VIEW1', 'View', 'UpdateType', 1) DBSetProp('VIEW1', 'View', 'WhereType', 3) DBSetProp('VIEW1', 'View', 'FetchMemo', .T.) DBSetProp('VIEW1', 'View', 'SendUpdates', .T.) DBSetProp('VIEW1', 'View', 'UseMemoSize', 255) DBSetProp('VIEW1', 'View', 'FetchSize', 100) DBSetProp('VIEW1', 'View', 'MaxRecords', -1) DBSetProp('VIEW1', 'View', 'Tables', 'ODBCDTOC') DBSetProp('VIEW1', 'View', 'Prepared', .F.) DBSetProp('VIEW1', 'View', 'CompareMemo', .T.) DBSetProp('VIEW1', 'View', 'FetchAsNeeded', .F.) DBSetProp('VIEW1', 'View', 'FetchSize', 100) DBSetProp('VIEW1', 'View', 'Comment', "") DBSetProp('VIEW1', 'View', 'BatchUpdateCount', 1) DBSetProp('VIEW1', 'View', 'ShareConnection', .F.) *!* Field Level Properties for VIEW1 * Props for the VIEW1.customer field. DBSetProp('VIEW1.name', 'Field', 'KeyField', .T.) DBSetProp('VIEW1.name', 'Field', 'Updatable', .T.) DBSetProp('VIEW1.name', 'Field', 'UpdateName', 'ODBCDTOC.name') DBSetProp('VIEW1.name', 'Field', 'DataType', "C(20)") * Props for the VIEW1.order field. DBSetProp('VIEW1.date', 'Field', 'KeyField', .T.) DBSetProp('VIEW1.date', 'Field', 'Updatable', .T.) DBSetProp('VIEW1.date', 'Field', 'UpdateName', 'ODBCDTOC.date') DBSetProp('VIEW1.date', 'Field', 'DataType', "D") Close All ? "Opening Remote View" Use ODBCDTOC!View1 ? "Inserting record 3 into Remote View" Insert Into View1 (Name, Date) Values ("Pear",{^2000-04-01}) ? "Locating record 2: " Locate For Name = "Banana" ?? Found(), "this should be .T. - as expected" ? "Replacing date field with new value" Replace Date With Date() =TableUpdate(.t.) Close All ? "Opening table" Use ODBCDTOC Order Tag NameDate ? "Seeking record 3: " ?? Seek(PadR("Pear",20)+DtoC({^2000-04-01})), "this should be .T." ? "Seeking record 2: " ?? Seek(PadR("Banana",20)+DtoC(Date())), "this should be .T." Close Data All *End of Code 2. Run the program and the results will be displayed. REFERENCES ========== For additional information on the Visual FoxPro ODBC drivers and Dates, click the article numbers below to view the articles in the Microsoft Knowledge Base: Q229854 PRB: ODBC Query Fails with Non-American Date Format Q176572 FIX: ODBC Date Filter Greater Than 12/31/1999 Return No Records Q191926 FIX: VFP ODBC Driver Problem w/ {d 'yyyymmdd'} and Year > 1999 For additional information on indexing with Date/DateTime fields, click the article numbers below to view the articles in the Microsoft Knowledge Base: Q97066 HOWTO: Index a Combined Date and Character Field Q253595 HOWTO: Build Indexes with DateTime Fields and Other Data Types Additional query words: Passthru Passthrough ====================================================================== Keywords : kbODBC kbvfp600 kbvfp600bug KbDBFDBC kbGrpDSFox kbDSupport kbCodeSnippet Technology : kbVFPsearch kbAudDeveloper kbODBCSearch kbODBCVFP600828100 Version : WINDOWS:6.0 Issue type : kbprb ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2001.