HOWTO: Using ASP/ADO to query an ODBC Datasource (SELECT)

Last reviewed: February 19, 1998
Article ID: Q165671
The information in this article applies to:
  • Microsoft Active Server Pages, version 1.0
  • Microsoft ActiveX Data Objects, version 1.x

SUMMARY

The following is an example of an Active Server Pages (ASP) script that can be used to troubleshoot connection problems with ODBC datasources. This example can also be used to demonstrate how to write simple scripts that select data from databases:

   BEGIN CODE: ===================================

   <HTML>
   <%
   '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '% File:    ADOselect.asp
   '% Author:    Aaron L. Barth (MS)
   '% Purpose:    For testing ADO connectivity to any ODBC Datasource
   '% Disclaimer:    This code is to be used for sample purposes only
   '%              Microsoft does not gaurantee its functionality
   '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

   if Request("REQUESTTYPE") <> "POST" then
   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '   % If the request does not contain REQUESTTYPE = "POST
   '   % then display Form Page
   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

      dsn = Session("dsn")
      dbuser = Session("dbuser")
      dbpass = Session("dbpass")
      dbtable = Session("dbtable")
      dbfield = Session("dbfield")
      dbwhere = Session("dbwhere")
   %>
   <FORM ACTION=adoselect.asp method=POST>
   <TABLE>
   <TR><TD><B>You are authenticated as: </TD>
      <TD><FONT COLOR=GREEN><% =
         Request.ServerVariables("LOGON_USER")%></TD></TR>
   <TR><TD><B>Your IP Address is: </TD>
      <TD><FONT COLOR=GREEN><% =
         Request.ServerVariables("REMOTE_ADDR")%></TD></TR>
   <TR><TD><B>System DSN:</TD>
      <TD><INPUT TYPE=TEXT NAME=datasource VALUE="<% = dsn %>"></TD></TR>
   <TR><TD><B>Username:</TD>
      <TD><INPUT TYPE=TEXT NAME=username VALUE="<% = dbuser %>"></TD></TR>
   <TR><TD><B>Password:</TD>
      <TD><INPUT TYPE=Password NAME=password VALUE="<% = dbpass
         %>"></TD></TR>
   <TR><TD><B>Table:</TD>
      <TD><INPUT TYPE=TEXT NAME=table VALUE="<% = dbtable %>"></TD></TR>
   <TR><TD><B><FONT COLOR=RED>WHERE</TD>
      <TD></TD></TR>
   <TR><TD><B>Field to Query:</TD>
      <TD><INPUT TYPE=TEXT NAME=field VALUE="<% = dbfield %>"></TD></TR>
   <TR><TD><B>Value to Query:</TD>
      <TD><INPUT TYPE=TEXT NAME=where VALUE="<% = dbwhere %>"></TD></TR>
     </TABLE>
     <INPUT TYPE=HIDDEN NAME=REQUESTTYPE VALUE="POST">
     <INPUT TYPE=Submit VALUE="Query Database">
     <HR>
     </FORM>
   <%

   else
   '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '% Perform Query to Database
   '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '   % Request the datsource  from the Previous Form
   '   % Set the Session variable so we can retrieve the
   '   % value for the next query
   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

      dsn  = Request("datasource")
      Session("dsn") = dsn

   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '   % Request the username  from the Previous Form
   '   % Set the Session variable so we can retrieve the
   '   % value for the next query
   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

      dbuser  = Request("username")
      Session("dbuser") = dbuser

   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '   % Request the password from the Previous Form
   '   % Set the Session variable so we can retrieve the
   '   % value for the next query
   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

      dbpass = Request("password")
      Session("dbpass") = dbpass

   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '   % Request the table from the Previous Form
   '   % Set the Session variable so we can retrieve the
   '   % value for the next query
   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

      dbtable = Request("table")
      Session("dbtable") = dbtable

   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '   % Request the table from the Previous Form
   '   % Set the Session variable so we can retrieve the
   '   % value for the next query
   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

      dbfield = Request("field")
      Session("dbfield") = dbfield

   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '   % Request the table from the Previous Form
   '   % Set the Session variable so we can retrieve the
   '   % value for the next query
   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

      dbwhere = Request("where")
      Session("dbwhere") = dbwhere

   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '   % Check to see if any of the requested values are blank, IF they
   '   % are, then inform the user which variables are blank ELSE
   '   % Continue with the query
   '   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
      if dsn = "" OR dbuser = "" OR dbtable = "" then

         Response.write "Error in SQL Statement:<BR>"
         if dsn = "" then
            Response.write "<FONT COLOR=RED>Missing System DSN</FONT><P>"
         end if
         if dbuser = "" then
            Response.write "<FONT COLOR=RED>Missing Username</FONT><P>"
         end if
         if dbtable = "" then
            Response.write "<FONT COLOR=RED>Missing Tablename</FONT><P>"
         end if
            Response.write "<FORM ACTION=adoselect.asp><INPUT TYPE=SUBMIT
               VALUE=ReQuery></FORM>"
       else
   '      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '      % Create the Conn Object and open it
   '      % with the supplied parameters
   '      % System DSN, UserID, Password
   '      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

         Set Conn = Server.CreateObject("ADODB.Connection")
         Set rs = Server.CreateObject("ADODB.RecordSet")
         Conn.Open dsn, dbuser, dbpass

   '      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '      % Build the SQL Statement and assign it
   '      % to the variable sql.  Concatinating the dbtable and the SELECT
   '      % statement
   '      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
         if dbfield = "" OR dbwhere ="" then
         sql="SELECT * FROM " & dbtable
         else

   '      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '      % IF dbfield and dbwhere are specified, then
   '      % change the SQL statement to use the WHERE clause
   '      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '
         sql="SELECT * FROM " & dbtable
         sql = sql & " WHERE " & dbfield
         sql = sql & " LIKE '%" & dbwhere & "%'"
         end if

   '      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '      % For Debugging, Echo the SQL Statement
   '      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
         Response.Write "<B><FONT SIZE=2 COLOR=BLUE>SQL STATEMENT: </B>" &
           sql & "<HR>"

   '      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '      % Open the RecordSet (RS) and pass it
   '      % the connection (conn) and the SQL Statement (sql)
   '      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
         RS.Open sql, Conn
         %>

         <P>
         <TABLE BORDER=1>
            <TR>
            <%
   '         %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '         % Loop through Fields Names and print out the Field Names
   '         %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

            For i = 0 to RS.Fields.Count - 1
            %>
            <TD><B><% = RS(i).Name %></B></TD>
            <% Next %>
            </TR>
            <%
   '         %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '         % Loop through rows, displaying each field
   '         %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
            Do While Not RS.EOF
            %>
            <TR>
            <% For i = 0 to RS.Fields.Count - 1 %>
            <TD VALIGN=TOP><% = RS(i) %></TD>
            <% Next %>
            </TR>
            <%
            RS.MoveNext
            Loop
   '         %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
   '         % Make sure to close the Result Set and the Connection object
   '         %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
            RS.Close
            Conn.Close
            %>
         </TABLE>

         <%
         end if
      end if
   %>

   END CODE: ===================================
Keywords          : AXSFDataBase adobj kbcode
Version           : 1.0
Platform          : IDEAS WINDOWS
Issue type        : kbhowto
Solution Type     : kbcode


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 19, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.