Thinkydink - About the Webmaster


About
Home
Braindump
Links
Donate to Thinkydink

	Ask the Thinkydink Opossum!

ASP Code Samples

On this page I present you with blocks of reuseable Active Server Pages code. All of the code shown here is actual code I've written for a real employee tracking web database application and all of it is currently in use in the live system---in other words, it's been tested and it works. This code was written for an ASP front end and a SQL 7 back end.

This page is not intended to act as any kind of a primer or ASP tutorial, but to provide reusable blocks of code to developers who already use ASP and understand its basic structure and syntax. Beginners may be able to glean some knowledge from reviewing these blocks, but I've not made any effort to make them into training exercises.

Since the code is real, it uses field and table names that are real and those names are probably not applicable to your specific application. However, I've heavily commented the code to make it easy for you to figure out what each block is doing and you should have no difficulty replacing my field and table names with your own if you want to use any of this code. For the most part, you should be able to copy and paste these blocks directly into your own ASP pages; in some cases, you may have to add the ASP brackets at the beginning or end of a block to designate it as ASP because the source block may have been copied from within a larger span of ASP code, but aside from that and editing the table and field names, you should be in business.

***********CHECK FOR NONEXISTENT RECORD*********

'verify employee record associated with record number the user has entered exists
'Build ADODB connection string
Set cnExists = Server.CreateObject("ADODB.Connection")
cnStExists = Application("SNConn_ConnectionString")
cnExists.Open cnStExists

'Create the recordset
Set rsExists = Server.CreateObject("ADODB.Recordset")

'EmpNo is the unique record number assigned to records in
'the Staff table. txtEmpNo is the form field in which the
'web application user enters an employee number for lookup

sqlExists = "SELECT EmpNo " &_
"FROM dbo.Staff " &_
"WHERE (dbo.Staff.EmpNo=" & Chr(39)&(Request.Form("txtEmpNo"))& Chr(39) & ");"

'Open the recordset
rsExists.Open sqlExists, cnStExists

If rsExists.EOF Then
iMsg = "There is no employee record for the number you entered. "
iMsg = iMsg + "Please click the Back button to restore your entries, then enter "
iMsg = iMsg + "a valid record number."
Exit Sub
End If

'close the recordset and destroy the connection
rsExists.close
Set cnExists = Nothing

***********CHECK FOR REQUIRED FIELDS*************

'verify required fields have entries
'in this example, txtEmpNo is a required field
'but you can apply the same basic syntax to
'any other required field(s) on your pages

If Request.Form("txtEmpNo") = "" Then
iMsg = "You must enter a Record Number. "
iMsg = iMsg + "Please click the Back button to restore your entries and make changes."
Exit Sub
End If

'Note that you must actually render the iMsg field somewhere on your page
'to display the message to the user. The next code block will
'render an HTML box to display any iMsg contents generated by the code

'If you use the iMsg code anywhere in the body of your page, the following code
'must be among the first lines in your page header code area
'so that any previously generated iMsg contents will be cleared
' when the page is loaded

<%
iMsg=""
%>

*****************MESSENGER BOX***********************

<HR>
<BLOCKQUOTE>
<TABLE WIDTH=75% BGCOLOR=#ffff00 BORDERCOLOR=Black ALIGN=center BORDER=1 CELLSPACING=1 CELLPADDING=1>
<TR>
<TD BGCOLOR=#ffff00>
<P>
<FONT FACE="Verdana, Arial, Helvetica, sans-serif" SIZE="2">
<STRONG>
Program Messenger:
<FONT COLOR="red">
<SPAN CLASS="err">
<%=iMsg%>
</SPAN>
</FONT>
</STRONG>
</FONT>
</P>
</TD>
</TR>
</TABLE>
</blockquote>
<HR>

************CHECK FOR DUPLICATE ENTRY**************

'In this example, the user is attempting to assign an
'employee to a team called an "IPT". IPT assignments
'are stored in a separate table, so in order to assign
'an employee to an IPT the program must create a new
'row in the IPT table. Before creating the new row
'and thereby adding the employee to the selected IPT,
'the program will first verify that the row it is
'about to create doesn't already exist in the IPT table
'and therefore that the employee is not ALREADY
'assigned to the specified IPT.

'Build ADODB connection string
Set cnInIPT = Server.CreateObject("ADODB.Connection")
cnStInIPT = Application("SNConn_ConnectionString")
cnInIPT.Open cnStInIPT

'Create the recordset
Set rsInIPT = Server.CreateObject("ADODB.Recordset")

sqlInIPT = "SELECT IPTStaffNo " &_
"FROM dbo.IPTStaff " &_
"WHERE (dbo.IPTStaff.EmpNo=" & Chr(39)&(Request.Form("txtEmpNo"))& Chr(39) & ") " &_
"AND (dbo.IPTStaff.IPTNo=" & Chr(39)&(Request.Form("selIPTNo"))& Chr(39) & ");"

