HOWTO: Use Offline ADO Recordsets in Visual FoxProID: Q191413
|
This article describes how to use offline ActiveX Data Objects (ADO) 2.x recordsets in Visual FoxPro 6.0.
The following code example creates an ADO recordset and opens the recordset using an existing data source, makes a change to the data, and updates all the records that have been updated:
LOCAL lcConnStr,lcSQL,oConnection,rs
lcConnStr="DSN=Visual FoxPro Database;UID=;PWD=;"+ ;
"SourceDB="+HOME(2)+"data\Testdata.dbc;"+ ;
"SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;"
lcSQL="select * FROM orders.dbf WHERE To_Region='WA'"
oConnection=CREATEOBJECT("ADODB.Connection")
oConnection.open(lcConnStr)
rs=oConnection.execute(lcSQL)
rs.close
rs.cursorType=3 && 3=ADOPENSTATIC
rs.cursorLocation=2 && 2=ADUSESERVER
rs.lockType=3 && 3=ADLOCKOPTIMISTIC
rs.open
MESSAGEBOX("Record 1 (before):"+CHR(13)+CHR(13)+ ;
"City: "+rs.fields("To_City").value+CHR(13)+ ;
"Order Amount: "+TRANSFORM(rs.fields("Order_Amt").value))
rs.fields("To_City").value="Bellevue"
rs.fields("Order_Amt").value=rs.fields("Order_Amt").value+1
rs.updateBatch
rs.close
rs.open
MESSAGEBOX("Record 1 (after):"+CHR(13)+CHR(13)+ ;
"City: "+rs.fields("To_City").value+CHR(13)+ ;
"Order Amount: "+TRANSFORM(rs.fields("Order_Amt").value))
rs.close
oConnection.close
RETURN
The results of the preceding program code can be checked by issuing the following commands from the Visual FoxPro Command window:
USE HOME(2)+"data\Orders"
BROWSE FOR To_Region="WA"
Look at the Order_amt column for the first record in the Browse window.
http://www.microsoft.com/data/Here is sample code to persist(save) a recordset:
LOCAL lcConnStr,lcSQL,oConnection,rs,lcFileName
lcFileName=LOWER(SYS(2023))+"\orders.rs"
lcConnStr="Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;"+ ;
"SourceDB="+HOME(2)+"data\Testdata.dbc;"+ ;
"SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;"
lcSQL="select * FROM orders.dbf WHERE To_Region='WA'"
oConnection=CREATEOBJECT("ADODB.Connection")
oConnection.open(lcConnStr)
rs=oConnection.execute(lcSQL)
rs.close
rs.cursorType=3 && 3=ADOPENSTATIC
rs.cursorLocation=3 && 3=ADUSECLIENT
rs.lockType=3 && 3=ADLOCKOPTIMISTIC
rs.open
ERASE (lcFileName)
rs.save(lcFileName)
rs.close
oConnection.close
RETURN
Look for Orders.rs in the Temp directory after running this code. The
SYS(2023) function returns the path to where Visual FoxPro stores its
temporary files which is the Windows' temporary directory by default.
OLE IDispatch exception code 0 from Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager]Data Source name not found and no default driver specified.
For additional information, please see the following World Wide Web URL:
http://www.microsoft.com/data/ado/For more information on ensuring that you have a valid connection string, see the following article in the Microsoft Knowledge Base, copy the Connect String from the Connection Designer and paste it into the preceding code for the lcConnStr variable:
Q186086 HOWTO: Get a File DSN in the Connection Designer
Additional query words: PGFEST600 kbVFp600 kbADO kbADO200
Keywords :
Version : WINDOWS:6.0
Platform : WINDOWS
Issue type : kbhowto
Last Reviewed: July 27, 1999