To read the article online, visit http://www.4GuysFromRolla.com/webtech/011700-1.shtml

Creating a SQL Query Test Page

By Geno Timlin


When creating a data-driven web site, you obviously need to use a number of SQL queries to grab datasets from your database. Before you use a particular SQL query in one of your production ASP pages, though, you want to ensure that it returns the correct information. Therefore, a SQL query test web page, one that allows you to try various SQL statements and view their results, will often come in handy.

For every web database project I work on, I pop this code somewhere onto the server. This page allows me to test out SQL queries before I put them into my ASP. Recently I was doing a project on a co-located SQL Server in Atlanta. I used this page to set up all the tables from my home office in Florida.

The idea is simple. You put SQL code into the form, select the DSN from the drop down list and click submit. If the recordset contains columns, a table is built to display all rows and all columns. If the SQL is an data manipulation statement, a message appears stating that the command completed successfully.

One of the cool features is the clipboard. You can cut and paste SQL into the clipboard and it will be there each time you submit the form. The drop down list of DSN's is fairly simple, you set up an array of DSN strings and loop through them to fill the drop down list. Sometimes I use another textarea for the DSN instead of the drop down list. This allows me to try out different connection strings.

I've become familiar enough with the Microsoft error messages so I haven't built in any fancy error checking.

Well, here's the code, I hope you find it useful! If you have any questions, comments, or ideas for improvement, please email me.

<% Response.Buffer=True %>
<%
  'database startup code
  dim RS	'recordset object
  Set RS = Server.CreateObject("ADODB.Recordset")

  'data source strings for drop down list
  dim dsnarray(2)
  
  'be sure to only populate the array elements zero
  'through the upper bound of the DSN array (2 in this example)
  dsnarray(0) = "MP3"
  dsnarray(1) = "Protfolio"
  dsnarray(2) = "ProductsDB"

  'retrieve the form values
  sql = Request.Form("sql")	'the SQL statement
  clp = Request.Form("clp")	'the clipboard
  dsn = Request.Form("dsn")	'the data source string
%>
<html>
<head>
  <title>SQL Test</title>
<style>
	TD {font-size: smaller }
</style>
</head>
<body bgcolor="#cecece">

<form action="sqltest.asp" method=POST>
<table border=0 cellspacing=0>
<tr>
  <td>
    <b>SQL Code</b>
  </td>
  <td>
    <b>Clipboard</b>
  </td>
</tr>

<tr>
  <td>
    <textarea name="sql" rows="8" cols="50" wrap=soft><%=sql%></textarea>
  </td>
  <td>
    <textarea name="clp" rows="8" cols="30" wrap=soft><%=clp%></textarea>
  </td>
</tr>

<tr>
  <td colspan=2>
    <!-- using the drop down list of connect string -->
	<select name="dsn">
	<% ' loop through the array of DSN's select the current one 
	  for i = LBound(dsnarray) to UBound(dsnarray)
		if dsnarray(i) = dsn then
	  	  Response.Write("<option selected>" & dsnarray(i))
		else
		  Response.Write("<option>" & dsnarray(i))
		end if
	  next
	%>
	</select>
  </td>
</tr>

<!-- this code allows you to test different connect strings -->
<!--
<tr>
  <td colspan=2>
    <textarea name="dsn" rows="2" cols="80" wrap=soft><%=dsn%></textarea>
  </td>
</tr> -->

</table>

  <input type=submit>
</form>

<% 
  'for long winded queries, this will write out the response buffers 
  Response.Flush
%>

<% 
if sql <> "" then	' execute the SQL if it's not empty
  RS.Open sql, dsn
  Response.Write("<table border=1 cellspacing=0>")
  if RS.State = 1 then	'if the recordset has rows		
 	'show the column names
	Response.Write("<tr bgcolor=LightSteelBlue>")

	for each f in RS.Fields
      Response.Write("<td><b>" & f.Name & "</b></td>")
	next

	Response.Write("</tr>")
		
	'show the rows
	do while not RS.EOF
	  Response.Write("<tr bgcolor=White>")

	  for each f in RS.Fields
	     Response.Write("<td>" & f.Value & "</td>")
	  next
	
	  Response.Write("</tr>")

	  RS.MoveNext
	loop
  else
	'DML was performed
	Response.Write("<tr bgcolor=White><td><b>")
	Response.Write("Command Completed Successfully</b>")
	Response.Write("</td></tr>")
  end if

  Response.Write("</table>")
end if 
%>

<%	
  'database clean up code
  Set RS = Nothing 
%>
</body>
</html>

Notice that I have the list of System DSN's hard coded into the above code. The disadvantage with that is when new DSNs are added, or old ones are removed, this ASP page must be updated. To overcome this problem, I highly recommend that you use Mike Shaffer's free DSN listing component. The complete source code and an detailed article can be found here.

Here is a screen shot of the system in action!

The SQL Query Test Page in action!

Happy Programming!


Attachments:

  • Download the source code in text format


  • Article Information
    Article Title: Creating a SQL Query Test Page
    Article Author: Geno Timlin
    Published Date: Monday, January 17, 2000
    Article URL: http://www.4GuysFromRolla.com/webtech/011700-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers