Microsoft KB Archive/165671

= HOWTO: Use ASP/ADO to Query an ODBC Datasource (SELECT) =

Article ID: 165671

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Active Server Pages 4.0
 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Internet Information Server 4.0
 * Microsoft Internet Information Services 5.0
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q165671



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:  <%  '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%   '% 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 guarantee 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") %>       <%

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:" if dsn = "" then Response.write "Missing System DSN" end if        if dbuser = "" then Response.write "Missing Username" end if        if dbtable = "" then Response.write "Missing Tablename</FONT><P>" end if           Response.write "<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>

<%        end if      end if   %>

Keywords: kbhowto kbscript kbcodesnippet kbdatabase KB165671

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.