Stored Procedures

Delete Stored Procedure

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 DELETE stored procedure, as shown below, giving it a Name of cmdDelete and a Caption of Delete:

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 SelectCase 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.

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are only 3 numbers of interest to a computer scientist: 1, 0 and infinity”