<% '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% '% 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") %>
You are authenticated as: <% = Request.ServerVariables("LOGON_USER")%>
Your IP Address is: <% = Request.ServerVariables("REMOTE_ADDR")%>
System DSN:
Username:
Password:
Table:
WHERE
Field to Query:
Value to Query:

<% 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

" end if Response.write "

" 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 "SQL STATEMENT: " & sql & "
" ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% ' % Open the RecordSet (RS) and pass it ' % the connection (conn) and the SQL Statement (sql) ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% RS.Open sql, Conn %>

<% ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% ' % Loop through Fields Names and print out the Field Names ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% For i = 0 to RS.Fields.Count - 1 %> <% Next %> <% ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% ' % Loop through rows, displaying each field ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Do While Not RS.EOF %> <% For i = 0 to RS.Fields.Count - 1 %> <% Next %> <% RS.MoveNext Loop ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% ' % Make sure to close the Result Set and the Connection object ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% RS.Close Conn.Close %>
<% = RS(i).Name %>
<% = RS(i) %>
<% end if end if %>