Connection
sConnString = "Provider=SQLNCLI;Server=MYSERVERNAME\SQLEXPRESS;" ??
& "Database=AdventureWorks;Trusted_Connection=yes;"
CHAPTER 2 n DATA IN, DATA OUT 83
cnn.Open sConnString
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
4. Next, fill the colParams collection with ADODB.Parameter objects:
Set colParams = SetParams(ActiveCell.Row)
The SetParams function returns a filled collection and looks like this:
Function SetParams(RowNum As Integer) As Collection
'returns a collection of filled ADO Parameter objects
Dim colReturn As Collection
Dim prm As ADODB.Parameter
Set colReturn = New Collection
Set prm = New ADODB.Parameter
With prm
.Name = "EmployeeID"
.Type = adInteger
.Value = Cells(RowNum, 1).Value
End With
colReturn.Add prm
Set prm = New ADODB.Parameter 'wipe prm and start over; best way to ??
prevent leftover data
With prm
.Name = "NationalIDNumber"
.Type = adLongVarWChar
.Size = 15
.Value = Cells(RowNum, 4).Value
End With
colReturn.Add prm
Set prm = New ADODB.Parameter
With prm
.Name = "BirthDate"
.Type = adDBTimeStamp
.Value = Cells(RowNum, 5).Value
End With
colReturn.Add prm
CHAPTER 2 n DATA IN, DATA OUT 84
Set prm = New ADODB.Parameter
With prm
.Name = "MaritalStatus"
.Type = adWChar
.Size = 1
.Value = Cells(RowNum, 6).Value
End With
colReturn.Add prm
Set prm = New ADODB.Parameter
With prm
.Name = "Gender"
.Type = adWChar
.Size = 1
.Value = Cells(RowNum, 7).Value
End With
colReturn.Add prm
Set prm = Nothing
Set SetParams = colReturn
End Function
There is nothing really fancy going here, although we have called upon a new property
of the Parameter object.
Pages:
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108