Did you find this useful?
Socialize it today.


ADO Querying a Database

Wednesday, September 26, 2012

tags ADO

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 display 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>
<html>
<head>
    <title>My Page</title>
</head>
<body>

  <-- Add in HTML and ASP Code -->

</body>
</html>

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 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.name & "=" & x.value & "<br />")
  next
  Response.Write("<br />")
  oRS.MoveNext
loop

oRS.close
oConn.close
Set oRS=nothing
Set oConn=nothing
%>

The sample results are as follows:

empID=1
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 query 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.

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

ADO Programming for Dummies ADO: ActiveX Data Objects ADO Programmers Reference