|
|
| Databases 101 | | | ASP.NET Chapter | | | Suggest Article | | | Discussions | | | Bookstore | | | Write for Us | | | OpenAuction | | | Our Authors | |
|
|
|
 Internet Jobs
Accept Check Payment
Manage Your E-Store
Database Web Tools
Buy Computer Products
Training Solutions
IT Solutions
Add Chat To My Site
Add Online Sales Reps
Automate your FAQs
|
 |
|
| Returning Ad-Hoc Queries in HTML Tables | | by Pete Nelson | | | Skill level: Beginner | | | First posted: Wednesday, November 10, 1999 | |  | | | | Overview
Being a busy ASP and SQL developer, I often times need to run ad-hoc queries against my tables to get some small bit of information. However, the need to load a separate tool to do so was beginning to become quite a waste of time. Most of the time I'd only need a couple records or a couple fields and didn't need the overhead of loading SQL Enterprise Manager. So, I spent some time and came up with an ASP page that you can use to run ad-hoc queries and display the results in a table. By using the Fields collection on the Recordset object, we can walk through and display all the fields in the result set without having to know anything about the underlying data.
The Page
The HTML page is pretty straightforward. Not much more than a Form with a text box and a submit button. The form posts data back to itself and the ASP code processes the query. The query you submit will automatically repopulate itself into the text box.
<html>
<head></head>
<body>
<form method="post" action="query.asp" name="run_query">
Enter Query:<br>
<textarea name="Query" rows="10" cols="60" wrap="virtual">
<% =Trim(Request("Query")) %>
</textarea><br>
<input type="submit" value=" Run Query ">
</form>
<!-- ' *** ASP Code below gets inserted here *** -->
</body>
</html>
|
Running the Query
First we need to run the query and get the results. We do some basic validation to be sure there's actually query to run.
if trim(request("Query")) > "" then
blnRanQuery = True
Set conDB = Server.CreateObject("ADODB.Connection")
conDB.Open "DNS=MyServer"
on error resume next
Set recTemp = Server.CreateObject("ADODB.RecordSet")
recTemp.Source = trim(request("Query"))
recTemp.ActiveConnection = conDB
recTemp.Open
if err.Number <> 0 then strError = err.Description
on error goto 0
if recTemp.BOF and recTemp.EOF then
blnNoRecords = True
end if
end if
|
Format the Results
Lets make sure we ran the query, and if we have results, call the DisplayQueryResults sub-routine and display the data. Also, display any error messages if the query had problems.
if blnRanQuery then
if trim(strError) <> "" then
Response.Write strError
end if
if not blnNoRecords then
Call DisplayQueryResults(recTemp)
end if
end if
|
DisplayQueryResults Sub-routine
This code takes advantage of the Fields collection in the RecordSet object. By parsing through all the fields, we can get the field names as well as the values. Pass the Recordset object to the sub-routine to get things started. This code will also handle Recordsets with multiple result sets. For example, running "sp_help TableName" in SQL will return several result sets.
<%
Sub DisplayQueryResults(byval recQuery)
do until recQuery Is Nothing
%>
<table width="100%">
<tr valign="top">
<%
' *** First we create the header row with all the field names
for each Field in recQuery.Fields
%><td><% =Field.Name %></td>
<%
next
%>
</tr>
<tr><td colspan="<% =recQuery.Fields.Count %>"><hr></
td></tr>
<%
' *** Loop through all the records in this result set
do while not recQuery.EOF
%>
<tr valign="top">
<%
' *** Now display the values for all the fields in this record
for each Field in recQuery.Fields
%><td><% =Field.Value %></td><%
next
%>
<tr>
<%
recQuery.MoveNext
loop
%>
</table>
<p>
<%
' *** If this Recordset has additional result sets, move to the next
Set recQuery = recQuery.NextRecordset
loop
End Sub
%>
|
The Output
Upon running a query, you should see something like the following:
And there you have it! Ad-hoc queries through an ASP page with nicely formatted results.
| Since this will let you run ANY query against your database (including deletes), be sure it's well protected by some sort of security authentication. It's an excellent tool but can also be a big security hole, so do be careful! |
|
|
| | | | |
This document can be found in these Encyclopedia chapters:
º Tricks of the Trade
|
|
|
|
Got something to add to this article?
Create a new discussion |
|
|
|
View Article Statistics
Authors...
Edit this article
View Preview Version
|
|
| Printable Copy of Article |
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |
|
|
