Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Code the Class Modules
From the Project menu, choose Add Class Module, select Class Module from the Add Class Module dialog box, change the Name property of this new class to clsPublishersList, and save it as PublishersList.cls. Repeat this procedure to add a class for clsPublishers. Make sure the Instancing property for both classes is set to the default value of 5-MultiUse. MultiUse allows multiple objects of this class to be instantiated outside of the component. You're now ready to start writing the code to implement the necessary features.
My design has clsPublisherList and clsPublisher working closely together to provide the functionality that allows the client code that uses this component to search the Publishers table; and fetch, insert, update, and delete individual Publishers. Exactly how this is done is not quite as important as providing a correct component interface because the encapsulation rules of object-oriented programming say it's all right to change the way something is implemented as long as you don't change the interface. I usually put the code that works with more than one entity (for example, the Search method) in the list class (for example, clsPublisherList). The methods that work with a single entity (for example, Fetch, Insert, Update, and Delete) are then normally delegated to the class that encapsulates the notion of one record in the table (for example, clsPublisher).
I also make the List class responsible for using ADO to open and close the database connection. clsPublishersList can do this automatically if you add code to the Class_Intialize and Class_Terminate events:
' Private PublisherList variables.
Private mDB As ADODB.Connection
' Automatically open a database connection when a PublisherList
' object is created.
Private Sub Class_Initialize()
Dim lConnectString As String
lConnectString = "Driver={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & App.Path & "\Biblio.mdb"
Set mDB = New ADODB.Connection
Call mDB.Open(lConnectString)
End Sub
' Automatically close the database connection when a PublisherList
' object is destroyed.
Private Sub Class_Terminate()
Call mDB.Close
Set mDB = Nothing
End Sub
Another method implemented by clsPublisherList is the Search method. Its job is to query the database for all Publishers whose name matches the search criteria passed as an argument. I code it like this:
' Returns a new collection containing a list of references to
' Publishers objects with a Name that matches the search criteria.
Public Function Search(ByVal lSearchString As String) As Collection
Dim lSQL As String
Dim lPublisher As clsPublisher
Dim lPublisherCol As Collection
Dim lRS As ADODB.Recordset
' Build the SQL statement.
lSQL = "SELECT * FROM Publishers " _
& " WHERE Name LIKE " & DBStr("%" & lSearchString & "%") _
& " ORDER BY Name"
' Query the database for the matching Publishers and create a
' new clsPublisher object for each record returned.
Set lPublisherCol = New Collection
Set lRS = mDB.Execute(lSQL)
Do Until lRS.EOF = True
Set lPublisher = New clsPublisher
Call lPublisher.RSGet(lRS)
Call lPublisherCol.Add(lPublisher, CStr(lPublisher.PubID))
Call lRS.MoveNext
Loop
Call lRS.Close
Set Search = lPublisherCol
End Function
Whenever you have SQL statements embedded in your source code that contains strings entered by the user, you always have the potential that the user will enter an apostrophe (', a.k.a. single-quote) somewhere in the string. Because this same apostrophe character is used as a delimiter within the SQL statement, it will generate SQL syntax errors if you don't write code to do something about it. I call this the "SQL embedded quote problem." The ugly solution to this problem is to modify the user interfaces so they do not allow apostrophes to be entered in string fields. However, in my opinion, a much better solution is to search through the strings when the SQL statements are built, and replace all apostrophes with two apostrophes. The DBMS then interprets the two apostrophes as just one. Because this must be done for every string passed to the database, I wrap this code in a function named DBStr and code it in a standard code module so it can be used from anywhere inside the component. For example:
' Replace each apostrophe with two apostrophes. Public Function DBStr(ByVal lStr As String) As String DBStr = "'" & Replace(lStr, "'", "''") & "'" End Function
As coded above, the Search method also relies on the clsPublisher.RSGet method to retrieve its property values from the ADO Recordset object. I put this chunk of code in the clsPublisher module because it is working with only one Publisher entity:
Friend Sub RSGet(ByVal lRS As ADODB.Recordset) ' Note that concatenating an empty string to the ' recordset fields prevents any errors caused by ' null fields. PubID = lRS!PubID Name = "" & lRS!Name CompanyName = "" & lRS![Company Name] Address = "" & lRS!Address City = "" & lRS!City State = "" & lRS!State Zip = "" & lRS!Zip Telephone = "" & lRS!Telephone Fax = "" & lRS!Fax End Sub
Note that RSGet is declared using the Friend keyword. A procedure declared Friend can be called from anywhere within the component, but is not visible outside of the component. In this sample code, it means that RSGet can be called from clsPublisher and clsPublisherList, but it can't be called from anywhere in the PublishersWebUI project. This is good because we don't want any code outside of the DHO to query the Publishers table. Instead, we want it to use the DHO's methods.
The methods in clsPublishersList that allow individual Publishers to be fetched, inserted, updated, and deleted are pretty simple to code because they just pass control to "friend" methods in clsPublisher:
' Removes a Publisher from the Publishers table. Public Sub Delete(ByVal lPublisher As clsPublisher) Call lPublisher.DBDelete(mDB) End Sub ' Inserts a Publisher into the Publishers table. Public Sub Insert(ByVal lPublisher As clsPublisher) Call lPublisher.DBInsert(mDB) ' If we were worried about duplicate primary keys, ' we would attempt to catch the error raised by ' the DBMS here, and re-raise a nicer message. End Sub ' Retrieves a Publisher from the Publishers table. Public Function Fetch(ByVal lPubID As Long) As clsPublisher Dim lPub As clsPublisher Set lPub = New clsPublisher lPub.PubID = lPubID Call lPub.DBGet(mDB) Set Fetch = lPub End Function ' Updates a Publisher in the Publishers table. Public Sub Update(ByVal lPublisher As clsPublisher) Call lPublisher.DBUpdate(mDB) End Sub
Because we'll add Publisher update functionality in the sample application, here's the code for the clsPublisher.DBUpdate method:
Friend Sub DBUpdate(ByVal lDB As ADODB.Connection) Dim lSQL As String lSQL = "UPDATE Publishers SET " lSQL = lSQL & " Name = " & DBStr(Name) & "," lSQL = lSQL & " [Company Name] = " & DBStr(CompanyName) & "," lSQL = lSQL & " Address = " & DBStr(Address) & "," lSQL = lSQL & " City = " & DBStr(City) & "," lSQL = lSQL & " State = " & DBStr(State) & "," lSQL = lSQL & " Zip = " & DBStr(Zip) & "," lSQL = lSQL & " Telephone = " & DBStr(Telephone) & "," lSQL = lSQL & " Fax = " & DBStr(Fax) lSQL = lSQL & " WHERE PubID = " & CStr(PubID) Call lDB.Execute(lSQL) End Sub
Note that this procedure also is coded using the Friend keyword so that all requests to update a publisher must go through the Public clsPublisherList.Update method first. clsPublisher.DBUpdate also uses the DBStr function mentioned earlier to avoid the SQL embedded quotes problem.
The DBFetch, DBDelete, and DBInsert methods of clsPublisher are coded very much the same as DBUpdate, and so aren't listed here for brevity sake. Download the sample project source code if you want to see how they are implemented.