Running Ad-Hoc SQL Queries in ASP

Have you ever wished you can run ad-hoc queries on your database from a Web page? This article shows you how a simple Web form and some ASP code can execute SQL statements against a database and display the results in your Web browser.

Step 1: Keep it secure!

The first thing to do is to secure the page you’re about to create! (In this article, we’ll call the page runsql.asp.) There are a number of ways to secure your Web page using IIS (which we won’t get in to here). If you don’t secure the page, you’re taking the chance that anyone can execute SQL statements on your server – something you likely don’t want to do – so be sure to start off with security in mind! You should also consider using the HTTPS protocol, as your SQL statements and results can be intercepted if they are sent in clear text.

Another suggestion is to use a read-only account in the connection string when connecting to a SQL Server database. Although this prohibits your from executing INSERT, UPDATE, and other SQL statements, it will eliminate a significant security risk.

Step 2: ASP Necessities

To keep things clean and proper, we’re going to use Option Explicit at the top of our ASP page. To trap errors later on, we’ll use On Error Resume Next. We’ll also declare the variables we’ll be using later on in the page, and set three ADO constants (which is much cleaner than importing the entire ADOVBS.INC file!).

Option Explicit
On Error Resume Next
Dim objConn, objRec, strSQL, i, iFields
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001

Step 3: Create the HTML form

The HTML form has a TEXTAREA field where we’ll type our SQL statements, and the typical submit and reset buttons. The form submits to itself (i.e. runsql.asp). Note that the TEXTAREA’s initial value is set to Request.Form("sql") – so after we submit the form, we’ll easily be able to see the query we just submitted and make changes to it.

<form action="runsql.asp" method="post">
<textarea name="sql" cols="60" wrap rows="10">
<%= Request.Form("sql") %>
</textarea><br>
<input type="submit">&nbsp;&nbsp;<input type="reset">
</form>

Step 4: Executing the SQL Query

We check to see if the form was submitted by examining Request.ServerVariables("REQUEST_METHOD"), which equals POST when our form is submitted. (Remember, we used the POST method in our form.)

If the form is submitted, we’ll be nice and display the SQL query received in Request.Form("sql"), then create our ADO Connection and Recordset objects. Be sure to open the Connection object with the connection string of your datasource.

Finally, we can open our Recordset. Note the use of adOpenForwardOnly and adLockReadOnly – since we don’t need any special recordset cursors or locking, specifying these options gives the best performance.

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
 
    Response.Write "Query:<br><b>" & Request.Form("sql") & "</b><br>"
 
    Set objConn = Server.CreateObject("ADODB.Connection")
    Set objRec = Server.CreateObject("ADODB.Recordset")
 
    strSQL = Request.Form("sql")
    objConn.Open "dsn=mydsn"
    objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

Step 5: Checking for Errors

Remember when we specified On Error Resume Next at the top of our code? We can trap the error here. Chances are if anything failed up to this point, the objRec.Open call will fail, too, generating an error. If there’s an error, we’ll display it to the user.

If Err.Number <> 0 Then
    Response.Write "An error has occurred!<br>"
    Response.Write "Error number:      " & Err.number & "<br>"
    Response.Write "Error description: " & Err.description & "<br>"

Step 6: Displaying the Results

Continuing right after the error checking, we have to handle two situations: if the recordset is empty, or if it is not empty. If it’s empty, we’ll simply tell the user.

ElseIf objRec.EOF Then
    Response.Write "Empty recordset returned."

If objRec is not empty, we need to display it in an HTML table. First, we want to display the field names as a header row. Recordset field names are stored in the Fields collection, which is the default member of the Recordset object. We’ll read the number of fields (objRec.Fields.Count) into a variable, then use a For loop to output the Name parameter of each field (which contains the column name for the field).

Note that we could have specified objRec(i).Name instead of objRec.Fields(i).Name. Since the Fields collection is the default member of the Recordset object, it isn’t necessary to specify it, assuming it’s what you want.

Else
    iFields = objRec.Fields.Count
    Response.Write "<table border=1><tr>"
    For i = 0 To iFields - 1
        Response.Write "<td><b>" & objRec.Fields(i).Name & "</b></td>"
    Next
    Response.Write "</tr>"

Next, to display the rows in the recordset, we’ll loop through the Recordset using a While loop, moving from record to record using Recordset.MoveNext. Using the same technique as above, we’ll loop through the Fields collection and return the Value parameter for each field, which contains the data in the field.

