Lesson 8 - Visual Basic Database Programming

VISUAL BASIC DATABASE PROGRAMMING

8

ActiveX Data Objects (ADO) Microsoft’s latest set of data access objects are the ActiveX Data Objects (ADO). These objects let you access data in a database server through any OLE DB provider. ADO is intended to give you a consistent interface for working with a wide variety of data sources, from text files to ODBC relational databases to complex groups of databases. The way Microsoft implements connections to all those data sources is with the OLE DB set of COM interfaces, but that standard is a very complex one. Our interface to that interface, so to speak, is ADO, a set of objects with properties, events, and methods. Here are the ADOs:

• • • • • •

Connection—Access from your application to a data source is through a connection, the environment necessary for exchanging data. The Connection object is used to specify a particular data provider and any parameters. Command—A command issued across an established connection manipulates the data source in some way. The Command object lets ADO make it easy to issue commands. Parameter—Commands can require parameters that can be set before you issue the command. For example, if you require a debit from a charge account, you would specify the amount of money to be debited as a parameter in a Parameter object. Recordset—If your command is a query that returns data as rows of information in a table, then those rows are placed in local storage in a Recordset object. Field—A row of a Recordset consists of one or more fields, which are stored in Field objects. Events—ADO uses the concept of events, just like other interface objects in Visual Basic. You use event handling procedures with events. There are two types of events: ConnectionEvents (issued when transactions occur, when commands are executed, and when connections start or end) and RecordsetEvents (events used to report the progress of data changes).

The ADO Data-Bound Controls There are three data-bound controls that are specially optimized for use with the ADO data control:

• • •

DataGrid controls DataCombo controls DataList controls

Don’t confuse these controls with the non-ADO optimized data-bound controls like the DBCombo and DBList controls. These controls are specifically designed to work with ADO data controls and won’t work with standard controls like the data control. To add these controls to a program, follow these steps: 1. Select the Project >> Components menu item. Information and Communication Technology Department Palompon Institute of Technology

64

VISUAL BASIC DATABASE PROGRAMMING

8

2. Click the Controls tab in the Components dialog box that opens. 3. Select both the Microsoft DataGrid Control entry and the Microsoft DataList Controls entry in the Controls list box. 4. Click on OK to close the Components dialog box. 5. This adds the DataGrid, DataCombo, and DataList control tools to the toolbox; draw those controls as you want them on your form. Here are the principal data properties you use with these three controls:

• •

•

DataGrid - DataSource = ADO data control’s name. You can also set the AllowAddNew, AllowDelete, AllowUpdate properties to True or False to enable or disable those operations. DataCombo - DataSource = ADO data control’s name; DataField = Name of the field to display in the combo’s text box; ListField = Name of field to display in the list; RowSource = ADO data control’s name; and BoundColumn = Name of the source field with which you can provide data to another control. DataList - DataSource = ADO data control’s name; DataField = Name of the field to display in the current selection, ListField = Name of field to display in the list, RowSource = ADO data control’s name, BoundColumn = Name of the source field with which you can provide data to another control.

Working with Database Objects in Code using ADO Data Control ActiveX Data Objects (ADO) access data from OLE DB providers. The Connection object is used to specify a particular provider and any parameters. To connect to a data source, you use a Connection object. Using that connection, you can create a new record set, and using the Recordset object’s methods and properties, you can work with your data. An ADO transaction marks the beginning and end of a series of data operations that are executed across a connection. ADO makes sure that changes to a data source resulting from operations in a transaction either all occur successfully, or not at all. If you cancel the transaction or one of its operations fails, then the result will be as if none of the operations in the transaction had occurred.

Establishing a Connection The first step in editing an ADO database is to open that database, which is called a data source in ADO terminology, by setting up a Connection object. To use that and other ADO objects in code, you use the Project >> References item, select the Microsoft ActiveX Data Objects Library item, and click on OK, adding the ADO Object Library to your program. A Connection object represents a physical connection to a data source. The Connection object maintains information about the data provider. To create a Connection object, you supply the name of either an ODBC data source or an OLE DB provider. To support as many data sources as possible, you can use ADO and ODBC to access a database.

Information and Communication Technology Department Palompon Institute of Technology

65

VISUAL BASIC DATABASE PROGRAMMING

8

A typical ADO-based application uses the following operations to access a data source: 1. Declare a Connection object variable. 2. Attempt to establish a connection with the data source and have the user authenticated. 3. Execute SQL statement. 4. Close the connection. Now we’re free to create a new ADO Connection object with the Connection object’s Open method: connection.Open ConnectionString, UserID, Password, OpenOptions Here are the arguments for this method:

