ASP Database Access Using ADO

While ASP as a server-side scripting language is very useful, without access to a database, you will be generally limited to serving static content. Using ADO, we can easily connect to a variety of data sources such as Microsoft Access, Microsoft SQL Server, MySQL, etc.

You can connect with or without a Data Source Name (DSN). Using a DSN provides you with an easy way to manage the data source connection and also provide you with access to sources other than the database platforms previously mentioned.

In the example below, we are connecting to a data source and query a table called employees. The results are simply displayed within a table element. Depending on how you connect to the data source will determine the value you assign to the variable cstr. In the example below we are connecting to a MySQL database, using a non-DSN connection string. However, if you connect via DSN, the string may look something like this:

cstr = "DSN=dsn_name;UID=user_name;PWD=password;Database=database_name"

or such as this if the username and password is stored in the DSN.

cstr = "DSN=dsn_name"

Example

<% 
Dim oConn, oRs
Dim qry, cstr
Dim db_name, db_username, db_userpassword
Dim db_server

db_server = "dbserver.com"
db_name = "databaseName"
db_username = "dbUserName"
db_userpwd = "dbPassword"
tablename = "employees"
fieldname1 = "empName"
fieldname2 = "empTitle"
fieldname3 = "empOffice"
q = request.querystring("q")

cstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpwd

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open cstr

qry = "SELECT * FROM " & tablename & " WHERE empID = " & q

Set oRS = oConn.Execute(qry)
response.write("<table>")
if not oRS.EOF then
    while not oRS.EOF
        response.write ("<tr><td>Name:</td><td>" & oRs.Fields(fieldname1) & "</td></tr>")
        response.write ("<tr><td>Title:</td><td>" & oRs.Fields(fieldname2) & "</td></tr>")
        response.write ("<tr><td>Office:</td><td>" & oRs.Fields(fieldname3) & "</td></tr>")
        oRS.movenext
    wend
    oRS.close
end if
response.write("</table>")
Set oRs = nothing
Set oConn = nothing
%>