(919) 404-9327 sales@lizardwebs.net

General ADO Examples

ADO is one of the greatest things about ASP as far as I’m concerned.Sure you can do all sorts of interesting things with ASP, but when it comes to creating powerful web sites, ADO is the draft horse that pulls the ASP cart as far as I’m concerned.Being able to put and pull information out of a database allows the developer to create flexibile applicationswith dynamic data based on a visitor’s needs or desires. If I wish to create a tailored application that shows a specific set of information for each user, ASP / ADO come to the rescue by dynamically creating SQL queries and executing them. Once the data has been selected, the output can be then fit into a standardized format.We’re going to examine the standard SQL SELECT statement and show some basic and slightly advanced ways of doing things. We will quickly create an ADO connection, create a SQL statement, create a recordset from that statement and then generate a table from the records.

Using ASP and ADO to create HTML pages

When your content is fairly static, create static html pages from ASP. This sample code started off on our “why ASP”page. Sometime a project page will have content that is very static and might not be changed for months (or even years) at a time, BUT it does contain some content that would be easier to generate via ASP. Examples of this kind of content would be database-based information such as an employee listing, long menu listings, and other contents that can be retrieved from a database and then formatted nicely.Here is a little example that makes use of the good old MS Northwinds database that shows a couple of different code pieces that are helpful.

    • Right off the bat, we use our OPTION EXPLICIT mentioned on the ASP tips page.
    • The difference between DIM and CONST. CONST is created and assigned a value at one time. A DIM can’t be done in one step. It has to be DIM’d and then assigned a variable in a separate step.
    • We show simple setup of a basic ADO connection and recordset using a DSN.
  • Our sql string uses a couple of neat things that are helpful to know about.
    1. We use a TOP 3 to return ONLY the top 3 items from this query. The TOP modifier will limit the amount of records that a query will return. It is helpful in doing things like “Top 10 Requested Pages” or something along those lines.
    2. We show the usage of the plus (“+”) sign in concatenating string inside a SQL query. The ampersand (“&”) cannot be used in a SQL string for that purpose. However, if desired, one can concatenate the returned fields into a single string later in VBScript outside of the SQL query. It is easier in many circumstances though to just put the fields together at one time in the original query.
    3. Though most people should already know this, the ORDER BY statement is used to order our returned records by the last name field value going from A – Z. One can also use DESC to go from Z – A. As a side note, one can sort on any field that exists IN the table. We could just as easily have used “ORDER BY HomePhone” to set a record order. One does NOT need to include the sorting field in the SELECT part of the statement.

 

 <% OPTION EXPLICIT

Dim objCN ‘as ADODB.Connection
Dim rs ‘as ADODB.RecordSet
Dim strSQL ‘as String

CONST DSN_NAME = “northwind”

‘ *** Create and open up our Database connections ***
Set objCN = Server.CreateObject(“ADODB.Connection”)
objCN.Open = “DSN=” & DSN_NAME & “;”

‘ *** Create our recordset object ***

Set rs = Server.CreateObject(“ADODB.Recordset”)

‘ *** Create the SQL query and get recordset ***
strSQL = “SELECT TOP 3 LastName + ‘, ‘ + FirstName as sFullName, ” &_
” HomePhone as sHomePhone FROM Employees ORDER BY LastName ASC;”
Set rs = objCN.Execute(strSQL)

‘ *** Make sure we have records ***

If NOT (rs.EOF and rs.BOF) Then

‘ *** We have records ***
rs.MoveFirst ‘ Get to the first record

‘ *** Set up our table for output ***
Response.write “<h1>Employee Listing</h1>” & vbCrLf
Response.write “<table cellpadding=””5″”>” & vbCrLf

‘ *** And create our content ***

Do While NOT rs.EOF ‘ Run until no more records
Response.write ” <tr><td style=””font-weight:bold;””>” &_
rs(“sFullName”) & “</td><td>” &_
rs(“sHomePhone”) & “</td></tr>” & vbCrLf

rs.MoveNext ‘ Move to our next record
Loop ‘ And do it again

‘ *** Close our table properly ***

Response.write “</table>” & vbCrLf

Else

‘ *** We DON’T have records ***
Response.write “No records – check your DB and DSN!”

End If

‘ *** And tidy up our ADO components ***

rs.Close
objCN.Close
Set rs = nothing
set objCN = nothing

%>