• • • •

ConnectionString - string containing connection information. UserID - an optional string containing a username to use when establishing the connection. Password - an optional string containing a password to use when establishing the connection. OpenOptions - determines whether the Open method should return after (synchronously) or before (asynchronously) the connection is established. You can set the value to either adConnectUnspecified or adAsyncConnect.

The following example establishes a connection object: Private Sub Form_Load() ‘Declare the connection object variable Dim conStudentDB As Connection ‘Instantiate the object variable Set conStudentDB = New Connection ‘Open the database connection conStudentDB.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & App.Path & “\StudDB.mdb;Persist Security Info=False” End Sub Now we have a connection to the data source. To actually work with the data in that data source, we’ll create an ADO recordset.

Disconnecting from a DataSource Once you finish with the connection, you use the Close method to disconnect from a data source. It is proper coding technique to close all open connections before the application is terminated. The following code closes an active connection to a data source and releases the connection object variable: Information and Communication Technology Department Palompon Institute of Technology

66

VISUAL BASIC DATABASE PROGRAMMING

Information and Communication Technology Department Palompon Institute of Technology

8

67

VISUAL BASIC DATABASE PROGRAMMING

8

conStudentDB.Close ‘Clear the object to free client resources Set conStudentDB = Nothing

Creating a Recordset from a Connection Now that we have created an ADO connection, we can open a recordset from that connection using the Recordset object’s Open method: recordset.Open Source, ActiveConnection, Type, LockType, Options Here are the arguments for this method: • Source - a valid Command object variable name, an SQL statement, a table name, a stored procedure call, or the file name of a Recordset. • ActiveConnection - a valid Connection object variable name or a string containing ConnectionString parameters.

• • •

Type - sets the Recordset type. LockType - a value that determines what type of locking (concurrency) the provider should use when opening the Recordset. Options - a Long value that indicates how the provider should evaluate the Source argument if it represents something other than a Command object, or that the Recordset should be restored from a file where it was previously saved.

Here are the possible values for the Type argument:

• • • •

dbOpenKeyset - opens a dynaset-type Recordset object, which is like an ODBC keyset cursor. dbOpenDynamic - opens a dynamic-type Recordset object, which lets the application see changes made by other users. dbOpenStatic - opens a static-type Recordset object. dbOpenForwardOnly - opens a forward-only-type Recordset object, where you can only use MoveNext to move.

Here are the possible values for the LockType argument:

• • • •

adLockReadOnly - the data is read-only and cannot be altered. This is the default option. adLockPessimistic - the provider ensures successful editing of the records, usually by locking records at the data source immediately upon editing. adLockOptimistic - the provider uses optimistic locking, which locks records only when you call the Update method. adLockBatchOptimistic - the records are locked in batch mode, as opposed to immediate update mode. This option is required for client-side cursors, including disconnected Recordsets.

Here are the possible values for the Options argument:

•

adCmdText - provider should evaluate Source as a definition of a command.

Information and Communication Technology Department Palompon Institute of Technology

68

VISUAL BASIC DATABASE PROGRAMMING

• • • • • • •

8

adCmdTable - ADO should generate an SQL query to return all rows from the table named in Source. adCmdTableDirect - provider should return all rows from the table named in Source. adCmdStoredProc - provider should evaluate Source as a stored procedure. adCmdUnknown - type of command in the Source argument is not known. adCommandFile - record set should be restored from the file named in Source. adExecuteAsync - source should be executed asynchronously. adFetchAsync - after the initial quantity specified in the CacheSize property is fetched, any remaining rows should be fetched asynchronously.

The following example opens a new recordset from an existing connection: Private Sub Form_Load() ‘Declare the connection object variable Dim conStudentDB As Connection ‘Declare the recordset object variable Dim rsStudent as Recordset ‘Instantiate the connection object variable Set conStudentDB = New Connection ‘Instantiate the recordset object variable Set rsStudent = New Recordset ‘Open the database connection conStudentDB.Open “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & App.Path & “\StudDB.mdb;Persist Security Info=False” ‘Open the Recordset for transaction rsStudent.Open “SELECT * FROM Student”, conStudentDB, adOpenStatic, adLockOptimistic End Sub Now that we’ve open our Recordset, we can bind that Recordset to various controls, like textboxes, etc.

Accessing Records in a Recordset Once a Recordset has been created, you can access the fields of each record in one of the following ways:

