Project 6

Project 6: Using ActiveX Data Objects

Objectives

In this lab, you will build and test forms that use ActiveX Data Objects (ADO).

After completing this lab, you will be able to:

® Open a database by using ADO.

® Navigate and manipulate a recordset by using ADO.

® Find information in a table.

® Create a recordset based on a query.

® Use a parameter query.

Exercise 1: Creating and Navigating a Recordset

In this exercise, you will use data access objects (ADO) to open a database and create a recordset.

u Create Categories form

1. Create a new Visual Basic Standard EXE project.

2. Name the default form frmCategories.

3. Add controls to the form, as shown in the following illustration.

Note Because the ID data will not be edited by the user, the Category ID field is a Label control.

u Open a database and create a recordset

1. On the Categories form, declare two form-level variables, as shown in the following code:

Dim cnn as New Connection

Dim rst as New recordset

2. Set a reference to the Microsoft ADO 2.0 Object Library.

3. In Load event procedure of the Categories form, follow these steps:

a. Open a connection to the Nwind.mdb file and save the resulting database object in the variable cnn.

b. Create a recordset by using the Categories table in the Nwind.mdb database, and capture the resulting Recordset object in the variable rst.

c. Move to the first record in the recordset.

d. Read the Category ID, Category Name, and Description fields into the appropriate controls.

e. Create a sub procedure called Fillfields that popullates the label control and text box controls with data from the Categories table. Again, this is a user-created sub procedure.

To see an example of how your code should look:

Private Sub Form_Load()

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source=c:\program files\Microsoft Visual Studio\vb98\nwind.mdb;"
    rst.Open "Select * from Categories", cnn, adOpenDynamic, adLockOptimistic
    rst.MoveFirst

    'Call the user defined procedure that populates the text boxes
    Fillfields

End Sub

Sub FillFields()

' this procedure will populate the fields on the form.

' it will be called from multiple procedures.

    lblCategoryID.Caption = rst.Fields("CategoryID")
    txtCategoryName.Text = rst.Fields("CategoryName")
    txtDescription.Text = rst!Description 'this line of code is a variation of the code above

End Sub

4. Test the application. Is the first record in the Categories table displayed correctly?

u Write code for the Move Previous and Move Next buttons

1. Add code to make the Move Previous and Move Next command buttons functional.

— After a move, fill in the fields on the form with data.

Private Sub cmdNext_Click()
rst.MoveNext
If rst.EOF Then
Beep
rst.MoveLast
Else
Fillfields
End If

End Sub

Private Sub cmdPrevious_Click()

rst.MovePrevious
If rst.BOF Then
Beep
rst.MoveFirst
Else
Fillfields
End If

End Sub

— Test for the beginning and the end of the recordset

u Test the project

1. Run the form.

2. Click the Move Previous button.

3. Click the Move Next button.

You should see different Category records.

Exercise 2: Adding and Editing Records

In this exercise, you will add new records and edit existing records using various ADO methods.

u Modify the form

1. Modify the Categories form by adding the command buttons shown in the following illustration.

u Write code for the Add, Edit, Save, Cancel, and Delete buttons

1. To clear the text fields and enter Add mode, write code for the Add button.

Note Because the Category ID field is a counter field, it will automatically contain a new value as soon as you issue the AddNew method. You can then place the value for this field in the Category ID label. For example, execute the following code immediately after the AddNew method.

rst.addnew ' I'm using rst as the recordset variable

        lblCategoryID.Caption = rst!CategoryID
        txtCategoryName.Text = ""
        txtDescription.Text = ""
        txtCategoryName.SetFocus

2. To enter Edit mode, write code for the Edit button. For more information about editing records, see Updating Records.

rst!CategoryName = txtCategoryName.Text
rst!Description = txtDescription.Text
rst.Update

3. To write changes from the text boxes back to the recordset and update it, write code for the Save button (use the same code from the Edit button).

