In our last example we
took a look at input and output parameters and return values. The stored procedure
we created was a SELECT stored
procedure as it was selecting a certain column from a row, based on the parameter
passed. The first stored procedure that was shown at the beginning of this chapter
selected all rows from the State_T table.
At this point, if you have not created that stored procedure to select the state
names, then do so now. The code for that stored procedure is listed below:
CREATE PROCEDURE up_select_state_names
AS
SELECT State_ID,
State_Name_VC,
State_Abbreviation_CH
FROM State_T
ORDER BY State_Name_VC
After creating the stored procedure, don't forget to set the permissions for the Developers, Sales Staff, and Management roles so they will be able to execute this stored procedure. You may need to choose Refresh in the Action menu to refresh the Stored Procedure view, before you see the new one that you just added.
As you can see from the two stored procedures that we have
created thus far in this chapter, SELECT stored procedures can select all data from all rows, or certain data
from certain rows, and can either accept parameters or not. In Chapter 9 we
will begin to create complex stored procedures that select data from multiple
tables and use cursors. Cursors
are database elements that control how we are able to
navigate the Recordset
object, and how we can perform updates to the fields
in the Recordset object.
Try It Out – Select Stored Procedure
This example will display all state names and state abbreviations
using the up_select_state_names stored
procedure you just created, and will be the building block for our next three
examples. Once you reach the end of the chapter, this program will be a fully
functional data entry program for states.
With this in mind, we must give some thought to the design
of our front-end program. To enable us to reuse some of this code in other front-end
programs and server components, we will place all of the stored procedures (only
one for now) in a class that will manage the states in our State_T table. This will allow us to reuse this class in other programs that
need to manage state names and abbreviations.
This program should use a DSN-less connection, and the connection will be open throughout the execution of the program. The state name and abbreviation should be displayed on the form, and you should have navigation buttons that allow you to navigate throughout the recordset.
1. Start a new Standard EXE VB project. This table lists the modules that make up our project:
|
Module |
Name |
|
Form |
|
|
Class |
|
2. Change the caption of the form if you wish. The following table details the controls that should be placed on the form:
|
Control |
Name |
Caption/Notes |
|
Label |
|
State Name |
|
Label |
|
State Abbreviation |
|
TextBox |
|
Set the Text property to nothing |
|
TextBox |
|
Set the Text property to nothing |
|
Label |
|
Place this label below |
|
CommandButton |
|
First |
|
CommandButton |
|
Previous |
|
CommandButton |
|
Next |
|
CommandButton |
|
Last |
|
CommandButton |
|
Exit |
The form should look something like this:
|
|
3. Don't forget to set a reference to the Microsoft ActiveX Data Objects Library in your project.
4. Add a class module, change its name to clsStates,
and add the following code in the clsStates class. Change the owner prefix as necessary:
Option Explicit
Public Function GetAllStates(ByRef objConn
As ADODB.Connection) As Recordset
'Declare local objects
Dim objRS As New ADODB.Recordset
'Setup error handling
On Error GoTo GetAllStates_EH
'Set recordset cursor location
and open recordset
objRS.CursorLocation = adUseClient
objRS.Open "willist.up_select_state_names",
objConn, _
adOpenStatic, adLockReadOnly,
adCmdStoredProc
'Disconnect recordset
Set objRS.ActiveConnection
= Nothing
'Return with the recordset
Set GetAllStates = objRS
Set objRS = Nothing
'All was successful, exit
function
On Error GoTo 0
Exit Function
GetAllStates_EH:
Set GetAllStates = Nothing
Set objRS = Nothing
On Error GoTo 0
End Function
5. Since this is a public method in our class, we should set the description in the procedure attributes. On the Tools menu choose Procedure Attributes and enter a description for this method.
6. The code which needs to be added to the form is presented step by step
in the How It Works – The Form section. You should work through and
add this code to the frmTryItOut2 form.
How It Works – The States Class
The GetAllStates function in the clsStates class accepts the Connection object that is set in the Form_Load event, and is passed by reference in case any properties are set as
a result of the execution of the recordset. This function returns a Recordset
object containing a list of all state names and abbreviations.
The first thing we do is to declare a Recordset object and set up error handling:
Public Function GetAllStates(ByRef objConn
As ADODB.Connection) As Recordset
'Declare local objects
Dim objRS As New ADODB.Recordset
'Setup error handling
On Error GoTo GetAllStates_EH
Since we are going to be passing the recordset back to the
caller, we need to use a client-side cursor, which allows us to disconnect the
recordset from the Connection object. Next we open the Recordset object, specifying
the stored procedure (with the owner prefix) as the Source parameter of the Open method. We specify the Connection object that was passed by reference as the ActiveConnection parameter, and the adOpenStatic constant from the CursorTypeEnum enumeration as the CursorType parameter. Since we are only going to be reading the data, we specify
the adLockReadOnly constant from the LockTypeEnum for the LockType parameter. Last, we specify the adCmdStoredProc constant from the CommandTypeEnum as the Options parameter, since we are executing a stored procedure.
We use a static cursor so that we can navigate through our recordset in both directions and use bookmarks. This type of cursor does not reflect changes by other users, which really does not matter since we are using a disconnected recordset anyway:
'Set recordset cursor location
and open recordset
objRS.CursorLocation = adUseClient
objRS.Open "willist.up_select_state_names",
objConn, _
adOpenStatic, adLockReadOnly,
adCmdStoredProc
We then disconnect our recordset by
setting its ActiveConnection property to Nothing:
'Disconnect recordset
Set objRS.ActiveConnection
= Nothing
After we have successfully opened and disconnected our recordset,
we need to have the function return the recordset. We do this by setting the function
name equal to the Recordset object, and then set the Recordset
object to Nothing, thereby releasing its resources. You may be tempted to close the
recordset before setting it to Nothing. Avoid this temptation, as closing the Recordset
object, even after setting the function to the recordset, will return a closed
recordset.
'Return with the recordset
Set GetAllStates = objRS
Set objRS = Nothing
Last, we exit the function if everything was normal. If an
error occurred, we set the function to Nothing and de-reference our Recordset object:
'All was successful, exit
function
On Error GoTo 0
Exit Function
GetAllStates_EH:
Set GetAllStates = Nothing
Set objRS = Nothing
On Error GoTo 0
End Function
How It Works – The Form
While this is a lot of code and most of it is pretty straightforward, we will step through it all because you will need to enter each section of it into your form.
The first part of code that we want to look at in the form
is the code in the general declarations section. We want to ensure that our
project includes the Option Explicit statement so that all of our variables must be declared, and any keying
errors of the variable names will be caught. Then we define our database objects
that will be common to all procedures in our form. Here we are declaring a Connection and a Recordset object:
Option Explicit
'Declare database objects
Private m_objConn
As ADODB.Connection
Private m_objRS
As ADODB.Recordset
Whenever a database error occurs we will disable all navigation
buttons, and have placed that function into a separate procedure listed here.
We set the Enabled property of all of the navigation buttons to False:
Private Sub DisableButtons()
'Disable navigation buttons
cmdMoveFirst.Enabled = False
cmdMovePrevious.Enabled =
False
cmdMoveNext.Enabled = False
cmdMoveLast.Enabled = False
End Sub
Since we will be executing the stored procedure to get the state names and abbreviations more than once in subsequent revisions of this program, it makes sense to place this function into a separate procedure as we have done here.
The first thing we want to do is to check the Recordset
object to see if it has been set to a recordset. If it has and it is currently
open then we want to close it. We do this by checking
to see if the Recordset object is equal to Nothing.
If it is not then we check to see if the State of
the object is open using the adStateOpen constant from the ObjectStateEnum, just like we did for the Connection object in the Form_Load event:
Private Sub GetStates()
'If the recordset has been
set and is open, then close it
If Not m_objRS
Is Nothing Then
If m_objRS.State
= adStateOpen Then
m_objRS.Close
End If
End If
We need to instantiate the clsStates
class, which contains the code to get the state
names, and then we call the method in that class that will return the state
names in a recordset. Notice that we are passing the Connection object to the GetAllStates function in our call:
'Instantiate an instance
of the clsStates class
Dim objSelect As New clsStates
'Call the method to return
a recordset of states
Set m_objRS = objSelect.GetAllStates(m_objConn)
After the function completes, we need to check the Recordset
object to ensure that it is not equal to Nothing which would indicate that an error
occurred in getting the state names. If it is equal to Nothing
then we set an error message and disable the navigation buttons, otherwise we
call the cmdMoveFirst_Click method which will move to the first record and display it:
If m_objRS Is Nothing Then
'Error occured getting
recordset, display message
Call SetMsg("Error
occured getting recordset", vbRed)
Call DisableButtons
Else
'Move to the first record
Call cmdMoveFirst_Click
End If
The last line of code will de-reference our class and set
it to Nothing to free the resources that it used:
'Dereference the class
Set objSelect = Nothing
End Sub
This next procedure uses the parameters passed to load the state name and state abbreviation textboxes:
Private Sub LoadFields(strStateName As String,
_
strStateAbbreviation
As String)
'Load fields
txtStateName.Text = strStateName
txtStateAbbreviation.Text
= strStateAbbreviation
End Sub
The next procedure will display the
error message that we pass, and set the color of the font. Notice that the font
color is an optional parameter that has a default color assigned. We are using
the built-in ColorConstants enumeration for our font color parameter:
Private Sub SetMsg(strMessage As String,
_
Optional lngFontColor
As ColorConstants = vbBlack)
The first thing we do in the procedure is to set the Caption
property of our message label to the message that was passed as a parameter.
Then we set the ForeColor property to the font color passed. If no font color was passed, this
procedure uses a default font color of black, which sets the font color back
to normal:
'Set the message
lblMsg.Caption = strMessage
'Set the message font color
lblMsg.ForeColor = lngFontColor
End Sub
In the Form_Load event we want to set the form icon to nothing and set up error handling
for this procedure. Since we are going to be keeping a connection to the database
open during the entire execution of our program, it makes sense to place the
code here – we do not need to place it into a separate procedure.
We set the m_objConn
variable to an ADO Connection object, and then open the connection using NT authentication with
a DSN-less connection. You can use SQL Server authentication
instead of NT authentication if that was how your login was defined to SQL Server.
If using SQL Server authentication you will need to specify the UID and PWD parameters
as you did in the last chapter. Also remember to change the server name.
Private Sub Form_Load()
'Use the standard Windows
icon
Me.Icon = Nothing
'Set up error handling
On Error GoTo Form_Load_EH
'Set a reference to the ADO
conection object
Set m_objConn = New ADODB.Connection
'Establish a database connection
using NT Authentication
m_objConn.Open "Driver=SQL
Server;Server=WSDevelopment;" & _
"Database=Wrox_Auto_Works"
After we open our connection we need to check the State of
the connection to ensure that it is open, which we do in the next statement.
If the State of the connection is not open, we call the procedures to display our
error message and to disable the navigation buttons:
'Check connection state
If Not m_objConn.State
= adStateOpen Then
Call SetMsg("Connection
Failed", vbRed)
Call DisableButtons
Exit Sub
End If
If all goes well, we find ourselves here and we execute the procedure to get the state names. We then turn off error handling for this procedure and exit the procedure, bypassing the error handling routine:
'Get a recordset of the states
Call GetStates
On Error GoTo 0
Exit Sub
The error handling routine merely calls the procedure to display a message that the connection failed and set the font color to red. Then we call the procedure to disable the navigation buttons:
Form_Load_EH:
Call SetMsg("Connection
Failed", vbRed)
Call DisableButtons
End Sub
The cmdMoveFirst_Click procedure
clears any existing error messages by passing the Empty keyword
to the SetMsg procedure:
Private Sub cmdMoveFirst_Click()
'Clear any previous messages
Call SetMsg(Empty)
Then we move to the first record in our recordset and call
the LoadFields procedure, passing it the recordset fields to be used to load the
form fields:
'Move to the first record
m_objRS.MoveFirst
'Load the form fields
Call LoadFields(m_objRS!State_Name_VC,
_
m_objRS!State_Abbreviation_CH)
End Sub
The cmdMovePrevious_Click and
cmdMoveNext_Click procedures
clear any existing error messages and then move in the appropriate direction.
Let's take a look at the cmdMovePrevious_Click procedure
first. The first thing we do is to clear any existing error messages, and then
move to the previous record using the MovePrevious method of the Recordset
object:
Private Sub cmdMovePrevious_Click()
'Clear any previous messages
Call SetMsg(Empty)
'Move to the previous record
m_objRS.MovePrevious
After we reposition the recordset we must verify that a beginning
of file condition has not occurred by checking the BOF property of the Recordset object. If this property is set to True, we
display a message that we were already on the first record and then call the
MoveNext
method to move back to the first record:
'Check for an BOF condition
and correct if necessary
If m_objRS.BOF Then
Call SetMsg("Already
at first record", vbBlue)
m_objRS.MoveNext
End If
Regardless of whether a beginning of file condition exists, we call the procedure to load the fields on the form:
'Load the form fields
Call LoadFields(m_objRS!State_Name_VC,
_
m_objRS!State_Abbreviation_CH)
End Sub
The cmdMoveNext_Click procedure
performs the exact opposite of the cmdMovePrevious_Click procedure.
Again we clear any existing error messages and this time we move forward one
record position in the recordset:
Private Sub cmdMoveNext_Click ()
'Clear any previous messages
Call SetMsg(Empty)
'Move to the next record
m_objRS.MoveNext
This time we check to see if we have moved past the last record
in the recordset by checking the EOF property. If it is set to True this indicates that an end of file condition has occurred. If an end
of file condition has been reached, we display an error message and move back
to the last record:
'Check for an EOF condition
and correct if necessary
If m_objRS.EOF Then
Call SetMsg("Already
at last record", vbBlue)
m_objRS.MovePrevious
End If
Then we call the procedure to load the fields on the form:
'Load the form fields
Call LoadFields(m_objRS!State_Name_VC,
_
m_objRS!State_Abbreviation_CH)
End Sub
The cmdMoveLast_Click procedure
performs the same function as the cmdMoveFirst_Click procedure,
except it moves to the last record in the recordset:
Private Sub cmdMoveLast_Click()
'Clear any previous messages
Call SetMsg(Empty)
'Move to the last record
m_objRS.MoveLast
'Load the form fields
Call LoadFields(m_objRS!State_Name_VC,
_
m_objRS!State_Abbreviation_CH)
End Sub
The cmdExit_Click procedure
is executed when we click on the Exit button,
and it unloads the form, which in turn fires the Form_Unload event:
Private Sub cmdExit_Click()
Unload Me
End Sub
The Form_Unload event
contains all of the code to close and de-reference our database objects. We
first ensure that the objects are set by checking to see if they are not equal
to Nothing, and then check the State of the objects to see if they are open. We Close and
set the objects to Nothing, thereby freeing all resources:
Private Sub Form_Unload(Cancel As Integer)
'Close and dereference database
objects
If Not m_objRS
Is Nothing Then
If m_objRS.State
Then
m_objRS.Close
End If
Set m_objRS
= Nothing
End If
If Not m_objConn
Is Nothing Then
If m_objConn.State
Then
m_objConn.Close
End If
Set m_objConn
= Nothing
End If
End Sub
You can run this example program and, if you entered more than one state name and abbreviation, you will be able to navigate forwards and backwards in the recordset. If you have entered only one state then be patient – the next example you will code will allow you to insert more state names and abbreviations.
Comments