•

Refer to the name of the field. If you know the name of the field you want to access, you can use the following syntax to access the current value of the field. RecordsetObject!FieldName For example:

rsStudent!LName = “Smith” rsStudent!FName = “John”

Information and Communication Technology Department Palompon Institute of Technology

69

VISUAL BASIC DATABASE PROGRAMMING

•

8

Use the field’s collection. You can use the Recordset object’s Fields collection. This technique is not as efficient when accessing a Recordset. Use the following syntax to access the value of a field using the Fields collection:

Information and Communication Technology Department Palompon Institute of Technology

70

VISUAL BASIC DATABASE PROGRAMMING

8

RecordsetObject.Fields(0) or RecordsetObject.Fields(“FieldName”) For example: rsStudent.Fields(“LName”) = “Smith” rsStudent.Fields(“FName”) = “John”

Binding Controls to Recordsets To bind a control to an ADO Recordset object, you just set that control’s DataSource property to that object, and then set whatever other data properties those control needs to have set. For example, to bind textboxes to an ADO Recordset: Private Sub Form_Load() ‘Declare the connection object variable Dim conStudentDB As Connection ‘Declare the recordset object variable Dim rsStudent as Recordset ‘Instantiate the connection object variable Set conStudentDB = New Connection ‘Instantiate the recordset object variable Set rsStudent = New Recordset ‘Open the database connection conStudentDB.Open “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & App.Path & “\StudDB.mdb;Persist Security Info=False” ‘Open the Recordset for transaction rsStudent.Open “SELECT * FROM Student”, conStudentDB, adOpenStatic, adLockOptimistic ‘Set the DataSource and DataField property Set txtLName.DataSource = rsStudent txtLName.DataField = “LastName” ‘Set the DataSource and DataField property Set txtFName.DataSource = rsStudent txtFName.DataField = “FirstName” End Sub We can also bind more complex controls to a recordset. The following illustrates how to bind complex controls to a recordset. Dim conStudentDB As ADODB.Connection Dim rsStudent As ADODB.Recordset ‘Instantiate a Connection object Set conStudentDB = New Connection Information and Communication Technology Department Palompon Institute of Technology

71

VISUAL BASIC DATABASE PROGRAMMING

8

‘Open a new connection conStudentDB.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & App.Path & “\StudDB.mdb;Persist Security Info=False” ‘Instantiate the Recordset object Set rsStudent = New Recordset ‘Open a new Recordset using the previous connection and return ‘the appropriate records rsStudent.Open “SELECT * FROM Student”, conStudentDB ‘Fill the DataGrid control with data from the Student table Set dtaGridStudent.DataSource = rsStudent

Adding a Record to a Recordset To add a new record to an ADO record set, we use the AddNew method. After you’ve updated the fields of the current record, you save that record to the database with the Update method. The following example uses the AddNew method to add a new record: rsStudent.AddNew rsStudent!LastName = txtLName.text rsStudent!FirstName = txtFName.text rsStudent.Update

Updating a Record in a Recordset After changing the data in a record’s fields or adding a new record, we update the data source to record the changes, using the Update method: recordset.Update For example, to update record in a Recordset (using data supplied by the user in the textbox): Private Sub cmdUpdate_Click() rsStudent!LName = txtLastName.text rsStudent!FName = txtFirstName.text rsStudent.Update End Sub

Deleting a Record in a Recordset

Information and Communication Technology Department Palompon Institute of Technology

72

VISUAL BASIC DATABASE PROGRAMMING

8

To delete a record from the recordset, we must first navigate to the appropriate record. Use the Delete method to permanently remove the record from the recordset and the data source. The following example deletes the current record in the rsStudent recordset: Private Sub cmdDelete_Click() rsStudent.Delete End Sub

Navigating Records in a Recordset ADO provides methods and properties to navigate and monitor recordsets. A recordset’s navigation options may differ, depending on the cursor type and location. You need to consider what navigation requirements are needed before opening a Recordset. Of all the ADO objects, only the Recordset object allows users to navigate through a set of records. Only one record within a Recordset is current at a given time, therefore, the Recordset object supports a number of properties and methods that allow users to navigate through the Recordset.

ADO Navigation Properties The following lists the properties of the Recordset object that are used to navigate a Recordset. Property

Description

AbsolutePage

Sets or returns the database page in which the current record exists.

AbsolutePosition

Sets or returns the absolute position of the current record (this can be affected by record additions or deletions).

BOF

Indicates if the record pointer has moved before the first record.

