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