ActiveX Data Objects (ADO) enable you to access and manipulate data from a variety of different sources. Through ADO, you can connect to various databases such as Access, mySQL, PostgreSQL, MSSQL, Oracle and more. Once connected you can execute SQL against the database, as per normal.
ADO provides a series of objects, methods and properties to make your life as a developer easier. One such object is the RecordSet, which provides a GetRows method. GetRows purpose is to copy a series of records from a populated RecordSet object and place them into an array. You can specify the number of rows to copy, where to start copying from and what fields in the RecordSet you’d like copied. The general format is as follows:
myArray = myRecordSet.GetRows(<number of rows>, <starting position>, <fields>)
After consulting the documentation for the method on MSDN, it appears that calling the GetRows method actually advances the current cursor position in the RecordSet. As an example, consider a RecordSet with the cursor position pointing to the first item. After calling GetRows with a three (3) for the first parameter, the next time you read the RecordSet the cursor will be pointing at the fourth record.
Unfortunately, I was caught out by this feature. Without consulting any documentation on the method first, I expected that calling GetRows would copy an arbitrary number of records from any position in the RecordSet without altering the current cursor position. If it did need to move the cursor to access the rows, I expected that it would return the cursor to the original position.
This is really just a fine implementation issue. I felt that since it was a copy type method, that it wouldn’t have made any modification to the RecordSet object. If you hadn’t of read the documentation, would you expect that it would advance the cursor or leave it in its original position?
Sorry Al, but I stand by my comment of the other day. It makes perfect sense to me that it works the way that it does.
damn, didnt mean to submit yet.
As I was saying, it makes perfect sense, especially in the case of forward only recordsets. You wouldn’t want it to be attempting to move the cursor back causing an error, would you?
Keep in mind, this would be something that ADO implements. As such, getting around things like a forward only recordset wouldn’t be a problem. ADO could traverse the internal data objects anyway it wanted, it could use the current cursor or open a new pointer to the internal data objects to pull out the data.
By your argument, ADO methods such as MovePrevious and MoveFirst should also work on a forward only recordset as they are implemented by ADO.
Yes, in my opinion they should.
This is how I picture it could work:
You declare a recordset forward only and the default structure (internal to ADO) is infact one way, so it is fast. If you then go and try and use MovePrevious, it should do one of two things
a) you pay the performance penalty of having to go back to the start and iterate through to the previous item
b) the internal data structure is manipulated to create a double linked list, which you would initially pay a performance hit for; subsequent MovePrevious executions would be fast however.
Using that sort of logic, you get a little more flexibility. If you know for a fact you don’t need to move backward, you declare a forward only recordset and you gain the performance benefit. If you are highly unlikely to move backward, you could use a forward only and risk the performance hit in the scenario where you need to move backwards. If you know you need to move both directions, then you create a two way recordset set and suffer an overall performance loss for it.