PRB: Updating a Table with DTOC() in an Index Using ODBC (259192)


The information in this article applies to:

  • Microsoft ODBC Driver for Visual FoxPro (Build 6.00.8281.00) 6.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 ",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(<date field>),2,"0") + "/" + PADL(DAY(<date field>),2,"0") + "/" + RIGHT(STR(YEAR(<date field>),4,0),2)

For a 4 digit year:
PADL(MONTH(<date field>),2,"0") + "/" + PADL(DAY(<date field>),2,"0") + "/" + STR(YEAR(<date field>),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:

229854 PRB: ODBC Query Fails with Non-American Date Format

176572 FIX: ODBC Date Filter Greater Than 12/31/1999 Return No Records

191926 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:

97066 HOWTO: Index a Combined Date and Character Field

253595 HOWTO: Build Indexes with DateTime Fields and Other Data Types

Modification Type:MajorLast Reviewed:10/15/2002
Keywords:kbBug kbCodeSnippet kbDSupport kbprb KB259192
©2002 Microsoft Corporation. All rights reserved.