4. To cancel the pending update and update the fields with the current record in the recordset, write code for the Cancel button.

rst.CancelUpdate
Fillfields

5. To delete the current record, write code for the Delete button. Move to the next record and retrieve the fields.

rst.Delete

rst.updatebatch

If rst.EOF Then
rst.MoveLast

u Test the application

1. Add a new record.

2. Cancel the Add. Is the previous information restored correctly?

3. Add and save a new record.

4. To ensure that you are on the correct record after the Add, test the Move Next and Move Previous buttons.

5. Delete the new record.

6. Try to delete the first category.

The referential integrity constraints on this table should cause a run-time error.

Exercise 3: Finding Records

In this exercise, you will let the user search for specific records in the Categories table, based on text within the Description field.

u Write code for the Find button

I will present this code in class on Tuesday, Nov. 23

u Test the application

1. Run the form.

2. Click the Find button.

3. Enter sweet in the Input box.

You should see two records that contain the text sweet in the description.

Exercise 4: Using Queries

In this exercise, you will use a saved query to return information from the Nwind.mdb database.

u Query for the ten most expensive products

1. Create a new form.

2. The saved query Ten Most Expensive Products in the Nwind.mdb database returns the ten highest priced products. Place a hidden ADO Data control on the form and set the DataSource property of the DBGrid control to the Data control. Then, you can set the Recordset property of the Data control equal to the recordset returned by the saved query.In the Form_Load event procedure on the new form, create a recordset based on this query, and display the results in a DBGrid (OLEDB) control, as shown in the following illustration.

To see examples of how your code should look:

Private Sub Form_Load()

'Establish the connection to the Nwind database

Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;"

'Set the ADO datacontrol = to the Nwind query

Adodc1.RecordSource = "[Ten Most Expensive Products]"

'set the DataGrid = to the Adodc1

Set DataGrid1.DataSource = Adodc1

End Sub

u Test the application

1. On the Project menu, click Project Properties.

2. Change the Startup Object to the new form, and then click OK.

3. Run the form.

You should see the ten most expensive products in the grid.

Exercise 5: Disabling Buttons During Edit

In this exercise, you will enable and disable the appropriate command buttons while the user is navigating or editing data on the Categories form.

u Enable and disable controls

1. When a user navigates in the Categories form, disable the text boxes, and the Save and Cancel button, so changes cannot be made to the data unless the user selects the Edit or Add button, as shown in the following illustration.

2. When a user selects the Edit or Add button, enable the text boxes, and the Save and Cancel buttons. Disable all other buttons on the form.

After you have finished the form, it should resemble the following illustration.

Your code for ButtonEditAddMode should resemble this:

Sub ButtonEditAddMode()

cmdSave.Enabled = True

cmdCancel.Enabled = True

cmdAdd.Enabled = False

cmdEdit.Enabled = False

cmdDelete.Enabled = False

cmdFind.Enabled = False

cmdClose.Enabled = False

cmdPrevious.Enabled = False

cmdNext.Enabled = False

txtCategoryName.Enabled = True

txtDescription.Enabled = True

End Sub

3. In the Save and Cancel buttons, add code that:

— Enables the command buttons Add, Edit, Delete, Find, Move Previous, and Move Next.

— Disables the text boxes.

— Disables the Save and Cancel buttons.

u Test the application

1. Add a new record and save your changes.

2. Edit the new record and save your changes.

3. Edit the new record again and cancel your changes.

Exercise 6: Using a Parameter Query

In this exercise, you will use a parameter query to return sales information from the Nwind.mdb database.

u Use a parameter query

1. Create a new form that uses the Employee Sales by Country parameter query to display sales records in a date range, as shown in the following illustration.

The parameter names for the query are Beginning Date and Ending Date. To see an example of how your code should look:

Private Sub cmdExecute_Click()

'This Code is currently unavailable. I will try to have it ready ASAP....Anthony

2. Test the application.