No matter how much
we update the data in our database there will come a time when some data just
becomes obsolete and we need to delete it from our tables. There could also
be an instance where data was entered into multiple columns in our table, or
the data could be totally inaccurate. Instead of trying to go through and update
all of the fields, it would be easier to delete the row of data and insert a
new one. This is where our DELETE
stored procedure comes into play.
We can delete rows of data based on the criteria passed to
the DELETE
statement.
To refresh our memory, take a look at the syntax of the DELETE
statement
that we covered in Chapter 5.
DELETE [From] TableName WHERE Expression
We need only specify the table name and the expression to delete data. If the expression evaluates to a Boolean value that matches multiple rows, then multiple rows of data will be deleted. If we use the primary key of the table, then only one row of data will be deleted.
DELETE
stored procedures, like INSERT and UPDATE
stored
procedures, usually need to accept parameters to be of any use. Normally you
will use just one parameter that specifies a primary key of the row to be deleted.
However, there will come a time when you need to pass multiple parameters and
use logic in your stored procedures to selectively delete rows of data. The
DELETE
stored procedure that we will be working with accepts only one parameter
and the value in this parameter represents a primary key value in the table:
CREATE PROCEDURE up_parmdel_state_name
(@state_id Int) AS
As you can see from the stored procedure code fragment above, the syntax is the same as the previous two stored procedures that we created. This time, however, we are only specifying one input parameter.
Try It Out – Delete Stored Procedure
1.
We start this example by building the DELETE
stored procedure using the code listed in the figure below:
|
2. Once you have executed the query to create the stored procedure, you need to grant execute authority to the Developers and Sales Staff roles.
3. We start this project as we did the last, by copying the form from
the last example and naming it frmTryItOut5.frm
.
4. Start a new Standard EXE VB project and set a reference to the Microsoft ActiveX Data Objects Library.
5. Then remove the current form and add the new 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.
6. Next we need to add the clsStates class from our last example to this project, and change the start up object for your project in the Project Properties dialog to start with the form frmTryItOut5.
7. We want to add a command button to execute our |
|
8. Add the following code to the cmdDelete_Click
procedure:
Private Sub cmdDelete_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 objDelete As New clsStates
'Call the method to delete
the state
lngRC = objDelete.DeleteState(m_objConn,
m_objRS!State_ID,
_
strError)
'Check for errors
If lngRC = 0 Then
'Display a message that
the record was deleted
Call SetMsg("Record
Deleted")
'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 objDelete = Nothing
End Sub
9. Add the following code to the clsStates class, again changing the owner prefix of the stored procedure:
Public Function DeleteState(ByRef objConn
As ADODB.Connection, _
ByVal
lngStateID As Long, _
ByVal
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 DeleteState_EH
'Build the SQL string
strSQL = "willist.up_parmdel_state_name
(" & lngStateID & ")"
'Delete the state data
objConn.Execute strSQL
'Return with a good return
code
DeleteState = 0
'Dereference the database
objects
Set objErr = Nothing
'All was successful, exit
function
On Error GoTo 0
Exit Function
DeleteState_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
DeleteState = 1
On Error GoTo 0
End Function
10. Modify the GetStates
procedure in the form as follows:
Private Sub GetStates()
'Setup error handling
On Error GoTo GetStates_EH
'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 occured getting
recordset, display message
Call SetMsg("Error
occured 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
'Exit the procedure
On Error GoTo 0
Exit Sub
GetStates_EH:
Select Case Err.Number
Case -2147217906
'Invalid bookmark
m_objRS.MoveFirst
m_strLastState
= "##"
Resume Next
End Select
End Sub
11. Don't forget to modify the description in the procedure attributes.
How It Works – The Stored Procedure
The stored procedure is straightforward as it only accepts one parameter, the ID of the state to delete:
'Build the SQL string
strSQL = "willist.up_parmdel_state_name
(" & lngStateID & ")"
The Class Module
The DeleteState
method in our class accepts three parameters,
the Connection
object, the state ID, and the error string:
Public Function DeleteState(ByRef objConn
As ADODB.Connection, _
ByVal
lngStateID As Long, _
ByVal
strError As String) As Long
There is no validation in this function as we are deleting
a row of data. The rest of this function is the same as the UpdateState
function and as such has not been covered again.
The cmdDelete
_Click
procedure
is the same as the cmdUpdate
_Click
procedure,
except for this line of code where we execute the method to delete a state.
We also need to change the message that is displayed, to state that a record
was deleted:
'Call the method to delete
the state
lngRC = objDelete.DeleteState(m_objConn,
m_objRS!State_ID,
strError)
Because we are deleting a record from the State
_T
table,
it is possible that the bookmark will no longer be valid. To handle this we
add error handling to the GetStates
procedure, as shown below. The first line of code sets up the error
handling for this procedure:
Private Sub GetStates()
'Setup error handling
On Error GoTo GetStates_EH
The error handler uses a Select
Case
statement to determine which error was received.
At this point we are only trapping the error for an invalid bookmark. We want
to move to the first record in the recordset, and set the m
_strLastState
variable to a low value. We do this so the comparison of the state
will fail and we will stay on the first record. We then resume at the next line
of code after the line that caused the error:
GetStates_EH:
Select Case Err.Number
Case –2147217906
'Invalid bookmark
m_objRS.MoveFirst
m_strLastState
= "##"
Resume Next
End Select
End Sub
At this point you are ready to run your program and test this new functionality. This wraps up this program and we now have a fully functional data entry program that uses stored procedures.
Comments