Visual Basic ADO Programming

1. Visual Basic ADO Programming 56:150 Information System Design

2. Introduction 1 • Microsoft ActiveX Data Objects (ADO) enables you to write an application to access and manipulate data in a database server through an OLE DB data provider. • High speed, ease of use, low memory overhead, and a small disk footprint

3. Introduction 2 • What’s data provider • A control or object that provides data for use with another control or program. The data provider makes data connectivity much easier by hiding most of the implementation of data storage. • What’s OLE DB • A set of COM-based interfaces provide applications with uniform access to data stored in diverse information sources, or data stores

4. Introduction 3 • To use ADO objects in an application, you must first add a reference to the ADO component. • Start a Standard EXE project and then select Project References. In the Reference window, locate Microsoft ActiveX Data Objects 2.x Library and check the box before it.

5. Main Objects • The ADO object model defines a collection of programmable objects that can be used by any of the Microsoft Visual languages

6. The Connection Object • to establish connections between the client and database server • ConnectionString Property • a long string with several attributes separated by semicolons • “Provider = Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program Files\VB98\Nwind.mdb” • “Provider=SQLOLEDB.1;User ID=sa; password=; Initial Catalog=Northwind; Data Source=EXPERTNEW”

7. The Connection Object • Open Method • CN.open • The open method accepts a number of optional arguments (ConnString, UserID, password, options) • Close Method • CN.Close • Set CN = Nothing (remove the Connection Object from memory)

8. Connection Example Dim dbcon as ADODB.Connection Set dbcon = New ADODB.Connection dbcon.ConnectionString _ ="Provider=MSDASQL.1;Persist Security _ Info=False;Data Source=NWIND" dbcon.ConnectionTimeout = 10 dbcon.Open dbcon.close Set dbcon = Nothing

9. The Command Object • to issue commands, such as SQL queries and updates, to the database • ActiveConnection Property • If ActiveConnection is set with a reference to a Connection Object, the Command object uses an exiting connection. • If ActiveConnection is set with a connection string, a new connection is established.

10. The Command Object • Execute Method • Use the Execute method of the Command object to execute a query, data definition command, or stored procedure. • Set rs = cmd.Execute(NumRecords, Parameters, Options) • Options specify the type of query (in the form of CommandTypeEnum constant) to optimize processing.

11. CommandTypeEnum • adCmdStoreProcThe command is the name of a Stored procedure • adCmdTableThe command is a table’s name. “Select * from table_name” is passed to the server • adCmdTableDirectThe command is a table’s name. More efficient that adCmdTable option • adCmdTextThe command is a SQL statement • adCmdUnknownThe command is unknown (default)

12. Command Example Dim cmd As ADODB.Command Dim rst As ADODB.Recordset Set cmd = New ADODB.Command cmd.CommandText = "select distinct ShipCountry from orders" cmd.CommandType = adCmdText Set cmd.ActiveConnection = dbcon Set rst = New ADODB.Recordset Set rst = cmd.Execute

13. Command Example • You can do delete, update, insert using Command Object with the right sql sentence. Dim cmd As ADODB.Command Dim lngAffected As Integer Set cmd = New ADODB.Command cmd.ActiveConnection = dbcon cmd.CommandType = adCmdText cmd.CommandText = "UPDATE tblOrders SET ShipCountry = 'United States' WHERE ShipCountry = 'USA'“ cmd.Execute lngAffected

14. The Recordset Object • to view and manipulate the results of the query • Open Method • To execute a query • open ( [Source], [ActiveConnection], [CursorType As CursorTypeEnum = adOpenUnspecified], [LockType As LockTypeEnum = adLockUnspecified], [Options As Long = -1])) • Source can be a sql statement, a valid command object, a table name, a query name (Access), a stored procedure name (SQL Server) • Options is a constant that indicates how the provider should evaluate the Source argument if it represents something other than a Command object

15. Cursor Type (CursorTypeEnum)

16. The Recordset Object • Example Dim rst As ADODB.Recordset Dim StrSQL As String Set rst = New ADODB.Recordset StrSQL = "select Description from categories where categoryname = '" & Combocategory.Text & "'" rst.Open Source:=StrSQL, ActiveConnection:=dbcon, Options:=adCmdText

17. The Recordset Object • AddNew: add new rows to recordset rst.AddNew rst.Fields("LastName") = "Smith" rsr.Fields("FirstName") = "Tommy" rst.Update • Use the update method to save the new row. If you attempt to close the recordset with an update pending but haven't explicitly saved the row, you'll get a runtime error

18. The Record Object • Change data • Move to the desired row • Make changes • optionally use update method to save updates rst.Find "[ContactTitle] = 'Owner'" If rst.EOF Then MsgBox "No Match was Found!" Else rst.Fields("ContactTitle") = "Manager" rst.Update

19. The Recordset Object • Delete records • Find the desired rows • Use delete method to delete. rst.Find "[ContactTitle] = 'Owner'" If rst.EOF Then MsgBox "No Match was Found!" Else rst.delete End if

20. The Recordset Object • Other frequently used methods • Cancelupdate, Movefirst, Movenext, Movelast, Moveprevious • Other frequently used Properties • Fields, Filter, RecordCount

21. ADO Data Control • Nothing new but a wrapper for the ADO Recordset object. • Unlike the Recordset object, ADODC is visible at run time. • It will be shown in Sample program.