Download source files - 29 KbIntroduction
Windows DNA provides a means to provide your user interface, business logic and data
sources as separate services working together in harmony over a distributed environment.
The browser has become an extremely powerful, yet simple method of providing the user
interface, since it handles the network considerations and allows you to create rich
user interfaces through simple scripting, HTML and style sheets.
Your database considerations can be taken care of simply through the use of SQLServer
or the Microsoft Jet Engine, and your business logic - the guts of your application that
processes the data from the database and sends it to the browser - can be simple ASP
pages (enhanced with ActiveX controls if the fancy takes you).
Once you have the basics of ASP, HTML and VBScript the business logic and user
interface are taken care of quickly and simply - but how do you use ASP to access your
database and hence complete your 3-tier application? Read on...
Simple database Access using ADO and ASP
For this example we'll use Access .mdb databases - but we could just as easily
use SQLServer by changing a single line (and of course, configuring the databases
correctly). We'll be assuming your application is ASP based running on Microsoft's
IIS Webserver.
We use ADO since it is portable, widespread, and very, very simple.
The Connection
To access a database we first need to open a connection to it, which involves
creating an ADO Connection object. We then specify the connection string and call
the Connection object's Open
method.
To open an Access database our string would look like the following:
Dim ConnectionString
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=C:\MyDatabases\database.mdb;DefaultDir=;UID=;PWD=;"
where the database we are concerned with is located at C:\MyDatabases\database.mdb, and has
no username or password requirements. If we wanted to use a different database driver (such as SQLServer) then we simply provide a different connection string.
To create the ADO Connection object simply Dim
a variable and get the server to do the work.
Dim Connection
Set Connection = Server.CreateObject("ADODB.Connection")
Then to open the database we (optionally) set some of the properties of the Connection
and call Open
Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80
Connection.Open ConnectionString
Check for errors and if everything is OK then we are on our way.
The Records
Next we probably want to access some records in the database. This is achieved via
the ADO RecordSet object. Using this objects Open
method we can pass in
any SQL string that our database driver supports and receive back a set of records
(assuming your are SELECTing records, and not DELETEing).
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open "SELECT * FROM MyTable", Connection, adOpenForwardOnly, adLockOptimistic
adOpenForwardOnly
is defined as 0 and specifies that we only wish to
traverse the records from first to last. adLockOptimistic
is defined as 3
and allows records to be modified.
If there were no errors we now have access to all records in the table "MyTable"
in our database.
The final step is doing something with this information. We'll simply list it.
Dim item
For each item in rs.Fields
Response.Write item.Name & "<br>"
next
while not rs.EOF
For each item in rs.Fields
Response.Write item.Value & "<br>"
next
rs.MoveNext
wend
End Sub
If we know the field names of the records we can access them using rs("field1")
where field1
is the name of a field in the table.
Always remember to close your recordsets and Connections and free any resources
associated with them
rs.Close
set rs = nothing
Connection.Close
Set Connection = nothing
Conclusion
This has been an extremely simple demonstration without serious error checking
or even legible formatting of the output, but it's a base to start with.
Chris Maunder is the co-founder of
CodeProject, DeveloperMedia and ContentLab, and has been a prominent figure in the software development community for nearly 30 years. Hailing from Australia, Chris has a background in Mathematics, Astrophysics, Environmental Engineering and Defence Research. His programming endeavours span everything from FORTRAN on Super Computers, C++/MFC on Windows, through to to high-load .NET web applications and Python AI applications on everything from macOS to a Raspberry Pi. Chris is a full-stack developer who is as comfortable with SQL as he is with CSS.
In the late 1990s, he and his business partner David Cunningham recognized the need for a platform that would facilitate knowledge-sharing among developers, leading to the establishment of CodeProject.com in 1999. Chris's expertise in programming and his passion for fostering a collaborative environment have played a pivotal role in the success of CodeProject.com. Over the years, the website has grown into a vibrant community where programmers worldwide can connect, exchange ideas, and find solutions to coding challenges. Chris is a prolific contributor to the developer community through his articles and tutorials, and his latest passion project,
CodeProject.AI.
In addition to his work with CodeProject.com, Chris co-founded ContentLab and DeveloperMedia, two projects focussed on helping companies make their Software Projects a success. While at CodeProject, Chris' roles included Architecture and coding, Product Development, Content Creation, Community Growth, Client Satisfaction and Systems Automation, and many, many sales meetings. All while keeping his sense of humour.