Tutorial:
Database - ASP - Edit Security Users By No With Access Database
Dr.
Thomas E. Hicks
Computer Science
Department
Trinity University
IIS 5 Install on Windows XP Pro
IIS 5 Installation on Windows 2000 Pro/Server
It might even help to read a tutorial that describes the need for Database Web Applications.
Database Need For Web Applications
You may use the basic ideas of this tutorial for either the OMDB connection or the MapPath connection..
Database - ASP - OMDB Connections
Database - ASP - MapPath Connections
For purposes of discussion, let us suppose that an Access Database, called Security.mdb, resides at the root of drive C. (See Below!) When a database is installed on the web server, the necessary OMDB drivers are also installed. If the web server does not have the database installed, then the OMDB drivers will have to be downloaded and installed; this process differs from database to database and is beyond the scope of this paper. Microsoft Access and the appropriate OMDB drivers have been installed on the server illustrated below.
The Security Database is opened and the Tables tab is selected; in order to keep our example simplistic, the Security Database has only a single table, called Users. The contents of our very simple Users table can be seen below.
Each record in the Users table contains fields Name, No, Password, and IDNo; these will be needed later.
The datatypes of each field may be seen below.
The Security Database above was is ultra trivial application which had only one database table. Relational databases often have many tables; this is true of Access databases as well. This database may be downloaded.
We are going to place all of the ASP files in folder C:\Inetpub\wwwroot\ASP. We could place the database files anywhere on the drive. For security reasons, it is not a good idea to place them in the wwwroot directory.
For purposes of this tutorial, we shall assume that the Security database files are located in C:\Security. For purposes of this tutorial, we shall assume that the Security database is located in C:\.
The first page shall be called EditSecurityUserByNo1.asp. It shall be the responsibility of this page to prompt the user for a SoughtNo and then transfer processing control to the second page.
The second page shall be called EditSecurityUserByNo2.asp. Control shall pass to a generic Error.asp page in the event that the SoughtNo is blank. Control shall pass to a generic Error.asp page in the event that the SoughtNo is not numeric. Control shall pass to a generic Error.asp page in the event that the there is no record whose No is SoughtNo. It shall be the responsibility of this page to retrieve the existing information from the user record and enable the user to alter the Name, Password, or No; upon completion, transfer processing control to the third page.
The third page shall be called EditSecurityUserByNoConfirmation.asp. It shall be the responsibility of this page to update/modify the user record in the database table if correct. Control shall pass to a generic Error.asp page in the event that the NewName is blank. Control shall pass to a generic Error.asp page in the event that the NewNo is blank. Control shall pass to a generic Error.asp page in the event that the NewPassword is blank. Control shall pass to a generic Error.asp page in the event that the NewPassword is not numeric.
The autonumber field, IDNo shall be automatically completed by the database.
<%@ LANGUAGE = VBScript %> |
The server will have one default scripting language. This may or may not be VBScript. This line makes sure that the scripting language is VBScript. Blocks of ASP code begin with <% and end with %>.
<% Option Explicit %> |
This line of ASP code forces the programmer to explicitly declare each and every variable.
<% Response.Expires = 0 %> |
This line of ASP reloads the most recent database data each time the page is refreshed.
<!-- #include virtual = "../../Common/adovbs.inc" --> |
Variables, such as AdOpenDynamic and AdLockOptimistic are defined in the adovbs.inc file. I keep a copy of this file in folder C:\Inetpub\WWWRoot\Common.
<% '======================================================================== '======================================================================== '==== EditSecurityUserByNo1.asp ==== '======================================================================== '==== ==== '==== Purpose : Prompt the user for the User Name & transfer ==== '==== to page EditSecurityUserByNo2.asp to load ==== '==== current information and allow user to edit. ==== '==== ==== '==== Written By : Dr. Thomas E. Hicks Date: 6/1/2003 ==== '======================================================================== '======================================================================== %> |
The documentation block provides a brief statement of purpose.
<html> <head><title>Dr. Thomas E. Hicks - EditSecurityUserByNo1.asp </title></head> |
The HTML places Dr. Thomas E. Hicks - EditSecurityUserByNo1.asp in the browser title bar.
<BODY TEXT = "#000000" BGCOLOR = "#000000" VLINK ="#000000" ALINK ="#000000" BACKGROUND ="Paper.jpg"> |
This HTML code above defines the page background, the default text color, the default background color, and the default link colors.
<CENTER><FONT FACE ="Arial" SIZE="4">EditSecurityUserByNo.asp<BR> Written By<BR>Dr. Thomas E. Hicks</FONT></CENTER><HR> <hr> |
The HTML code above creates the following commercial at the top of the page.
EditSecurityUserByNo1.asp
Written By
Dr. Thomas E. Hicks
<FORM METHOD = "POST"
ACTION = "EditSecurityUserByNo2.asp">
|
This is a standard HTML form which shall provide the user an opportunity to enter a user No and transfer that information to page EditSecurityUserByNo2.asp.
<TABLE BORDER = "5"
CELLPADDING = "4"
CELLSPACING = "4"
STYLE = "border-collapse: collapse"
BORDERCOLOR = "#800000"
BGCOLOR = "#FFFFFF"
ALIGN = "Center">
|
A table shall be used to organize the prompts, buttons, and input boxes.
<TR><TD ALIGN = "Right"><B><font face="Arial" color="#0000FF">
Edit User No
<INPUT TYPE = "Text"
VALUE = ""
NAME = "SoughtNo"
SIZE = 10
MAXSIZE = 10></B></FONT>
</TD></TR>
|
In the first row of the table shall be a prompt ==> Edit User No ==> and a 10 character text box in which to enter the information. Note that the name of the textbox is SoughtNo; the post method will transfer this information to the confirmation page.
<TR><TD>
<INPUT TYPE = "SUBMIT"
VALUE = "Edit This User Now!"
STYLE = "BACKGROUND=BLUE; COLOR=#FFFFFF ;CURSOR=hand;
FONT-FAMILY ='SYSTEM';FONT-SIZE=10pt">
</TD></TR>
</TABLE></FORM>
</BODY></HTML>
|
The second row of the table shall contain a blue submit button whose caption is Edit This User Now! The remainder of the HTML code above simply ends the table, the form, the body, and the document.
The complete code may be found below. A working model may be found at
<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<% Response.Expires = 0 %>
<!-- #include virtual = "../../Common/adovbs.inc" -->
<%
'========================================================================
'========================================================================
'==== EditSecurityUserByNo1.asp ====
'========================================================================
'==== ====
'==== Purpose : Prompt the user for the User Name & transfer ====
'==== to page EditSecurityUserByNo2.asp to load ====
'==== current information and allow user to edit. ====
'==== ====
'==== Written By : Dr. Thomas E. Hicks Date: 6/1/2003 ====
'========================================================================
'========================================================================
%>
<html>
<head><title>Dr. Thomas E. Hicks - EditSecurityUserByNo1.asp
</title></head>
<BODY TEXT = "#000000"
BGCOLOR = "#000000"
VLINK ="#000000"
ALINK ="#000000"
BACKGROUND ="Paper.jpg">
<CENTER><FONT FACE ="Arial" SIZE="4">EditSecurityUserByNo1.asp<BR>
Written By<BR>Dr. Thomas E. Hicks</FONT></CENTER><HR>
<FORM METHOD = "POST"
ACTION = "EditSecurityUserByNo2.asp">
<TABLE BORDER = "5"
CELLPADDING = "4"
CELLSPACING = "4"
STYLE = "border-collapse: collapse"
BORDERCOLOR = "#800000"
BGCOLOR = "#FFFFFF"
ALIGN = "Center">
<TR><TD ALIGN = "Right"><B><font face="Arial" color="#0000FF">
Edit User No
<INPUT TYPE = "Text"
VALUE = ""
NAME = "SoughtNo"
SIZE = 10
MAXSIZE = 10></B></FONT>
</TD></TR>
<TR><TD>
<INPUT TYPE = "SUBMIT"
VALUE = "Edit This User Now!"
STYLE = "BACKGROUND=BLUE; COLOR=#FFFFFF ;CURSOR=hand;
FONT-FAMILY ='SYSTEM';FONT-SIZE=10pt">
</TD></TR>
</TABLE></FORM>
</BODY></HTML>
|
You can see the results below:
<%@ LANGUAGE = VBScript %> |
The server will have one default scripting language. This may or may not be VBScript. This line makes sure that the scripting language is VBScript. Blocks of ASP code begin with <% and end with %>.
<% Option Explicit %> |
This line of ASP code forces the programmer to explicitly declare each and every variable.
<% Response.Expires = 0 %> |
This line of ASP reloads the most recent database data each time the page is refreshed.
<!-- #include virtual = "../../Common/adovbs.inc" --> |
Variables, such as AdOpenDynamic and AdLockOptimistic are defined in the adovbs.inc file. I keep a copy of this file in folder C:\Inetpub\WWWRoot\Common.
<% '======================================================================== '======================================================================== '==== EditSecurityUserByNo2.asp ==== '======================================================================== '==== ==== '==== Purpose : Read the desired record into a form for editing. ==== '==== Go to page EditSecurityUserByNoConfirmation.asp ==== '==== to update the record. ==== '==== ==== '==== Written By : Dr. Thomas E. Hicks Date: 6/1/2003 ==== '======================================================================== '======================================================================== |
The documentation block provides a brief statement of purpose.
'------------------------------------------------------------------------ ' Declarations '------------------------------------------------------------------------ Dim Conn, UserSQL, UserRecordSet, ConnString, Counter Dim SoughtNo, No, NoInt, NoChar Dim Name, NewNo, Password |
Declare all of the variables to be used by this page.
'------------------------------------------------------------------------
' Transfer To Page Error If User Leaves SoughtNo Blank
'------------------------------------------------------------------------
If ( Len(SoughtNo) = 0 ) Then
Session("Error") = "You Have Not Entered A No"
Response.Redirect "Error.asp"
End If
|
Transfer to Error.asp and report error if the user leaves the SoughtNo blank.
'------------------------------------------------------------------------
' Transfer To Page Error If User Does Not Enter A Numeric Value
'------------------------------------------------------------------------
If ( NOT IsNumeric(SoughtNo) ) Then
Session("Error") = "You have Not Entered A Valid Number"
Response.Redirect "Error.asp"
End If
|
Transfer to Error.asp and report error if the SoughtNo is not numeric.
'------------------------------------------------------------------------ ' More Initializations '------------------------------------------------------------------------ No = Cint(SoughtNo) |
Fill No with the integer value passed from SoughtNo in the text input box.
'------------------------------------------------------------------------
' Create The Console Object & MapPath Connection To The Server
'------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")
ConnString = "DBQ=" & Server.MapPath("../../../Security.mdb")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString
|
The block of code above connects this page to the Security database. A connection object is created. Variable ConnString contains part of the connection arguments. The Conn.Open opens the Security database and associates it with the connection object.
'------------------------------------------------------------------------
' Attempt To Read The User Information
'------------------------------------------------------------------------
UserSQL = " Select * " &_
" From Users " &_
" Where Users.No = " & SoughtNo
Session("SoughtNo") = SoughtNo
Set UserRecordSet = Server.CreateObject ("ADODB.RecordSet")
UserRecordSet.Open UserSQL, Conn, AdOpenDynamic, AdLockOptimistic
|
The SQL query is to search for and read the user whose No is SoughtNo. This query may or may not be successful. There may be no matches.
'------------------------------------------------------------------------
' Transfer To Page Error If There Are No Query Matches
'------------------------------------------------------------------------
If ( UserRecordSet.EOF ) Then
Session("Error") = "We Are Unable To Find Any Users With This No"
Response.Redirect "Error.asp"
End If
|
Transfer to Error.asp and report error if there are no users whose No is SoughtNo.
'------------------------------------------------------------------------
' Fill Local Variables With Info From Query Record
'------------------------------------------------------------------------
Name = UserRecordSet.Fields("Name")
Password = UserRecordSet.Fields("Password")
NewNo = UserRecordSet.Fields("No")
|
Fill the local variables with the information returned from the query above.
'------------------------------------------------------------------------ ' Convert From Int To Char '------------------------------------------------------------------------ NoChar = CStr(NewNo) '------------------------------------------------------------------------ ' Not Needed - Just To Show Convert From Char To Int '------------------------------------------------------------------------ NoInt = CInt(NoChar) |
We shall need the string equivalent of the value from the No field. ASP has a function, called CStr, to convert from Integer to Character. ASP also has a reciprocal function, called CInt to convert from Character to Integer; this is not needed, but shown for completeness.
<html><head> <title>Dr. Thomas E. Hicks - EditSecurityUserByNo2.asp </title> </head> |
Fill No with the integer value passed from SoughtNo in the text input box.
<BODY TEXT = "#000000" BGCOLOR = "#000000" VLINK ="#000000" ALINK ="#000000" BACKGROUND ="Paper.jpg"> |
This HTML code above defines the page background, the default text color, the default background color, and the default link colors.
<CENTER><FONT FACE ="Arial" SIZE="4">EditSecurityUserByNo2.asp<BR> Written By<BR>Dr. Thomas E. Hicks</FONT></CENTER><HR> <hr> |
The HTML code above creates the following commercial at the top of the page.
EditSecurityUserByNo2.asp
Written By
Dr. Thomas E. Hicks
<FORM METHOD = "POST"
ACTION = "EditSecurityUserByNo2.asp">
|
This is a standard HTML form which shall provide the user an opportunity to enter a user No and transfer that information to page EditSecurityUserByNo2.asp.
<TABLE BORDER = "5"
CELLPADDING = "4"
CELLSPACING = "4"
STYLE = "border-collapse: collapse"
BORDERCOLOR = "#800000"
BGCOLOR = "#FFFFFF"
ALIGN = "Center">
|
A table shall be used to organize the prompts, buttons, and input boxes.
<TR><TD ALIGN = "Right"><FONT FACE ="Arial" SIZE="3" COLOR = "#0000FF"><B>
Enter Name
<INPUT TYPE = "Text"
VALUE = "<% = Name %>"
NAME = "NewName"
SIZE = 20
MAXSIZE = 20></B></FONT>
</TD></TR>
|
In the first row of the table shall be a prompt ==> Enter Name ==> and a 20 character text box in which to enter the information. Note that the name of the textbox is NewName; the post method will transfer this information to the confirmation page. It is initialized with the value read from the record.
<TR><TD ALIGN = "Right"><FONT FACE ="Arial" SIZE="3" COLOR = "#0000FF"><B>
Enter No
<INPUT TYPE = "Text"
VALUE = "<% = NoChar %>"
NAME = "NewNo"
SIZE = 10
MAXSIZE = 10></B></FONT>
</TD></TR>
|
In the second row of the table shall be a prompt ==> Enter No ==> and a 10 character text box in which to enter the information. Note that the name of the textbox is NewNo; the post method will transfer this information to the confirmation page. It is initialized with the value read from the record.
<TR><TD ALIGN = "Right"><FONT FACE ="Arial" SIZE="3" COLOR = "#0000FF"><B>
Enter Password
<INPUT TYPE = "Text"
VALUE = "<% = Password %>"
NAME = "NewPassword"
SIZE = 15
MAXSIZE = 15></B></FONT>
</TD></TR>
|
In the third row of the table shall be a prompt ==> Enter Password ==> and a 15 character text box in which to enter the information. Note that the name of the textbox is NewPassword; the post method will transfer this information to the confirmation page. It is initialized with the value read from the record.
<TR><TD><CENTER>
<INPUT TYPE = "SUBMIT"
VALUE = "Update This Record Now!"
STYLE = "BACKGROUND=BLUE; COLOR=#FFFFFF ;CURSOR=hand;
FONT-FAMILY ='SYSTEM'">
</CENTER></TD></TR>
</TABLE></FORM>
|
The fourth row of the table shall contain a blue submit button whose caption is Update This Record Now! The remainder of the HTML code above simply ends the table, the form, the body, and the document.
The complete code may be found below. A working model may be found at
<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<% Response.Expires = 0 %>
<!-- #include virtual = "../../Common/adovbs.inc" -->
<%
'========================================================================
'========================================================================
'==== EditSecurityUserByNo2.asp ====
'========================================================================
'==== ====
'==== Purpose : Read the desired record into a form for editing. ====
'==== Go to page EditSecurityUserByNoConfirmation.asp ====
'==== to update the record. ====
'==== ====
'==== Written By : Dr. Thomas E. Hicks Date: 6/1/2003 ====
'========================================================================
'========================================================================
'------------------------------------------------------------------------
' Declarations
'------------------------------------------------------------------------
Dim Conn, UserSQL, UserRecordSet, ConnString
Dim SoughtNo, No, NoInt, NoChar
Dim Name, NewNo, Password
'------------------------------------------------------------------------
' Initializations
'------------------------------------------------------------------------
SoughtNo = Trim(Request("SoughtNo"))
'------------------------------------------------------------------------
' Transfer To Page Error If User Leaves SoughtNo Blank
'------------------------------------------------------------------------
If ( Len(SoughtNo) = 0 ) Then
Session("Error") = "You Have Not Entered A No"
Response.Redirect "Error.asp"
End If
'------------------------------------------------------------------------
' Transfer To Page Error If User Does Not Enter A Numeric Value
'------------------------------------------------------------------------
If ( NOT IsNumeric(SoughtNo) ) Then
Session("Error") = "You have Not Entered A Valid Number"
Response.Redirect "Error.asp"
End If
'------------------------------------------------------------------------
' More Initializations
'------------------------------------------------------------------------
No = Cint(SoughtNo)
'------------------------------------------------------------------------
' Create The Console Object & MapPath Connection To The Server
'------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")
ConnString = "DBQ=" & Server.MapPath("../../../Security.mdb")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString
'------------------------------------------------------------------------
' Attempt To Read The User Information
'------------------------------------------------------------------------
UserSQL = " Select * " &_
" From Users " &_
" Where Users.No = " & SoughtNo
Session("SoughtNo") = SoughtNo
Set UserRecordSet = Server.CreateObject ("ADODB.RecordSet")
UserRecordSet.Open UserSQL, Conn, AdOpenDynamic, AdLockOptimistic
'------------------------------------------------------------------------
' Transfer To Page Error If There Are No Query Matches
'------------------------------------------------------------------------
If ( UserRecordSet.EOF ) Then
Session("Error") = "We Are Unable To Find Any Users With This No"
Response.Redirect "Error.asp"
End If
'------------------------------------------------------------------------
' Fill Local Variables With Info From Query Record
'------------------------------------------------------------------------
Name = UserRecordSet.Fields("Name")
Password = UserRecordSet.Fields("Password")
NewNo = UserRecordSet.Fields("No")
'------------------------------------------------------------------------
' Convert From Int To Char
'------------------------------------------------------------------------
NoChar = CStr(NewNo)
'------------------------------------------------------------------------
' Not Needed - Just To Show Convert From Char To Int
'------------------------------------------------------------------------
NoInt = Cint(NoChar)
%>
<html><head>
<title>Dr. Thomas E. Hicks - EditSecurityUserByNo2.asp </title>
</head>
<BODY TEXT = "#000000"
BGCOLOR = "#000000"
VLINK ="#000000"
ALINK ="#000000"
BACKGROUND ="Paper.jpg">
<CENTER>
<font face="Arial" size="4">EditSecurityUserByNo2.asp<br>
Written By<br>
Dr. Thomas E. Hicks</font></p>
</CENTER>
<hr>
<FORM METHOD = "POST"
ACTION = "EditSecurityUserByNoConfirmation.asp">
<TABLE BORDER = "5"
CELLPADDING = "4"
CELLSPACING = "4"
STYLE = "border-collapse: collapse"
BORDERCOLOR = "#800000"
BGCOLOR = "#FFFFFF"
ALIGN = "Center">
<TR><TD ALIGN = "Right"><FONT FACE ="Arial" SIZE="3" COLOR = "#0000FF"><B>
Enter Name
<INPUT TYPE = "text"
NAME = "NewName"
VALUE = "<% = Name %>"
SIZE = 20
MAXSIZE = 20
STYLE = "FONT-FAMILY ='SYSTEM'">
</B></FONT>
</TD></TR>
<TR><TD ALIGN = "Right"><FONT FACE ="Arial" SIZE="3" COLOR = "#0000FF"><B>
Enter No
<INPUT TYPE = "Text"
VALUE = "<% = NoChar %>"
NAME = "NewNo"
SIZE = 10
MAXSIZE = 10
STYLE = "FONT-FAMILY ='SYSTEM'">
</B></FONT>
</TD></TR>
<TR><TD ALIGN = "Right"><FONT FACE ="Arial" SIZE="3" COLOR = "#0000FF"><B>
Enter Password
<INPUT TYPE = "Text"
VALUE = "<% = Password %>"
NAME = "NewPassword"
SIZE = 15
MAXSIZE = 15
STYLE = "FONT-FAMILY ='SYSTEM'">
</B></FONT>
</TD></TR>
<TR><TD><CENTER>
<INPUT TYPE = "SUBMIT"
VALUE = "Update This Record Now!"
STYLE = "BACKGROUND=BLUE; COLOR=#FFFFFF ;CURSOR=hand;
FONT-FAMILY ='SYSTEM'">
</CENTER></TD></TR>
</TABLE></FORM>
</BODY></HTML>
|
You can see the results below:
<%@ LANGUAGE = VBScript %> |
The server will have one default scripting language. This may or may not be VBScript. This line makes sure that the scripting language is VBScript. Blocks of ASP code begin with <% and end with %>.
<% Option Explicit %> |
This line of ASP code forces the programmer to explicitly declare each and every variable.
<% Response.Expires = 0 %> |
This line of ASP reloads the most recent database data each time the page is refreshed.
<!-- #include virtual = "../../Common/adovbs.inc" --> |
Variables, such as AdOpenDynamic and AdLockOptimistic are defined in the adovbs.inc file. I keep a copy of this file in folder C:\Inetpub\WWWRoot\Common.
<% '======================================================================== '======================================================================== '==== EditSecurityUserByNoConfirmation.asp ==== '======================================================================== '==== ==== '==== Purpose : Transfer to page Error.asp and report error if ==== '==== user does not enter the Sought Name. Transfer ==== '==== to page Error.asp and report error if the ==== '==== search comes up empty. Update the corrected ==== '==== record. ==== '==== ==== '==== Written By : Dr. Thomas E. Hicks Date: 6/1/2003 ==== '======================================================================== '======================================================================== '------------------------------------------------------------------------ ' Declarations '------------------------------------------------------------------------ Dim Conn, UserSQL, UserRecordSet, ConnString Dim NewName, NewNo, NewPassword |
The documentation block provides a brief statement of purpose. Immediately following the documentation block are declarations for all of the variables used on this page.
'------------------------------------------------------------------------
' Initializations
'------------------------------------------------------------------------
NewName = Request("NewName")
NewNo = Request("NewNo")
NewPassword = Request("NewPassword")
|
Counter is initialized to 1. It shall be used to number the rows/records in the table.
NewName, NewNo, and NewPassword shall be passed to the confirmation page. You will not see them in the URL with the Post option.
'------------------------------------------------------------------------
' Transfer To Page Error If User Does Not Enter A Name
'------------------------------------------------------------------------
If ( Len(NewName) = 0 ) Then
Session("Error") = "You Have Not Entered Data In Field Name"
Response.Redirect "Error.asp"
End If
|
If the user fails to enter a NewName, then the length will be zero. A successful add requires the NewNameto be greater than zero. In the event that the user leaves the NewName blank, a session variable, describing the error, is created and the generic Error.asp is loaded to display the error.
'------------------------------------------------------------------------
' Transfer To Page Error If User Does Not Enter A No
'------------------------------------------------------------------------
If ( Len(NewNo) = 0 ) Then
Session("Error") = "You Have Not Entered Data In Field No"
Response.Redirect "Error.asp"
End If
|
If the user fails to enter a NewNo, then the length will be zero. A successful add requires the NewNobe greater than zero. In the event that the user leaves the NewNo blank, a session variable, describing the error, is created and the generic Error.asp is loaded to display the error.
'------------------------------------------------------------------------
' Transfer To Page Error If User Does Not Enter A Password
'------------------------------------------------------------------------
If ( Len(NewPassword) = 0 ) Then
Session("Error") = "You Have Not Entered Data In Field Password"
Response.Redirect "Error.asp"
End If
|
If the user fails to enter a NewPassword, then the length will be zero. A successful add requires the NewNameto be greater than zero. In the event that the user leaves the NewPassword blank, a session variable, describing the error, is created and the generic Error.asp is loaded to display the error.
'------------------------------------------------------------------------
' Transfer To Page Error If User Does Not Enter A Numeric Value
'------------------------------------------------------------------------
If ( NOT IsNumeric(NewNo) ) Then
Session("Error") = "You Have Not Entered a Numerical Value For No"
Response.Redirect "Error.asp"
End If
|
If the user must enter a numerical value for NewNo. In the event that the user a non-numerical value, a session variable, describing the error, is created and the generic Error.asp is loaded to display the error.
'------------------------------------------------------------------------
' Create The Console Object & MapPath Connection To The Server
'------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")
ConnString = "DBQ=" & Server.MapPath("../../../Security.mdb")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString
|
The block of code above connects this page to the Security database. A connection object is created. Variable ConnString contains part of the connection arguments. The Conn.Open opens the Security database and associates it with the connection object.
'------------------------------------------------------------------------
' Update The User Information
'------------------------------------------------------------------------
UserSQL = " UPDATE Users SET " &_
" Name = '" & NewName & "', " &_
" No = " & NewNo & ", " &_
" Password = '" & NewPassword & "' " &_
" Where Users.No = " & Session("SoughtNo")
Set UserRecordSet = Server.CreateObject ("ADODB.RecordSet")
UserRecordSet.Open UserSQL, Conn, AdOpenDynamic, AdLockOptimistic
|
The SQL query updates the record in the Users database table.
<html><head> <title>Dr. Thomas E. Hicks - EditSecurityUserByNoConfirmation.asp </title> </head> |
The HTML places Dr. Thomas E. Hicks - EditSecurityUserByNoConfirmation.asp in the browser title bar.
<BODY TEXT = "#000000" BGCOLOR = "#000000" VLINK ="#000000" ALINK ="#000000" BACKGROUND ="Paper.jpg"> |
This HTML code above defines the page background, the default text color, the default background color, and the default link colors.
<CENTER> <font face="Arial" size="4">EditSecurityUserByNoConfirmation.asp<br> Written By<br> Dr. Thomas E. Hicks</font></p> </CENTER> <hr> |
The HTML code above creates the following commercial at the top of the page.
EditSecurityUserByNoConfirmation.asp
Written By
Dr. Thomas E. Hicks
<hr> <p><font color="#FF0000" face="Arial"> <% = NewName %> has been updated in the database! </font></p> |
Although it is not essential, we are going to display the name of the user added to the database.
</BODY></HTML> |
The HTML code above simply ends the body and the document.
The complete code may be found below. A working model may be found at
<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<% Response.Expires = 0 %>
<!-- #include virtual = "../../Common/adovbs.inc" -->
<%
'========================================================================
'========================================================================
'==== EditSecurityUserByNoConfirmation.asp ====
'========================================================================
'==== ====
'==== Purpose : Transfer to page Error.asp and report error if ====
'==== user does not enter the Sought Name. Transfer ====
'==== to page Error.asp and report error if the ====
'==== search comes up empty. Update the corrected ====
'==== record. ====
'==== ====
'==== Written By : Dr. Thomas E. Hicks Date: 6/1/2003 ====
'========================================================================
'========================================================================
'------------------------------------------------------------------------
' Declarations
'------------------------------------------------------------------------
Dim Conn, UserSQL, UserRecordSet, ConnString
Dim NewName, NewNo, NewPassword
'------------------------------------------------------------------------
' Initializations
'------------------------------------------------------------------------
NewName = Request("NewName")
NewNo = Request("NewNo")
NewPassword = Request("NewPassword")
'------------------------------------------------------------------------
' Transfer To Page Error If User Does Not Enter A Name
'------------------------------------------------------------------------
If ( Len(NewName) = 0 ) Then
Session("Error") = "You Have Not Entered Data In Field Name"
Response.Redirect "Error.asp"
End If
'------------------------------------------------------------------------
' Transfer To Page Error If User Does Not Enter A No
'------------------------------------------------------------------------
If ( Len(NewNo) = 0 ) Then
Session("Error") = "You Have Not Entered Data In Field No"
Response.Redirect "Error.asp"
End If
'------------------------------------------------------------------------
' Transfer To Page Error If User Does Not Enter A Password
'------------------------------------------------------------------------
If ( Len(NewPassword) = 0 ) Then
Session("Error") = "You Have Not Entered Data In Field Password"
Response.Redirect "Error.asp"
End If
'------------------------------------------------------------------------
' Transfer To Page Error If User Does Not Enter A Numeric Value
'------------------------------------------------------------------------
If ( NOT IsNumeric(NewNo) ) Then
Session("Error") = "You Have Not Entered a Numerical Value For No"
Response.Redirect "Error.asp"
End If
'------------------------------------------------------------------------
' Create The Console Object & MapPath Connection To The Server
'------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")
ConnString = "DBQ=" & Server.MapPath("../../../Security.mdb")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString
'------------------------------------------------------------------------
' Update The User Information
'------------------------------------------------------------------------
UserSQL = " UPDATE Users SET " &_
" Name = '" & NewName & "', " &_
" No = " & NewNo & ", " &_
" Password = '" & NewPassword & "' " &_
" Where Users.No = " & Session("SoughtNo")
Set UserRecordSet = Server.CreateObject ("ADODB.RecordSet")
UserRecordSet.Open UserSQL, Conn, AdOpenDynamic, AdLockOptimistic
%>
<html><head>
<title>Dr. Thomas E. Hicks - EditSecurityUserByNoConfirmation.asp </title>
</head>
<BODY TEXT = "#000000"
BGCOLOR = "#000000"
VLINK ="#000000"
ALINK ="#000000"
BACKGROUND ="Paper.jpg">
<CENTER>
<font face="Arial" size="4">EditSecurityUserByNoConfirmation.asp<br>
Written By<br>
Dr. Thomas E. Hicks</font></p>
</CENTER>
<hr>
<p><font color="#FF0000" face="Arial">
<% = NewName %> has been updated in the database! </font></p>
</BODY></HTML>
|
You can see the results below:
The complete code may be found below.
<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<% Response.Expires = 0 %>
<!-- #include virtual = "../Common/adovbs.inc" --> <%
'=========================================================================
'=========================================================================
'==== ====
'==== Written By : Dr. Thomas E. Hicks ====
'=========================================================================
'=========================================================================
%>
<HTML><BODY BACKGROUND = "Paper.jpg">
<HR>
<CENTER><p align="center"><b><font size=+0 color="#660033">
<% = Session("Error") %><br></font></b>
<FORM METHODb=b"POST">
<INPUT TYPE = "BUTTON"
VALUE = " Take Me Back To Correct The Problem "
OnClick = "history.go( -1 ); return true;">
</FORM>
<HR></CENTER></BODY></HTML>
|