Now that we have covered stored
procedure parameters, it's time to see how we can apply them to an INSERT stored procedure. If we couldn't provide parameters to an INSERT stored
procedure, the stored procedure really wouldn't do us any good and we would
have to use in-line SQL statements instead. This section covers simple INSERT stored
procedures and how we use parameters to maximize their usefulness.
Let's start by looking at what is required to build an INSERT stored
procedure to insert the state names and state abbreviations. Our table design
has four columns in the State_T table:
State_ID, State_Name_VC, State_Abbreviation_CH, and
Last_Update_DT. The
first column, State_ID, is an
Identity column. We do not have to specify any values for that column, as SQL
Server will automatically populate that column for us with the next available
number. The last column, Last_Update_DT, contains
the last date and time the row of data was inserted or updated. SQL Server provides
a built-in function called GetDate() that we can call to populate
that column, so we do not need to pass the stored procedure data for that column.
That leaves us with two columns for which we need to pass data to the stored
procedure.
If you recall our discussion on parameters at the beginning of this chapter, we need to prefix our parameter names with the @ sign, and specify the data types.
Looking at the following stored procedure code fragment, we
see that we have specified two input parameters that the stored procedure will
require. The first parameter is a VarChar data type and can contain up to 15 characters. The second parameter
is a Char data type and requires two characters. If you only pass one character,
SQL Server will pad that parameter with an extra character,
because a Char data
type requires the exact number of characters
that are specified.
CREATE PROCEDURE up_parmins_state_name
(@state_name VarChar(15),
@state_abbreviation
Char(2)) AS
The parameters that are specified are considered local variables
because they are required to be prefixed with the @ sign. We can use these variables in our stored procedure just like
we could in VB. We can change the values of these variables, and we could use
them in an expression that tests their values to ensure that they are equal
to something.
The actual heart of the INSERT stored procedure is to insert values into a table. This can be coded
in one of two ways, as we discussed in Chapter 5. We could use the following
short method:
INSERT [Into] TableName VALUES(ValueList)
Or we could use the proper method, shown below, which will cause us less headaches in the long run. This also makes the stored procedure easier to build and debug, as well as maintain.
INSERT [Into] TableName [(ColumnList)] VALUES(ValueList)
We will be using the last method in our stored procedures,
and our parameters will get populated in the ValueList. So let's move on and build our INSERT stored
procedure and VB program to execute the stored procedure.
Try It Out – Insert Stored Procedure
1.
The first thing we need to do is to build
the INSERT stored procedure to insert state names and state abbreviations, as
shown in the figure below.
2. Execute the SQL statements to create the stored procedure.
|
` |
3. Next you need to use Enterprise Manager to set the permissions for this stored procedure, to allow the Developers and Sales Staff roles to execute this stored procedure.
4. To build the VB project, start by copying the frmTryItOut2.frm form from the last example (for example by using Windows Explorer)
and name it frmTryItOut3.frm.
5. Start a new Standard EXE VB project and set a reference to the Microsoft ActiveX Data Objects Library.
6. Remove the current form and add the existing form that you just copied. You will need to change the form name once it is loaded in your project, and change the form caption.
7. Now add the clsStates class from your last project to this project – we do not want to change
the name of the class or copy it to another file.
8. Last, change the start up object for your project in the Project Properties dialog to start with the form frmTryItOut3. At this point the project should run and behave as in the last example.
|
9. We want to add a command button to execute our |
|
10. To provide the functionality to execute the INSERT stored
procedure we have just created, add the following code to your project. In the
general declarations of the form add:
'Declare module level variables
Private m_varBookMark
As Variant
Private m_strLastState
As String
11. Add the following code to the cmdAdd_Click procedure:
Private Sub cmdAdd_Click()
'Declare local variables
Dim lngRC As Long
Dim strError As String
'Save the current record
m_varBookMark = m_objRS.Bookmark
m_strLastState = m_objRS!State_Name_VC
'Instantiate an instance
of the clsStates class
Dim objAdd As New clsStates
'Call the method to add the
state
lngRC = objAdd.AddState(m_objConn,
_
txtStateName.Text, txtStateAbbreviation.Text,
strError)
'Check for errors
If lngRC = 0 Then
'No error, get a new listing
of state names
Call GetStates
Else
'An error occurred, display
the error
Call SetMsg(strError,
vbRed)
End If
'Dereference the class
Set objAdd = Nothing
End Sub
12. Modify the GetStates procedure by adding the following code:
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
'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)
If m_objRS Is Nothing Then
'Error occurred getting
recordset, display message
Call SetMsg("Error
occurred getting recordset", vbRed)
Call DisableButtons
Else
If Not IsEmpty(m_varBookMark)
Then
'Move to the appropriate
record and load the fields
m_objRS.Bookmark
= m_varBookMark
If m_objRS!State_Name_VC
< m_strLastState
Then
Call cmdMoveNext_Click
Else
Call LoadFields(m_objRS!State_Name_VC,
_
m_objRS!State_Abbreviation_CH)
End If
Else
'Move to the first
record
Call cmdMoveFirst_Click
End If
End If
'Dereference the class
Set objSelect = Nothing
End Sub
13. The code which needs to be added to the end of the clsStates
class is listed in the How It Works – The States Class Module section.
Don't forget to add a description for this method in the Procedure Attributes, as
described in the last example.
How It Works – The Stored Procedure
Let's take a look at the INSERT stored procedure. As we mentioned in Chapter 5, we specify each column
name that we are inserting data into to avoid confusion and problems later.
Next we insert the values using the parameters that were specified in the first
line of our stored procedure. SQL Server provides a built-in function called
GetDate() to get the current date and time in the required format for
a DateTime
data type. We use this function to insert the current date and time into the
Last_Update_DT column:
CREATE PROCEDURE up_parmins_state_name
(@state_name VarChar(15),
@state_abbreviation
Char(2)) AS
INSERT INTO State_T
(State_Name_VC,
State_Abbreviation_CH,
Last_Update_DT)
VALUES(@state_name,
@state_abbreviation,
GetDate())
The States Class Module
We want to take a look at the code in our class module next. All of the code presented step by step in this section needs to be entered into the class module. Before we add a new state to the database we need to validate that some data exists and that the state name passed is not greater than 15 characters. We do this in a validation routine that accepts the state name as a parameter and returns a Boolean value indicating success or failure of the validation:
Private Function ValidateStateAbbreviation(
_
ByVal strStateAbbreviation
As String) As Boolean
The first thing we do in this function is to set up
the return value, which by default is set to True:
'Set default return value
ValidateStateAbbreviation
= True
Next we validate that the parameter passed in, state abbreviation,
contains some data, by checking to ensure that the length of the parameter is
not equal to zero. If it is then we set the function return value to False:
'Validate data exists
If Len(Trim(strStateAbbreviation))
= 0 Then
ValidateStateAbbreviation
= False
End If
Next we ensure that the state abbreviation is exactly two
characters. If it is any more or any less than two characters we set the function
to return a value of False:
'Validate state name is equal
to 2 characters
If Len(Trim(strStateAbbreviation))
<> 2 Then
ValidateStateAbbreviation
= False
End If
End Function
We provide similar functionality for the state name:
Private Function ValidateStateName(ByVal
strStateName As String) As Boolean
'Set default return value
ValidateStateName = True
'Validate data exists
If Len(Trim(strStateName))
= 0 Then
ValidateStateName = False
End If
'Validate state name is not
greater than 15 characters
If Len(Trim(strStateName))
> 15 Then
ValidateStateName = False
End If
End Function
The method in our class to add a new state accepts the Connection object by reference, and the state name and state abbreviation by
value. We need a means to know what error message was encountered, if any, so
we pass a string variable by reference. This method will set the strError
variable to the error description it encountered. We will return a long return
code of zero if everything was successful:
Public Function AddState(ByRef objConn As
ADODB.Connection, _
ByVal
strStateName As String, _
ByVal
strStateAbbreviation As String, _
ByRef
strError As String) As Long
The first thing we do in our function is to declare the database objects and local variables that we need. Then we set up our error handling to force us to our error handler if we encounter any errors:
'Declare database objects
and local variables
Dim objErr As ADODB.Error
Dim strSQL As String
'Setup error handling
On Error GoTo AddState_EH
We call our validation routines to validate the data passed.
If validation failed, we raise an error using the VB Error object's Raise method. We really aren't concerned with the error number so we use
the same error number in both routines. What we are
concerned with is the error message, which is unique for each error.
After an error is raised, we are forced to the error handler at the bottom of
our procedure:
'Validate data
If Not ValidateStateName(strStateName)
Then
'Raise an error
Err.Raise vbObjectError
+ 513, "clsStates.AddState", _
"State Name is
either empty or exceeds 15 characters"
End If
If Not ValidateStateAbbreviation(strStateAbbreviation)
Then
'Raise an error
Err.Raise vbObjectError
+ 513, "clsStates.AddState", _
"State Abbreviation
is either empty or not equal to 2 characters"
End If
If the data has passed validation then we build our SQL string
next. Since we are not expecting any return parameters or a return value from
the stored procedure, we will not use the Command object to execute our stored procedure. Instead we are going to use
the Connection object and we need only pass it a SQL string to execute.
Here we specify the stored procedure to execute, prefixed
with the owner ID, which you must remember to change. The parameters that are
passed to the stored procedure need to be enclosed in parentheses and all string
parameters need to be enclosed in single quotes. A comma separates each parameter
passed to the stored procedure. Notice that we are forcing the state abbreviation
to uppercase using the built-in VB UCase function:
'Build the SQL string
strSQL = "willist.up_parmins_state_name
('" & _
strStateName & "','"
& UCase(strStateAbbreviation) & "')"
We execute our SQL string using the Connection object. If an error occurs we are forced to the error handler:
'Insert the state data
objConn.Execute strSQL
If all goes well, we find ourselves here and set a good return code, de-reference our database objects and exit the function:
'Return with a good return
code
AddState = 0
'Dereference the database
objects
Set objErr = Nothing
'All was successful, exit
function
On Error GoTo 0
Exit Function
Our error handler code is a bit more complex than the last
method that we coded in this class. Here we could receive either a database
error or an error that we raised from the validation of data. First we check
to see if the Errors collection contains any errors, by querying the Count property.
If it is greater than zero we know that the error that forced us here came from
ADO or SQL Server. Since we are only interested in the first error message,
we set the objErr object to the first error message in the Errors collection,
and then set the strError variable to the error description:
AddState_EH:
'Ensure the error came from
ADO
If objConn.Errors.Count >
0 Then
'Get the first error in
the errors collection
Set objErr = objConn.Errors(0)
'Set the error message
strError = objErr.Description
If the error came from the VB Error object we set the strError variable to the description of the VB Error object:
Else
'Set the error message
strError = Err.Description
End If
Last we de-reference our database objects and set the return code for this function:
'Dereference the database
objects
Set objErr = Nothing
'Return with a bad return
code
AddState = 1
On Error GoTo 0
End Function
The Form
Switching to our VB code in the form, we added a variant variable
to the general declarations section of our form to hold
a bookmark. When we add a new state we need to have that state added to our
recordset. Since we are using a disconnected recordset, we must call the GetStates
procedure to get a new copy of the recordset. Providing a good UI, we reposition
the user to the record they were on when they added the new state. We also add
a variable to hold the last state name, which we will explain shortly:
'Declare module level variables
Private m_varBookMark
As Variant
Private m_strLastState
As String
When a user clicks on the Add button on the form we want to execute the code below. This will call the method above to add the state name and abbreviation. First we declare the local variables that we need in this procedure:
Private Sub cmdAdd_Click()
'Declare local variables
Dim lngRC As Long
Dim strError As String
Just in case the user has navigated through the recordset, we save the current position. We do this by setting the bookmark variable, so we can return them to this point in the recordset once we have added the new record. We also save the current state name that will be used shortly:
'Save the current record
m_varBookMark = m_objRS.Bookmark
m_strLastState = m_objRS!State_Name_VC
We instantiate an instance of the clsStates class and execute the method to add the new state, passing it the
required parameters. The strError variable that we have declared locally is being passed by reference.
If an error occurs, this variable will contain the error description set by
the AddState method:
'Instantiate an instance
of the clsStates class
Dim objAdd As New clsStates
'Call the method to add the
state
lngRC = objAdd.AddState(m_objConn,
_
txtStateName.Text, txtStateAbbreviation.Text,
strError)
After executing the AddState method we need to check the return code. If it equals zero then everything
was successful. We then need to get a new copy of the recordset that contains
the state just added, by calling the GetStates
procedure.
If an error occurred then we call the SetMsg procedure, passing it the strError variable that contains the error
that was set in the AddState method:
'Check for errors
If lngRC = 0 Then
'No error, get a new listing
of state names
Call GetStates
Else
'An error occurred, display
the error
Call SetMsg(strError,
vbRed)
End If
Last, we de-reference the instance of the clsStates class by setting it to Nothing:
'Dereference the class
Set objAdd = Nothing
End Sub
In the GetStates
procedure we need to modify the code to reposition the recordset to the current
record that was displayed when the user added a new state. We do this by checking
to see if the m_varBookMark variable is empty. If it is not then we want to reposition the user
to the record they were on before the add was carried out. We do this by setting
the Bookmark property of the Recordset object to the bookmark that we saved.
While the bookmark method is efficient at repositioning us
to the current record we were on, there is one little drawback. First, we have
got a new recordset that contains one more row than before. Second, the state
names are returned in our recordset alphabetically. If you add a state that
comes before the state you were on, you are positioned to the state you were
on minus one. In other words, if the state displayed is North Carolina and you
add Arizona, then Arizona becomes the current record. This is assuming of course
that there is only one state listed in the recordset. Using the m_strLastState variable we can compare the current state name in the recordset with
the saved state name. If the recordset contains a state name less than what
was saved, then we know that we need to advance one record position forward
to return the user to the exact record they were on.
If we move forward one record, the cmdMoveNext_Click procedure
will take care of loading the fields with the correct data. If the bookmark
was accurate then we need to call the procedure LoadFields to load the current data:
If Not IsEmpty(m_varBookMark) Then
'Move to the appropriate
record and load the fields
m_objRS.Bookmark
= m_varBookMark
If m_objRS!state_name_vc
< m_strLastState
Then
Call cmdMoveNext_Click
Else
Call LoadFields(m_objRS!State_Name_VC,
_
m_objRS!State_Abbreviation_CH)
End If
Else
'Move to the first
record
Call cmdMoveFirst_Click
End If
End If
At this point you should be able to run your program and add a new state. After entering a new state name, tab to the state abbreviation field. What you will notice is that you have to highlight the text before entering a new abbreviation. After you enter a couple of states and abbreviations you can see what a nuisance this is.
Try It Out – Inserting State Data
1. To help make our UI more user-friendly we need to automatically highlight the text when the user tabs to a textbox, and we achieve this using the following code, which you should add to the form:
Private Sub txtStateAbbreviation_GotFocus()
txtStateAbbreviation.SelLength
= Len(txtStateAbbreviation.Text)
End Sub
Private Sub txtStateName_GotFocus()
txtStateName.SelLength =
Len(txtStateName.Text)
End Sub
2. Enter the state names and abbreviations listed in the table below:
State Name
Abbreviation
State Name
Abbreviation
Alabama
AL
Montana
MT
Alaska
AK
Nebraska
NE
Arizona
AZ
Nevada
NV
Arkansas
AR
New Hampshire
NH
California
CA
New Jersey
NJ
Colorado
CO
New Mexico
NM
Connecticut
CT
New York
NY
Delaware
DE
North Carolina
NC
Florida
FL
North Dakota
ND
Georgia
GA
Ohio
OH
Hawaii
HI
Oklahoma
OK
Idaho
ID
Oregon
OR
Illinois
IL
Pennsylvania
PA
Indiana
IN
Rhode Island
RI
Iowa
IA
South Carolina
SC
Kansas
KS
South Dakota
SD
Kentucky
KY
Tennessee
TN
Louisiana
LA
Texas
TX
Maine
ME
Utah
UT
Maryland
MD
Vermont
VT
Massachusetts
MA
Virginia
VA
Michigan
MI
Washington
WA
Minnesota
MN
West Virginia
WV
Mississippi
MS
Wisconsin
WI
Missouri
MO
Wyoming
WY
Comments