Computers & ProgrammingADOBackend Development

ADO Querying a Database

In most cases, if you are running a web application that includes server-side scripting such as ASP, you are most likely providing dynamic content to your visitors by accepting input and displaying the results from data stored in a database such as MySQL or MSSQL.

In this tutorial, we will cover how to create a few SQL queries that you can use to retrieve information from your database.

In the following examples, we will connect to a MySQL database and query records from a table called Employees. The first step is to create an ASP page and include some of the typical HTML components.

<!DOCTYPE html>
    <title>My Page</title>
    <-- Add in HTML and ASP Code -->

The next step is to add our ASP code in between the starting and ending <body> tags. Our beginning ASP code will simply include creating our variables and objects. Notice where we assign our SQL query to the sql variable.

Dim oConn      'Connection Object
Dim oRS        'Recordset Object
Dim ds         'datasource
Dim qs         'query string
Dim sql        'sql statement

ds = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
qs = Request.Querystring("id")
sql = "SELECT * FROM employees"

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

Set oCmd = Server.CreateObject("ADODB.Command") 
oCmd.ActiveConnection = oConn
oCmd.CommandText = sql
oCmd.CommandType = 1
oCmd.Prepared = True

Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.Open oCmd

Well, get back to the ‘sql’ variable in a moment because we will be looking at different examples by modifying this statement. The next step is to display the results. We use a loop to go through the Fields in the Recordset. Finally, tidy up.

do until oRS.EOF
  for each x in oRS.Fields
    Response.Write( & "=" & x.value & "<br />")
  Response.Write("<br />")

Set oRS=nothing
Set oConn=nothing

The sample results are as follows:

empName=John Smith
empTitle=Sales Associate 

empID=2 empName=Jane White empTitle=CEO

empID=3 empName=Jim Bore empTitle=Accountant

Query for a Specific Record

If you were interested in only querying a subset of the data, we can modify our SQL query by using the WHERE clause. In the following example, we only want to query the database for an employee that has an ID of 2. We can simply modify the variable sql as follows.

sql = "SELECT * FROM employees WHERE empID = 2"

However, to make the website more dynamic, we would prefer to have the information provided by the user. In this case, we will assume the scenario where a user clicked a link on another web page in the application and the link contained the information in the query string. We can modify the syntax as follows.

sql = "SELECT * FROM employees WHERE empID = " & qs

NOTE: The previous example should not be implemented. While it will technically work, it is vulnerable to SQL Injection because the query string can be modified to include additional SQL commands. The recommended approach is to use parameters. Here is an example. First, change the SQL query to include a parameter by using a ‘?’ and secondly add the parameter to the Command object.

sql = "SELECT * FROM employees WHERE empID = ?" In the following section, add the parameter.. oCmd.ActiveConnection = oConn
oCmd.CommandText = sql
oCmd.CommandType = 1
oCmd.Parameters(0) = qs

By using Parameters, we can safely send the database the SQL command including the query string data as a literal.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top