Column
Use ADO data sources in Visual FoxPro
Hongjun
ADO is an object-oriented data connection method with many similarities with ODBC. Use ADO as a data source, first require an ADO connection, similar to the ODBC's connection handle; then you need to create a record collection object (Ado Recordset), which is used to store read data, which is similar to Visual FoxPro's temporary table; finally, the OPEN method using the record collection is performed according to the established connection object, which is a SQL query statement, which is similar to the SQLEXEC () function, which is also based on a connection handle to perform a SQL query.
1. Establish a connection (Connection)
ADO also supports ODBC data sources and string connections. If you use an ODBC data source connection, you can create a file data source, steps below:
(1) Open the "ODBC Data Source Manager" dialog box in Control Panel, and select the File DSN tab, as shown in Figure 20-60.
(2) Click the Add button in the dialog to open the Create New Data Source dialog box, select SQL Server in the driver list, as shown in Figure 20-61.
(3) Click the "Next" button, the screen as shown in Figure 20-62 can appear, you can enter the data source name to be established in the text box, such as: linknorthwind. You can also click the "Browse" button later in the text box to select the path saved by the data source file, and the default is "c: / program files / common files / odbc / data sourcees /".
(4) Click the "Next" button to appear as shown in Figure 20-63.
(5) Click the "Finish" button, which will appear "Create a New Data Source" dialog box shown in Figure 20-64. You can enter the instructions for the connection in the "Description" text box, and select the Database Server in the Server drop-down list.
(6) Click the "Next" button, select SQL Server authentication in the screen shown in the 20-65 appearing, and enter the login ID and password.
(7) Click the "Next" button to select the database you want to connect in the screen shown in the appearance of 20-66, such as Northwind.
(8) Click the "Next" button, you can select options such as system message language in the screen shown in the 20-67 appearing.
(9) Click the "Complete" button to display the database connection options shown in Figures 20-68. You can click the "Test Data Source" button to test whether it has been successfully connected, click the "Finish" button, complete the setting, return to the ODBC Data Source Manager dialog, as shown in Figure 20-69.
Figure 20-60 Select the "file DSN" tab
Figure 20-61 Select SQL Server Driver
Figure 20-62 Enter the data source name
Figure 20-63 Click the "Finish" button
Figure 20-64 Select the database server
Figure 20-65 Enter the login ID and password
Figure 20-66 Select the database
Figure 20-67 Click the "Finish" button
Figure 20-68 Data Source Settings Options List
Figure 20-69 After setting completion, the data source is displayed in the list.
Below you can use this file data source to create an ADO connection, the code is as follows:
Local Oconn As Adodb.Connection
Oconn = CreateObject ("AdoDB.Connection") establishes a connection object Oconn.open ("FiledSn = C: / Program Files / CommON files / ODBC / DATA SOURES / LINKNORTHWIND.DSN", "SA", "")
If you are connected in a string, there are two ways to choose: one is an ODBC mode, one is an OLEDB method.
The file data source file is actually a text file, where the server name and database name are saved, locate the save location of the file data source, and use Notepad to open the file, as shown in Figure 20-70.
Figure 20-70 Content in the file data source
Therefore, you can use the contents directly in the OPEN method in the connection object to open the connection in an ODBC mode, the code is as follows:
Local Oconn As Adodb.Connection
Oconn = CreateObject ("AdoDb.Connection") establishes a connection object
Oconn.open ("Driver = SQL Server; UID = SA; PWD =; Database = Northwind; Server = CCB-ZHJ")
From the two connection methods described above, it can be seen that ADO is actually an ODBC-based application that provides an object-oriented data call mode. However, what is OLEDB? OLEDB is a data access component located between the ODBC layer and the application, that is, an OLEDB can be used between ADO and ODBC, and an intermediate layer can be used. The data is called by ADO, first sent to OLEDB, and then hand it over to ODBC. Using OLEDB can improve data access performance. Below is the code to connect to the SQL Server database using OLEDB:
Local Oconn As Adodb.Connection
Oconn = CreateObject ("AdoDb.Connection") establishes a connection object
Oconn.open ("provider = sqloledb.1; data source = ccb-zhj; initial catalog = northwind; uid = sa; pwd =;
Trusted_connection = no ")
Once the connection is established, you can use the EXECUTE method of the connection object to execute a SQL query statement to create a simple record collection object. To better explain this connection process, we compare them to the SPT connection, as shown in Table 20-12.
Table 20-12 Compare data using ADO and SPT
ADO method
*! * Declaration variable
Local Oconn As Adodb.Connection ,;
CSTRUNG AS Character, Ors as adodb.recordset
*! * Establish a connection string
CString = "provider = SQLOLEDB.1; DATA SOURCE = CCB-ZHJ;" ;
"InTIAL CATALOG = Northwind;" ;
"UID = SA; PWD =; trusted_connection = no"
*! * Establish a connection object
Oconn = CreateObject ("AdoDb.Connection")
Oconn.open (cstring) opens the connection
*! * Execute the query, save to the ORS record collection object
ORS = OCONN.EXECUTE ("Select * from customers")> *! * Displays the value of the first field of the record collection
Do While Not Ors.eof
Ors.fields (0) .value Displays a field value
Ors.movenext moves down a record
Enddo
*! * Display the number of fields of the collection
? orse.fields.count
*! * Close the connection object, closing the object collection at the same time
Oconn.close
SPT method
*! * Declaration variable
Local Nhandle As Number, CString As Character
*! * Establish a connection string
CString = "driver = SQL Server; Server = CCB-ZHJ;" ;
"UID = sa; pwd =; database = northwind"
*! * Establish a connection handle
NHANDE = SQLStringConnect (CString)
*! * Execute the query, save to the CURRS temporary table
= SQLEXEC (Nhandle, "SELECT * from Customers", "CURRS")
*! * Displays the value of the first field of the temporary table
Do While Not Eof ()
? Evaluate (Field (1)) Displays a field value
Skip moves a record down
Enddo
*! * Show the number of fields of the temporary table
? Fcount ()
*! * Close the temporary table
Use in currs
Note that in the above code, the FIELDS object of the ADO identifies the first field of the record collection, and the Field () function of Visual FoxPro is identified in 1 of the first field of the table.
2. Establish a record collection (RECORDSET) object
Although the Execute method using the connection object can save the query result in a record collection, the control is limited to the record. For example, in the above-established ORS record collection, the ibe.MovelaSt method cannot be used to move the pointer directly to the tail of the record collection. Therefore, in order to more accurately track data, create a cursor containing data (Temporary tables similar to Visual FoxPro) with the RecordSet object.
For example, the following code has established a Recordset object:
Ors = createObject ("AdoDb.Recordset")
You can use the Open method of the Recordset object to create a cursor. The syntax format of the Open method is as follows:
Ors.open (Source, ActiveConnection, Cursortype, LockType, Options)
Among them, Source is a variable that can be established as a Command object, which can be a SQL statement, a stored procedure, or a table name, etc .; ActiveConnection is a established connection; Cursortype is the type of cursor, and the available values are shown in Table 20-13; LockType Specifies the type of lock, and the available values are shown in Table 20-14; Options is used to specify the type of Source to determine which SQL statement is still a SQL statement or a stored procedure, etc., the available values are shown in Table 20-15.
Table 20-13 CURSORTYPE parameter usable value
Available value description
-1 does not specify a cursor type
0 front roller. You can only browse the record collection forward, for example, with MoveNext can scroll forward. This way can improve the browsing speed, but you can't use Bookmark, RecordCount, AbsolutePosition or AbsolutePage, etc.
1 button collection cursor. Similar to a dynamic cursor, you can see the modifications recorded in the record collection, but you can't see the records added by other users.
2 Dynamic cursor. All the operations of the record collection will be immediately reacted on each user recordset.
3 static cursor. It produces a static backup for the record set, but the new, delete, and update operations of other users do not affect the recordset.
This is similar to the private data working period of Visual FoxPro
Table 20-14 LockType parameter usable value
Available value description
-1 does not specify a lock type
1 read record set, can not change data
2 pessimistic locking method. Data locked all other actions at the time of update, this is the safest lock mechanism
3 optimistic lock mode. Only lock the record only when calling the UPDATE method, and you can still do data update, insert, delete and other actions.
4 Optimistic bulk update. When editing, the record does not lock, change, insert, and delete it is done in batch mode.
Table 20-15 Options parameter available value
Available value description
-1 does not specify the command type
1 Specify Source as a command or stored procedure to call the original definition
2 Specify Source is a table name, the column is returned by the internal generated SQL query
4 Specify Source is a stored procedure
8 default, indicating that the command in the source is unknown
256 Specify Source as a file name of a continuously stored Recordset
512 Specify Source is a table name
Let's take a relatively complete code of acquisition records:
Local Oconn As Adodb.Connection, CSTRUNG AS Character, Ors as adoDb.recordset
CString = "provider = sqloledb.1; data source = ccb-zhj; initial catalog = northwind; uid = sa; pwd =;
Trusted_Connection = NO "
Oconn = CreateObject ("AdoDb.Connection") establishes a connection object
Oconn.open (cstring) opens the connection
ORS = CREATEOBJECT ("AdoDb.Recordset") establishes a record collection object
Ors.open ("Select * from Customers", OCONN, 1, 3, 1) read data, establish a cursor
? ors.recordcount Displays the number of records
The common attributes and methods for recording collection objects are shown in Table 20-16.
Table 20-16 Recording Common Attributes and Methods of Collective Objects
Attribute / method description
Ors.Recordcount numerical type. Return the number of records
Ors.fields.count numerous types. Returns the number of fields
Ors.fields (n) .Name characters. Returns the field name of the Nth field in the collection, where n is 0 represents the first field, and n is 1 represents the second field, and the secondary is pushed.
The maximum is ORS. Fields.count-1
Ors.fields (n) .DATATYPE value. Returns the data type of the field, the ADO data type is shown in Table 20-17
Ors.fields (n) .value or
Ors.fields (cfieldname) .Value returns the value of the norture in the collection. You can also use the field name cfieldName instead of Nors.eOF logical type without knowing the field name. The judgment has been to the tail of the collection, return .t., Indicating that the tail of the collection is
Ors.moveFirst moves pointers to the first record of the collection
Ors.Movelast moves pointers to the last record of the collection
Ors.MovePrev will move a pointer forward a record
Ors.Movenext will move a record backward
Ors.PageCount numerical type. Returns the number of pages of the collection. ADO is not downloading all the data in the collection to the local, but is downloaded by page, which is similar to the distance.
On-demand download in the view
Ors.pageSize numerical type. Back to page size
Ors.properties.count numerical type. Returns the number of attribute settings
Ors.properties (n) .Name characters. Returns the name of the Nth attribute of the collection, where n is 0 indicates the first attribute, and n is 1 represents the second attribute.
Upper, the maximum is ips.properties.count-1
Ors.properties (n) .Value Returns the value of the Nth attribute of the collection
Ors.Requery re-executes the query
Ors.close Close Collection
Table 20-17 ADO data type
Constant value description
AdARRAY is not applied to 0x2000 This is a flag value, which is an array that represents other data types.
Adbigint 208 byte band symbol integers
ADBINARY 128 binary value
Adboolean 11 logic value
ADBSTR 8 Empty Termination String (Unicode)
ADCHAPTER 136 chapter type, indicating a sub-record set
ADCHAR 129 string value
ADCURRENCY 6 currency value, 8 bytes are symbolic integers, range to 1000, 4 digits after decimal point
Addate 7 dates value, a double-precision number, its integer is expressed in the days from December 30, 1899,
Its decimal part is the score of the day
AddBDATE 133 Date (YYYYMMDD)
AddBTIME 134 Time Value (HHMMSS)
AddBTimeStamp 135 Date Time Sign (YYYYMMDDHMMSS plus 12 decimal)
Addecimal 14 represents an exact value of fixed precision and range
Addouble 5 A double precision floating point value
ADEMPTY 0 No value is specified
ADERROR 10 32-bit error code
The file time under Adfiletime 64 DOS / WIN32 is from January 1, 1601.
ADGUID 72 global unique identifier
AdidisPatch 9 A pointer to the Idispatch interface to the OLE object, this type is not supported by ADO
Adinteger 3 4 bytes are symbol integers
AdiunkNown 13 A pointer to the iUnknown interface pointing to the OLE object, this type is not supported by ADO
ADLONGVARBINARY 205 long binary value
Adlongvarchar 201 long string value
Adlongvarwchar 203 long-term termination string value
The AdNumeric 131 has an exact value for fixed precision and range
Adpropvariant 138 A Variant (with an automated variant inequality)
ADSINGLE 4 single precision floating point value
Adsmallint 2 2 bytes have symbol integers
ADTINYINT 16 1 byte Symbol Integers AdunsignedBigint 21 8 bytes unsigned integer
AdunsignedInt 19 4 bytes unsigned integer
AdunsignedSmallint 18 2 bytes unsigned integers
Adunsignedtinyint 17 1 byte unsigned integer
ADUSERDEFINED 132 User Custom Variables
Advarbinary 204 binary value
Advarchar 200 string value
Advariant 12 Automated Variant, this type is not supported by ADO
AdvarnuMERIC 139 represents a variable number of widths, range values for symbols
Advarwchar 202 Empty Termination Unicode String
Adwchar 130 Empty NetioDe string
3. Create a command (Command) object
Command objects are specific to handling various types of commands, especially those that require parameters. Similar to the Connection object, the Command object can run the return record set and the number of commands that do not return the recordset. In fact, if the command does not contain parameters, then it doesn't care whether it is using the Connection object or a Command object or a RECORDSET object. The following command will create a Command object:
OCMD = CREATEOBJECT ("AdoDb.command")
To return a record collection, you can use the Execute method of the Command object. However, different from the Connection object is that the command text must be set in the CommandText property of the Command object, and the command text cannot be used directly in the Execute method. The syntax format of the Execute method is as follows:
Ocmd.execute (Recordsaffected, Parameters, Options)
Among them, RecordSaffected is the number of records affected by the command; the parameters parameter value; Options is the command option, similar to the Options option in the RECORDSET object Open method, see Table 20-15.
The following code is an example of a simple SELECT query using the Command object.
Local Oconn As Adodb.Connection, CSTRUNG As Character, Ors as adoDb.recordset, OCMD As Adodb.command
CString = "provider = sqloledb.1; data source = ccb-zhj; initial catalog = northwind; uid = sa; pwd =;
Trusted_Connection = NO "
Oconn = CreateObject ("AdoDb.Connection") establishes a connection object
Oconn.open (cstring) opens the connection
OCMD = CREATEOBJECT ("AdoDb.command") Create a Command object
OCMD.ActiveConnection = Oconn Settings Activity Connection
Ocmd.comMandText = "Select * from customers" setting command
ORS = OCMD.EXECUTE ()
Do While Not Ors.eof
? ors.fields (0) .value
Ors.movenext
Enddo
As can be seen from the code, the query using the Command object is more cumbersome, and the returned record set is read only by default. You can use the CURSORTYPE and LOCKTYPE properties of the Recordset object to reset. For the available values of these two properties, see Tables 20-13 and Table 20-14. For example, the following statement is set to a static cursor and an optimistic lock method after establishing a Recordset object, which becomes a renewable record collection. Ors.cursortype = 3 static cursor
Ors.lockType = 3 optimistic lock
The biggest advantage of using the Command object is that the parameterized query can be transmitted for the query command or stored procedure. To establish a parameter for the Command object, you need to use the CreateParameter method for the Command object, the syntax format is as follows:
Ocmd.createParameter (Name, Type, Direction, Size, Value)
Where Name is a string value containing the parameter object name; Type specifies the data type of the parameter object, and the available values are shown in Table 20-17; the direction specifies the type of parameter object, the available values are shown in Table 20-18; Specify the maximum character or byte length of the parameter value; Value Specifies the value of the parameter.
Table 20-18 Available value for TYPE parameters
Constant value description
ADPARAMINPUT 1 default. Represents input parameters
AdpaaMinputOutput 3 is expressed as output, output parameters
ADPARAMOUTPUT 2 is expressed as output parameters
AdparamReturnValue 4 is expressed as return value
ADPARAMUNKNOWN 0 is expressed as unknown parameters
The method will return a parameter object after successful, which can be added to the Command object using the Append method of the Parameters collection of the Command object.
In the following code, you can pass two parameters ccountry and ccountry and CCustomerID for the query command, and the parameters will be passed to the query statement in the sequence of established. Note that two methods for parameter assignment: one is to use the createParameter method value parameter; the other is to specify the parameter collection after establishing a parameter object.
Local Oconn As Adodb.Connection, CSTRUNG As Character, Ors as adoDb.recordset, OCMD As Adodb.command
CString = "provider = sqloledb.1; data source = ccb-zhj; initial catalog = northwind; uid = sa; pwd =;
Trusted_Connection = NO "
Oconn = CreateObject ("AdoDb.Connection") establishes a connection object
Oconn.open (cstring) opens the connection
OCMD = CREATEOBJECT ("AdoDb.command")
Ocmd.activeConnection = OCONN
Ocmd.commandtext = "SELECT * from customs where country =? and customerid =?" parameterized query command
Ocmd.Parameters.Append ("CCOUNTRY", 129, 1, 10, "Brazil") directly specifies the parameter value Brazilocmd.Parameters.Append (Ocmd.createParameter ("Ccustomerid", 129, 1, 10)) Not specified
Parameter Value Ocmd.Parameters ("ccustomerid") = "famia" Specifies the parameter value of CCUSTomerid
ORS = OCMD.EXECUTE ()
Do While Not Ors.eof
? ors.fields (0) .value space (10)
?? Ors.fields ("country"). Value
Ors.movenext
Enddo
4. Update
After modifying the data in the cursor, you can send updates to the Update or UpdateBatch method of the RecordSet object, save the modification. Where the UpdateBatch method is used to set up a batch transmission update when the LockType property of the cursor is set to 4.
For example, the following code first acquires customer information for Famia, then modify its companyName field value to "People's Posts and Telecommunications Press", send updates and re-query, you can see that the record has been saved.
Local Oconn As Adodb.Connection, CSTRUNG AS Character, Ors as adoDb.recordset
CString = "provider = sqloledb.1; data source = ccb-zhj; initial catalog = northwind; uid = sa; pwd =;
Trusted_Connection = NO "
Oconn = CreateObject ("AdoDb.Connection") establishes a connection object
Oconn.open (cstring) opens the connection
ORS = CREATEOBJECT ("AdoDb.Recordset") establishes a record collection object
Ors.open ("SELECT * from Customers Where Customerid = 'Famia'", OCONN, 1, 3, 1) reads data, establishing a cursor
Ors.fields ("companyName"). Value Displays the current field value
IF ics.recordcount = 1
Ors.fields ("companyName"). Value = "People's Posts and Telecommunications Press" modified field values
Ors.Update () Send Update
Ors.Requery () Re-execute the query
Ors.fields ("companyName"). Value Displays the modified field value
ENDIF
