uspUpdateEmployeePersonalInfo"
For i = 1 To colParams.Count
.Parameters.Append colParams(i)
Next i
End With
cmd.Execute
We end by calling the Command.Execute method to send the updated data to the database.
Before we run this command, let??™s take a look at the entire procedure. It should look like this:
Sub UpdateEmpPersonalInfo()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim colParams As Collection
Dim sConnString As String
Dim i As Integer
Sheets("Sheet1").Activate 'make sure we're on the data sheet
Set cnn = New ADODB.Connection
sConnString = "Provider=SQLNCLI;Server=MYSERVERNAME\SQLEXPRESS;" ??
& "Database=AdventureWorks;Trusted_Connection=yes;"
cnn.Open sConnString
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
Set colParams = SetParams(ActiveCell.Row)
With cmd
.CommandType = adCmdStoredProc
.CommandText = "HumanResources.uspUpdateEmployeePersonalInfo"
For i = 1 To colParams.Count
.Parameters.Append colParams(i)
Next i
End With
cmd.Execute
cnn.Close
CHAPTER 2 n DATA IN, DATA OUT 86
Set colParams = Nothing
Set cmd = Nothing
Set cnn = Nothing
MsgBox "Record has been updated", vbOKOnly, "Record Processed"
End Sub
Now we??™ll modify some data and run the procedure. Figure 2-29 shows the data before we
make any changes.
Figure 2-29. Employee data before update
Kevin Brown, EmployeeID 2, has been recently married. Change his marital status to M,
and then move the cursor to save the change. Run the UpdateEmpPersonalInfo routine, making
sure the cursor is in the row containing Kevin??™s record.
Pages:
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110