Microsoft KB Archive/259192

= PRB: Updating a Table with DTOC in an Index Using ODBC =

Article ID: 259192

Article Last Modified on 10/15/2002

-

APPLIES TO


 * Microsoft Open Database Connectivity Driver for Visual FoxPro 5.0

-



This article was previously published under Q259192



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 &quot;,1&quot; 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 &quot;-&quot; instead of &quot;/&quot;): For a 2 digit year: PADL(MONTH,2,&quot;0&quot;) + &quot;/&quot; + PADL(DAY,2,&quot;0&quot;) + &quot;/&quot; + RIGHT(STR(YEAR,4,0),2)

For a 4 digit year: PADL(MONTH,2,&quot;0&quot;) + &quot;/&quot; + PADL(DAY,2,&quot;0&quot;) + &quot;/&quot; + 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 lnHandle=CursorGetProp(&quot;ConnectHandle&quot;) =SQLExec(lnHandle,&quot;SET DATE USA&quot;) =SQLExec(lnHandle,&quot;SET CENTURY OFF&quot;) 
 * get connection handle to table
 * if the DATE format was set to USA when the index was built
 * if CENTURY was OFF when the index was built
 * perform table updates, index values will be created properly

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol>  Create a program and paste the following code into it: *Start of Code Clear Set Century Off ? &quot;Creating table&quot; Create Table ODBCDTOC (Name C(20), Date D) ? &quot;Creating index Name&quot; Index On Name Tag Name ? &quot;Creating index NameDate&quot; Index On Name+DtoC(Date) Tag NameDate ? &quot;Adding record 1&quot; Insert Into ODBCDTOC (Name,Date) Values (&quot;Apple&quot;,{^1962-07-14}) ? &quot;Adding record 2&quot; Insert Into ODBCDTOC (Name,Date) Values (&quot;Banana&quot;,{^1960-04-01}) Set Order To Tag NameDate ? &quot;Seeking record 1: &quot; ?? Seek(PadR(&quot;Apple&quot; ,20)+DtoC({^1962-07-14})), &quot;this should be .T. - as expected&quot; ? &quot;Seeking record 2: &quot; ?? Seek(PadR(&quot;Banana&quot;,20)+DtoC({^1960-04-01})), &quot;this should be .T. - as expected&quot; Close All ? &quot;Creating DBC&quot; Create Database ODBCDTOC ? &quot;Creating Connection&quot; Create Connection Connect1 ConnString &quot;DSN=Visual FoxPro Tables;UID=;PWD=;SourceDB=&quot; + 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', '') ? &quot;Creating Remote View&quot; Create SQL View &quot;View1&quot; Remote Connect &quot;Connect1&quot; 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', &quot;&quot;) DBSetProp('VIEW1', 'View', 'BatchUpdateCount', 1) DBSetProp('VIEW1', 'View', 'ShareConnection', .F.) DBSetProp('VIEW1.name', 'Field', 'KeyField', .T.) DBSetProp('VIEW1.name', 'Field', 'Updatable', .T.) DBSetProp('VIEW1.name', 'Field', 'UpdateName', 'ODBCDTOC.name') DBSetProp('VIEW1.name', 'Field', 'DataType', &quot;C(20)&quot;) DBSetProp('VIEW1.date', 'Field', 'KeyField', .T.) DBSetProp('VIEW1.date', 'Field', 'Updatable', .T.) DBSetProp('VIEW1.date', 'Field', 'UpdateName', 'ODBCDTOC.date') DBSetProp('VIEW1.date', 'Field', 'DataType', &quot;D&quot;) Close All ? &quot;Opening Remote View&quot; Use ODBCDTOC!View1 ? &quot;Inserting record 3 into Remote View&quot; Insert Into View1 (Name, Date) Values (&quot;Pear&quot;,{^2000-04-01}) ? &quot;Locating record 2: &quot; Locate For Name = &quot;Banana&quot; ?? Found, &quot;this should be .T. - as expected&quot; ? &quot;Replacing date field with new value&quot; Replace Date With Date =TableUpdate(.t.) Close All ? &quot;Opening table&quot; Use ODBCDTOC Order Tag NameDate ? &quot;Seeking record 3: &quot; ?? Seek(PadR(&quot;Pear&quot;,20)+DtoC({^2000-04-01})), &quot;this should be .T.&quot; ? &quot;Seeking record 2: &quot; ?? Seek(PadR(&quot;Banana&quot;,20)+DtoC(Date)), &quot;this should be .T.&quot; Close Data All </li> Run the program and the results will be displayed.</li></ol>
 * !* Field Level Properties for VIEW1
 * Props for the VIEW1.customer field.
 * Props for the VIEW1.order field.
 * End of Code

<div class="references_section">