'Open the recordset
rsInIPT.Open sqlInIPT, cnStInIPT

If Not rsInIPT.EOF Then
iMsg = "Specified employee is already a member of the specified IPT."
Exit Sub
End If

'destroy the connection
rsInIPT.close
Set cnInIPT = Nothing

***************BUILD A TABLE FROM DATA***************

'This code constructs a recordset and then renders the
'data to an HTML table

'The first part of the code constructs the recordset
<%
'Build ADODB connection string
Set cnGetNo = Server.CreateObject ("ADODB.Connection")
cnStGetNo = Application("SNConn_ConnectionString")
cnGetNo.Open cnStGetNo

'Create the recordset
Set rsGetNo = Server.CreateObject("ADODB.Recordset")
sqlGetNo = "SELECT dbo.Staff.EmpNo, dbo.Staff.FullNameString "&_
"FROM dbo.Staff "&_
"WHERE (dbo.Staff.LastName=" & Chr(39) & Request.Form("txtLast") & Chr(39) & ") " &_
"ORDER BY dbo.Staff.FullNameString;"

'Open the recordset
rsGetNo.Open sqlGetNo, cnGetNo

'if file is empty, echo message to user
If rsGetNo.EOF Then
Response.Write "<FONT FACE=Verdana Size=2 Color=RED><STRONG>There are no employees with that last name.</STRONG></FONT>"
End If
%>

'The second part of the code renders the table
<table width="85%" border="2" bordercolor="#000000" bgcolor="#cccccc">

<tr BGCOLOR="#d3d3d3" style="BACKGROUND-COLOR: #ffa500">
<th><div align="left"><FONT color=#000000>
<font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<strong>
Employee Name
</font></STRONG></FONT>
</div></th>

<th><div align="left"><FONT color=#000000>
<font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<strong>
Record Number
</font></STRONG></FONT>
</div></th>
</tr>
<%
'Loop to end of file
Do While Not rsGetNo.EOF
%>


<tr BGCOLOR="#ffffff">


<td valign="top">
<font FACE="Verdana, Arial, Helvetica, sans-serif" SIZE="2">
 
<%=rsGetNo("FullNameString")%>
</font>
</td>


<td valign="top">
<font FACE="Verdana, Arial, Helvetica, sans-serif" SIZE="2">
 
<%=rsGetNo("EmpNo")%>
</font>
</td>
</tr>


<%
rsGetNo.MoveNext
Loop
%>
</table>
</P>

'The last part of the code closes the recordset
'and connection and destroys them
<%
'Close the recordset and destroy it
rsGetNo.Close
Set rsGetNo = Nothing

'Close the data connection and destroy it
cnGetNo.Close
Set cnGetNo = Nothing
%>

*****************BUILD A SELECT LIST FROM DATA*************************

'This code constructs a recordset and then uses those records to
'populate an HTML select list.

'The first part of the code constructs the recordset
<%
'recordset to create dropdown list of IPTs

'Build ADODB connection string
Set cnIPTList = Server.CreateObject ("ADODB.Connection")
cnStIPTList = Application("SNConn_ConnectionString")
cnIPTList.Open cnStIPTList

'Create the recordset
Set rsIPTList = Server.CreateObject("ADODB.Recordset")
sqlIPTList = "SELECT dbo.IPTs.IPTNo, dbo.IPTs.IPTName " &_
"FROM dbo.IPTs " &_
"ORDER BY " &_
"dbo.IPTs.IPTName;"

'Open the recordset
rsIPTList.Open sqlIPTList, cnIPTList
%>

'The next part of the code renders and populates
'an HTML select list
<P>
<FONT FACE=Verdana SIZE=2>
<select name="selIPTNo">

<%do while not rsIPTList.eof%>

<Option value="<%=rsIPTList("IPTNo")%>"><%=rsIPTList("IPTName")%></Option>

<%rsIPTList.movenext
loop%>

</select>
</FONT>
</P>

'This is where you would add code to close and
'destroy the recordset and connection

**************LOOKUP RECORD NUMBER FORM**************

'This form accepts user input as a query parameter to
'retrieve the record number(s) associated with the
'Last Name entered by the user, then writes the
'results to an HTML table

<HR>
<FORM NAME=frmLookup>
<blockquote style="MARGIN-RIGHT: 0px">
<BR>
<P>
<FONT face=Verdana size=2>
<STRONG>
Lookup Employee Record Number
</STRONG>
</FONT>
</P>

<P>
<FONT face=Verdana size=2>
<STRONG>
You can use the form below to look up the record number of
the employee for whom you wish to make edits. If you
already know the record number, you can skip this step.
Note that in StaffNet, a wildcard character is represented
by a '%' sign.
</STRONG>
</FONT>
</P>

<P>
<TABLE bgColor=#ff8c00 border=1 cellPadding=1 cellSpacing=1 width="85%" style="WIDTH: 85%">
<TR>
<TD>
<P>
<FONT face=Verdana size=2>
<STRONG>Enter the Last Name of the employee: &nbsp
</STRONG>
<INPUT type="text" id=txtLast name=txtLast MaxLength=35 size=35>
</FONT>
</P>
</TD>

<TR>
<TD>
<INPUT id=btnLookup name=btnLookup type=submit value="Lookup">
</TD>
</TR>
</TABLE>
</P>
<BR>

<%
'Build ADODB connection string
Set cnGetNo = Server.CreateObject ("ADODB.Connection")
cnStGetNo = Application("SNConn_ConnectionString")
cnGetNo.Open cnStGetNo

'Create the recordset
Set rsGetNo = Server.CreateObject("ADODB.Recordset")
sqlGetNo = "SELECT dbo.Staff.EmpNo, dbo.Staff.DeptNo, dbo.Staff.FullNameString "&_
"FROM dbo.Staff "&_
"WHERE (dbo.Staff.LastName LIKE" & Chr(39) & Request.Form("txtLast") & Chr(39) & ") " &_
"ORDER BY dbo.Staff.FullNameString;"

'Open the recordset
rsGetNo.Open sqlGetNo, cnGetNo

'if file is empty, echo message to user
If Request.Form("btnLookup")<>"" AND rsGetNo.EOF Then
Response.Write "<FONT FACE=Verdana Size=2 Color=RED><STRONG>There are no employees with that last name.</STRONG></FONT>"
End If
%>

'This part of the code takes the results of the query run above
'and renders them as an HTML table in the browser window

<table width="85%" border="2" bordercolor="#000000" bgcolor="#cccccc">

<tr BGCOLOR="#d3d3d3" style="BACKGROUND-COLOR: #ffa500">
<th>
<div align="left">
<FONT color=#000000>
<font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<STRONG>
Employee Name
</STRONG>
</FONT>
</div>
</th>

<th><div align="left"><FONT color=#000000>
<font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<strong>
Dept Number
</font></STRONG></FONT>
</div></th>

<th><div align="left"><FONT color=#000000>
<font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<strong>
Record Number
</font></STRONG></FONT>
</div></th>
</tr>
<%
'Loop to end of file
Do While Not rsGetNo.EOF
%>


<tr BGCOLOR="#ffffff">


<td valign="top">
<font FACE="Verdana, Arial, Helvetica, sans-serif" SIZE="2">
 
<%=rsGetNo("FullNameString")%>
</font>
</td>


<td valign="top">
<font FACE="Verdana, Arial, Helvetica, sans-serif" SIZE="2">
 
<%=rsGetNo("DeptNo")%>
</font>
</td>


<td valign="top">
<font FACE="Verdana, Arial, Helvetica, sans-serif" SIZE="2">
 
<%=rsGetNo("EmpNo")%>
</font>
</td>
</tr>


<%
rsGetNo.MoveNext
Loop
%>
</table>
</P>

<%
'Close the recordset and destroy it
rsGetNo.Close
Set rsGetNo = Nothing

'Close the data connection and destroy it
cnGetNo.Close
Set cnGetNo = Nothing
%>

<BR>
</blockquote>
</FORM>
<HR>

***************DEFAULT A TEXTBOX VALUE ATTRIBUTE*******
***********TO PERSIST CURRENT DATA DISPLAY ************

'This code checks to see if a given field already has
'data entered, and if it does, saves the data previously
'entered as the default value of the field. If the field
'is blank the code does nothing.

<INPUT type="text" id=txtCurEmpNo name=txtCurEmpNo MaxLength=5 size=5 value=
<%If Request.Form("txtCurEmpNo")<> "" Then
Response.Write Chr(39) & Request.Form("txtCurEmpNo") & Chr(39)
End If
%>
'Don't forget the closing bracket for the INPUT tag!
>

****************DEFAULTED TEXT BOX*******************

'This code renders an HTML input box, but defaults
'the value of that box to a data field pulled from
'a previously opened recordset (in this case, the
'recordset is called rsBCData and the field is
'called Mobile). This is a good method to use when
'you want users to be able to edit existing data but
'don't want to force them to re-enter all the data.

<FONT FACE="Verdana" SIZE="2" COLOR="black">
<INPUT type="text" id=txtNewMobile name=txtNewMobile MaxLength=35 size=35 value=
<%=rsBCData("Mobile")%>
>
</FONT>