10-databases.htm; May 4, 2004

Accessing Database Files

Simple Table Processing

OBJECTIVES:

· Become familiar with database terminology.

· Create a project to display data for a single database table.

· Use a DataGrid control.

· Bind and display data from databases to textbox and label controls.

· Write procedures to navigate table rows in a dataset.

· Display a row number on a form.

· Create a parameterized query.

· Write procedures to perform data maintenance activities including Add, Edit, and Delete rows.

· Use data validation routines in a data maintenance program.

---

Database Files

A database – a special repository—sometimes a single file, sometimes multiple files—used to store and to retrieve information.

Relational database – a specific type of database where data rows are stored in separate tables, and the tables are related to each other by key values (see Figures 10.0 and 10.1).

VB.NET enables both system users and system developers to store and retrieve data for many different database products including, but not limited to:

· Oracle and DB2 for large systems—these are competing relational database management systems.

· Microsoft SQL Server for mid-sized systems and larger scalable systems.

· Microsoft Access and other small-sized, individual user or small group systems.

VB.NET uses ADO.NET – a database technology that supports connection to different database products.

ADO.NET stores and transfers data using the Extensible Markup Language (XML).

ADO.NET uses two basic types of connections to databases across networks:

· SQLClient – used to connect to Microsoft's SQL Server DBMS.

· OLEDB – used to connect to all other database formats.

We will use Microsoft Access in this chapter simply because an Access database is a single file (file name extension of MDB) and easily portable for student work.

Further, the connection to an Access database is made in the same fashion as the connection to other database products.

Microsoft Access uses a database "engine" named the Jet database engine. Access 2000 and 2002 use version 4.0 of the Jet database engine.

In the real world, large corporate databases are created and maintained by specialists known as database administrators.

Sometimes system users create their own small databases in order to support their managerial decision-making processes.  Microsoft Access is often used to build these small databases.

Database Concepts and Terminology

A database stores data about individual entities in separate tables.  Examples of entities in a university database include Students, Courses, and Enrollment in courses.

Figure 10.0 shows an entity-relationship diagram for tables in the VB University database.

Figure 10.0

As you can see from Figure 10.0, a database can store many tables in a single file.

Figure 10.1 shows details about the data stored in the Student table of the VB University database used for this set of notes.

Figure 10.1

Each table consists of rows and columns.

· Row = row for an individual student; also referred to as a record.

· Column = field of data stored for each student such as the SSN, Last Name, First Name, etc.

Each row in a table is uniquely identified by the primary key field.  Here the primary key field for the Student table is the StudentSSN (social security number).

VB programs that process database tables have mechanisms for keeping track of the current row that is being processed.  This is termed the current row concept.

XML Data

XML is an industry-standard format used to store and transfer data.

You do not need to know how to write XML to use VB to program database applications. VB will automatically generate any necessary XML. It does help to understand basic facts about XML.

Proprietary database formats typically store data in binary format – proprietary data cannot be processed by other systems or pass through Internet firewalls.

XML data is stored as plain text identified by tags like HTML tags. You can edit an XML file with a plain text edit such as Notepad, and the data can be transferred through Internet firewalls.

Example XML tagged data:

<b>Bold text is here.</b><i>Italic text is here.</i>

Tags can also be used to identify fields. Here the Student table's SSN, and LastName fields (columns) are stored using XML.

<?xml version="1.0" encoding = "UTF-8"?>

<dataroot xmlns:0d = "urn:schemas-microsoft-com:officedata">

<Student>

<SSN>111-11-1111</SSN>

<LastName>Able</LastName>

</Student>

<Student>

<SSN>222-22-2222</SSN>

<LastName>Airhead</LastName>

</Student>

</dataroot>

An XML schema file can also be used to describe fields (columns), data types, and any constraints such as fields that are required. Each VB.NET project that connects to a database has an XML schema file. The data in the XML data file is validated against the data definitions in the XML schema file.

---

ADO.NET

ADO.NET supports database access using forms developed for either a Windows or Web Form environment. These notes only cover simple database operations for Windows projects.

ADO.NET provides controls that you add to a form that are used to connect to and manage data in a database table. Columns from a database table are bound to database controls.

Some of the controls you've already used such as labels, textboxes, and combo boxes can be found to database columns. Other special data controls such as the DataGrid and DataList controls will be demonstrated in these notes.

Connecting to a Database with Visual Basic

Figure 10.2 shows the steps in setting up a database connection.

Figure 10.2

1. Configure a connection. The connection links to a data source – a data source is either a specific database file and/or a server computer.

2. Configure a data adapter. A data adapter handles data retrieval and updating. The data adapter creates a dataset. A dataset stores rows that have been retrieved. You can actually do this step first and create the connection at the same time that you configure the data adapter.

3. Add controls to the form and set properties of the controls to bind the controls to the columns of a specific table in the dataset.

4. Write VB code to fill the dataset.

Locating a Database

In an operational business environment, servers are usually not moved around. Thus, when connection information is generated for a server, you can install the application program on any client computer and connect to the server.

For our projects, you may work at home or in the computer laboratory. We will use a Microsoft Access because it is easily portable and connections made to it are generated in the same fashion as for other databases such as Oracle or SQL Server.

You should always store your database file on drive and folder: C:\Temp – this folder is setup at SIUE with read/write permissions. If you generate connection information on your home computer with the database file located on C:\Temp, then bring the project to the university for additional work and testing or submit the project for grading, you can be assured that the connection information will work satisfactorily. We will treat C:\Temp as our server location.

Creating a Connection

The Connection object links your application program to a specific file or database. ADO.NET provides two types of Connection objects: OleDbConnection and SqlConnection. As was noted earlier SqlConnection is used only for Microsoft SQL Server. OleDbConnection is used for all other database connections such as Oracle and Microsoft Access. See Figure 10.3.

Figure 10.3

The Server Explorer window can be opened by accessing the View-Server Explorer menu option (see Figure 10.4). This window lists all data connections and servers that are available (if there are any).

Figure 10.4

Four different techniques enable creating a new connection.

