Again, this method is called from the visitor.asp form when the user already exists in our database. Here we will return a variant array with the fields of data that we stored. This will allow us to present the user with pertinent information such as their name, the last time they visited, and how many times they have surfed into our site in the past.
Add another method, getVisitor, to the DLL file. Again, note the both the input and the output of the function – as shown in the signature – a variant. This is important!
Public Function getVisitor(ByVal visitorNumber As Variant) As Variant
Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim connectString As String
Dim sqlString As String
Dim visitorArray() As Variant 'must be a variant
connectString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data Source=C:\begdb\visitors.mdb"
Set adoConnection = CreateObject("ADODB.Connection")
Set adoRecordset = CreateObject("ADODB.Recordset")
adoConnection.Open connectString
sqlString = "Select * From SiteVisitors WHERE CookieID = " & visitorNumber
adoRecordset.Open sqlString, adoConnection, adOpenDynamic, adLockOptimistic
If (adoRecordset.BOF) And (adoRecordset.EOF) Then
ReDim visitorArray(1)
visitorArray(0) = "Not Found"
Else
ReDim visitorArray(4)
visitorArray(0) = adoRecordset!firstName
visitorArray(1) = adoRecordset!lastName
visitorArray(2) = adoRecordset!previousVisit
adoRecordset!previousVisit = Now()
adoRecordset!totalVisits = adoRecordset!totalVisits + 1
adoRecordset.Update
visitorArray(3) = adoRecordset!totalVisits
End If
adoRecordset.Close
adoConnection.Close
getVisitor = visitorArray
End Function
When this method is called, the visitorNumber is passed in as a parameter of type variant. We retrieve the record from our database where the CookieID field equals the visitorNumber passed in. Of course, the visitorNumber is the cookie value of the current user:
sqlString = "Select * From SiteVisitors WHERE CookieID = " & visitorNumber
We know that the record will be there, but for safety we check the .BOF and .EOF properties to ensure the recordset is not empty. If there was some error and the user was not found, we redim our array to 1 element and add the text "Not Found".
Assuming the record is there - and we know it will be - we just redim our visitorArray to have four elements. We then add the fields that we want our DLL to return to the calling ASP form. These are taken from the record that was retrieved from our recordset for the current user.
ReDim visitorArray(4)
visitorArray(0) = adoRecordset!firstName
visitorArray(1) = adoRecordset!lastName
visitorArray(2) = adoRecordset!previousVisit
adoRecordset!previousVisit = Now()
adoRecordset!totalVisits = adoRecordset!totalVisits + 1
adoRecordset.Update
visitorArray(3) = adoRecordset!totalVisits
Remember when we earlier discussed the immediate update method of ADO? We don't need to invoke a .Edit method - in fact one does not exist. We just update the fields we want and call the .Update method to save the changes. Once we extract the previousVisit time and place it in the third element (2) of our visitorArray, we then update the field to Now() which reflects the last time this surfer visited our web site. Remember that our array is starting at base 0, not 1.
We then add 1 to the number of visits and update the recordset. Next, we assign the new totalVisits to the fourth element (3) of our array. If we had an on-line store, we could have stored the last purchase made. Or if our newUser.asp was more involved, we could have added all kinds of survey information and stored it as well. You get the idea.
We then close the recordset and connection normally. Finally, we assign the visitorArray with our information to the return value of our function, getVisitor. Again, this must be a variant:
getVisitor = visitorArray