Before you can begin Project 1, you need to create a Solution environment where you can save your projects throughout the semester. Run Visual Basic .NET now. If you are prompted to open a project, select the Cancel button on the Open Project dialog. Drop down the File menu and hold your mouse pointer over the New menu item. Choose Blank Solution from the pop-out menu:
The New Project dialog will appear:
Type your name in the Name textbox, leave all the other settings at their defaults, and click the OK button. This creates a new folder inside the Visual Studio Projects folder in My Documents, with your name:
My Documents\Visual Studio Projects\<Your Name>.
Once your new blank solution is created, drop down the File menu and choose New Project on the Add Project pop-out menu:
The Add New Project dialog will appear:
Be sure the Windows Application template is selected in the Templates pane on the right side, then type DatabaseViewer in the Name textbox. Then click the OK button. This creates a new folder inside the \Visual Studio Projects\<Your Name> folder named DatabaseViewer:
My Documents\Visual Studio Projects\<Your Name>\DatabaseViewer
Note: When class is over, be sure to follow the instructions at the end of this project that tell you how to copy your project to your floppy diskette so you can take it home with you.
Rename the Form file
Make sure the form is selected in the Solution Explorer window:
The Properties window displays the File properties of your Form1.vb form file. Click on the File Name property and type frmData1.vb (don't forget to include the .vb extension):
Change the Name and Text properties of the Form
To display the properties of the form in the Properties window, click once on the Form itself. Change the Name property of the form to: frmData1, then scroll the properties windows down and change the Text property to: Database Viewer
Setting the Startup Object
Right-click on the DatabaseViewer project in your Solution Explorer window, click on the Properties item at the bottom of the context-menu. The DatabaseViewer Property Pages dialog will appear:
Drop down the Startup object combo box and choose frmData1, if it is not already selected. Click the OK button. Now click on the Data button in the Toolbox window to display the database related controls, which should match this illustration:
Adding a Data Adapter Control to the Form
A Data adapter is an integral part of ADO.NET managed providers, which are the set of objects used to communicate between a data source (Database) and a dataset (in your application). It allows us to read data from a database into our dataset, and then writes changed data from the dataset back to the database. When you place a Data Adapter on your form a Data Adapter Configuration Wizard leads you through the process of configuring the Data Adapter. Let's do that now.
Place an OleDbDataAdapter control on your form. The first dialog of the Data Adapter Configuration Wizard appears after a moment:
Click on the Next button, then click on the New Connection button on the next screen:
This opens the Select the Data Link Properties dialog box. Click on the Provider tab:
Select the Microsoft Jet 4.0 OLE DB Provider. This will allow you to connect to a Microsoft Access database. This large list of Providers lets you connect to many different types of databases. Each Provider is also called a Database Engine. Click on the Next button to access the Connection section of the Data Link Properties dialog:
Here is where we actually specify the database file that we want to establish a connect with. Click on the ellipses button (...) at the end of the Select or enter a database name textbox to browse for the provided prj1data.mdb file, and select it. Once you've selected the prj1data.mdb file (download the prj1data.mdb file here), click on the Test Connection button to make sure our link to the database is working. The Microsoft Data Link message box, confirming the data link, should appear:
Click the OK buttons on both the Microsoft Data Link message box, and the Data Link Properties dialog to close them both. Now click the Next button on the Data Adapter Configuration Wizard dialog. The next dialog lets us Choose a Query Type:
With the Use SQL statements option selected, click on the Next button:
SQL stands for Simple Query Language. We need to specify an SQL statement that will include the name of the Table and the Fields from that table that we want to have access to in our program. The format of the SQL statement will look like this:
SELECT <field names> FROM <table name>
In the above dialog we have the option to type this SQL statement ourselves or use the Query Builder to help us create it. Click on the Query Builder button now. This opens the Query Builder dialog and the Add Table dialog:
The Add Table dialog lists all the tables that the database (prj1data.mdb) contains. Select the Employees table and click the Add button. Now click the Close button, which leaves just the Query Builder dialog on the screen. It should look like this:
Notice in the bottom part of the dialog that part of our SQL statement has already been added for us:
SELECT <blank>
FROM Employees
We could put checkmarks in front of the individual fields (also called Columns) from the table to include just those fields if we wished. But since we want to include ALL the fields from the table, put a checkmark in front of the All Columns item in the little Employees field list dialog:
This changes the SQL statement to this:
SELECT Employees.*
FROM Employees
The asterisk (*) is a wildcard character that means: include all fields. Click the OK button to close the Query Builder dialog. Now the Data Adapter Configuration Wizard should look like this:
Notice that our SQL statement from the Query Builder is entered for us. Click on the Advanced Options button to display the Advanced SQL Generation Options dialog:
The Generate Insert, Update and Delete statements will let us implement record editing features in our program--you would not need these select statements if the table was opened for viewing only. The Use optimistic concurrency item will helps to maintain data integrity within the table. Leave the checkmarks on in front of both these items and click the OK button. Then click the Next button on the Data Adapter Configuration Wizard dialog. If the above steps were followed correctly, you should see a dialog that confirms that the process completed successfully. Now click the Finish button to close the Data Adapter Configuration Wizard dialog. Click the Don't include password button on the the Do you want to include the password in the connection string? message box, since we have not specified one anyway:
Notice on the Component Tray, at the bottom of your form designer screen, the addition of the Data Adapter (OleDbDataAdapter1), and Connection (OleDbConnection1) objects:
The Connection object is created automatically when we added a Data Adapter. The Connection object actually contains all the information about the Provider (in it's ConnectionString property) that we specified with the Data Link Properties dialog when we setup our Data Adapter. The Data Adapt has a SelectCommand.CommandText property that stores the SQL Select statement information that we specified above. Note: A data adapter needs an open connection to a data source to read and write data. Therefore, an adapter uses the Connection object to communicate with a data source.
Rename the Data Adapter and Connection objects
Click on the Data Adapter (OleDbDataAdapter1) to display it's properties in the Properties window. Change the Name property to odaEmployees. Then click on the Connection (OleDbConnection1) object and change its Name property to odcEmployees:
Adding a Dataset object
Right-click on the odaEmployees data adapter and choose Generate Dataset from the pop-up context menu:
Datasets store data in a disconnected cache (on your local computer, if the database is located on a network server). The structure of a dataset is similar to that of a relational database; it exposes a hierarchical object model of tables, records (rows), and fields (columns). In addition, it contains constraints and relationships defined for the dataset.
Note: Because a dataset is a fully disconnected container for data, datasets (unlike ADO recordsets) do not need or support the concept of a current record. Instead, all records in the dataset are available. Because there is no current record, there is no specific property that points to a current record and there are no methods or properties for moving from one record to another. (In contrast, ADO recordsets support an absolute record position and methods to move from one record to the next.) You access individual tables in the dataset as objects; each table exposes a collection of rows (records). You treat this like any collection, accessing the records via the collection's index or using collection-specific statements in your programming language--more on this later.
After choosing Generate Dataset, the Generate Dataset dialog appears:
In the New textbox change Dataset1 to dsEmployees, then click the OK button. While this sets the DataSetName property of the dataset to dsEmployees, notice that the name of our new dataset object is actually DsEmployees1. Make sure the DsEmployees1 dataset object is selected at the bottom of the form designer window so that you are viewing it's properties in Properties window. Now change the Name property of the dataset to dsEmployees and press enter. You should see the following objects in the component tray:
This completes the addition of controls necessary to allow our program to access a database. As a review, we added the following objects to our program to allow database access:
- Data Adapter
Let's you specify the SELECT statement that chooses the Table from the database and the Fields from that table that will be available to the Dataset object. In a nutshell, Data Adapters are used to exchange data between a data source and a dataset. - Data Connection
Allows you to specify the Data Provider (Database Engine) and the name of the Database to be accessed, and provides properties to establish and modify these security items: user ID and password, isolation level, and connection-timeout settings. A Data Connection is added to your project automatically when you add a Data Adapter. - Dataset
Stores the records from a table in the database in a disconnected cache. Similar in structure to that of a relational database; the dataset exposes a hierarchical object model of tables, records (rows), and fields (columns). In addition, it contains constraints and relationships defined for the dataset. The Dataset gives us access to the records (rows) in the table that the Data Adapter is linked to. We will be binding textboxes and other controls to the fields (columns) exposed by the Dataset so that we can view and modify the records (rows) of the Table.
Taking a quick look at the data
Right-click on the Data Adapter (odaEmployees) at the bottom of the form designer window and choice the Preview Data... item on the pop-up context menu. This displays the Data Adapter Preview dialog box. Click on the Fill Dataset button to populate the Results panel of the dialog with the row values of the Employees table--These are the records that the table contains:
You cannot edit records from here. This is just a quick way to view the records in the table. Click the Close button to close the Data Adapter Preview dialog box.
Adding Controls to the Form and then Binding them to the Dataset
Place
a Label control on the form, as shown above. Be sure the label is selected so that
it's properties are displayed in the Properties
windows. Set these properties as follows:
| Property | Value |
| Text | Social Security |
| Font.Size | 12 |
| Font.Bold | True |
| TextAlign | MiddleRight |
| BackColor | Light Yellow |
Once you've set the above property values of the Label (leave it's default name unchanged), right-click on the label, and select Copy from the context pop-up menu. Once the original label is copied, right-click on the form and select Paste from the context pop-up menu. Paste 7 copies of the label. Arrange them in a single column and change their Text properties to match the following illustration:
Place
a Textbox control on the form
where shown above. Be sure the Textbox is selected so that
it's properties are displayed in the Properties
windows. Set these properties as follows (leave it's default name unchanged):
| Property | Value |
| ReadOnly | True |
| Font.Size | 10 |
We're setting the ReadOnly property to True so that records from the table cannot be edited by mistake. Once you've set these properties, right-click on the Textbox and select Copy from the context pop-up menu. Right-click on the form and select Paste. Paste 7 copies of the textbox, arrange them in a single column to match the following illustration:
Setting the Tab Order for the Textbox controls
Drop down the View menu and select the Tab Order item. Click on the textboxes to set their tab order values. Set their tab order values so that they match the following illustration:
Drop down the View menu and select the Tab Order item again to hide the tab order numbers.
Naming the Textbox controls
Select
each textbox one by one, from the top down, and change their
Name properties, like so:
| Property | Value |
| Name | txtSS |
| Name | txtLast |
| Name | txtFirst |
| Name | txtStreet |
| Name | txtCity |
| Name | txtState |
| Name | txtZip |
| Name | txtAge |
Binding the Textbox controls to the Dataset
With the txtSS textbox selected, expand its DataBindings property in the Properties window. In the DataBindings' sub-properties, click the dropdown arrow on the Text property to view the available datasets (so far, we only have the dsEmployees dataset):
We need to bind the SocialSecurity field (column) from the Employees table to the txtSS textbox. To see a list of the fields that the Employees table contains, click on the plus (+) sign in front of the dsEmployees dataset in the drop-down list to expand its contents:
Scroll down the list of fields and click on the SocialSecurity entry. This binds (joins) the SocialSecurity field from the employees table to the txtSS textbox. Select each textbox one by one from the top down and use the method above to bind their DataBindings.Text properties to these fields from the Employees table:
| Textbox | Bind to Field (column) |
| txtSS | SocialSecurity |
| txtLast | LastName |
| txtFirst | FirstName |
| txtStreet | Street |
| txtCity | City |
| txtState | State |
| txtZip | Zip |
| txtAge | Age |
Displaying record values in our bound controls
Before we can see the actual values of records in our bound textboxes, we need to Fill our dataset (similar to filling it with the Fill button on the Data Adapter Preview dialog box). We do this by adding some code to the frmData1_Load event procedure. Double-click the form to bring up the Code View window, and add this code to the frmData1_Load event procedure:
'Always clear the Dataset before filling it!
dsEmployees.Clear()
'The Fill method of the Data Adapter fills data sets that are attached to it
odaEmployees.Fill(dsEmployees, "Employees")
Testing the program so far
Now let's test run the program and see what happens. Click on the Start button on the toolbar. The form should appear similar to the following illustration:
The values displayed are from the first record (row) in the Employees table.
Navigating the table: Adding a move Next button
Let's begin by adding a move Next button:
Place
a Button control on the form
where shown above. Be sure the Button is selected so that
it's properties are displayed in the Properties
windows. Set these properties as follows:
| Property | Value |
| Name | btnNext |
| Text | > |
| Font.Bold | True |
Now double-click on the btnNext button and add the following code to its Click event procedure:
'Increment the Position property of the BindingContext to move forward one record (row),
' Position is equal to 0 (zero) on the first record
Me.BindingContext(dsEmployees, "Employees").Position += 1
The BindingContext and CurrencyManager classes
Any data source you bind to a Form or container control (group box, picture box, etc.) will have an associated CurrencyManager object. The CurrencyManager object keeps track of the position and otherwise supervises bindings to that data source. There is a CurrencyManager object on the form for each discrete data source that you are binding to. If the controls on the form all bind to a single source (for example, if several TextBox controls are bound to the same data table like in our program), then they will share the same CurrencyManager. However, there are times when controls on the form will be bound to different data sources. In that case, there will be multiple CurrencyManager objects on the form, each one keeping track of which record or data element is being used by the controls. This can get very confusing, so this is where the BindingContext object comes in. Every Windows Form has a BindingContext object. The BindingContext object keeps track of all of the CurrencyManager objects on a form. And more importantly for us, the BindingContext helps us to navigate, add, delete, and edit records in a table.
So we will be using the BindingContext object to do the following:
- Navigate through the records (rows) -- by incrementing and decrementing its Position property.
- Determine the total number of records (rows) in the table -- with its Count property (which is always equal to the number of records-rows-in the table).
- Lets us add and delete records (rows) from the table with its AddNew and RemoveAt methods.
- Lets us modify the contents of existing records with its EndCurrentEdit and CancelCurrentEdit methods.
Adding move First, Previous, and Last buttons
Return to Design View
so that you can see the form. Add a move
Previous button by placing another
Button control on the form. Select it, and then set these
properties as follows:
| Property | Value |
| Name | btnPrevious |
| Text | < |
| Font.Bold | True |
Now double-click on the btnPrevious button and add the following code to its Click event procedure:
'Decrement the Position property of the BindingContext to move backward one record (row)
Me.BindingContext(dsEmployees, "Employees").Position -= 1
Now you can add move First and Last buttons. Return to Design View so that you can see the form. Add a move First button to the form and set its properties like so:
| Property | Value |
| Name | btnFirst |
| Text | << |
| Font.Bold | True |
Double-click on the btnFirst button and add the following code to its Click event procedure:
'Setting the Position property of the BindingContext to 0 moves to the first record (row)
Me.BindingContext(dsEmployees, "Employees").Position = 0
Return to Design View and add a move Last button to the form and set its properties like so:
| Property | Value |
| Name | btnLast |
| Text | >> |
| Font.Bold | True |
Double-click on the btnLast button and add the following code to its Click event procedure:
'The Count property of the BindingContext is equal to the number of records in the table.
' Setting the Position property of the BindingContext to Count - 1 puts us on the last record (row)
Me.BindingContext(dsEmployees, "Employees").Position = _
Me.BindingContext(dsEmployees, "Employees").Count - 1
Here is how our form should look after all four buttons have been added:
Testing the program so far
Save and run the program. Test your First, Next, Previous, and Last buttons, and make sure they are working.
Enabling and disabling the buttons to guide user interaction
We can't have the user trying to click on the Next button if they are already on the last record, or the First button if they have already reached the first record. To guide user interaction, we should enable and disable the First, Next, Previous, and Last buttons when appropriate. The following code will do that for the Next and Last buttons (don't type this code yet):
'When the Position property is equal to the Count property minus one, we have reached
' the last record (row) in the table, so disable the Next and Last buttons.
If Me.BindingContext(dsEmployees, "Employees").Position = _
Me.BindingContext(dsEmployees, "Employees").Count - 1 Then
btnNext.Enabled = False
btnLast.Enabled = False
Else 'Else enable the Next and Last buttons.
btnNext.Enabled = True
btnLast.Enabled = True
End If
And this code will enable and disable the Previous and First buttons when appropriate:
'When the Position property is equal to the 0 we have reached the first record (row) in the
' table, so disable the Previous and First buttons.
If Me.BindingContext(dsEmployees, "Employees").Position = 0 Then
btnPrevious.Enabled = False
btnFirst.Enabled = False
Else 'Else enable the Previous and First buttons.
btnPrevious.Enabled = True
btnFirst.Enabled = True
End If
Where do we put this code? When do we want this code executed? The second of these two questions is the easiest one to answer, so let's do it first. The above code needs to be executed whenever the current record changes. To change to a different record, we modify the value of the Position property in the Click event procedure of each of the navigation buttons. So we could create a custom procedure that contains the above code and call it at the bottom of the Click event procedure of the First, Next, Previous, and Last buttons. But there has got to be a better way!
Implementing a custom Delegate Procedure with the AddHandler statement
A Delegate procedure is a procedure that we create that is called automatically when a normal event procedure is triggered. So instead of adding a dozen calls to a custom procedure at the end of each procedure where we change the value of the Position property of the BindingContext object, we just create a custom delegate procedure that will be called automatically whenever the Position property changes. This is easy to do with the AddHandler statement. To set this up we first need to create a delegate procedure containing the above code. Here's how the declaration of our delegate SetButtons procedure will look:
Private Sub SetButtons(ByVal sender As Object, ByVal e As EventArgs)
For SetButtons to be a delegate procedure, it must include the special sender and e parameters that normal event procedure have passed to them by the operating system. The structure of delegate procedures and event procedures have this in common. Even though we won't be using the values in these parameters in our code in the SetButtons procedure, they must be included for SetButtons to be a delegate procedure. Before you type in this procedure, we should create a Region in our code window where we can place the code for all of our custom procedures--so that they will be easier to find later. On a blank line above the final End Class line at the bottom of the code window, type the following line and press the enter key:
#Region "Custom Procedures"
An #End Region line is automatically added. Inside our new Custom Procedures region, type the following line of code and press the enter key to create a custom SetButtons procedure:
Private Sub SetButtons(ByVal sender As System.Object, ByVal e As System.EventArgs)
An End Sub line is automatically added after you press enter. Type the code outlined above that disables and enables the First, Next, Previous, and Last buttons, into the SetButtons procedure. When you're finished, the completed SetButtons procedure should look like this:
Private Sub SetButtons(ByVal
sender
As System.Object, ByVal
e
As System.EventArgs)
'When the
Position property is equal to the Count property minus one, we
have reached
' the last record (row) in the table, so
disable
the
Next
and
Last
buttons.
If Me.BindingContext(dsEmployees,
"Employees").Position
= _
Me.BindingContext(dsEmployees,
"Employees").Count
- 1 Then
btnNext.Enabled = False
btnLast.Enabled = False
Else 'Else
enable
the
Next
and
Last
buttons.
btnNext.Enabled = True
btnLast.Enabled = True
End If
'When the Position property is equal to the 0 we have reached the
first record (row) in the
' table, so
disable
the
Previous
and
First buttons.
If Me.BindingContext(dsEmployees,
"Employees").Position
= 0 Then
btnPrevious.Enabled = False
btnFirst.Enabled = False
Else 'Else
enable
the
Previous
and
First
buttons.
btnPrevious.Enabled = True
btnFirst.Enabled = True
End If
End Sub
Now to make Setbuttons a delegate procedure--that is automatically executed whenever the Position property of the Me.BindingContext changes--we use the AddHandler statement. This is the format of the AddHandler statement:
AddHandler <event> AddressOf <delegate procedure>
- <event> is the name of an existing event procedure. When it is triggered the <delegate procedure> is called automatically.
- <delegate procedure> is the name of the custom procedure that is called automatically when <event> is triggered.
We need to find an event procedure that is triggered whenever the Position property of the BindingContext object changes. Luckily the BindingManagerBase class has a PositionChanged event procedure that will work nicely. Note: The BindingManagerBase enables the synchronization of all data-bound controls on a Windows form that are bound to the same data source. It is an abstract class--you won't find it in the Control's Toolbox. But it is intrinsic to every project you create. Question: If the BindingManagerBase class has a PositionChanged event procedure, why don't we just add a call to our SetButtons procedure directly to the PositionChanged event procedure? Answer: We cannot, because the code of event procedures of an abstract class are in not accessible. Hence the need for delegate procedures and the AddHandler statement: So that we can add extended functionality to the event procedures of abstract classes.
Since the BindingManagerBase class is abstract, we need to create a reference variable to it to access it's PositionChanged event procedure to provide the <event> parameter of our AddHandler statement. Add the following line of code at the end of the frmData1_Load event procedure:
Dim bmEmployees As BindingManagerBase = _
Me.BindingContext(dsEmployees, "Employees")
Now we can use bmEmployees to provide the <event> parameter of the AddHandler statement. So add the following line of code directly below the above line:
' Add the delegate for the PositionChanged event.
AddHandler bmEmployees.PositionChanged, AddressOf SetButtons
This makes our custom SetButtons procedure a Delegate procedure that will be called automatically whenever the PositionChanged event of the BindingManagerBase class is triggered--when the Position property of the BindingContext object is changed.
Testing the program so far
Save and run the program. Test your First, Next, Previous, and Last buttons, and make sure that they are disabled and enabled when they should be. What's wrong with the state of the navigation buttons when the program first starts? Why are the First and Previous buttons enabled at program start up, until we navigate to the next record and then back to the first record? Our SetButtons procedure is only executed when the Position property of the BindingContext object is changed--and at start up, the position property has yet to be changed. We can get around this problem by manually calling the SetButtons procedure in the frmData1_Load event procedure. Add the following code at the bottom of the frmData1_Load event procedure (after the code that fills the data adapted):
'The Position property is not modified when the program starts--which would
' automatically call the SetButtons procedure--so we must manually call
' SetButtons to enable/disable the navigation buttons.
SetButtons(sender, e)
Displaying the Total number of records and the Current record (row) number
Add four label controls to the form as shown in the above illustration (the backcolor properties of two of the labels are set to white). Set their properties--from left to right--like this:
| First Label | Second Label | ||
| Property | Value | Property | Value |
| Text | Record: | Name | lblCurrRow |
| BackColor | White | ||
| Third Label | Fourth Label | ||
| Property | Value | Property | Value |
| Text | of | Name | lblTotRows |
| BackColor | White | ||
To set the initial number values for the current record (lblCurrRow) and total records (lblTotRows) labels, we can use the following code (don't type this yet):
'Since the Position property is the record (row) index value--which is 0 on record 1--be sure to
' add 1 to display the actual record number
lblCurrRow.Text = _
Me.BindingContext(dsEmployees, "Employees").Position + 1
'The Count property of the BindingContext is always equal to the total number of records (rows)
lblTotRows.Text = _
Me.BindingContext(dsEmployees, "Employees").Count
Where should this code go? This code should be executed when the program starts, and every time the current record position changes. Instead of adding this code to the Load event procedure of the form, and to the Click event procedure of your First, Next, Previous, and Last buttons, let's add this code to a single procedure that is already being called whenever the Position property of the BindingContext object is changed. Add the above 2 lines of code at the bottom of your custom SetButtons delegate procedure.
Testing the program so far
Save and run the program. Test your First, Next, Previous, and Last buttons, and make sure the labels displaying the current record and total records numbers are working.
Allowing the user to edit records
Adding Edit and Save buttons
Add the Edit button first. Add a new
Button control to the form, select it, and then set these
properties as follows:
| Property | Value |
| Name | btnEdit |
| Text | Edit |
Now double-click on the btnEdit button and add the following code to its Click event procedure:
'Disable the navigation buttons to force the user to complete the edit process on the current record
btnFirst.Enabled = False
btnPrevious.Enabled = False
btnNext.Enabled = False
btnLast.Enabled = False
'Set the ReadOnly properties of the textboxes to False so that their contents can be modified
txtSS.ReadOnly = False
txtLast.ReadOnly = False
txtFirst.ReadOnly = False
txtStreet.ReadOnly = False
txtCity.ReadOnly = False
txtState.ReadOnly = False
txtZip.ReadOnly = False
txtAge.ReadOnly = False
'Set the Focus on the Social Security textbox
txtSS.Focus()
The above code set the ReadOnly property of the textboxes to False so that their contents can be edited. To make sure that the user completes the edit procedure by either clicking the Save or Cancel buttons only, the above code also disables the navigation buttons until the edit process is completed.
Now add the Save button. Add a new Button
control to the form, select it, and then set these
properties as follows:
| Property | Value |
| Name | btnSave |
| Text | Save |
Now double-click on the btnSave button and add the following code to its Click event procedure:
'The EndCurrentEdit method of the BindingContext object, completes the editing process
Me.BindingContext(dsEmployees, "Employees").EndCurrentEdit()
'Use the HasChanges method of the Dataset to be sure that the record was modified before
' wasting resources doing an Update to the data source. HasChanges returns True if
' modifications have been made.
If dsEmployees.HasChanges() = True Then
'To write the modifications of the record to the database, we must call the Update
' method of the Data Adapter
odaEmployees.Update(dsEmployees)
'After editing a record we need to re-fill the Data Adapter. Always clear the Dataset
' before refilling the Data Adapt
dsEmployees.Clear()
'The Fill method of the Data Adapter, fills all datasets that are attached to it
odaEmployees.Fill(dsEmployees, "Employees")
End If
'Disable further editing by changing the ReadOnly property of the textboxes back to True
txtSS.ReadOnly = True
txtLast.ReadOnly = True
txtFirst.ReadOnly = True
txtStreet.ReadOnly = True
txtCity.ReadOnly = True
txtState.ReadOnly = True
txtZip.ReadOnly = True
txtAge.ReadOnly = True
'The Position property is not modified when we edit a record--which would
' automatically call the SetButtons procedure--so we must manually call SetButtons
' to enable the navigation buttons, once the edit process has been completed.
SetButtons(sender, e)
The above code uses the EndCurrentEdit method of the BindingContext object to stop the editing process. Then a quick call to the HasChanges method of the dataset tells us whether or not the user actually modified the record before we waste valuable resources updating the data source. If HasChanges returns True, then a call to the Update method of the data adapter is required to actually save the modifications to the data source (database). Remember that the records you see and modify were copied from the data source (database) to your computer when the Data Adapter was last filled. Any modifications you make are not actually being made directly to the data source. Note: The Update method of the Data Adapter saves the modifications you've made to the data source (database).
Testing the program so far
Save and run the program. Navigate to any record and click on the Edit button. Modify one or two of the fields (i.e. Change the First and Last name fields to your name). Click on the Save button. Did it work?
Adding a Cancel button
We need to add a Cancel
button so that if the user changes their mind after they start editing a
record--before clicking on the save button--they can cancel any changes
they have made. Add a new Button control to the form, select it, and set these
properties as follows:
| Property | Value |
| Name | btnCancel |
| Text | Cancel |
Now double-click on the btnCancel button and add the following code to its Click event procedure:
'The CancelCurrentEdit method of the BindingContext object, cancels any changes made prior to an Update
Me.BindingContext(dsEmployees, "Employees").CancelCurrentEdit()
'Change the ReadOnly property of the textboxes back to True
txtSS.ReadOnly = True
txtLast.ReadOnly = True
txtFirst.ReadOnly = True
txtStreet.ReadOnly = True
txtCity.ReadOnly = True
txtState.ReadOnly = True
txtZip.ReadOnly = True
txtAge.ReadOnly = True
'The Position property is not modified when we cancel an edit, so we must manually call
' SetButtons to enable the navigation buttons, once the edit process has been completed.
SetButtons(sender, e)
The above code uses the CancelCurrentEdit method of the BindingContext object to restore the state of any modified fields prior to the last Update call.
Testing the program so far
Save and run the program. Navigate to any record and click on the Edit button. Modify one or two of the fields (i.e. Change the First and Last name fields to your name). Click on the Cancel button. Did the fields that you modified revert back to the values they had before you clicked the edit button?
Adding an Add button
The user may need to add new records to the table.
Add a new Button control to the form, select it, and then set these
properties as follows:
| Property | Value |
| Name | btnAdd |
| Text | Add |
Now double-click on the btnAdd button and add the following code to its Click event procedure:
'Set the ReadOnly properties of the textboxes to False so that values can be entered into them
txtSS.ReadOnly = False
txtLast.ReadOnly = False
txtFirst.ReadOnly = False
txtStreet.ReadOnly = False
txtCity.ReadOnly = False
txtState.ReadOnly = False
txtZip.ReadOnly = False
txtAge.ReadOnly = False
'Set the Focus on the Social Security textbox
txtSS.Focus()
'Store the current record position, so that if the user cancels the add, they are returned to the record
' position that they started on.
iStartRec = Me.BindingContext(dsEmployees, "Employees").Position
'The AddNew method of the BindingContext object adds a new record (row) to the table
Me.BindingContext(dsEmployees, "Employees").AddNew()
In the event that the user changes their mind after they start the add record process, we have added a line of code above that stores the current record position value (in an iStartRec variable) before the AddNew call at the bottom of the code. This way, if the user clicks on the Cancel button, we can return them to the record they were on when they started the add record process. The iStartRec variable needs to be dimensioned in the Declarations section at the top of the code window, like so:
Dim iStartRec As Integer
Now, when the user clicks on the Cancel button, we need to return them to the record they were on before they clicked the Add button. To do this, add the line of code below to the btnCancel_Click event procedure--be sure to place this code below the CancelCurrentEdit line of code:
'Move the user back to the record they were on before the Add/Edit began
Me.BindingContext(dsEmployees, "Employees").Position = iStartRec
Both the Add and Edit processes can be canceled with the Cancel button. With the addition of the line of code above, to the btnCancel_Click event procedure, we will also need to assign a value to the iStartRec variable in the Click event procedure of the Edit button. If we don't, when the user cancels out of an edit process, they will be moved to whatever record index that iStartRec was last set to. Add the following line of code at the bottom of the btnEdit_Click event procedure:
'This line of code is required to be compatible with the code in the Cancel button's Click event procedure
iStartRec = Me.BindingContext(dsEmployees, "Employees").Position
Testing the program so far
Save and run the program. Click on the new Add button. Notice that all the textboxes are cleared so that you can enter the values for a new record. Come up with values and fill in all the fields, then click on the Save button. Did it work? At what position was the new record (row) added in the table?
Adding a Delete button
The final edit option to be added is a
Delete button (as shown in the
illustration above). Add a new Button control to the form, select
it, and then set these properties as follows:
| Property | Value |
| Name | btnDelete |
| Text | Delete |
Now double-click on the btnDelete button and add the following code to its Click event procedure:
Dim iResponse As Integer
'Always confirm the deletion of a record (row) from a table, because it is permanent
iResponse = MessageBox.Show("Are you sure you want to delete this record?", _
"Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If iResponse = vbYes Then
'Use the RemoveAt method to delete a record (row)
Me.BindingContext(dsEmployees, _
"Employees").RemoveAt(Me.BindingContext(dsEmployees, _
"Employees").Position)
End If
It is always a good idea to ask the user for confirmation before doing something as permanent as deleting a record (row). In the code above we use a message box to ask their permission before actually deleting the record with the RemoveAt method of the BindingContext object. The RemoveAt method is passed the index value of the record to be removed--in this case, the Position property of the BindingContext is used.
Testing the program so far
Save and run the program. Try deleting a record. That completes this project. We will use this project to create project 2.
Recommended Enhancements
Disabling buttons to guide user interaction
We spent some time in this project adding code to disable and enable the navigation buttons, to guide user interaction. The basic rule is: Options that are not available should not be selectable; buttons, menu items, etc. should be enabled and disabled to guide user interaction with your program.
If you test this little program thoroughly, you'll discover some serious problems with our edit buttons, for example:
- The Cancel and Save buttons should not be enabled until the Edit or Add buttons are selected
- The Delete button should not be enabled while in the process of editing or adding a record
Test the program and enable and disable the editing option buttons where appropriate.
Do the Add and Delete options work the way they should? After adding a new record or editing an existing record and clicking the Save button, which record is displayed? Is the program user friendly?