Introduction
Have you ever had times in Visual Basic .NET when you'd like to show a simple
data form to allow your user to Add/Update/Delete some information, but found
that the data form produced by the data form wizard was lacking?
Wouldn't you like your data form to come standard, looking nice and have a
simple listbox from which to choose the current record? Of course, this may not
be the best solution for a table that has a large number of records, but at
least it has plenty of uses for those simple tables for which you need to
provide a way to change but really don't want to spend a lot of time on. A good
example would be that of simple lookup tables. Simple lookup tables, for this
article, are tables that hold all of the possible values that a certain field in
another table can contain. But this form also has applications for allowing the
user to manipulate the data of simple tables with a minimal number of
records--say less than 1000. And you may choose to take the idea and make it
suitable for a large number of records.
Say you have a table that tracks stolen property for a law enforcement agency
and one field in that table is named category. You make a table called
tblCategory to hold all of the valid property categories. When the user
is entering in stolen property, the category combobox (cboPropCategory) would be
filled with all of the values in tblCategory (the lookup table). Although
you would probably spend some time making the data entry form for entering
stolen property, you probably wouldn't want to spend a lot of time making a
simple data form to manage the available categories, but your users will want
the power to add to them or maybe even change them from time to time.
In this tutorial we'll explore how to make an unbound base data form that
uses a datareader instead of a dataset. While the IDE provides many tools for
using the dataset, the datareader has very little support. However, there are
times, such as this, when you might want to change some values used by the rest
of the program and have the database changes made instantly, thus making a
datareader, and some command objects the best choice. We'll start by discussing
form inheritance in Visual Basic .NET.
Background
I wrote this code to solve a real problem that I encountered and am presently
using it in some applications. I was so frustrated at the amount of work
involved just to have a nice data entry form, that was simple to use, looked
nice and that used a datareader instead of the dataset. Since I have written
this base form, I have used it again and again and have been very pleased at its
flexibility and ease of use. I hope that you will enjoy it and that it helps you
in at least some of your needs.
Even if it may not be something that you want to use, I hope that you will
gain something in terms of maybe learning how to use Visual Inheritance, or how
to use some OO features provided in VB.NET, such as methods that can and must be
overriden, constructors and the like. You will also find, included in the source
code, a nice class called clsParamBuilder that makes
building a command object and adding parameters take about a third as much code
as it normally would. So hopefully there is something for everyone in this
article.
Please feel free to post any comments, bugs or suggestions for
improvement.
Create the Base Form
A base form is actually just a form like any other form, but one from which
another form's appearance and code are inherited. So, from the IDE, create your
VB.NET project, then in Solution Explorer, right-click the project, choose
"Add", and then "Add Windows Form". In the dialog that appears, name your base
form. I chose to distinguish my base form from any other forms by naming it
BASEfrmUnboundDataForm. The "frm" prefix is usually used in form names in
Visual Basic. I chose to prefix that with the word "BASE" to help me remember
that this is a base form to be inherited from and not to be used directly.
The idea is to create this base form, layout the controls on it as you want
all data forms to appear, and then also include the basic code needed to do most
operations. We should allow many methods to be overridden, should the programmer
inheriting the form desire to deviate from the basic operations. Remember other
forms will inherit this one, and yes some programming and adding of controls
will still be required, but we will greatly reduce the amount of code and
thinking required to make one or many working data forms. For this reason, we
should keep our base form's layout simple and generic.
Controls to Add to the Base Form
- Add button (btnAdd)
- Update button (btnUpdate)
- Delete Button (btnDelete)
- Refresh Button (btnRefresh)
- Lookup Listbox (lstLookup)
- A label above the Lookup Listbox for a Caption (lblLookupList)
- Some panel controls to handle resizing and spacing issues (pnlTop,
pnlRight, and pnlFields)
- A GroupBox control used to make a simple rectangle for looks
(gbHorvRule)
- An ErrorProvider to allow for data entry error notification
(errProvider)
When you get done your form should look like that of Figure 1 below.
Figure 1
Looking at the form above (figure 1), notice that there are 3 panel controls
and that all of my controls are placed in those. pnlTop is the top left
panel and includes the lookup listbox, lstLookup. Its dock
property is set to "Top."
The panel to the right, having the Add(btnAdd),
Update(btnUpdate), Delete(btnDelete) and
Refresh(btnRefresh) buttons, is named pnlRight and has its
dock property set to "Right." The third panel control, named
pnlFields is to the bottom left. This panel has its dock property
set to "Fill." This is where the form inheriting this form will place its data
entry controls i.e. textboxes, checkboxes, etc. Its dock property is set
to "Fill" in order to take up whatever space is left over from the other two
panel controls.
(Note: My base data form had a bigger height and width initially but I
shrank them in order to keep the image dimensions down for this site. Make yours
about the height and width you'd like your inheriting form to have
initially.)
One thing to keep in mind is that these controls will not be able to be
changed at all unless you change the modifiers property of each of the
controls appropriately. For example setting btnAdd's modifiers property to
"private" means the inheriting form can't change it at all in size, text or any
other way. For this reason I set all of the control's modifiers
properties to "protected friend" so that any inheriting form can change them as
desired. This provides maximum flexibility.
I added a GroupBox control named gbHorvRule just to visually separate
the control buttons from the left side of the form.
Code to Add to the Base Form
A lot of thought had to go into this part in order to provide the desired
default functionality, but allow the inheriting form to deviate from it.
First we need an enum to keep track of whether the form is in Add or Update
mode. This is necessary because a person could click btnAdd in order to
add a new record thus resetting the form to accept new data, and after entering
the data the user must click btnUpdate in order to accept the changes.
However, the person could have been editing an existing record, just made
changes and clicked btnUpdate to accept them. btnUpdate will call a
method will decide whether to call InsertRecord or
UpdateRecord based on the current operation.
Protected Enum FormDataMode
Add
Update
End Enum
We also need to add some member variables in our base form.
Private _DataMode As FormDataMode = FormDataMode.Update
Below are member variables that must get set or things won't work.
Private _ConnectionString As String = String.Empty
Private _TableName As String
Private _PrimaryKeyControl As Control
Private _PrimaryKeyField As String
I chose to name member variables starting with "_". _ConnectionString will obviously contain the string needed to
connect to the database. _TableName is the name of the
table that the form will represent. _PrimaryKeyControl
is actually a reference to the control on the form that contains the primary
key. _PrimarykeyField is the name of the field in this
table that is the primary key. Having all of this information allows the base
form to perform some actions correctly without much or any additional coding. Of
course all of the member variables are set using properties or through the
constructor and not directly.
Below are those that can be set, but have
their defaults:
Private _PanelsUseFormsBGColor As Boolean = True
Private _PanelsUseFormsBGImage As Boolean = True
Private _ConfirmBeforeDeleting As Boolean = True
Private _PrimaryKeyValueNeedsQuotes As Boolean = False
Private _TypeOfDatabase As ifrmDataProviderType = _
ifrmDataProviderType.SQLServer
ifrmDataProviderType.SQLServer is an enum that I
provide in a file called BASEFORMEnums.vb. It is in a separate file so that I
can use it it various places. Its contents are below:
Public Enum ifrmDataProviderType
SQLServer = 0
OleDB
Oracle
End Enum
The last member variable (below) is a field that will have a readonly
property that can be read to see if data has changed.
Private _DataChanged As Boolean = False
Properties
Basic properties are added for each of the member variables, following the
obvious naming conventions and doing nothing more than returning the value or
setting it. See the ConnectionString property (below)
for example.
Property ConnectionString() As String
Get
Return _ConnectionString
End Get
Set(ByVal Value As String)
_ConnectionString = Value
End Set
End Property
An exception is the DataMode property (below) which
not only sets the value of _DataMode, but also calls an
empty overridable function to provide similar functionality of an event. The
overriding form can just override this function and provide whatever
functionality is desired to take place when the data mode is changed.
Private Property DataMode() As FormDataMode
Get
Return _DataMode
End Get
Set(ByVal Value As FormDataMode)
_DataMode = Value
DataModeChanged(Value)
End Set
End Property
DataModeChanged method (below) Protected Overridable Sub DataModeChanged(ByVal _
CurDatamode As FormDataMode)
End Sub
(The code for all other properties is included in the download.)
Constructor Code
For the constructor code, I chose to provide three constructors for my base
form. The first "New()" was provided just so the Form Designer would allow my
form to load. It should not be used for other purposes. One of the other two
constructors should always be used.
Both of the other two constructors are identical except that one has an
additional parameter to specify the type of database. If not specified, then SQL
Server is assumed. You may add additional database support as you like.
Notice that through the constructor the form gets the connection string, the
name of the table to work with and the name of the primary key for that
table.
Public Sub New()
MyBase.New()
InitializeComponent()
Debug.WriteLine("Do not use the new() constructor but" & _
" only those with arguments")
End Sub
Public Sub New(ByVal strConnectionString As String, _
ByVal strTableName As String,
ByVal strPrimaryKeyField As String)
MyBase.New()
InitializeComponent()
_ConnectionString = strConnectionString
_TableName = strTableName
_PrimaryKeyField = strPrimaryKeyField
End Sub
Public Sub New(ByVal strConnectionString As String, _
ByVal strTableName _
As String, ByVal strPrimaryKeyField As String,_
ByVal TypeOfDatabase _
As ifrmDataProviderType)
MyBase.New()
InitializeComponent()
_ConnectionString = strConnectionString
_PrimaryKeyField = strPrimaryKeyField
_TableName = strTableName
_TypeOfDatabase = TypeOfDatabase
End Sub
Methods
Now let's take a look at the methods that we must implement in our base
form.
Our Form_Load event needs to fill our lookup by
calling FillLookup. But it must not fill it when in
design mode.
Private Sub BASEfrmUnboundDataForm_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
If Me.DesignMode = False Then FillLookup()
End Sub
The methods related to FillLookup will demonstrate
the general philosophy for providing basic functionality in the base form,
calling methods at various steps along the way--some of which do something by
default and are overridable should a change be desired and others that are empty
and overridable and do nothing unless overridden.
The code for FillLookup is shown below:
Protected Overridable Sub FillLookup()
If Me.DesignMode = True Then Exit Sub
Dim conn As System.Data.IDbConnection
Dim cmdGetRecords As System.Data.IDbCommand
Dim dr As System.Data.IDataReader
Dim objListItem As clsListItem
Dim strID As String
conn = OpenConnection()
If conn Is Nothing Then Exit Sub
Try
Notice, in the code segment above, how the conn
and cmdGetRecords variables are defined. Instead of
defining them as SQLConnection and SQLCommand objects, I chose to define them using the
interface that the SQLConnection and OLEDBConnection implement (IDBConnection) and did the same for the Command and DataReader Objects (IDBCommand,
IDataReader). In doing this the connection and command
objects used won't be tied to just one database but can accept any database that
implements these interfaces. So for example, I used IDbConnection, IDbCommand, and IDataReader instead of SQLConnection, SQLCommand, and SQL
DataReader, respectively.
Also notice that when setting the command object, cmdGetRecords, which will actually get the records to fill up
the lookup listbox, I call the _SelectCommandToFillLookup
method. I chose to name all methods that must be overriden with a
preceding "_" so that they would show up at the top in the "Method Name"
dropdown of the code window in the IDE. There are 6 such methods that the
inheriting form must override. Thus the base form is simply calling this
method, and the inheriting form is getting the appropriate SQL string into a
command object and returning it. This command object is then executed and the
results passed to a datareader.
cmdGetRecords = _SelectCommandToFillLookup()
cmdGetRecords.Connection = conn
dr = cmdGetRecords.ExecuteReader()
The lookup listbox, lstLookup, is then cleared and using the
datareader, each record is added to the listbox.
As you know listboxes in Visual Basic .NET can contain objects instead of
just strings. For that reason, one can simply make a class that has whatever
inside, and override the ToString function to return a
value that will be shown in the listbox or wherever.
The code in this method calls _CreateNewLookupListItem which must be overridden. The
programmer can override as he likes, within limits, but must return a clsListItem which consists of what to display and the primary
key. _CreateNewLookupItem is passed the datareader,
which contains the current row (a read should not be performed on the
datareader). Perhaps if someone else were going to use your base form you'd
only want to pass them a cloned copy of the datareader or something. You can
decide if this behavior should be modified.
lstLookup.Items.Clear()
Do While dr.Read()
objListItem = _CreateNewLookupListItem(dr)
lstLookup.Items.Add(objListItem)
Loop
An example of the inheriting form overriding _CreateNewLookupListItem is show below: Protected Overrides Function _CreateNewLookupListItem(ByVal _
CurDataReader As System.Data.IDataReader) As clsListItem
Dim li As New clsListItem(CurDataReader.GetValue(0), _
CurDataReader.GetValue(0))
Return li
End Function
Continuing with the FillLookup method (below), we
see that if there is anything in the list, then the first item is selected.
There is also some cleanup. Notice that if anything goes wrong i.e. an exception
occurs, then FillLookupError is called and is passed
the exception. FillLookupError just uses a msgbox to
display the ex.message property of the exception. It is
overridable if the programmer chooses to do something a little different with
the error.
If lstLookup.Items.Count > 0 Then lstLookup.SetSelected(0, True)
dr.Close()
conn.Close()
cmdGetRecords.Dispose()
conn.Dispose()
Catch ex As System.Exception
FillLookupError(ex)
End Try
End Sub
This is the general scheme in which each operation is carried out. Insert,
Update, and Delete all use the same general concept. For example, when an record
is to be updated, UpdateRecord is called, this gets the
command to use to do the update from _UpdateCommand,
which was overridden by the inheriting form, executes it, and then calls UpdateError if anything goes wrong, or UpdateSucceeded if all goes right. UpdateSucceeded does nothing by default but could be
overridden by the inheriting form in order to do something if the update
succeeds. Similar methods are used for inserting and deleting--and using the
same naming conventions.
Rather than display the entire code for the Insert, Update and Delete
methods, I will just highlight the interesting parts of the UpdateRecord method which is the main method for updating
records. DeleteRecord and InsertRecord are the main methods for deleting and inserting
records respectively. Each of these is overridable, but I wouldn't recommend
overriding them, but rather overriding the "helper" methods that they call in
their normal course of operation. This works in a way that is similar to the
FillLookup method, that we examined above.
All source code can be downloaded, but lets take a look at one of the three
methods--the UpdateRecord method:
Protected Overridable Sub UpdateRecord()
Dim conn As System.Data.IDbConnection
Dim cmdUpdate As System.Data.IDbCommand
Dim intRowsAffected As Integer
If Not IsValidForm() Then Exit Sub
Notice the IsValidForm method. This is a function
that returns a boolean (true/false) value. By default it returns true. It can be
overridden by the inheriting form in order to validate the data before an update
is made. If it returns false, then Exit Sub is called and the update never
happens.
conn = OpenConnection()
If conn Is Nothing Then Exit Sub
Try
cmdUpdate = _UpdateCommand()
cmdUpdate.Connection = conn
intRowsAffected = cmdUpdate.ExecuteNonQuery()
If intRowsAffected <> 1 Then
UpdateError()
Else
_DataChanged = True
In the code segment above, notice that if anything goes wrong, UpdateError (an overridable method) is called. Also, if all
goes well, then _DataChanged is set to true, some other
things happen and UpdateSucceeded is called. Dim pkValue As String = PrimaryKeyControl.Text
FillLookup()
FindLookupItemByPK(pkValue)
UpdateSucceeded()
End If
In the above code, notice that the primary key control's value is read
and stored, then the lookup is filled so that the new value will appear, and
then FindLookupItemByPK (a private method) is called
with the stored value to make the item updated be the current row. Finally,
UpdateSucceeded (another overridable method) is called
since the update was successful. Catch ex As System.Exception
UpdateError(ex)
Finally
conn.Close()
cmdUpdate.Dispose()
conn.Dispose()
End Try
End SubUpdateError is called if any exceptions
occurred. Like the FillLookup method's error method,
this one can be overridden, but if not just shows the ex.message value in a MsgBox. Before
the sub exits, the connection is closed, and the command and connection objects
are disposed.
The InsertRecord and DeleteRecord methods follow a similar pattern. However the
DeleteRecord method uses a few new methods, mainly
GetLookupItemIndexByPK and AllowRecordDeletion.
If Not AllowRecordDeletion(PrimaryKeyControl.Text) Then Exit Sub
AllowRecordDeletion (called above) is a function
that is passed the primary key and returns true or false--true meaning "ok to
delete." This function is overridable, but by default, checks the ConfirmBeforeDeleting property and if it is set to true,
prompts the user with a MsgBox asking them if they are
sure they want to delete. The default code, returns true or false depending on
their answer or always returns true if ConfirmBeforeDeleting is set to false. This function could be
overridden to perform some checks like, "does this record have child records
that would need to be deleted, etc?" and maybe refuse deletion if true. This
would be good in the case of our "Lookup Table" where we have a list of possible
field values and we want the user to be able to Add, Update and Delete these,
but if they are in use i.e. at least one record was created using a certain
value, then they shouldn't be allowed to be deleted. If the value had never been
used then allowing deletion would be desirable.
iItemIndex = GetLookupItemIndexByPK(PrimaryKeyControl.Text)
The other differing method is the GetLookupItemIndexByPKmethod (called above) which as its name
suggests, is passed a primary key value and returns the index of that item in
the listbox (lstLookup). This is needed so that after
the record is deleted, the listbox can select the prior record if one is
available or none if the user deleted the last record. DeleteRecord handles all of these chores by default, but as
you can see can be overridden to provide more or differing functionality on a
form by form basis as required.
Of course there are a few other methods that need to be considered. For
example, when you click Add, you basically need to clear the form to its default
"new record" values. This is facilitated by calling the ClearForm method.
The default implementation of ClearForm uses some
pretty nifty intelligence in order to clear each control in the fields panel,
pnlFields, which holds the data entry controls that the
inheriting form will supply. This implementation examines each control to see
what type of control it is and then assigned it the most logical and expected
default values. A listbox, for example, selects -1 which means "no selection"
and a textbox clears the text. A combobox attempts to select the first item in
the list, but upon failure selects nothing, and a checkbox clears the checkmark.
The code for this method is shown below.
Protected Overridable Sub ClearForm()
Dim ctl As Control
For Each ctl In pnlFields.Controls
If ctl.GetType.Equals(GetType(TextBox)) Then
CType(ctl, TextBox).Text = String.Empty
ElseIf ctl.GetType.Equals(GetType(CheckBox)) Then
CType(ctl, CheckBox).Checked = False
ElseIf ctl.GetType.Equals(GetType(ComboBox)) Then
Try
CType(ctl, ComboBox).SelectedIndex = 0
Catch
CType(ctl, ComboBox).SelectedIndex = -1
End Try
ElseIf ctl.GetType.Equals(GetType(ListBox)) Then
CType(ctl, ListBox).SelectedIndex = -1
End If
Next
End Sub
Wiring These Methods to Our Controls and Handling Some UI Events
While we won't discuss them all, we will take a look at a few of the
important ones--a casual browsing of the downloaded source will reveal a few
additional methods whose purpose should be obvious.
btnAdd_Click()
The Add button's click method only needs to call ClearForm, set the DataMode property
to FormDataMode.Add and disable both the Add and Delete
buttons. Add is clicked when the user wants to add a new record. It is required
that they click the Update button to commit this record.
btnUpdate_Click()
The Update button's click method just calls InsertRecordif the DataMode property
is set to FormDataMode.Add, and UpdateRecord if it was set to FormDataMode.Update.
btnDelete_Click()
The Delete button's click method is simple. It just calls DeleteRecord. By using the primary key to perform a delete,
this method should, offer the desired functionality by default, and will
probably never need changing.
btnRefresh_Click()
The Refresh button's click method calls the RefreshClicked method, which by default calls FillLookup, but can also be overridden. You should override
RefreshClicked if you have other listboxes, etc.,
besides the lstLookup listbox, that need
refreshing.
lstLookup_SelectedIndexChanged
When the item selected in the lookup listbox,lstLookup, changes, we
need to fill the controls in pnlFields with the record values associated
with the item selected. For that reason, this event first calls PopulateForm and then enables the Add and Delete buttons. It
also sets the DataMode to FormDataMode.Update as opposed to FormDataMode.Add. That way if the user changes something and
hits the Update button, the UpdateRecord method is
called to save the changes, and not the InsertRecord
method which would add a new record based on the current field values.
Now We Must Provide Some Methods to Allow for Validation Using the
ErrorProvider Control
We should provide some methods that will affect the controls in
pnlFields, based on some validation code, probably placed in the IsValidForm method, which use the ErrorProvider control to
display an error beside the control having the offending data.
I created five such methods, all public, to facilitate this. First lets take
a look at them and later we'll see how they can be used in the "Writing Code In
the Inherited Form" section. The first two, ClearErr
and SetErr, can be used anywhere, but are mostly used
as helper methods for remaining three methods which are easier to use.
ClearErr |
Is passed the appropriate control and clears any errors for it
by calling errProvider.SetError and passing it the name
of the control and an empty string. |
SetErr |
Is passed the appropriate control and calls errProvider.SetError to set that controls error string to the
one passed in to SetErr. |
ErrRequired-ValueSupplied |
Is passed the appropriate control and an error message.
Functions like the RequiredFieldVaildator in ASP.NET.
Requires that the user enters something. Returns false and calls SetErr if they didn't. |
ErrRegExpMatched |
Is passed the appropriate control, a regular expression, and an
error message. Tests to see that the control in question fits the regular
expression given and if not returns false and calls SetErr with the error message. |
ErrText-LengthInRange |
Is passed the appropriate control, a minimum and maximum length
for the text entered, and an error message. This method is only used for text
related controls such as the textbox or combobox (which has a textbox). Returns
false if invalid and calls SetErr with the error
message. |
Feel free to add your own validation methods as you need them. Perhaps you
might want to add one that limits the min and max values for numeric data i.e.
"must be between 1 and 99." When IsValidForm is called,
and if you have overridden it, you can call these methods to decide if the form
is valid and return false if not.
Perhaps I have failed to mention some of the other methods but you will find
any of them in the downloadable source.
Now, let's take a look at using this joker...
Create the Inherited Form
Creating an inherited form is simple, just right click on the project in
Solution Explorer, choose Add and then instead of "Add Windows Form" choose "Add
Inherited Form." Name your inherited form and then click OK. After this the
"Inheritance Picker" dialog is displayed. Choose BASEfrmUnboundDataForm as the form to inherit from and click
OK. You should now see your form in the Solution Explorer window.
Remember there are 6 methods that absolutely must be overridden before
your form will work and there are some other little subtleties that should also
be performed.
Viewing the code for your new form, you will notice something like the
following for the first 2 lines of code.
Public Class frmInheritingForm
Inherits MYPROJECTNAME.BASEfrmUnboundDataForm
Of course, your form's name would be the class name and the name of your
project would actually be there instead of "MYPROJECTNAME." We can see that our
form inherits from BASEfrmUnboundDataForm. This means
it has all the methods and code that the base form has, and may access those
items that inheriting forms are allowed to access. Since the visual aspects of a
form are actually just generated code from the form designer to the .vb code
file, our form inherits the methods and the visual appearance of the base form.
This is really cool and can save you 100's of hours of recoding the same old
thing again and again.
Imagine that we are writing an application for the police department. All of
our users are officers. As a result it makes sense for our officer table to also
be our users i.e. based upon the officers badge number (actually badge number
plus last 4 digits of SSN--this done incase officer 55 leaves and a new officer
is hired and gets badge 55. If officer one was 559306 and officer two is 552963,
then they are easily distinguished as different officers and no historically
linked data is lost.) Using this example let's imagine that our officer table
contains Badge, FirstName, MidName, LastName, Password, UserType, and Active
(still with us or not).
With this in mind we would add text box controls for each field except the
last two, which would be a listbox and checkbox, respectively. We would name
them obvious names such as txtBadge.
A Screen Shot of My Actual Officers Form, frmOfficer (below)
Of course the data is fake, and I added a few things that I won't speak about
in the article, but the complete source code to this form is included as
frmOfficer.vb and frmOfficer.resx for your viewing. I am actually using this
very form in a law enforcement application at present. For that reason, please
vary the appearance of your form, if you should actually create a real officer's
form as part of a program to sell. Notice in my version I have a special button
for changing the badge. I also have the Badge field as read-only except during
addition of a new record. Also the officer icon, is my own creation and property
and not for commercial use. Feel free to replace it with your own icon in the
event that you do something commercial with a form like this one for law
enforcement.
Coding the Constructor
The first thing we should do is create a constructor for the new form. In
order for our form to function we must have a connection string.
Public Sub New(ByVal strConnectionString As String)
MyBase.New(strConnectionString, "tblOfficer", "Badge")
InitializeComponent()
PrimaryKeyControl = txtBadge
PrimaryKeyValueNeedsQuotes = True
PrimaryKeyField = "Badge"
End Sub
Notice in the constructor (above) that we accept a connection string so that
when someone instantiates our form, they will pass the connection string as part
of the New method--the constructor.
We can still access our base form by using the MyBase keyword. For this reason, our new constructor, first
calls the base form's constructor passing it the connection string, the table
name and the name of the primarykey, by calling MyBase.New. This line should
always be added to your form's constructor.
Next we set some of the properties--these are required. We tell the
base form what the name of the control that will hold the primary key values
is--in this case txtBadge. Then we set PrimaryKeyValueNeedsQuotes because the primary key is a
string and not a number although most will be stored in a textbox regardless.
Finally we set the PrimaryKeyField property to
Badge.
Now all that we need to do is override the 6 methods that must be overridden.
Remember these start with the "_" character. To see them, with the code window
for your inherited form, click "(overrides)" in the top left listbox and then
the method to be overridden from the right listbox. One of these methods is the
_CreateNewLookupListItem method. By selecting one of
these methods, VB.NET adds the method code template to the code window i.e. the
function or sub and its parameters and an "End Function" or "End Sub." We just
have to fill in the blanks.
_CreateNewLookupListItem
The _CreateNewLookupListItem method gets called each
time that an item is to be added to the listbox. The current datareader is
passed to this method so that by using the GetString or GetValue methods and
creating a clsListItem, we can tell the base form how
to make the list items. This allows us some flexibility in what is to be
displayed and the key of that item. A sample implementation is shown below.
Dim li As New clsListItem(CurDataReader.GetString(0), _
CurDataReader.GetValue(1))
Return li
_SelectCommandToFillLookup
The _SelectCommandToFillLookup method returns a
command object with the appropriate SQL required to fetch all the records of our
table showing two fields, the first being what to display in the listbox--could
be concatenated such as the code below--but should only return 2 fields. The
second field should be the primary key field of the table. This is used by FillLookup.
Dim cmd As New System.Data.SqlClient.SqlCommand(_
"SELECT LastName + ', ' + _
FirstName + ' ' +
MidName as Name,Badge FROM tblOfficer ORDER BY LastName")
Return cmd
The above is a whopping 2 lines of code to accomplish all that we would like
to accomplish in displaying our rows in a lookup table that will be used to
navigate to the record desired for deletion or modification. And the second line
merely returns the command object. So actually this could be done in just one
line by replacing the "Dim cmd as" with "return" and getting rid of the "Return
cmd" line if you like.
_SetFields
The base form method, PopulateFields, calls _SetFields in order to move data from the datareader to the
corresponding controls. Since the base form knows nothing about these controls
it allows you to tell it how to do that by passing you the datareader, being set
to the current row. You simply use GetString or GetValue to read the appropriate fields. An example is found
in the code below:
With CurDataReader
txtBadge.Text = .GetString(0)
txtFirstName.Text = .GetValue(1)
txtMidName.Text = .GetValue(2)
txtLastName.Text = .GetValue(3)
txtPassword.Text = .GetValue(4)
Try
cboUserType.SelectedIndex = _
cboUserType.FindString(.GetValue(5))
Catch
End Try
ckActive.Checked = .GetBoolean(6)
End With
As you can see from the simple code above, it is merely reading the right
field and sticking it in our control. This is very straightforward and easy. For
the combobox, will use the FindString method to find
the right value and select it. This is a combobox that doesn't allow items that
are not in its list.
_SelectCommandToGetRecordByPK
The base form method, PopulateFields calls _SelectCommandToGetRecordByPK, passing it the primary key of
the item in lstLookup that was selected and gets a
Command object to be used to fetch that exact record. PopulateFields then calls _SetFields
in order to transfer that record data into the appropriate controls located in
pnlFields.
_InsertCommand
The base form calls InsertRecord if the Add button
was clicked, data was entered and then the Update button was clicked. The btnUpdate event decides that since we are in Add mode, it
will call InsertRecord which uses _InsertCommand to get the correct command object to insert
the new record.
Basically you create a new command object, that will take the data from your
controls (those in pnlFields) and move it to
DataParameters that are added to the command object and then return that command
object. To simplify things and since I was working with MSDE (SQL Server Desktop
Addition), I decided to make a stored procedure to handle my Insertions. And
also a special class to make the whole process simpler.
Dim pb As New clsParamBuilder(New SqlClient.SqlCommand(_
"spAddNewOfficer"), CommandType.StoredProcedure)
pb.AddParam("@Badge", txtBadge.Text)
pb.AddParam("@FirstName", txtFirstName.Text)
pb.AddParam("@MidName", txtMidName.Text)
pb.AddParam("@LastName", txtLastName.Text)
pb.AddParam("@Password", txtPassword.Text)
pb.AddParam("@UserType", cboUserType.Text)
pb.AddParam("@Active", ckActive.Checked)
Return pb.CommandObjYou can do this by creating a new command object
as normal, with spAddNewOfficer as the text and setting the commandtype
to "stored procedure", declaring a variable as a SQLParameter, adding each
parameter's info and then adding each parameter to the command object and
returning it. However, this results in a lot of code. For that reason, as you
will notice above, I created a class called clsParamBuilder to make this a lot simpler.
The code for this class, which is in a separate code file, is shown
below:
Code for clsParamBuilder
Public Class clsParamBuilder
Private _cmd As IDbCommand
Public ReadOnly Property CommandObj() As IDbCommand
Get
Return _cmd
End Get
End Property
Sub New(ByVal cmd As IDbCommand)
_cmd = cmd
End Sub
Sub New(ByVal cmd As IDbCommand, ByVal CommandType As _
System.Data.CommandType)
_cmd = cmd
_cmd.CommandType = CommandType
End Sub
Public Sub AddParam(ByVal ParamName As String, _
ByVal ParamValue As Object, _
Optional ByVal ParamDirection As System.data.ParameterDirection = _
ParameterDirection.Input)
Dim p As System.Data.IDbDataParameter
If TypeOf _cmd Is System.Data.SqlClient.SqlCommand Then
p = New System.Data.SqlClient.SqlParameter(ParamName, ParamValue)
ElseIf TypeOf _cmd Is System.Data.OleDb.OleDbCommand Then
p = New System.Data.oledb.OleDbParameter(ParamName, ParamValue)
End If
p.Direction = ParamDirection
_cmd.Parameters.Add(p)
End Sub
End ClassI won't take the time to explain this class as its usage and
function should be evident given the code and the above examples. I have found
it to be a real time saver. It prevents repetitive verbose coding when dealing
with command objects and their parameters.
_UpdateCommand
The base form method calls UpdateRecord if the Add
button was not clicked, data was changed and then the Update button was clicked.
The btnUpdate event decides that since we are in Update mode, it will
call UpdateRecord which uses _UpdateCommand to get the correct command object in order to
update the current record.
The _UpdateCommand method works the same as the
_InsertCommand except that it has code to perform an
update. In my case it just calls a different stored procedure and the rest of
the code is the same as the one for _InsertCommand.
Now, since we used a combo box for User Type we must somehow fill it with
values. Assuming that these are fixed values, we would simply override the Form_Load event and place code there to fill our combo with
those fixed values. Be sure to call PopulateForm at
the end of this code if you over load Form_Load.
At this point our form should run. And as you can see, by inheriting the base
form, which has around 835 lines of code, excluding the code that the form
designer generates and maintains, we have saved a lot of time and coding. A
recent, fully functioning inherited form of mine had less than 200 lines of
code--of course this is counting spaces, comments, function definitions and
stubs that the IDE adds by just selecting the method from the method drop-down
list. So, yes there is still some coding required and especially if you have
controls such as combos to restrict to certain values, or want to write
validation code, but it is pretty simple code. Actually what I do is just open
an already inherited form, copy much of the code and paste it into my new
inherited form. Be careful not to copy all of it. You won't want to copy the
same generated code, nor over the name of the form class, etc. of
course.
Adding the Finishing Professional Touches to Our Form
Although our form will run, we should add a few more things to it, such as
validation in order to make it more professional.
ClearForm
In my form I override the ClearForm method which
would clear all textboxes and clear my checkbox (ckActive). Since most
people are active by default I want ClearForm to do its
normal thing so I call MyBase.ClearForm, but then I
want to check the checkbox by default so I say add the line
ckActive.Checked=true after the call the MyBase.ClearForm. That's all there is to it.
DataModeChanged
You may wish to override the DataModeChanged method
to
- Make some controls readonly based upon whether adding a new record or
updating and existing one
- Perform some additional action(s) based upon the mode changing
AllowRecordDeletion
In my form I also override the AllowRecordDeletion
method so that it takes the primary key that is passed in, sees if the officer's
badge(PK) is already in another table as a foreign key and if so will not allow
the deletion of the officer. You may have similar needs to check certain things
before allowing a record to be deleted. AllowRecordDeletion is the place to do that.
IsValidForm
You will most likely want to override the IsValidForm method in order to provide data validation for
your form. Let's take a look at how I did it in my officers form.
Dim bValid As Boolean = True
If Not ErrRegExpMatched(txtBadge, "[A-Z][A-Z]\d{6}", _
"Badge format should be 2 Letters, 2-digit badge number, " & _
"then last 4 SSNO of officer. example BD021234") _
Then bValid = False
If Not ErrRequiredValueSupplied(txtFirstName, _
"First name is required")_
Then bValid = False
If Not ErrRequiredValueSupplied(txtLastName, "Last name is required")_
Then bValid = False
If Not ErrTextLengthInRange(txtPassword, 4, 15, _
"Password must be between 4 and 15 characters long.") _
Then bValid = False
If Not bValid Then
MessageBox.Show("There were invalid entries on this form." & _
" It cannot be added/updated until you've fixed the problems." & _
" There are exclamation points(!) beside each problem.", _
"INCOMPLETE OR INVALID DATA", MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation, _
MessageBoxDefaultButton.Button1, _
MessageBoxOptions.DefaultDesktopOnly)
End If
Return bValid
From the above code listing (the contents of my IsValidForm overridden method), you can see how to use the
validation methods that we added to our base form. See how simple form
validation is when done in this way. We start with bValid set to true and if anything goes wrong we set it to
false. If it is false we tell the user something is invalid and then we return
bValid so that the base form knows what action to take.
Basically if adding or updating a record and we return invalid, the base form
will not add or update the record. The problem controls will have the little red
exclamation icon beside them and will show a tool tip if we hover over the top
of them.
You may very well desire many more features in your inherited form and you
can easily add them. Just remember that overriding some methods in the base form
will necessitate that you call the method in the base form (using the MyBase keyword) at some point in your overridden method in
order to get the default behavior in addition to your new behavior.
Conclusion
Well, I hope that you've enjoyed this tutorial and that this base form will
be of use to you. And even if you may not use this base form, hopefully you have
gained some insight into using Visual Inheritance, and some other concepts.
Fill free to use this code and expand upon it as you like. I would ask that
you give me credit for my work by keeping my copyright line at the top of the
BASEfrmUnboundDataForm and other code file(s) and make
a comment below it if you make any changes to it. Also this code is provided "AS
IS WITH ABSOLUTELY NO WARRANTIES AND I TAKE NO RESPONSIBILITY FOR ANY
UNINTENTIONAL DAMAGES THAT IT MAY OR MAY NOT CAUSE." Other than these
requirements, feel free to use and modify this code as you desire. It may be
included in a product to be sold, but may not be sold in and of itself. In other
words, I am providing this code to be freely used by all and no one should have
to pay for it, but if you make a program to sell that uses it, then that is
perfectly acceptable as long as I am given credit as mentioned above--a
copyright notice of the original design.
Again, any comments, or suggestions for improvements, or bugs are
welcome.
History
- March 2004 Initial submission