1) Right-click on Data Connections in the Server Explorer – select Add Connection from the shortcut menu, or

2) Click Connect to Database button in the Server Explorer, or

3) Click the Data tab in the Toolbox – drag the OleDbConnection to the form – select ConnectionString in the properties window and click on New Connection, or

4) Wait until later when you create a data adapter – the Data Adapter Configuration Wizard has a button to create a new connection.

Setting Up a Data Adapter

A data adapter passes data back and forth between a data source and your program. This is done through the use of the Structured Query Language (SQL)—you'll study SQL in detail in your database class later in your studies. Fortunately, SQL is fairly standardized across all DBMS platforms so that once you learn it, you can use with MS Access, MS SQL Server, Oracle, etc.

The data adapters available with ADO.NET are:

· OleDbDataAdapter – used only with OleDbConnection used with all other databases such as Oracle and Microsoft Access.

· SqlDataAdapter – used only with SqlConnection for Microsoft SQL Server databases.

You can add a new data adapter in several different ways:

1) Add an OleDbAdapter control from the Data tab of the toolbox – this opens the Data Adapter Configuration Wizard (This Approach Is Easiest to Use). This approach also enables you to create a connection or use an existing connection while you configure the data adapter control.

2) Drag a table name from the Server Explorer to the form – this adds a new connection and data adapter to the project.

3) Ctrl+click on the field names you want to include and then drag them as a group to the form – this also creates a new connection and data adapter.

The Data Adapter Configuration Wizard is nice because it walks you through the steps to selecting fields; however, using the second two approaches listed above will also automatically configure the data adapter.

---

In-Class Exercise

Copying the Database and Starting the Project

· Copy the VBUniv.Mdb Microsoft Access database from the class server to your client computer to drive and folder: C:\Temp.

· Begin a new project – name it Ch10VBUniversity.

· Name the form frmStudent. Set the project's startup object to the new form name.

· Set the form's startup position as center of the screen.

· Set the form's text property to VB University Student Information.

· Copy the VBUniv.MDB database file from C:\Temp to your project folder. This will be a copy for use in the event that the file on C:\Temp becomes corrupted.

o This provides you with two copies of the database – one with your project, and a second backup copy to use if your computer program makes errors in recording data to the database at C:\Temp.

Configuring a Data Adapter and Connection

· Add a data adapter by using the Data Adapter Configuration Wizard – add the OleDbDataAdapter control from the toolbox Data tab by double-clicking. This will open the wizard shown in Figure 10.5.

· Click Next.

Figure 10.5

· The Choose Your Data Connection window appears. You could select an existing connection; however, for this project you practice as if you need to create a new connection.

· Click the New Connection button shown in Figure 10.6.

Figure 10.6

· Figure 10.7 shows the Data Link Properties page that opens next.

· By default, the Connection tab is selected because VB.NET assumes you'll be connecting to a Microsoft SQL Server database (their preferred database) – you need to click the Provider tab.

· Select the Microsoft Jet 4.0 OLE DB Provider (or latest Jet OLE DB Provider) and click the Next button.

Figure 10.7

· The Connection tab shown in Figure 10.8 is used to configure the connection. Use the Browse button to locate the VBUniv.MDB database file at C:\Temp. Select the file when you find it.

· Leave the User name as Admin with no password—a password is not needed for Microsoft Access as it is basically a single-user database management system.

· Click the Test Connection button. The connection should succeed as shown in Figure 10.8 – if it doesn't, use the Browse button again to ensure you have the correct database or use the Provider tab to ensure you have the correct OLE DB Provider. Click OK to close the Data Link Properties page. The Advanced and All tabs are not used.

Figure 10.8

· You've just completed configuring your connection and the wizard returns you to the Choose Your Data Connection window shown earlier in Figure 10.6. Click Next.

· The next window is the Choose a Query Type screen shown in Figure 10.9.

o For Microsoft Access, you have a single choice – Use SQL statements.

o Only the Microsoft SQL Server or Oracle databases support the other two options (Create new stored procedures or Use existing stored procedures).

o A query is a SQL command that retrieves the desired data rows and columns from a database table. Click Next.

Figure 10.9

· Figure 10.10 shows a form used to write SQL statements. You can either type the SQL statement into the form as shown or click the Query Builder button.

· The SELECT * FROM Student; statement shown in Figure 10.10 will select all rows and columns (this is the meaning of the asterisk * ) from the STUDENT table.

· We will elect to use the Query Builder.

Figure 10.10

· Clicking the Query Builder button displays the screen shown in Figure 10.11. All of the tables in the VBUniversity database (States, Course, Enrollment, and Student) are listed and can be added to the Query Builder by double-clicking or selecting and clicking the Add button.

· If the Add Table window is not shown, right-click on the Query Builder window to open the Add Table window.

· Select the Student table and close the Add Table window.

Figure 10.11

· The Query Builder window interface is used to select the columns from the STUDENT table to include in the SQL Select statement. The columns (fields) are selected in the order in which they are checked. Notice that the output will be sorted by student's LastName column.

· Select ONLY the StudentSSN, LastName, FirstName, and MiddleInitial columns.

· You can specify the data rows to be sorted by last name as shown in Figure 10.12 by selecting the Sort Type.

Figure 10.12

· When you finish with the Query Builder click OK. You will be returned to the Generate the SQL Statements form window. Click Next.

· Click Finish on the View Wizard Results window.

Prior to leaving the wizard, the system may display the popup shown in Figure 10.13 asking if you want to include or not include the password in the ConnectionString. With a Microsoft Access database, it does not matter since the password is blank.

o You should just click the Include password button and proceed. If the form does not appear, don't worry about it.

Figure 10.13

You are now finished with the Data Adapter Configuration Wizard.

· Examine your VB project form. You will discover the addition of two controls in the component tray named OleDbDataAdapter1 and OleDbConnection1.

· Data adapters are often referenced in code and the prefix "da" is used to name them (your book uses db, but most books use da). Rename the OleDbDataAdapter1 as daStudent.

· Rename the OleDbConnection1 control conVBUniversity.

