<%
Dim strDSN
Dim strUID
Dim strPWD
Dim strTable
Dim strSQL
Dim blnOutput
Dim strButton
Dim conn
Dim rs

' get the data from the form if it was previously submitted
strButton = Request.Form("btn")
strDSN = Trim(Request.Form("dsn"))
strUID = Trim(Request.Form("uid"))
strPWD = Trim(Request.Form("pwd"))
strTable = Trim(Request.Form("table"))
strSQL = Trim(Request.Form("sql"))
blnOutput = (Request.Form("nooutput") <> "on")

' do we have enough info to open a valid connection
If (strDSN <> "") Then
' we have enough info so try to open the connection
Set conn = PrepareDBConn(strDSN, strUID, strPWD)
Else
Set conn = Nothing
End If

' fill out the table list if that was requested
If Not(conn Is Nothing) And ((strButton = "Tables") Or (strDSN <> Session("SqlExecDSN"))) Then
Session("SqlExecTableList") = CreateTableSelectList(conn, strTable)
End If

' show the query form
OutputQueryForm strDSN, strUID, strPWD, strTable, blnOutput, strSQL

' if this form was submitted before then display the results
If Not(conn Is Nothing) And (strButton = "Submit") And ((strTable <> "") Or (strSQL <> "")) Then
' get all of the records from the table shown if a SQL statement wasn't provided
If (strSQL = "") Then
strSQL = "SELECT * FROM " & strTable
End If

' output the connection settings
Response.Write("<b>DSN/Connection:</b>&nbsp;<nobr>" & strDSN & "</nobr><br><br>" & vbCrLf)
Response.Write("<b>SQL Statement:</b>&nbsp;<nobr>" & strSQL & "</nobr><br><br>" & vbCrLf)

' execute SQL statment
Set rs = Conn.Execute(strSQL)

If blnOutput Then
If (rs.State = 0) Then ' adStateClosed
Response.Write("<big><b>SQL statment did't return a value</b></big>")
Else
DisplayResults(rs)
End If
Else
Response.Write("<big><b>Query complete</b></big>")
End If

' close recordset if not closed already
If (rs.State <> 0) Then
rs.Close
End If
' close the database connection
conn.Close

Set rs = Nothing
Set conn = Nothing
End If

'
' utility functions
'
Sub OutputQueryForm(strDSN, strUsername, strPassword, strTable, blnOutput, strSQL)
' if a DSN wasn't provided then substitute the default connection used by the diagnostic utilities
If (strDSN = "") Then
strDSN = "Enter Your DSN Or Connection String Here"
End If
%>
Enter a System Data Source Name (DSN) and a Table name, and the sample
code will dump the returned records if any into an HTML table.<br>
<br>
If you would like to perform a specific SQL statement, leave the "Table"
field blank and write in your SQL statement. After you enter DSN,
UID and Password you can click on "Tables" button to get a list of
the tables in the database.<br> <br> <form method="post" action="sqlexecute.asp">
<table border="0" width="100%">
<tr>
<td width="5%">DSN/Connection:</td>
<td width="95%"><input type="text" name="dsn" size="50" value="<%= Server.HTMLEncode(strDSN)%>"></td>
</tr>
<tr>
<td width="5%">UID:</td>
<td width="95%"><input type="text" name="uid" size="15" value="<%= Server.HTMLEncode(strUsername)%>"></td>
</tr>
<tr>
<td width="5%">Password:</td>
<td width="95%"><input type="text" name="pwd" size="15" value="<%= Server.HTMLEncode(strPassword)%>"></td>
</tr>
<tr>
<td width="5%">Table Name:</td>
<td width="95%"><input type="text" name="table" size="25" value="<%= Server.HTMLEncode(strTable)%>">
<% If (Session("SqlExecTableList") <> "") Then %> <select size="1" name="tablelist" OnChange="PopulateonChange(this, 'table');">
<%= Session("SqlExecTableList") %></select> <% End If %> <input type="submit" value="Tables" name="btn">
</td>
</tr>
<tr>
<td width="100%" colspan="2"> <br>
SQL Statement:<br> <textarea name="sql" rows=9 cols=77><%= Server.HTMLEncode(strSQL) %></textarea>
</td>
</tr>
<tr>
<td width="100%" colspan="2">Don't show results:
<input type="checkbox" name="nooutput" <% If Not(blnOutput) Then %>checked="true"<% End If %>></td>
</tr>
</table>
<br>
<p>
<input type="submit" value="Submit" name="btn">
</form>
<%
End Sub

