Execute Method (ADO Command)
When using the PSQL OLE DB Provider, the RecordsAffected parameter of the Execute Method on a command returns different results based on the type of operation.
SELECT Operations
When performing a SELECT statement, RecordsAffected returns a -1 (negative one), which means that this option is not supported. For example:
cn.Open "Provider=PervasiveOLEDB;Data Source=TestData;"
SQLst = "Select * From MyData”
cmd.ActiveConnection = cn
cmd.CommandText = SQLst
Set rs = cmd.Execute(RecordsAffected)
In this scenario RecordsAffected equals -1.
If you want to obtain the number of records returned by a SELECT query, use the RecordCount property, as shown in the following example:
recordcount = rs.RecordCount
’ number of records on MyData
Batch Insert, Update, or Delete
RecordsAffected will return the correct number of records that the operation affected when performing a batch insert, update or delete.
Example - Batch Insert
cn.Open "Provider=PervasiveOLEDB;Data Source=TestData;"
SQLst = "Insert into MyData(utinyint_, usmallint_,uinteger_, ubigint_, char_, character_, bit_) Values (1, 12, 13, 100, 'testdata', 'chardata', 1)”
cmd.ActiveConnection = cn
cmd.CommandText = SQLst
cmd.Execute RecordsAffected
In this scenario RecordsAffected equals 1.
Example - Batch Update
SQLst = "Update MyData set char_ = ‘SampleTest’ where uinteger_ = 13"
cmd.ActiveConnection = cn
cmd.CommandText = SQLst
cmd.Execute RecordsAffected
In this scenario RecordsAffected equals x, which is all the records that have the value 13.