Web Based Database Queries

Originally Published 8/19/2000 on MCPCentral.com

With most websites now sporting databases at their back-end, it is more crucial than ever to be able to access and modify that data at a moment’s notice. In this article, we will use ASP and ADO to create a web-based form that interfaces with a database, allowing you to run queries to view or modify your data over the web. This article can also serve as an introduction to using ADO on web pages to interface with a database.

Active Server Pages (ASP) and Active-X Data Objects (ADO) are Microsoft technologies that provide the ability to run scripts from within a web page and view and manipulate databases via Structured Query Language (SQL) queries. Using ASP as the scripting engine, we will instantiate ADO objects that can execute SQL queries against virtually any type of database to view or modify data.

To begin developing this application, we will need a web form such as the one shown in Figure 1 that will allow a user-query to be entered and submitted. Once the query is submitted, the ASP script will process that query using an ADO Recordset object and display the results in a table on the web page, as seen in Figure 2. The HTML code to create the web form is shown as a snippet in Listing 1. Figure 1 - Screen Shot of Initial Page

<form method="post" action="" id=form1 name=form1>
    <table width="100%">
        <TR>
            <TD><textarea name=query cols=60 rows=10><%=sQuery %></textarea></TD>
            <TD align="center"><input type=submit id=submit1 name=submit1></TD>
        </TR>
    </table>
</form>

This is simple HTML/ASP code that creates a table containing a form with two objects in it: a textarea object used to type in the query, and a submit button. Notice that the form’s action attribute is set to a blank string (“”). This will post any submitted data back to the same page, which will need to be prepared to handle it. Also note that the textarea object will contain the value of the variable sQuery. We will define this variable and set it to the text posted from this form. Thus, after the form is submitted, it will be rebuilt containing the same text just submitted, allowing the user to modify and resubmit the query.

The next step in the process will be to determine if the form has been submitted or if this is the first time the page is being loaded in this browser session. To do so, we will check the value of the sQuery variable, which we will set to the value stored in Request.Form("Query"). This represents the data from the textarea. If it is blank, we will assume that we are loading the page for the first time this session and just display the form. If it is not blank, we will begin the process of executing the query it contains.

        
    <%
    'Get the query text from the forms
    Query = Request.Form("Query")

    'if a query was supplied, run it
    if sQuery<>"" then	
        'instantiate the ADO objects	
        Set db = server.createobject("adodb.connection")	
        set rs = server.createobject("adodb.recordset")	

        'Setup the connection string, change this to point to the	
        'appropriate database and provider (SQL, Jet, Oracle etc)	
        sConnection = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + _
        server.MapPath(".") + "\biblio.mdb"			

        'If used in a production environment there should be	
        'some error checking done here.	
        db.open sConnection 'Open the Database connection

        on error resume next	
        'Open the recordset using the supplied query	
        rs.open sQuery, db, adOpenKeySet, adLockOptimistic	
        lResult = err.number	
        sError = err.description	
        on error goto 0	
        if lResult<>0 then		
            response.write("<h3>" + sError + "</h3>")	
        elseif (rs.State = 0) then 'Test if recordset is open
            response.write("<h3>Your query: <font color=""red"">" & sQuery)
            response.write("</font> returned no results.</h3>")	
        elseif not rs.EOF then 'Recordset was opened successfully		
            rs.movelast		
            rs.movefirst		
            response.write("<h3>" & sQuery & "</h3><BR>" & chr(13))		
            response.write("<center><h4>Recordcount: " & rs.recordcount)
            response.write("</h4></center>")		
            response.write("<table><TR>" & chr(13))	    
            for each item in rs.fields			
                response.write("<TD><b>" & item.name & "</b></TD>")		
            next		
            response.write("</TR>" & chr(13))		
            do while not rs.eof			
                response.write("<TR>" & chr(13))			
                for each item in rs.fields				
                    response.write("<TD>" & item & "</TD>”)			
                next			
                response.write("</TR>" & chr(13))			
                rs.movenext		
            loop		
            response.write("</table></h3><hr>" & chr(13))		
            rs.Close		
            db.Close	
        else		'Unknown error (this should never happen)		
            response.write("<h3>An unknown error has occurred.</h3>")	
        end if	

        set rs = nothing	
        set db = nothing
    end if
%>

As shown in Listing 2, the next step is to instantiate ADO Connection and Recordset objects into the variables db and rs respectively. We then build the ADO connection string and store it in the variable sConnection. For this example, we will use the ADO provider for the Microsoft JET engine (used in Microsoft Access) and connect to the biblio.mdb database which is included as an sample with Microsoft Access. The server.mappath(“.”) in this statement converts to the folder where the current page is being executed, so you must move a copy of biblio.mdb into the same directory as your ASP page to be able to run this example. This same methodology could be used for SQL server, Foxpro or Oracle databases simply by changing the provider and data source properties of the connection string. We then open the connection and recordset respectively using the connection string and query string we have built.

Following the execution of the query, there are three possible states for the recordset object. The first is an error condition because the query did not run successfully. This situation can be caused by any number of reasons, but is most often caused by entering an invalid SQL statement. If this condition occurs, we will display the error message that is returned. The second possibility is that the recordset is unopened as indicated by a State value of 0. This is generally not an error condition, but an indication that the query did not produce any results. This situation will always occur when an insert or update SQL statement is executed. To handle this situation, we will display the query that was executed, and state that it returned no results. Finally, the recordset could have been opened and returned results. In this case, we need to display those results in an HTML table. To build this table without knowing the structure of the database could be difficult, but ASP allows us to use the ‘for each’ construction of VBScript to traverse the recordset, displaying the name of the field using the name property of the field object of the recordset, and the value of each record in the row using the default property of that same object. By looping in this manner and building an HTML table, we can output the results in a readable form without knowing how many fields there are prior to executing the query. A sample of these results are shown in Figure 2.

Once the results have been displayed, we can redisplay the form to allow another query to be executed. There are also a number of additions that can be made to this application that will improve its functionality. For example, this application will currently only work on the database name that is hardcoded into the connection string. The database name could be made variable and this same form used for a number of different databases. As well, some security issues should be taken into account since it would be very easy for anyone to change or delete data in the database using this form if it is not properly secured. I strongly recommend using only sample databases until you fully understand the security implications of making this form available.