Visual Basic Programming: Much Ado about ADO - Improper Ways of Opening the Recordset Object
(Page 3 of 5 )
One reason why ADO programming is a tricky business is mainly because ADO objects offer more than one method of executing an SQL statement, and the novices are often given to the belief that any way they do it is going to be fine. Well, here’s the catch. Have a look at the following piece of code:
Private Sub Form_Load()
Set Con = New ADODB.Connection
Set Com = New ADODB.Command
Set Rst = New ADODB.Recordset
With Con
.Provider = "SQLOLEDB"
.ConnectionString = "user id= sa; password=; initial catalog=videolib"
.Open
End With
Com.ActiveConnection = Con
Set Rst= Com.Execute (Select * from Actors)
With Rst
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End with
End Sub
This code will work fine as far as establishing connection and fetching and displaying data in the Visual Basic form is concerned. The big shock comes when you try the MovePrevious, Delete and AddNew methods of the Recordset objects. An error pops up saying, “The operation requested by you is not supported by the provider”.
Don’t go by what the error reads. There’s nothing wrong with the provider. The problem lies in how the Recordset object has been created in this example. Actually, when you use Execute method of Connection or the Command object, the Recordset created is by default a read-only and forward-only. That’s why, despite having specified the appropriate cursor type and lock type, the Recordset won’t allow you to navigate backwards or modify the records.
To create a Recordset that allows addition and deletion of records and navigation in both directions, the Open method of the Recordset object must be used with the appropriate cursor type and lock type. Here’s how:
Rst.Open “Select * from Actors”, Con, adOpenDynamic, adLockOptimistic
Next: Wasteful Creation of ADO Objects >>
More Visual Basic.NET Articles
More By Maneesh Makheeja
/ 40 