---

Defining a Dataset

A dataset is a set of data stored in the client computer's memory. Data is copied over the network to the client computer – the data connection is not kept active with ADO.NET.

A dataset can store data rows from more than one table, but we will focus on single-table processing in these notes.

To define a dataset:

Ø Select the OleDbAdapter named daStudent,

Ø Select Generate Dataset from the Data menu, or

Ø Right-click and select from the shortcut menu.

Ø Name a dataset with the prefix "ds"—here we will name the dataset dsStudent.

Figure 10.14

After you click OK, you'll see the dataset listed in the system tray with the name DsStudent1 – Visual Basic automatically adds the digit 1 to the first dataset, 2 to the second dataset, and so forth.

The Fill Method

At runtime, the data adapter's Fill method is executed to fill the dataset.

This is usually coded in the Form_Load event for Windows applications. Examples:

DataAdapterName.Fill(DataSetName)

daStudent.Fill(DsStudent1)

When there is more than one table in a dataset, you can specify the dataset name and table name.

daStudent.Fill(DsStudent1, "Student")

Binding Data to Controls

A dataset is bound to controls on a form by setting control properties.

When binding to a DataGrid, these properties include the:

· DataSource property – set it to the name of the dataset.

· DataMember property – set it to the name of the table.

---

In-Class Exercise

Bound DataGrid Control Example

Continue the in-class exercise by generating a dataset for the student data adapter and by adding a DataGrid control to the form.

· Select the OleDbAdapter named daStudent and right-click the adapter and select Generate Dataset select from the shortcut menu.

· Name the dataset dsStudent. Note that in the Solution Explorer now displays a new file named dsStudent.xsd.

o This is the XML code that defines the dataset schema.

o You can click the file to open it in order to examine the XML code – you will not need to modify the code.

· Notice that the dataset shown in the system tray is named DsStudent1.

· Click the Toolbox-Windows Forms tab and add a DataGrid control to the form.

o Name the DataGrid control dbgStudent (the three-character prefix for a DataGrid is dbg although some books use dgr).

o Set the DataSource to DsStudent1.

o Set the DataMember to Student.

o Set the PreferredColumnWidth property to 120.

· Double-click the form to open the Form_Load event and use the Fill method to fill the dataset.

'Project: Ch10VBUniversity

'D. Bock

'12/04/03

Option Strict On

Public Class frmStudent

Inherits System.Windows.Forms.Form

[+] Windows Form Designer generated code

Private Sub frmStudent_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Try

'Fill dataset and bind the grid

daStudent.Fill(DsStudent1)

Catch

