Visual Basic Programming: Much Ado about ADO - The Purpose of Command Objects

(Page 5 of 5 )

 

Now the only question that remains unanswered is, What the heck is Command object used for? The answer is -- in two cases.

1. When we have to execute a stored procedure created at the backend. We need to use the CommandType property of the Command object to specify that the SQL statement is a stored procedure, and the CreateParameter method to create a parameter that is assigned to the Parameter object. Finally, the Execute method executes the stored procedure.
   
2. When we need to access more than one table through a single Visual Basic form and store them in a forward-only, unalterable recordset. This is accomplished by creating as many Command objects as the tables to be accessed. Every Command object in this case is made to represent the respective table by binding the CommandText property of the Command object with the respective table in the SQL statement:

.CommandText = “Select * from Actors”

Here’s the complete code for accessing two tables -- Actors and Directors, using two command objects with a single Connection object:

Private Sub Form_Load()
Set Con = New ADODB.Connection
Set Com = New ADODB.Command
Set Com1 = New ADODB.Command
With Con
.Provider = "SQLOLEDB"
.ConnectionString = "user id= sa; password=; initial catalog=videolib"
.Open
End With
With Com
.ActiveConnection = Con
.CommandText = “Select * from Actors”
.Execute
End With
With Com1
.ActiveConnection = Con
.CommandText = “Select * from Directors”
.Execute
End with
End Sub

NOTE  Apart from creating multiple Command objects for accessing multiple tables, we can also accomplish the same by creating a single Recordset object, which can be bound to multiple tables using a different SQL statement for each table. This eliminates the need of creating Command object, thus enhancing the performance of the program. Here’s the sample code for the same:

Rst.Open “Select * from Actors”, &  _
“Select * from Directors”, Con, adOpenDynamic, adLockOptimistic
Do While Not <some conditions>
Loop
Set Rst = Rst.NextRecordset
Do While Not <some conditions>
Loop

The NextRecordset method of Recordset object here will take you to the next recordset.

Conclusion

We just saw how the prospect of an efficient and error-free Visual Basic application boils down to one’s familiarity with the ADO concepts. So, before you go on to code that first Visual Basic application, it makes sense to first get your feet in the ADO concepts. For, programming in Visual Basic is more about knowing the ins and outs ADO rather than Visual Basic itself.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Visual Basic Programming: Much Ado about ADO

>>> Be the FIRST to comment on this article!