Again, we could have substituted objRec(i).Value for of objRec.Fields(i).Value, since Fields is the default member of the Recordset object.

    While Not objRec.EOF
        Response.Write "<tr>"
        For i = 0 To iFields - 1
            Response.Write "<td>" & objRec.Fields(i).Value & "</td>"
        Next
        Response.Write "</tr>"
        objRec.MoveNext
    Wend
    Response.Write "</table>"
End If

Step 7: Cleaning Up

As always, you should close your Connection and Recordset objects when you’re done with them, and set their values to Nothing. Don’t rely on the script engine to do this for you. One memory leak can do considerable damage to your server’s performance!

objRec.Close
Set objRec = Nothing
objConn.Close
Set objConn = Nothing

Summary: RUNSQL.ASP

Being able to execute SQL statements from a Web page can come in very handy, especially when you’re not able to connect to the SQL server directly (if it’s inaccessible through a firewall or proxy server). If you secure this properly, you’ll have a powerful and easy to use tool in your Web development toolbox.


COMPLETE SOURCE CODE FOLLOWS:


<%

Option Explicit

On Error Resume Next

Dim objConn, objRec, strSQL, i, iFields

Const adOpenForwardOnly = 0

Const adLockReadOnly = 1

Const adCmdText = &H0001

%>

<html>

<head>

<title>RUNSQL</title>

</head>

<body>

<form action="runsql.asp" method="post">

<textarea name="sql" cols=60 wrap rows=10>

<%= Request.Form("sql") %>

</textarea>

<input type="submit">&nbsp;&nbsp;<input type="reset">

</form>

<%

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then

  Response.Write "Query:<br><b>" & Request.Form("sql") & "</b><br>"

  Set objConn = Server.CreateObject("ADODB.Connection")

  objConn.Open "dsn=mydsn"

  strSQL = Request.Form("sql")

  Set objRec = Server.CreateObject("ADODB.Recordset")

  objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

  If Err.Number <> 0 Then

   Response.Write "An error has occurred!<br>"

   Response.Write "Error number:      " & Err.number & "<br>"

   Response.Write "Error description: " & Err.description & "<br>"

  ElseIf objRec.EOF Then

   Response.Write "Empty recordset returned."

  Else

   iFields = objRec.Fields.Count

   Response.Write "<table border=1><tr>"

   For i = 0 To iFields – 1

    Response.Write "<td><b>" & objRec.Fields(i).Name & "</b></td>"

   Next

   Response.Write "</tr>"

   While Not objRec.EOF

    Response.Write "<tr>"

    For i = 0 To iFields – 1

     Response.Write "<td>" & objRec.Fields(i).Value & "</td>"

    Next

    Response.Write "</tr>"

    objRec.MoveNext

   Wend

  End If

  objRec.Close

  Set objRec = Nothing

  objConn.Close

  Set objConn = Nothing

End If

%>

</body>

</html>

One thought on “Running Ad-Hoc SQL Queries in ASP

  • I was so thrilled when I found this, but it doesn’t work. Initially, I had to change the database connection. When it appeared I got that working, I tried a number of different things and either get:
    Error number: 438
    Error description: Object doesn’t support this property or method or
    Error number: -2147352571
    Error description: Type mismatch.

    What I need to do is activate the page from another. I’ve removed some of your code for simplification but just can’t get it to work. Here is what I currently have:

    RUNSQL

    <%
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("..\DB\ILCAEVENTCALENDAR.MDB") & ";"
    objConn.Open
    strSQL = "INSERT INTO mem_rnw ( ID, lastName, firstName, midInit ) SELECT mem.ID, mem.lastName, mem.firstName, mem.midInit FROM mem"
    Set objRec = Server.CreateObject("ADODB.Recordset")

    objRec.Open objConn, adOpenForwardOnly, adLockReadOnly, adCmdText, strSQL

    If Err.Number 0 Then
    Response.Write “An error has occurred!”
    Response.Write “Error number: ” & Err.number & “”
    Response.Write “Error description: ” & Err.description & “”

    ElseIf objRec.EOF Then
    Response.Write “Empty recordset returned.”

    End If

    objRec.Close
    Set objRec = Nothing
    objConn.Close
    Set objConn = Nothing

    %>

    Has anything changed since you posted?

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.