It’s not as hard as you think - it can be implemented as two separate ASP pages, combined (by a third frame page for easy viewing) into a nifty SQL command processing tool.
Referring to Figure 01, The SQL Command Tool is simply a upper/lower set of frames. The upper frame contains the SQL command entry text box. The bottom frame contains the ASP page that processes the SQL command and displays any results in tabular format. If you’ve done any work at all in ASP, neither of these pages will astound you - but putting them together gives you a real-time tool for modifications to a live database without downloading or uploading.
Figure 1: SQL Command Tool showing basic layout of the upper and lower frames
Click Here For Figure
Now, the types of commands performed by this SQL Command Processor appear (based upon personal experience) to be dependent upon your IIS and its ASP extensions, but most every important SQL command is available for creating tables, creating/deleting/modifying columns, and creating/deleting/modifying records. Perhaps the only SQL command that can’t be carried out on my host’s system is a simple DESCRIBE command (perhaps as ASP extensions become more powerful).
Using the SQL Command Tool is simple - merely type in a syntactically-correct SQL statement in the upper text box, and click on the SUBMIT button. The command is taken (via the ASP extensions on your IIS web server) to your server’s DBMS. The command is carried out to create and/or modify tables, and if a result is generated – such as a table – it is translated by the SQL Processor into an HTML table which is displayed in the lower frame.
Notice that the frame border in this example is invisible and fixed, but it would be quite possible to code in a moveable visible frame boundary – it’s all up to your particular needs. The example shown permits a “report” of sorts to be created and then printed as a single document – complete with the SQL Command used to generate it as well as the results produced.
Figure 2: SQL Command Tool displaying SQL command (upper frame) and results (lower frame), and frames.
Click Here For Figure
The pages themselves were created in Microsoft FrontPage 98. The partcular project is shown in Figure 03. Note that only 3 files are required, and only ONE file actually contains any ASP coding… It is important to note that the database itself is stored in the directory labeled “data”, and can be any type of database the host has drivers for. In this example the database is a MS Access DB, initially created in MS Access as an empty structure, then imported into the website and loaded/modified “live” from there on in.
Figure 3: FrontPage 98 folder view showing three files comprising SQL Command Tool
Click Here For Figure
Figure 4: FrontPage 98 view of SQLFrame.htm file, showing upper and lower frames.
Click Here For Figure
Figure 5: FrontPage 98 view of SQL Entry Form (the upper frame)
Click Here For Figure
Figure 6: FrontPage 98 view of SQL Processor Form (lower frame)
Click Here For Figure
Example 1 shows the entire snippet of code used by the SQL Entry Form.
There is nothing tricky at all here - the command text is merely assigned to a variable and transported to the form processor (actually the ASP file) via the standard GET method. It’s the ASP file that will be doing the processing that is the interesting part.
Example 1 - ASP code underneath the SQL Entry Form
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 3.0">
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>SQL Entry Form (WebMasters only!)</title>
<base target="middle">
</head>
<body stylesrc="http://www.pondscum.com/WestWindDatabase/Default.htm">
<table border="0" width="100%">
<tr>
<td width="69%"><h5><big><font size="+1">
SQL Command Processor Entry Form</font><blink><font
color="#FF0000"> [WebMasters only!] </font></blink></big>
<small><br>
<small>Valid instructions include SELECT, INSERT, DELETE, UPDATE,
ALTER and PROCEDURE...</small></small></h5>
</td>
<td width="31%"><img src="images/hd4logo.gif" width="200" height="39"
alt="hd4logo.gif (17048 bytes)" align="right"></td>
</tr>
</table>
<form method="GET" action="SQLEntryProcessor.asp" target="bottom">
<div align="left"><p><textarea rows="2" name="txtSQLStatement" cols="83">
</textarea>
<br>
<input type="submit" value="Submit"></p>
</div>
</form>
<table border="0" width="100%">
<tbody>
</tbody>
</table>
</body>
</html>
The real magic of the SQL Command Tool is found in the code below (see Example 02). Using Microsoft’s VBscript, an ADO connection object is created and attached to the database. Then the incoming SQL command is processed and a result set (if generated) is stored in a temporary recordset.
If the SQL command returns a result set, a looping structure is created that begins to build the table. The table includes column labels taken from the field names themselves.
Example 2: ASP code underneath the SQL Entry Processor.ASP file Form
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 3.0">
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>SQLEntryProcessor</title>
</head>
<body stylesrc="http://www.pondscum.com/WestWindDatabase/Default.htm">
<%
on error resume next
txtSQLStatement = request.querystring("txtSQLStatement")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "WestWind","username","password"
‘The next line executes the SQL command and returns a result set…
‘-----------------------------------------------------------------
Set RS = Conn.Execute(txtSQLStatement)
%>
<h2 align="left">RESULTS<br>
<small><small>SQL statement: [<%=txtSQLStatement%>]
</small></small><br>
<font color="#0000FF"><small><small>SQL statement processed;
results (if generated) listed
below...</small></small></font></h2>
<%
If (InStr(txtSQLStatement, "SELECT") > 0) OR
(InStr(txtSQLStatement, "select") > 0) then
'Begin creating the table for the SELECT response...
'----------------------------------------------------
howmanyfields=RS.fields.count -1
Response.write "<table border=1>"
Response.write "<tr>"
'Put field names on the table header...
'---------------------------------------
for i=0 to howmanyfields
Response.write "<th align=left nowrap><b>" & RS(i).name &_
"</b></th>"
next
Response.write "</tr>"
'Create and write each row for the table...
'-------------------------------------------
do while not RS.eof
Response.write "<tr>"
for i = 0 to howmanyfields
Response.write "<td nowrap>" & RS.fields(i).value & "</td>"
next
Response.write "</tr>"
RS.movenext
loop
Response.write "</table>"
End If
Conn.close
%>
<%
If err.number>0 then
response.write "VBScript Errors Occured:" & "<P>"
response.write "Error Number=" & err.number & "<P>"
response.write "Error Descr.=" & err.description & "<P>"
response.write "Help Context=" & err.helpcontext & "<P>"
response.write "Help Path=" & err.helppath & "<P>"
response.write "Native Error=" & err.nativeerror & "<P>"
response.write "Source=" & err.source & "<P>"
response.write "SQLState=" & err.sqlstate & "<P>"
else
'response.write "No VBScript Errors Occured" & "<P>"
end if
If conn.errors.count> 0 then
response.write "Database Errors Occured" & "<P>"
for counter= 0 to Conn.errors.count
response.write "Error #" & Conn.errors(counter).number & "<P>"
response.write "Error desc. -> " &_
Conn.errors(counter).description & "<P>"
next
else
'response.write "No Database Errors Occured" & "<P>"
end if
%>
<!--webbot bot="Include" u-include="ww_footer.htm" tag="BODY" -->
</body>
</html>
That’s it for implementing a SQL Command Tool - all you have to do is modify the code to match your database, its alias and its login information. The rest of code is fairly well documented and intuitive.
Once implemented, experiment with what SQL commands are available on your webserver/RDBMS combination. Let me know if anyone can successfully execute the "DESCRIBE" command.
