When first testing a database, nothing is more useful than iSql, and to be able to display and modify your data quickly and simply. This software is given with almost all sql databases and is used to execute queries.
In this article we will see how to build our own iSql with ASP and ADO 2.0. Using ASP we can build a database administration page that will allow you to modify your database from your browser. It’s both easy to implement and very useful, and it’s a good way to see how to work on a database with ASP.
How to use it: First you will have to enter your DSN and your query. Then you can execute a Data Definition Language type of query (like Create Table, Alter table), a transaction (Insert into, Update or Delete) or a selection query (select * from mytable) and see the result.
Because it’s supposed to be used on the Internet, I choose to open a connection at the beginning of the page and to close it at the end of the page (or before when an error occurs).
We will see with more details:
Two external functions
How to deal with a syntax error
How to check the result of the query
How to display “quickly” the recordset
Part I: Two External Functions
When you have portions of code appearing more than once in your program, it’s a good habit to put these portions of code into functions or subs and in a specific file. After, you just have to add an include file at the beginning of your program in order to be able to call these functions.
<!-- #INCLUDE FILE="dblib.inc" -->
The content of this file:
<%
Sub dbClose()
rsIsql.Close
set rsIsql=Nothing
cnIsql.Close
set cnIsql=Nothing
End Sub
Sub doRedirect(sUrl)
Response.expires=0
Response.Buffer=true
Response.redirect sUrl
Response.Flush
End Sub
%>
The first sub will be used for closing the recordset and the connection to the database. And the second sub is used to redirect a page.
Part II: How To Deal with a Syntax Error
The purpose is to detect the error (with Err object) and then to redirect to a specific page to display all the information on the error and to log the error in a log file.
If sSql is empty, it means that it’s the first time you have opened the page and the program does nothing.
Here is the portion of code :
On Error Resume Next
if sSql <>"" then
Session("SQLDebug") = sSql
Set cnIsql = CreateObject("ADODB.Connection")
cnIsql.Open(sDsn)
cnIsql.BeginTrans
Set rsIsql = cnIsql.Execute(sSql)
'Check Error
if Err.Number<>0 then
cnIsql.RollbackTrans
dbClose()
doRedirect("feedback_error.asp?ErrNo=" & Err.Number &_
"&Source=" & Server.urlencode(Err.Source) &_
"&Msg=" & Server.urlencode(Err.Description))
End if
When an error occurs we roll back the transaction. (Normally you only have to do that if you have more than one query, but it’s a good habit to add it.) When an error occurs we also close the connection to the database. Then we can redirect to the page that is specifically design for the process of logging an error. The advantage of having a specific page for this purpose is that you can share it with all your programs. Here the error is logged in a file, but we can also send an e-mail message to the database administrator
The page logs the error in a file in the log folder and then displays the error message on the screen. Here is the code for saving the error message in a file:
You can have three different types of queries. Only in the selection query will we have to display a result. In this case, we will also check if there are records.
With ADO, when executing an query without a result, like an “insert into …”, the recordset is closed and the test “is Nothing” generates an error. So we will use this test to know if there is a recordset or not. We also check if there are records (because your table may be empty).
In the first part of the program, all the Boolean are initialized as false.
if Not (rsIsql is Nothing) then
if (rsIsql.EOF) then fEmpty=true
if Err.Number=0 then fShow=true
End if
Part IV: How To Display “Quickly” the Recordset
Now we know which type of result we have, so we can prepare the string to display.
If there is no result, it means that the transaction is done.
If there is a result, we display first the names of the Fields as the header of our displaying table.
And then if there are records, we use the getstring function to generate the result table.
'Display the recordset
if fShow then
sResult="<table border=0 cellspacing=1 cellpadding=1><tr>" & sCR
'Header with name of fields
For cFields=0 to rsIsql.Fields.count-1
sResult = sResult & "<th bgcolor=silver> " & Trim(rsIsql.Fields(cFields).Name) & "</th>" & sCR
Next
sResult = sResult & "</tr>" & sCR
if Not fEmpty then
sResult = sResult & "<tr bgcolor='#eeeecc'><td>" & sCR
sResult = sResult & rsIsql.getstring(,,"</td><td>","</td></tr>" & sCR & "<tr bgcolor='#eeeecc'><td>"," ")
'Cut the last row delimiter
sResult = Mid(sResult,1,Len(sResult)-Len("<tr bgcolor='#eeeecc'><td>"))
end if
sResult = sResult & "</table>"
else
sResult = "Your Transaction is done."
end if
'We choose to close the connection at the end of the script
cnIsql.CommitTrans
dbClose()
End if
%>
At the end of the page you see the commit of the transaction. We close the database and it’s done!!
You may have noticed that we have to cut the last row delimiter. Run the program without it and if you use a background color for each row, you will see a little empty line at the end of your table.
You may also have noticed that I used chr(13) to add a carriage return at each line. I use that only to have an HTML response page that is more readable (when using “View source” on the browser). It’s sometimes useful for debugging.
Now we just have the HTML code of our iSql Page.
<HTML><HEAD>
<META HTTP-EQUIV="Cache-Control" CONTENT="no cache">
<META HTTP-EQUIV="Pragma" CONTENT="no cache">
<META HTTP-EQUIV="EXPIRES" CONTENT="0">
<TITLE>My iSql</TITLE>
<STYLE>
<!--
BODY { font size:10pt; font-family:Verdana,ARIAL,Helvetica; }
TH { font size:8pt; font-family:Verdana, ARIAL,Helvetica; }
TD { font size:10pt; font-family:Verdana, ARIAL,Helvetica; }
INPUT { font size:10pt; font-family:Verdana, ARIAL,Helvetica; }
-->
</STYLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#99cdff" LINK="#000000" VLINK="#A9A9A9">
<CENTER>
<TABLE ALIGN=CENTER VALIGN=CENTER WIDTH=640>
<TR><TD ALIGN=CENTER>
<!-- Title -->
<TABLE WIDTH=100%><TR>
<TD ALIGN=LEFT>iSql</TR><TR>
<th bgcolor=black><p style="font-size:2pt;"> </p></th>
</TR></TABLE>
</TD></TR>
<!—- Form Query Area -->
<TR><TD ALIGN=CENTER>
<TABLE WIDTH=100%>
<TR><TD>
<FORM ACTION="isql.asp" METHOD=GET>
Your ODBC DSN : <INPUT TYPE="text" NAME="alias" VALUE="<%=sDsn%>" SIZE=30><br>
Your Query : <INPUT TYPE="text" NAME="sql" VALUE="<%=sSql%>" SIZE=80>
<INPUT TYPE="submit" VALUE="DO">
</FORM>
</TD></TR>
</TABLE></TD></TR>
<!-- Result Area -->
<TR><TD ALIGN=CENTER>
<%=sResult%>
</TD></TR>
</TABLE>
</CENTER>
</BODY></HTML>
We use the META tags to be sure that our page will be refreshed after every submit.
We use the TABLE tag to center the text. We just have to add sResult in the right place.)
Conclusion
This simple example shows a different part of an ASP script when your are working with SQL databases. I also use it for ACCESS, because I’m more used to the SQL syntax than the specific functions of ACCESS. By choosing to only use SQL syntax, you will have less problems moving from one database engine to another.
If you put it on line, be aware that everybody can access it and then modify your database structure. So it may be important to add a secured access to your page (with a little include file that will check in the session if the user has entered his login and his password) using a table of users or the security functions of NT (NTFS). If you use an SQL table, your table of users may look like the following:
"select user_id, user_fullname, user_surname, user_level from " &_
"users where (user_login = '" & sUserLogin &_
"' and user_pwd = '" & sUserPwd & "')"
I use the level field to be able to add different types of rights like only viewing data, updating data, and deleting data. I give the access to iSql only for users who are on level 3 because it’s an administration tool.
Christophe Berg who lives in Paris, is a freelance project manager on the Internet and Intranet . He builds flexible and robust database-driven Web sites with Active Server Pages and SQL databases. His company, Toodoo (http://www.toodoo.net), plans to create on-line adventure games.
IImages may also be used via the ASP Request Object. This article will
show you how the use the Request.Form("ImageName.X") property for such tasks as
record navigation (e.g. << Record 1 of 15 >>) or column headings for HTML tables
may use images rather than buttons.
type = "SUBMIT") are the common mechanism to allow the user to request actions from
your Web site. Images may also be used via the ASP Request Object. This article will
show you how the use the Request.Form("ImageName.X") property for such tasks as
record navigation (e.g. << Record 1 of 15 > >) or column headings for HTML tables
may use images rather than buttons. [Read This Article][Top]
Where the collection of specific feedback is necessary, the mailto attribute just won't cut it. With ASP and CDONTS, Web site owners can obtain specialized information from everyone -- even those without e-mail clients. [Read This Article][Top]
In this issue 15 Seconds implements a catalog site that is build with Active Server pages and SQL Server. Along with the implementation there is source code and a discussion of the advantages and disadvantages of creating a catalog site that gets its content from a database. Included are pages for displaying products, creating a menu page, category page, and running a search across a database. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
