Stored Procedures

Update Stored Procedure

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 UPDATE stored procedure as shown below, giving it a Name of cmdUpdate and a Caption of Update:

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.

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 10 types of people in the world, those who can read binary, and those who can't.”