How do you use ASP to query a ODBC datasource

How do you use ASP to query a ODBC datasource

Use ASP to query a ODBC datasource

<%

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 "

VALUE=ReQuery>

"

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

%>