MessageBox.Show("Project Load Failed" & ControlChars.NewLine & ex.Message, "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

Me.Close()

End Try

End Sub

End Class

· Figure 10.15 shows the form at run time.

Figure 10.15

· Notice that the grid requires adjustment for columns widths to display the data properly and that a larger grid is more desirable than a smaller one.

· Scroll bars are automatically added when the system detects that they are needed..

---

Displaying a Data Preview

You can preview the display. Select the data adapter control and right-click to display the shortcut menu – select Data/Preview Data.

In the dialog box select Fill Dataset – see Figure 10.16.

Figure 10.16

---

Using Data-Bound Labels and Text Boxes

Note: This section of notes deviates significantly from the approach taken in your textbook because the textbook has ERRORS IN THE CODE and the code will not execute properly under different situations.

By using data-bound labels and/or text boxes, you can display one row (row) at a time on a form.

Each control displays a single column from the Student table in the dataset. Buttons can also be added for navigation as shown in Figure 10.17 below.

---

In-Class Exercise

Start a new project. Name it Ch10InClassStudent2.

· Name the form frmStudent.

· Add a group box, labels, text boxes, and buttons as shown in Figure 10.17. Name the labels, textboxes, and buttons as shown.

Figure 10.17

· Add an OleDbDataAdapter control to the project. Use the Data Adapter Configuration Wizard to configure the data adapter and connection objects.

o Name the connection conVBUniversity.

o Name the data adapter daStudent.

o Generate a dataset and name it dsStudent (displays as DsStudent1)

· See Figure 10.18. Select lblSSN and expand the DataBindings property in the Properties window (look at the very top of the window).

Figure 10.18

· Select Text and expand the DsStudent1 until you see the columns listed in the Student table. Select the StudentSSN column name.

· Do this again for the txtLastName, txtFirstName, and txtMiddleInit textboxes.

· Add code for the Form_Load event.

Private Sub frmStudent_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Try

'Fill dataset and bind the grid

daStudent.Fill(DsStudent1)

Catch ex As Exception

MessageBox.Show("Project Load Failed" & ControlChars.NewLine & ex.Message, "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

Me.Close()

End Try

End Sub

· Test the project – the form will display the first row from the Student table.

---

BindingContext Method and Row Navigation

The keyword Me is often used to refer to a form when writing code.

Each form has a BindingContext method. This method specifies the context within which database binding is to occur – here the form's controls are bound to the dataset (DsStudent1) and table name (Student).

Row navigation for the display of individual rows can be accomplished by changing the value of the Position property of the BindingContext Method.

· The rows are numbered from zero (0) to Count – 1.

· Recall that a Count property is a numeric property that stores an integer value equal to the number of occurrences of something, in this case the number of rows in the Student table.

· Because VB.NET has the annoying habit of counting starting with zero, there is always one fewer rows than the value of Count.

This example navigates to the next row by increasing the value of the Position property by one.

Me.BindingContext(DsStudent1, "Student").Position += 1

The BindingContext manager for the project automatically maintains the value of Position as a legal value. That is, if you try to navigate to a Next or Previous row that does NOT exist, then Position property value will NOT change and no error will occur.

---

In-Class Exercise

Code the Navigation Buttons

Write code for the four buttons to navigate within the dataset. The four sub procedures are shown here.

· Storing zero (0) to the Position property moves to the first row.

Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click

'Navigate to the First row

Me.BindingContext(DsStudent1, "Student").Position = 0

End Sub

· Adding one (1) to the Position property advances to the next row.

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

'Navigate to the Next row

Me.BindingContext(DsStudent1, "Student").Position += 1

End Sub

· Subtracting one (1) from the Position property moves to the previous row.

Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click

'Navigate to the Previous row

Me.BindingContext(DsStudent1, "Student").Position -= 1

End Sub

· Storing Count - 1 to the Position property moves to the last row.

Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click

'Navigate to the Last row

Me.BindingContext(DsStudent1, "Student").Position = _

Me.BindingContext(DsStudent1, "Student").Count - 1

End Sub

· Add the code shown above for the navigation buttons.

· Test the project.

Display Row Position

· Write code to display the row number to the form's Text property. This requires adding a sub procedure named DisplayRowNumber to the project, then calling this sub procedure in the click events for the buttons as well as in the Form_Load event.

Private Sub DisplayRowNumber()

'Display the row number

Dim intCount As Integer

Dim intRow As Integer

intCount = DsStudent1.Tables("Student").Rows.Count

If intCount = 0 Then

Me.Text = "VB University - No Student Data"

Else

'Add 1 to the position because row counts start at 0;

'Counting from 0 would confuse the system user

intRow = _

Me.BindingContext(DSStudent1, _

"Student").Position + 1

Me.Text = "VB University - Student: " _

& intRow.ToString _

& " of " & intCount.ToString

End If

End Sub

You should notice that the row number display at the top of the form does not update when you navigate to a new student record. You can correct this by writing code for a TextChanged event for any of the textboxes that display student information. As each row changes, this event fires. Here the txtLastName control's TextChanged event is coded. The event fires even if two records store the same last name and the row number on the form will be updated.

Private Sub txtLastName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtLastName.TextChanged

'Update the Form's row number display

'when a new student is selected

DisplayRowNumber()

End Sub

· Add the DisplayRowNumber sub procedure and the txtLastName_TextChanged sub procedure to your project.

· Test the project again.

---

Populating Combo Boxes with Data

List box and combo box controls are useful for listing data to assist system users with making valid selections. Example: a list box of valid state abbreviations or valid zip codes.

These values can be filled from database tables known as validation tables. To fill a list box or combo box (or similar control) with data from a dataset, you must set two properties:

· DataSource – connects to the data adapter.

· DisplayMember – connects to the specific field name from the table.

You can also use a combo box to navigate between rows in a dataset.

---

In-Class Exercise

Now you will use a combo box to provide an alternative means for selecting a student row on the form. System users may want to find student data directly by using the last name to locate the student's record or a row that is close to the desired record.

Figure 10.19

· Add a combo box control named cboLastName as shown to display student last name values.

· Set the combo box's Text property to blank.

· Set the DataSource and DisplayMember properties as follows:

o DataSource = DsStudent1

o DisplayMember = LastName column from the Student table.

· Test the project to confirm that when you select a last name from the combo box, the system navigates directly to the desired row. The navigation buttons should also still work.

---

Parameterized Query and Multiple Data Adapters

Up to now we've selected ALL rows from a table. This section of the notes focuses on selecting an individual row from the Student table and associated rows from the Enrollment table.

Using Multiple Data Adapters

When might you use more than one data adapter?

§ You may need a separate data adapter and dataset for a control such as a list box, combo box, data grid, or other control that can display values from more than one table row.

§ You may need a separate data adapter and dataset if you display information on a form that comes from more than one table in a database (although you can store data from more than one table in a dataset, using two different datasets is often clearer and creates fewer problems if the dataset has to be cleared).

A single connection object can support more than one data adapter – this just makes sense because regardless of the table from which you are displaying information, you're usually accessing the same database.

Creating a Parameterized Query

A parameterized query is used to specify a dataset with one or more selected rows from a table or set of tables based on a specific condition. Sometimes, we are only interested in a specific row.

Suppose we want to display enrollments for a specific student as is shown in Figure 10.19a. The SELECT statement in SQL has an optional WHERE clause that can be used to specify conditions that limit rows in a dataset to specific rows.

Figure 10.19a

You may observe that the student LastName column is not guaranteed to be unique. How then can we select the proper enrollments based on the student LastName column?

The answer is that we won't – instead, we'll select based on the social security number. Because a system user may not know a student's social security number, we will use the combo box control's ValueMember property to store social security number information that corresponds to a student's LastName.

The ValueMember property stores a value from a column corresponding to the value selected from the combo box control. For example, if we select Ms. Claudia Charming (LastName = Charming), then the ValueMember property will be set to Ms. Charming's social security number (333-33-3333) by specifying that the ValueMember property should store the StudentSSN column.

Figure 10.19b

A second data adapter control needs to be added to the project and configured to use the existing connection, but it should select data from the Enrollment table. All of the table's columns are needed. Additionally, it is necessary to specify the parameter condition for selecting the correct rows from the table.

The SELECT query to select an individual student's enrollment rows is:

SELECT *

FROM Enrollment

WHERE (StudentSSN = '111-11-1111')

However, at design time you will not know which StudentSSN value is desired. The solution is to replace the StudentSSN value with a question mark (?) in the query – this converts this SELECT query into a parameterized query – the question mark is a parameter to be filled in by VB at run time.

SELECT *

FROM Enrollment

WHERE (StudentSSN = ?)

We can add a new data adapter to a project, for example, daEnroll and write the SQL query shown above to display enrollment information for a specific student when the data adapter is configured through use of the Data Adapter Configuration Wizard.

---

In-Class Exercise

Modify the project by adding a second data adapter

· Open the Data tab on the toolbox and double-click the OleDbAdapter control to open the Data Adapter Configuration Wizard.

· Under the Choose Your Data Connection window select the existing data connection for the project. Click Next.

· Under the Choose a Query Type window just click Next to select the Use SQL statements option.

· Under the Generate the SQL statements window type in the query (you can also use the Query Builder and type a question mark in the criteria column cell that corresponds to the StudentSSN column in the Enrollment table).

SELECT *

FROM Enrollment

WHERE StudentSSN = ?

· Under the View Wizard Results window you will note that the data adapter was successfully configured. Click Finish.

· In the project component tray, select OleDbAdapter1 and change the name to daEnrollment.

· Generate a dataset named dsEnrollment by right-clicking the control and selecting Generate a Dataset from the context menu. See Figure 10.20.

Figure 10.20

· Click OK – note the new dataset is named DsEnrollment1 and is shown in the project component tray.

· Select cboLastName and set the ValueMember property to the StudentSSN column from the Student table.

· Add a DataGrid control to the form as shown in Figure 10.21. Name it dgrEnrollment.

o Set the CaptionText to Enrollment Information.

o Set the DataSource to DsEnrollment1.

o Set the DataMember to Enrollment.

o Set the PreferredColumnWidth property to 120.

Figure 10.21

· If you test the project now, you will discover that the enrollment data displayed is NULL. We now have to link the two datasets.

---

Linking the Datasets

Displaying Data Based on a Selected LastName Value

We will supply the value for the parameterized query (the student last name) at run time. The SSN column value will be selected from the cboLastName combo box control because the ValueMember property was set to StudentSSN.

The parameter is created automatically by VB when you write the query with the question mark. The parameter is added to the Parameters collection of the data adapter's SelectCommand property. The value for the parameter (StudentSSN) must be supplied prior to executing the Fill method in both the Form_Load and the combo box's SelectedIndexChanged events.

This code shows the sub procedure for the SelectedIndexChanged event for cboLastName.

Private Sub cboLastName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboLastName.SelectedIndexChanged

'Clear the dataset for the Enrollment table first

DsEnrollment1.Clear()

'Specify a value for the parameter as the

'SelectedValue property of the combo box

daEnrollment.SelectCommand.Parameters("StudentSSN").Value _

= cboLastName.SelectedValue

'Fill the dataset

daEnrollment.Fill(DsEnrollment1)

End Sub

First the dataset is cleared – this is necessary because as different student names are selected, the enrollment data displayed would accumulate and be incorrect for a specific student.

Next the cboLastName.SelectedValue property is stored to the Value property of the SelectCommand's Parameters collection for the StudentSSN parameter. When the system user makes a selection, the ValueMember property for the combo box has its stored value moved to the SelectedValue property of the combo box. In this case, this will be the StudentSSN value that corresponds to the student's LastName!!!

Following this, the dataset is filled with the Fill method.

One more change is necessary. When the form first starts, the combo box's SelectedIndexChanged event doesn't execute. The event can be called from the Form_Load event. The modified code is shown here and the Call statement is highlighted in red. The parameters sender and e are passed as dummy values to the called procedure (SelectedIndexChanged) because they are not used in the called procedure.

Private Sub frmCh10VBUniv_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Try

'Fill Student DataSet

daStudent.Fill(DsStudent1)

DisplayRowNumber()

Me.cboLastName_SelectedIndexChanged(sender, e)

Catch

MessageBox.Show("Database Connection Error", "No Connection", MessageBoxButtons.OK, MessageBoxIcon.Error)

'Close the application - can't work

Me.Close()

End Try

End Sub

Figure 10.21a

---

In-Class Exercise

· Modify the project by adding the code shown above for the cboSSN_SelectedIndexChanged event and the Form_Load event.

· Test the project. The DataGrid control should be coordinated with the student information.

---

Updating a Dataset – Data Maintenance

Note: This section of notes deviates significantly from the approach taken in your textbook because the textbook has ERRORS IN THE CODE and the code will not execute properly under different situations.

Now we turn to the topic of adding new rows, editing existing rows, and deleting rows from a dataset.

Figure 10.22 shows a form used to display information about courses offered by the VB University. The data is in the Course table of the VBUniv.mdb Microsoft Access database.

Figure 10.22

---

In-Class Exercise

New Project and Form

In order to reinforce your learning, you will start a new project named VBUniversityCourse. Close the previous project if it is open.

· Name the form frmCourse and make it the startup object for the project.

· Ensure the VBUniv.mdb database file is stored on drive/folder C:\temp.

· Add controls to the form as shown in Figure 10.22. Name the textbox, label, and button controls as indicated.

· Set the Enabled property of each text box to False.

· Set the Enabled property of the Save and Cancel buttons to False.

Data Adapter and Connection

· Create data adapter and connection objects by using the technique of adding an OleDbAdapter data adapter control from the toolbox Data tab – use the wizard to create a new connection for the VBUniversity database.

o Select all of the Course rows from the Course table in the order in which they are listed on the form – CourseID, Title, CreditHours, Department, DateLastUpdate (although the order by which columns are selected is actually irrelevant, this may help you to match the table columns to the task at hand).

o Sort the rows by CourseID.

o Name the OleDbAdapter1 as daCourse.

o Name the OleDbConnection1 as conCourse.

· Right-click the daCourse data adapter and generate a dataset – name it dsCourse – the system will display a name for the dataset as DsCourse1.

· Now bind the text box controls to the dataset – in the Properties window expand the DataBindings property and select Text, then expand the DsCourse1 until you see the column names listed in the Course table. Select the appropriate column name for each textbox.

· Add comments to the code to identify the project, programmer, date, and description.

· Set Option Strict On.

· Add the code for the frmCourse_Load, DisplayRowNumber, and TextChanged event for the txtLastName textbox. procedures to fill the dataset and to display the row number to the form's Text property.

Private Sub frmCourse_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Try

'Fill dataset and bind the grid

daCourse.Fill(DsCourse1, "Course")

DisplayRowNumber()

Catch

MessageBox.Show("Network Connection Failed", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

Me.Close()

End Try

End Sub

Private Sub DisplayRowNumber()

'Display the row number

Dim intCount As Integer

Dim intRow As Integer

intCount = DsCourse1.Tables("Course").Rows.Count

If intCount = 0 Then

Me.Text = "VB University - No Course Data"

Else

'Add 1 to the position because row counts start at 0;

'Counting from 0 would confuse the system user

intRow = _

Me.BindingContext(DsCourse1, "Course").Position + 1

Me.Text = "VB University-Course Add, Edit, and Delete: " & intRow.ToString & " of " & intCount.ToString

End If

End Sub

Private Sub txtLastName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtLastName.TextChanged

'Update the Form's row number display

'when a new student is selected

DisplayRowNumber()

End Sub

· Test the project – you will be able to see the first course row information.

---

Navigating Rows

In order to support project testing, it is necessary to add code for the Navigation buttons.

The code is shown here for your reference.

Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click

'Navigate to the First row

Me.BindingContext(DsCourse1, "Course").Position = 0

End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

'Navigate to the Next row

Me.BindingContext(DsCourse1, "Course").Position += 1

End Sub

Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click

'Navigate to the Previous row

Me.BindingContext(DsCourse1, "Course").Position -= 1

End Sub

Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click

'Navigate to the Next row

Me.BindingContext(DsCourse1, "Course").Position = Me.BindingContext(DsCourse1, "Course").Count - 1

End Sub

---

In-Class Exercise

· Add code for the Navigation buttons as shown above.

---

Editing (Updating) Existing Rows

You could allow system users to simply edit the values in the text box controls – however, sometimes a system user will inadvertently change a value that is not intended to be changed!

A good approach – set the Enabled property of each text box control to False thus locking the control. If the system user clicks the Edit button, then unlock the text box controls by setting Enabled to True. This can be coded as a single procedure by the use of a Boolean variable that accepts a value of True or False from the calling procedure.

Private Sub EnableControls(ByVal blnValue As Boolean)

'Disable/enable textbox controls

txtCourseID.Enabled = blnValue

txtTitle.Enabled = blnValue

txtCreditHours.Enabled = blnValue

txtDepartment.Enabled = blnValue

txtDateUpdated.Enabled = blnValue

End Sub

During edit operations disable the navigation buttons until the system user either clicks the Save button or the Cancel button. You should also disable the Add, Edit, Delete, and Exit buttons. Only the Save and Cancel buttons should be enabled.

The Enabled property settings are reversed for all buttons after the Save or Cancel button is clicked. This can be coded as a single procedure as with the EnableControls procedure through use of a Boolean variable. The procedure shown here named SetButtons accomplishes this.

Private Sub SetButtons(ByVal blnValue As Boolean)

'Enable/disable navigation buttons

btnFirst.Enabled = blnValue

btnPrevious.Enabled = blnValue

btnNext.Enabled = blnValue

btnLast.Enabled = blnValue

'Enable/disable maintenance buttons

btnSave.Enabled = Not blnValue

btnCancel.Enabled = Not blnValue

btnAdd.Enabled = blnValue

btnDelete.Enabled = blnValue

btnEdit.Enabled = blnValue

btnExit.Enabled = blnValue

End Sub

Pseudocode for an Edit

· Enable text boxes by sending True

· Disable buttons by sending False

· The system user now proceeds to modify the data row.

Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click

'Configure the form to allow editing the

'current Course row

EnableControls(True)

SetButtons(False)

End Sub

---

Saving Data

Following the use of an Edit button, the system user has the option to Save or Cancel the modification to an existing row by selecting either the Save or Cancel button.

EndCurrentEdit and CancelCurrentEdit Methods

The EndCurrentEdit method executes automatically whenever a system user navigates away from the current row in a DataGrid control.

You can use this method in code to cause the end of a current edit operation such as an Edit or Add operation in order to prepare a dataset for saving.

'Inside the click event for the Save button

Me.BindingContext(DsCourse1, "Course").EndCurrentEdit()

You can also cancel an Edit or Add operation with the CancelCurrentEdit method. We use this method in the Cancel button click event.

'Inside the click event for the Cancel button

Me.BindingContext(DsCourse1, "Course").CancelCurrentEdit()

The Update Method

A dataset is a temporary set of data in memory. The data is moved across the client-server connection to memory on the client computer, then the connection to the database is broken – it is said to be disconnected.

If you make changes to a dataset, those changes must be transferred back to the database by executing the Update method of the data adapter. The connection is reestablished and any rows in the dataset that have been modified in any way (edited, added, deleted) are transmitted back to the database.

You can update the database after each change or only once when the program executes – the choice is yours, but the best option is to update after each change to the dataset.

The Update method saves changes and is coded as follows:

DataAdapterName.Update(DatasetName,"Tablename")

daCourse.Update(DsCourse1, "Course")

Your book demonstrates using the Update method in the form Closing event. The Closing event fires when Me.Close executes – this happens when you close the form from a button or menu click event or when you click a form's close button.

Actually, this is a very poor choice because it DOESN'T WORK well at all for multiple updates to a dataset, for example, when you're modifying an existing row and also adding a new row. For this reason, we will NOT use that approach.

The AcceptChanges Method

Your text does not discuss the AcceptChanges method, but this is what we will use instead of coding the Update method in the Form's Closing event.

While the Update method reconnects to a database source and updates (moves modifications of a dataset including adds, edits, and deletions) back to the data source, the AcceptChanges method of the DataSet modifies the existing dataset that is in memory. It is used in conjunction with the Update method.

Special note: I have only found the AcceptChanges method necessary for use with Microsoft Access databases. The Update method updates both the database and dataset with Oracle and SQL Server databases.

'Example of the AcceptChanges Method

DataSet.AcceptChanges()

dsCourse1.AcceptChanges()

'Example of combining the Update and AcceptChanges Methods

'to save modifications to an existing row in a dataset

'and to update a datasource (database table)

daCourse.Update(DsCourse1, "Course")

DsCourse1.AcceptChanges()

You will use the Update or AcceptChanges method when you code the Click event for the Save and Delete buttons.

Logic for Saving Data

Saving data is EXACTLY THE SAME for the Edit of an existing row as it is for the Add of a new row with this approach (your textbook uses a different approach which does not work very well). You will learn about how to add a new row in the next section – here we focus on saving the Edit operation that we just learned. Unlike the textbook, absolutely NO Boolean variables are needed.

Pseudocode to Save

· End the current edit

· Update the data source

· Accept the changes to the dataset

· Disable text boxes by sending False

· Enable buttons by sending True

· Display the current row number

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

Try

'Complete the current edit operation

Me.BindingContext(DsCourse1, "Course").EndCurrentEdit()

'Update the database table

daCourse.Update(DsCourse1, "Course")

'Reset the dataset

DsCourse1.AcceptChanges()

'Disable the textboxes, enable the buttons

EnableControls(False)

SetButtons(True)

'Display current row number

Me.DisplayRowNumber()

Catch eDataError As Exception

'This catches errors such as attempting to save

'a row with a CourseID value that duplicates

'one that already exists in the database

MessageBox.Show("Error: " & ControlChars.CrLf & eDataError.Message, "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

---

Canceling an Edit or Add

Canceling either an Edit or Add operation requires the same logic. You will use the CancelCurrentEdit method of the Binding Manager.

Pseudocode to Cancel

· Disable text boxes by sending False

· Enable buttons by sending True

· Cancel the current edit

· Display the current row number

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

'Cancel the edit or add

EnableControls(False)

SetButtons(True)

'Cancel the current Edit operation

Me.BindingContext(DsCourse1, "Course").CancelCurrentEdit()

'Display current row number

Me.DisplayRowNumber()

End Sub

---

In-Class Exercise

· Add code for the EnableControls and SetButtons procedures.

· Add code for the Edit button's Click event.

· Add code for the Save button's Click event.

· Add code for the Cancel button's Click event.

· Test the project by editing an existing row – cancel the Edit operation to see if the original row values are returned.

· Test the project by editing an existing row – save the Edit operation.

· Check the navigation buttons and other data maintenance buttons during an Update operation to ensure that they are correctly disabled.

---

Adding Rows

Clicking the Add button begins an Add operation.

The text boxes must be enabled so the system user can enter data for a new data row. The navigation and data maintenance buttons need to be disabled as was done for an Update operation. This is accomplished by calling the two procedures that we have already defined.

'Inside the click event for the Add button

EnableControls(True)

SetButtons(False)

The AddNew Method

The AddNew method is another Binding Manager method. While it appears that this method clears the text boxes (and labels and other bound controls) so that a system user can enter new data into the controls, what actually happens is that a new "empty" row is added to the dataset.

This gives the appearance of clearing the current contents of the form, but it is actually the empty row that is being displayed – the form is now ready for the system user to enter new data values.

The system user has the option to Save or Cancel the addition of a new row. The CancelCurrentEdit method described earlier is used to cancel an AddNew – this eliminates the new row that was added by the AddNew method from the dataset.

Pseudocode to Add a New Row

· Enable text boxes by sending True

· Disable buttons by sending False

· Use AddNew method to add a blank row

· Store values to any textboxes that need a default value such as the current system date

· Display the current row number

· Set focus to the first text box

Note in the code below that we default the txtDateUpdated textbox value to the current date and time with the Date.Now system object.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

'Begin Add operation -- Enable textboxes and

'disable buttons

EnableControls(True)

SetButtons(False)

'Add a new blank row to the dataset

Me.BindingContext(DsCourse1, "Course").AddNew()

'Store system date to txtDateUpdated textbox

txtDateUpdated.Text = Date.Now.ToString

'Update the row number display on the form

DisplayRowNumber()

'Set the focus to the first textbox

txtCourseID.Focus()

End Sub

---

Deleting Rows

Rows are removed with the RemoveAt method of the dataset's DataRows collection.

The database is updated with the Update method of the database adapter control, while the dataset is updated to accept the deletion of the row with the AcceptChanges method, and the row number display is updated.

Your program also needs to ask the system user to confirm the row deletion.

Pseudocode for Delete

· If Deletion is confirmed Then

o Identify the row number of the row to delete

o Remove the current row

o Update the database

o Accept Changes to the dataset (delete row)

o Display new current row number

· End If

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

Dim dlgResult As DialogResult = _

MessageBox.Show("Confirm to Delete Row?", "Course Deletion", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)

'Delete current row if the system user clicked YES

If dlgResult = DialogResult.Yes Then

Try

'Store the current position of

'the row to be deleted

Dim intRec As Integer = Me.BindingContext(DsCourse1, "Course").Position()

'Delete the Row (row)

Me.BindingContext(DsCourse1, "Course").RemoveAt(intRec)

'Use update method to delete the row from the database

daCourse.Update(DsCourse1, "Course")

'Accept deletion to the current dataset

DsCourse1.AcceptChanges()

'Update the row number display on the form

DisplayRowNumber()

Catch eDataError As Exception

MessageBox.Show("Unable to Delete Course" & ControlChars.NewLine & eDataError.Message, "Course Deletion Failed", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End If

End Sub

---

Exit Button and Form Closing Event

You must add code for the remaining buttons and procedures including the Exit button and the form's Closing procedure. The code for the Exit button is simple.

Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuFileExit.Click

'Exit the form

Me.Close()

End Sub

Whenever Me.Close executes, the form's Closing event fires.

The Closing event is coded to catch situations where the system user attempts to press the Close button on the upper-right corner of the form (the X) while an Add or Edit operation is in progress. In this situation, the Save button will be enabled so the code in the procedure tests the Enabled property = True condition to determine if an Add or Edit operation is in progress.

The parameter e as a CancelEventArgs argument is passed by the system to the procedure – setting e.Cancel to True cancels the Close event.

Private Sub frmCourse_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing

'If an Add or Edit is in progress, inform the system user

If btnSave.Enabled = True Then

MessageBox.Show("An Edit or Add operation is in progress" & ControlChars.NewLine & "Cancel the transaction before closing.", "Closing Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

'Cancel the close event.

e.Cancel = True

End If

End Sub

---

Data Validation

Additional Error Checking

You'll find that the program can be improved by adding additional data validation code to ensure that data entered during Add operations or modified during Edit operations is clean.

For example, the current code catches errors where a text box has been left blank, but does not clean up the update or add operation. The result is an Add or Edit operation that fails to be accepted by the DBMS—the error is trapped by exception handling.

It is a better practice to use a ValidData function to validate the data entered prior to executing an Update method to modify the database. You learned to use this type of function in an earlier module in this course and can apply it to this program.

ValidData Function

The function shown here enforces three different validation rules:

1) Each text box control is validated for the existence of data. Recall that the Trim() function trims leading and trailing blank characters from a character string – this ensure that if a system user accidentally pressed the space bar for one of the textboxes, this is not treated as the existence of valid data.

2) The txtCreditHours text box control is tested for the reasonableness of the data – a rule is enforced requiring the credit hours value to be a positive integer between 1 and 12.

3) The txtCourseID text box controls is tested for the exact length of the data entered by using the Len() function – this function returns the number of characters in a data string – the university has a rule that all course identifiers are exactly 7 characters in length.

Private Function ValidData() As Boolean

'Initialize function return value

ValidData = False

Dim strMessage As String

'Test existence of values in each textbox

If Trim(txtCourseID.Text) = "" Then

strMessage = "Course ID is missing."

MessageBox.Show(strMessage, "Data Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

txtCourseID.Focus()

ElseIf Trim(txtTitle.Text) = "" Then

strMessage = "Title is missing."

MessageBox.Show(strMessage, "Data Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

txtTitle.Focus()

ElseIf Trim(txtDepartment.Text) = "" Then

strMessage = "Department is missing."

MessageBox.Show(strMessage, "Data Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

txtDepartment.Focus()

ElseIf Trim(txtCreditHours.Text) = "" Then

strMessage = "Credit Hours is missing."

MessageBox.Show(strMessage, "Data Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

ElseIf Trim(txtDateUpdated.Text) = "" Then

strMessage = "Date Updated is missing."

MessageBox.Show(strMessage, "Data Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

txtDateUpdated.Focus()

ElseIf CInt(txtCreditHours.Text) < 0 Or _

CInt(txtCreditHours.Text) > 12 Then

'Reasonableness test for credit hours value

strMessage = "Credit Hours Invalid." _

& ControlChars.NewLine _

& "Hours must be between 1 and 12"

MessageBox.Show(strMessage, "Data Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

txtCreditHours.Focus()

txtCreditHours.SelectAll()

ElseIf Len(Trim(txtCourseID.Text)) <> 7 Then

'The course identifier is not 7 characters

strMessage = "Course ID must be 7 characters in length."

MessageBox.Show(strMessage, "Data Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

txtCourseID.Focus()

txtCourseID.SelectAll()

Else

'All of the data is valid

ValidData = True

End If

End Function

The Click event for the Save button must be modified to call the ValidData function. The new code is highlighted in red.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

Try

'Call the ValidData function

If ValidData() Then

'Complete the current edit operation

Me.BindingContext(DsCourse1, "Course").EndCurrentEdit()

'Update the database table

daCourse.Update(DsCourse1, "Course")

'Reset the dataset

DsCourse1.AcceptChanges()

'Disable the textboxes, enable the buttons

EnableControls(False)

SetButtons(True)

'Update the row number display on the form

DisplayRowNumber()

End If

Catch eDataError As Exception

. . . More code follows to handle exceptions.

MaxLength Property

One of the easiest ways to enforce validation rules regarding the maximum length (size) of data to be stored to a particular column in a table is by setting the MaxLength property of a control such as a text box.

Setting the MaxLength property limits the number of characters that can be typed into a control.

For example, the value entered for Credit Hours cannot be larger than 12 credit hours as shown below in Figure 10.23. This means the data cannot be greater than two digits (characters in the textbox). You should set the MaxLength property of the txtCreditHours text box control to 2.

Figure 10.23

The MaxLength should be set for each control that stores data to be saved to the database as follows:

· String data. If the control (a text box or label) displays string data, you can examine the database table in design mode and determine the maximum allowed length for values to be stored/displayed in the control.

· Numeric data. For numeric data, you need to use good judgment or determine what rules the business wishes to enforce.

o An example is the credit hours value above where 12 hours is the maximum credit hours for a course and two characters are sufficient to store the largest value.

o Consider an example where you need to store a currency value such as $99,999.00. You need to allow characters for the display/storage of the dollar sign, comma and decimal point. This particular value is 10 characters at a maximum.

· Date/Time data. For date/time data, do not worry about enforcing a maximum length – date/time values are stored in a database as data that are a fixed length in size.

Key_Press Event

This event validates the keystrokes for a text box or other type of control such as a combo box. Consider as an example the txtCreditHours text box for the Course table. This text box control should only store numbers – your program needs to restrict keystrokes that are accepted into the text box control to numbers (Asc values 48 to 57) and the backspace key (Asc value 8) to allow a system user to erase numbers typed in error.

The Key_Press event of a control is used to validate various keys on the keyboard as shown in the example below for the txtCreditHours_KeyPress subroutine.

The value of the KeyChar property of "e", the KeyPressEventArgument, is used in the SELECT Case structure and the Handled property is set to False to allow the key; otherwise the keystroke is handled (ignored) by this event.

Private Sub txtCreditHours_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtCreditHours.KeyPress

'Allow Backspace (8), and numeric keys (48 to 57)

Select Case Asc(e.KeyChar)

Case 8, 48 To 57

e.Handled = False 'Allow the key

Case Else

e.Handled = True 'Ignore the key

End Select

End Sub

---

In-Class Exercise

· Add code for the Add button's Click event.

· Add code for the Delete button's Click event.

· Add code for the Exit button's Click event.

· Add code for the Form's Closing event.

· Add code for the txtCreditHours control's Key_Press event.

· Add code for the ValidData function.

· Set the MaxLength for the textbox controls to match the database limitations for the Course table: CourseID = 7; Title = 40; Department = 20; CreditHours = 2. You do not need to set a limit for the DateLastUpdated column as this size is controlled by storing the value from the system clock.

· Test the database by adding several rows – test the MaxLength for each textbox and the ValidData function for each textbox.

· Test the database by deleting several rows – try canceling a deletion.

· Attempt to close during an Add or Edit operation to test the Closing event.

· Check the database file to ensure that the modifications were saved to the database.

Happy Computing!

---

END OF NOTES