Bookmark

Returns a unique identifier for the current record. Setting this property to a specific record’s bookmark moves the record pointer to that record.

EOF

Indicates if the record pointer has moved past the last record.

The following example evaluates the EOF property: If rsStudent.EOF then Msgbox “The end of the Recordset has been reached.” End if

Moving to the First Record in a Recordset To move to the first record in an ADO record set, we use the Recordset object’s MoveFirst method. For example: Information and Communication Technology Department Palompon Institute of Technology

73

VISUAL BASIC DATABASE PROGRAMMING

Information and Communication Technology Department Palompon Institute of Technology

8

74

VISUAL BASIC DATABASE PROGRAMMING

8

Private Sub cmdFirst_Click() rsStudent.MoveFirst End Sub

Moving to the Last Record in a Recordset To move to the last record in an ADO record set, we use the Recordset object’s MoveLast method. For example: Private Sub cmdLast_Click() rsStudent.MoveLast End Sub

Moving to the Next Record in a Recordset To move to the next record in an ADO recordset, we use the Recordset object’s MoveNext method. To make sure that we don’t move past the end of the recordset we use the recordset’s EOF property: Private Sub cmdNext_Click() If Not rsStudent.EOF Then rsStudent.MoveNext End If If rsStudent.EOF = true And rsStudent.RecordCount > 0 Then rsStudent.MoveLast End If End Sub

Moving to the Previous Record in a Recordset To move to the previous record in an ADO recordset, you use the Recordset object’s MovePrevious method. To make sure that we don’t move past the beginning of the recordset we use the recordset’s BOF property Private Sub cmdPrevious_Click() If Not rsStudent.BOF Then rsStudent.MovePrevious End If If rsStudent.BOF = true And rsStudent.RecordCount > 0 Then rsStudent.MoveFirst End If End Sub Information and Communication Technology Department Palompon Institute of Technology

75

VISUAL BASIC DATABASE PROGRAMMING

8

Adding a Record to a Recordset using SQL Statements Once a connection has been established, you can begin any transaction to the database. By using the Connection object’s Execute method, you can send SQL commands to the database without having to return records to the client. For example, to add record to a Recordset using SQL statements: Private Sub cmdAdd_Click() Dim conStudentDB As ADODB.Connection Dim rsStudent As ADODB.Recordset ‘Instantiate a Connection object Set conStudentDB = New Connection ‘Open a new connection conStudentDB.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & App.Path & “\StudDB.mdb;Persist Security Info=False” ‘Instantiate the Recordset object Set rsStudent = New Recordset ‘Open a new Recordset using the previous connection and return ‘the appropriate records rsStudent.Open “SELECT * FROM Student”, conStudentDB conStudentDB.BeginTrans conStudentDB.Execute “INSERT INTO Student (LName, FName) VALUES (‘Smith’, ‘Jane’)” conStudentDB.CommitTrans conStudentDB.Close Set conStudentDB = Nothing End Sub We can also insert a record to a Recordset using the values supplied by the user in a textbox or any other control. For example, we have two textboxes named txtLastName and txtFirstName, we can insert record to a Recordset using the following code: conStudentDB.Execute “INSERT INTO Student (LName, FName) VALUES (‘” & txtLastName.text & “’,’” & txtFirstName.text & “’)”

Information and Communication Technology Department Palompon Institute of Technology

76

VISUAL BASIC DATABASE PROGRAMMING

8

Updating a Record in a Recordset using SQL Statements To update a record in a Recordset (when you know the values to update): conStudentDB.Execute “UPDATE Student SET FName = ‘Juan’ WHERE LName = ‘dela Cruz’” OR conStudentDB.Execute “UPDATE Student SET FName = ‘” & txtFirstName.text & “’ WHERE LName = ‘” & txtLastName.text & “’” If you are using the data supplied by the user in the txtLastName and txtFirstName textboxes.

Deleting a Record in a Recordset using SQL Statements To delete a record in a Recordset when you know the values to delete: conStudentDB.Execute “DELETE FROM Student WHERE LName = ‘Smith’” OR conStudentDB.Execute “DELETE FROM Student WHERE LName txtLastName.text & “’ OR FName = ‘” & txtFirstName.text & “’”

=

‘”

&

If you want to use the data supplied in the txtLastName or txtFirstName textbox.

Refreshing Data in a Recordset To refresh data in a Recordset, use the following code: RecordsetObject.Requery For example:

rsStudent.Requery

Information and Communication Technology Department Palompon Institute of Technology

77