People make mistakes, that's just human nature.
While entering the state names and abbreviations,
you might have made one or two spelling mistakes yourself. UPDATE stored
procedures come to our rescue by providing a means to correct our mistakes.
They also help us to keep our data from becoming obsolete, such as data in a
rate table. This section focuses on UPDATE stored
procedures.
In Chapter 5 we discussed the syntax of the UPDATE statement but, to refresh our memory, let's just take a quick look at it again:
UPDATE TableName SET ColumnName = Expression
WHERE ColumnName = Expression
It should all be coming back to you now, and all we need to do is to create a stored procedure that accepts parameters and updates our columns using the parameters passed. Let's take a look at the following stored procedure code fragment. This stored procedure will be used to update the state names, as indicated by the stored procedure name. Because we are using the naming standard discussed in Chapter 3 we can quickly identify what function this stored procedure is performing.
CREATE PROCEDURE up_parmupd_state_names
(@state_id Int,
@state_name
VarChar(15), @state_abbreviation Char(2)) AS
If you compare this code fragment to the INSERT stored procedure you created earlier, you will notice the addition
of the @state_id input
parameter. This is because the WHERE clause
in the UPDATE SQL statement needs to know what row of data it should update. This
parameter will be used to limit the update to one row – the row whose State_ID column
matches the @state_id parameter
passed.
The WHERE clause
does not have to match just one row. It can be used to make a generic
update against many rows of data. This all depends on the data in the
table you are updating and the expression in the WHERE clause.
Try It Out – Update Stored Procedure
1.
We want to build our UPDATE stored
procedure using the code shown in the figure below. After executing the query
to create your stored procedure, you need to grant execute authority to the
Developers and Sales
Staff roles.
|
|
2. To build this example we want to follow the same steps as we did in
the last example. We start by copying the form from the last example and naming
it frmTryItOut4.frm.
3. Then start a new Standard EXE VB project and set a reference to the Microsoft ActiveX Data Objects Library.
4. Remove the current form and add the existing form that you just copied. Change the form name once it is loaded in your project, and change the form caption.
5. Add the clsStates class from the last example to your project, and change the start
up object for your project in the Project
Properties dialog to start with the form frmTryItOut4. At this point the project should run and behave as in the last example,
with select and insert functionality.
|
6.
This time we want to add a command
button to execute our |
|
7. Add the following code to the cmdUpdate_Click procedure:
Private Sub cmdUpdate_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 objUpdate As New clsStates
'Call the method to update
the state
lngRC = objUpdate.UpdateState(m_objConn,
m_objRS!State_ID,
_
txtStateName.Text, txtStateAbbreviation.Text,
strError)
'Check for errors
If lngRC = 0 Then
'Display a message that
the update was applied
Call SetMsg("Record
Updated")
'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 objUpdate = Nothing
End Sub
8. Add the following code to the clsStates class, changing the owner prefix of the stored procedure:
Public Function UpdateState(ByRef objConn
As ADODB.Connection, _
ByVal
lngStateID As Long, _
ByVal
strStateName As String, _
ByVal
strStateAbbreviation As String, _
ByRef
strError As String) As Long
'Declare database objects
and local variables
Dim objErr As ADODB.Error
Dim strSQL As String
'Setup error handling
On Error GoTo UpdateState_EH
'Validate data
If Not ValidateStateName(strStateName)
Then
'Raise an error
Err.Raise vbObjectError
+ 513, "clsStates.UpdateState", _
"State Name is
either empty or exceeds 15 characters"
End If
If Not ValidateStateAbbreviation(strStateAbbreviation)
Then
'Raise an error
Err.Raise vbObjectError
+ 513, "clsStates.UpdateState", _
"State Abbreviation
is either empty or not equal to 2 characters"
End If
'Build the SQL string
strSQL = "willist.up_parmupd_state_name
(" & _
lngStateID & ",'"
& strStateName & "','" & _
UCase(strStateAbbreviation)
& "')"
'Update the state data
objConn.Execute strSQL
'Return with a good return
code
UpdateState = 0
'Dereference the database
objects
Set objErr = Nothing
'All was successful, exit
function
On Error GoTo 0
Exit Function
UpdateState_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
Else
'Set the error message
strError = Err.Description
End If
'Dereference the database
objects
Set objErr = Nothing
'Return with a bad return
code
UpdateState = 1
On Error GoTo 0
End Function
How It Works – The Stored Procedure
The first line of this stored procedure contains the input
parameters that are required to be passed by the calling program. Since this
stored procedure is using the UPDATE SQL statement, we use the SET keyword to
set the column values equal to the local variables (input parameters). The WHERE clause
specifies the condition of the update, and only updates rows of data that satisfy
the WHERE condition:
CREATE PROCEDURE up_parmupd_state_name
(@state_id Int,
@state_name
VarChar(15), @state_abbreviation Char(2)) AS
UPDATE State_T
SET State_Name_VC
= @state_name,
State_Abbreviation_CH
= @state_abbreviation
WHERE State_ID
= @state_id
The VB Program
The code that we need in order to incorporate update functionality
in our VB program is minimal, and is contained in two procedures. Let's start
with the UpdateState method in our class. This method is essentially the same as the AddState
method, except that it contains one extra parameter – the ID of the state that
should be updated:
Public Function UpdateState(ByRef objConn
As ADODB.Connection, _
ByVal
lngStateID As Long, _
ByVal
strStateName As String, _
ByVal
strStateAbbreviation As String, _
ByRef
strError As String) As Long
The next difference comes when we build the SQL string. The first parameter that our stored procedure expects is the state ID and we have specified it first in this string, followed by the state name and abbreviation:
'Build the SQL string
strSQL = "willist.up_parmupd_state_name
(" & _
lngStateID & ",'"
& strStateName & "','" & _
UCase(strStateAbbreviation)
& "')"
The rest of the code is the same as the AddState method. We still validate the
state name and abbreviation to ensure that we do not allow empty fields to be
inserted, or that the data is not longer than what the database can handle.
Some may wonder why we do not validate the state ID. There are a couple of reasons,
the first of which is that a valid Long data
type must be passed in order for this method to be executed. Secondly, if the
value passed does not exist on the database, no updates will be performed.
The cmdUpdate_Click procedure
is basically the same as the cmdAdd_Click procedure. When we execute the UpdateState method we pass the state ID contained in the current record:
'Call the method to update
the state
lngRC = objUpdate.UpdateState(m_objConn,
m_objRS!State_ID,
_
txtStateName.Text, txtStateAbbreviation.Text,
strError)
If the call to the UpdateState method was successful, we display a message indicating that the record
was updated, and get a new copy of the recordset to reflect the changes made:
'Check for errors
If lngRC = 0 Then
'Display a message that
the update was applied
Call SetMsg("Record
Updated")
'No error, get a new listing
of state names
Call GetStates
At this point our little data entry program can execute a stored procedure to select records, execute a stored procedure to add new data to our table, and execute a stored procedure to update records. Let's move on and complete our program.
Comments