Sub DisplayResults(rs)
' outputs a recordset as an HTML table

' parameters:
' rs (Recordset object): An open and already queried recordset to display

Dim i

' start the table
Response.Write("<table border=""1"">")

' write column names in the header
Response.Write("<tr>")
For i = 0 To (rs.Fields.Count - 1)
Response.write("<td><b>" & rs(i).Name & "</b></td>" & vbCrLf)
Next
Response.Write("</tr>")

' loop through all the records and write out each records as a row in the table
Do While Not(rs.EOF)
Response.Write("<tr>" & vbCrLf)

For i = 0 To (rs.Fields.Count - 1)
Response.Write("<td valign=""top"">")

If IsNull(rs(i).Value) Then
Response.Write("<NULL>")
Else
Response.Write(CStr(rs(i).Value))
End If

Response.Write("</td>" & vbCrLf)
Next

Response.Write("</tr>" & vbCrLf)

rs.MoveNext
Loop

Response.Write("</table>")
End Sub

Function CreateTableSelectList(conn, strDefault)
' creates a dropdown SELECT list with the list of tables available in the database
' schema for the provided DSN

' parameters:
' conn (Connection object): the database connection to query for a list of tables
' strControlName (String): the name of the SELECT control
' strDefault (String): the default table to pre-select

' returns (String): the HTML for a SELECT list of tables

Dim strTableList
Dim strTableName
Dim rs

' call to OpenSchema to get a recordset of tables in the database
Set rs = conn.OpenSchema(20) ' adSchemaTables

' output the OPTION that will be shown if there is no default
strTableList = strTableList & "<option value="""">_____ Tables _____</option>" & vbCrLf

Do While Not(rs.EOF)
' make sure this is a table, if not skip it
If (rs("TABLE_TYPE") = "TABLE") Then
' get the name of the table
strTableName = rs("TABLE_NAME")

' start the OPTION tag for this table
strTableList = strTableList & "<option"
' if this is the default then make sure it is selected initially
If (strDefault = strTableName) Then
strTableList = strTableList & " selected"
End If
' output the rest of the OPTION tag
strTableList = strTableList & ">" & Server.HTMLEncode(strTableName) & "</option>" & vbCrLf
end if

' go to the next item in the schema
rs.MoveNext
Loop

' close and discard the recordset
rs.Close
Set rs = Nothing

CreateTableSelectList = strTableList
End Function

Function PrepareDBConn(strDSN, strUID, strPWD)
Dim conn
Dim strConn

If (Session("SqlExecDSN") <> strDSN) Then
' if DSN has changed reset the session variables
Session("SqlExecDSN") = strDSN
Session("SqlExecTableList") = ""
End If

If (strDSN <> "") Then
strConn = strDSN
' check to see if a DRIVER was provided in the connection string
' if not then this is a DSN name
If (InStr(1, strDSN, "driver", 1) = 0) Then
' this is a DSN
strConn = "DSN=" & strDSN
End If

' if provided, add the database username
If (strUID <> "") Then
strConn = strConn & ";UID=" & strUID
End If

' if provided, add the database password
If (strPWD <> "") Then
strConn = strConn & ";PWD=" & strPWD
End If

' create Connection object
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 30
conn.CommandTimeout = 30
conn.Open strConn
Else
Set conn = Nothing
End If

Set PrepareDBConn = conn
End Function
%>