If you want, you can further extract the data access code from your presentation layer / ASP pages. Where you move it to depends a bit on your setup, the time you have available, and maybe even the scale of the project.
One of the easiest ways to accomplish this is to create generic functions in an ASP include file. These helper functions can perform the data access methods for you. Let's take your previous example and see how that ends up. I take a Categories list as an example. The number of items in this list depends on the access rights a user has (just making something up here)
Public Function GetDropdownCode(ByVal Table, ByVal IDCol, ByVal DescriptionCol)
SQL = "SELECT " & IDCol & ", " & DescriptionCol & " FROM " & _ &
Table & " ORDER BY " & DescriptionCol
' Code for data access here.
' Use common connectionstring to create a connection and
' get a recordset
Set GetDropdownCode = MyRecordset
This allows you to retrieve the drop down like this:
Set lstBookTitle = New cLST
lstBookTitle.DataSource = GetDropdownCode "Category", "ID", "Description"
This makes it much easier to create multiple drop downs, without repeating the code.
You could create "overloaded" versions of these methods, that support direct SQL statements, for example.
Another alternative is to create COM+ objects that do this for you. In a more complicated scenario, with more variables like user rights, permissions etc, you could create a business object that performs a number of validations, data changing, whatever, and then calls a separate data access layer. This layer, another COM+ object, creates a bunch of ADO objects, gets the data and returns a recordset to the business layer which in turn returns it to the presentation layer. You would end up with something like this:
Set myBusinessObj = Server.CreateObject("MyProject.MyBusiness")
myBusinessObj.User = Session("UserName")
myBusinessObj.Permissions = Session("Permissions")
Set lstBookTitle = New cLST
lstBookTitle.DataSource = GetCategories()
Inside the GetCategories method of the MyBusiness class, some logic takes place that takes the user name / id and permissions into account. Then it constructs a SQL query and sends it off to the database layer, with a method like GetFirehoseRecordset(), for example.
Using COM+ object construction and configuration you can configure your data access layer to run under a specific account which solves a part of the security puzzle in your project. You can also configure the component to accept a connection string. This allows you to configure the connection string in one central location (the COM+ management console) so your data access object can use it. This way, you don't have to store the connection string in an include file and pass it along with every method call.
The good thing about the data access layer is that you can easily reuse it in different projects. You start off with a few methods and expand as your project grows. Older projects will continue to work, while newer project can benefit from the new functionality.
These are just some ideas. I could ramble on for hours about this topic, but unfortunately, I do not have that much time available right now.
If you have specific questions about any of the topics I mentioned, or want to discuss these ideas further, feel free to post here.
Everyone is unique, except for me.
While typing this post, I was listening to: Body Movin'
by Beastie Boys
(Track 6 from the album: Hello Nasty
) What's This?