ASP Production data Entry and Management

chunkyscreechΔιακομιστές

4 Δεκ 2013 (πριν από 3 χρόνια και 10 μήνες)

121 εμφανίσεις


ASP Production data Entry and Management


EET 499 Final Project


By Ian Gass




INTRODUCTION

On November 11, I received approval to begin my project, “ASP Production Data Entry
and Management”. At that point I began researching ASP and ADO, which is what

ASP
uses to communicate to databases.


ASP is short for
A
ctive
S
erver
P
age. It is a

Web server technology from Microsoft that
allows for the creation of dynamic, interactive sessions with the user. An ASP is a Web
page that contains HTML and embedded p
rogramming code written in VBScript or
Jscript. ASPs are Microsoft's alternative to CGI scripts and Java Server Pages (JSPs),
which allow Web pages to interact with databases and other programs.


My goal for this project was to have a working site that wo
uld be secure, where users
could enter data, and users could manage and change data and the website administrator
could manage user permissions.


PROCEDURE

I first began my project by doing some planning. By examining my goals for the project
further I de
termined the pages that I would need for my website. I determined that I
would need a page for logging on and off of the site, pages for data entry, editing and
deleting production records and a page to search for which records a user would want to
change
, and pages for adding, editing, and deleting users (Figure 1).

Root
index.htm
main.htm
navbar.htm
DailyLogAdmin.asp
DailyLogEntry.asp
EditRecord.asp
DeleteRecord.asp
DataEntrySuccessful.asp
login.asp
logoff.asp
results.asp
CommonFunctions.inc
UserAdministration
AddUser.asp
EditUser.asp
DeleteUser.asp


Figure 1

Once I had determined what pages I needed I then determined a little bit about my
database design, what tables I would need and what fields they should contain. I
determined that
I would need two tables, one for the production data (Figure 2) and the
other for the user information (Figure 3).


Figure 2



Figure 3

In order to run my website I needed a computer running Microsoft Windows 2000 with

IIS and a server running Microsoft SQL Server 2000. I then proceeded to install both of
these on the same system. Installing Windows 2000 Server was fairly routine and so was
setting up IIS. The only thing I needed to do for IIS was make sure that anon
ymous users
had execute permissions for my ASP’s. Setting up and installing SQL Server 200 was a
little more involved. The one portion that confused me was whether to use Windows
authentication or to use SQL Server authentication. I decided to use Wind
ows
authentication so that I wouldn’t have to log in twice. I then proceeded to create a new
database and named it Production. In the database I created my two tables, Daily_Log
and user_list as seen in Figure 2 and Figure 3.


In order to access the data
base from my ASP’s I needed to configure ODBC in Windows.
While this wasn’t required to access the SQL server from ASP it made creating the
database connections much simpler by being able to use the System DSN name as
opposed to writing out all the inform
ation in code.


First I chose Add from the menu (Figure 4).


Figure 4

Next I had to choose which database driver I wanted to use to which I chose the SQL
Server database driver (Figure 5).


Figure 5


Next I had to name my DSN connection and choose wheth
er to use Windows
authentication or SQL Server authentication. Since I had chosen Windows authentication
during the SQL server installation I chose Windows authentication in this step also
(Figure 6).


Figure 6

Next I had to change the default database.

The default database that it points to is the
“master” database but I wanted it to point to the database that I had created so I needed to
change it (Figure 7).


Figure 7


Once the connection had been created I then tested it to make sure I had set every
thing up
properly (Figure 8).


Figure 8

The next thing I needed to do before users visiting the site could access the database was
to add the default website user account to the database user list. While I didn’t realize
this at first I was able to find
it out after a few hours of troubleshooting my pages. I came
to realize that if I had used SQL Server authentication when setting up SQL Server 2000 I
probably could have passed the username and password to the SQL server when creating
the connection obje
ct in ADO. Since I was using Windows authentication it was
automatically passing the default website user account IUSR to the database. Once I
gave this account database access permission my pages began to work properly (Figure
9).


Figure 9


PROCESS

W
hen a user first accesses the site they see index.htm, which is a frames page. It contains
a navigation bar with buttons to “Login”, “Logoff” and go back to the “Home” page
(Figure 10).


Figure 10


From here the user clicks the “Login” button to go to l
ogin.asp. “login.asp” contains 4
procedures within the page itself. They are Main(), InvalidPassword(), FailedMustWait()
and LoginOK() along with some standard HTML code.


Login.asp

Main()

When the page is first accessed it runs the procedure Main().
This procedure first
checks to see if the user has a cookie on their system signifying that they have visited
the site recently, within 20 minutes. If the cookie is present and its value is “Failed”
then the procedure FailedMustWait() is called. If the c
ookie is present and its value is
“Success” then session variables are set from the values contained within the cookie
and the procedure LoginOK() is called. The session variables that are set contains
information about the user such as their name and wha
t pages they are allowed to
access. If no cookies were present on the user’s system then the procedure checks to
see if the “REQUEST_METHOD” was POST. If it evaluates false then the rest of
the code on the page is essentially ignored and the HTML login f
orm is displayed to
the user. If it evaluates true then it means that the HTML login form had been filled
in. It then opens the “user_list” table and checks to see if the username and password
supplied are valid. If they aren’t valid then the procedure
InvalidPassword() is called.
If they are valid then a cookie that expires after 20 minutes is written to the user’s
system that signifies that they have successfully logged in, along with their name and
their access rights. Session variables are also set

with the same values. Then the
procedure LoginOK() is called


InvalidPassword()

This procedure gets called from Main() if the user enters an invalid username
password combination. If this procedure gets called five times then a cookie is
written to the
user’s system that expires after 1 hour. This cookie signifies that they
have failed to login too many times by using the value “Failed” and the next time they
try to login within the hour they aren’t even displayed the log in page but are
displayed a dif
ferent one. However if the number of failed attempts is less than 5
then the user is displayed a page notifying them that they have failed to login and
provides a link for them to try again.


FailedMustWait()

This procedure is called when a user has faile
d to login 5 times or more. It displays a
page notifying them that they have failed too many times and should try again later.


LoginOK()

If the user successfully logs in then this procedure is called. It looks to see which
session variables are set for
the user and then displays them a list of links accordingly.
If they have data entry permissions then they are displayed a link for
“DailyLogEntry.asp”. If they have data management permissions then they are
displayed a link for “DailyLogAdmin.asp”. If
they have user administration
permissions then they are displayed links for “AddUser.asp”, “EditUser.asp”, and
“DeleteUser.asp”


The next page I will go over isn’t really a page but is a file that contains procedures that
are common to many pages. On most

of my pages this file is included and it contains the
following procedures: LoggedOn(), DataEntryFailed(), VerificationForm(),
WriteToDB(), DeleteFromDB(), NoSearchResults(), WriteStyles(), and CodeTable()


CommonFunctions.inc

LoggedOn()

This procedure i
s passed a session variable from the calling page. It checks to see if
the user has permissions to be accessing the page. If they have permission then the
Main() procedure of the page is called. If they don’t have permission but are logged
in then they
are displayed a page notifying them that they do not have appropriate
rights to view the page. If they are not logged in then they are re
-
directed to
login.asp.


DataEntryFailed()

This procedure is called from pages that access databases. If at any point

in the
process of making a connection, opening a Recordset, etc… an error occurs, the
control is transferred to this procedure and a page is displayed with the error number
and the error description. It receives 4 parameters. The first is a custom messa
ge that
can be displayed, the second is the URL of the page that called it, the third is the
name of the page that called it and the fourth is the URL of the main menu page.


VerificationForm()

This procedure is called by pages that write to the database.

Its gets displayed before
the data gets stored and gives users a chance to double
-
check their information. It
receives 6 parameters: PostURL, URL for the form to POST to, FormFields, an array
containing the values from the form fields, FormFieldName, and

array containing the
names of the fields from the form, DSNName, the DSN of the database, TableName,
the name of the table that data will be input to, and PageTitle, custom message that
gets displayed at the top of the page.


WriteToDB()

This procedure is

called by pages that write to the database. It opens the database and
writes the values passed to it to the correct table. It receives 3 parameters: PageURL,
URL of the page that called it, PageTitle, name of the page that called it, and
MainURL, the UR
L of the main page.


This procedure opens up the database by using the DSN name and knows which table
to open by means of a hidden field in the VerificationForm. It then adds a new record
to the Recordset and loops through the fields of the Recordset and
writes session
variables with the same name as the fields to the table. If the data entry succeeds then
the page DataEntrySuccessful.asp is displayed


DeleteFromDB()

This procedure gets called from “DeleteRecord.asp” and removes a chosen record
from a spe
cified table. It opens the specified table and then chooses the record
specified by the RecordID session variable and remove that record from the table. It
then displays a page to the user letting them know that their data was successfully
removed. It r
eceives 5 parameters: DSNName, DSN name of the database,
TableName, the name of the table to open, PageURL, URL of the page that called it,
PageTitel, title of the page that called it, and MainURL, the URL of the main page.


NoSearchResults()

This procedur
e is called from “Results.asp” and is used to display a message to the
user if no records are found matching their specified criteria. It receives only one
parameter PageURL which is the URL of the page that called it.


WriteStyles()

This procedure simply

writes some common styles that are used on many of the
ASP’s in the head of pages. Receives no parameters.




CodeTable()

Writes out a table that is common to the user administration pages. Receives no
parameters.


The next page I will go over is the pa
ge that is used to enter production data into the
database. This page is linked by login.asp when the user logs into the site.

DailyLogEntry.asp

When this page is first loaded it calls the procedure LoggedOn() and passes to it the
session variable “Poppet
DateEntry”. The Main() procedure is then called from
LoggedOn() but doesn’t execute unless the form data has been posted. If the user is
logged on they are then displayed an HTML form for entering production data. This
form then posts the data back to i
tself at which point eh Main() procedure executes. In
it there are 3 arrays that are declared, FormFields, which holds the values from the
form, FormFieldNames, which holds the names of the form fields, and finally
SessionVars, which holds the names of th
e session variables that will be shortly
created. The session variables must have the same name as the fields into which they
will be entering data into otherwise the procedures won’t operate properly and the data
won’t be entered. Once the arrays are in
itialized a loop executes that creates session
variables with the names assigned in the array SessionVars and assigns the session
variable the values of the variables in the FormFields array. All 3 arrays are then
passed to the VerificationForm procedure.

If the “Submit” button is pressed on the
verification for then the procedure WriteToDB is called which then stores the values in
the table.


The next page I will go over is the page that is used to manage production data. This
page is linked by login.as
p when the user logs into the site.

DailyLogAdmin.asp

This page first start by calling LoggedOn to check if the user has access rights to the
page. If they do then they are then displayed a HTML form that they can use to search
for fields. This form send
s its data through the URL to Results.asp


Results.asp

This page first call LoggedON to make sure the user can access it. It then gets the
variables from the URL. Before it passes the variables it first checks to see if the
variables are empty or not and

if they are it then sets a default value to the variable. It
then passes these parameters to a stored procedure in the “Production” database named
“DailyLogQuery”. This stored procedure then runs a select query on the table and
return the results a Reco
rdset. The user is then displayed the results based on the
criteria they selected. Next to each record is a link to edit the record or delete the
record. When the user clicks one of these links a variable containing the RecordID
number of the record is
passed to the corresponding page through the URL.


EditRecord.asp

This page is accessed via the link provided by Results.asp. It first calls LoggedOn() to
check the user access rights. It then checks to see if the button cmdEdit has a value and
if it doe
sn’t then it runs the procedure EditRecordForm() located within the page. This
procedure extracts the RecordID from the URL then runs a select query on the
Daily_Log table and displays a form to the user. The form contains text boxes filled in
with the c
urrent values of the record they selected on the Results.asp page. The user
can then change the values and submit their changes. The form posts back to itself at
which point the button cmdEdit now has a value of “Submit”. The Main() procedure
then calls

EditRecord() from the page, which then opens the table, gets the record,
applies the changes, and closes the record. This procedure then displays a notification
page upon successful completion of the task.


DeleteRecord.asp

This page is accessed via a li
nk from Results.asp. Upon access it first calls the
procedure LoggedOn() to check user access and then runs the Main() procedure. This
procedure check to see if the button cmdDelete has a value. If it doesn’t then the
procedure DeleteRecordForm() locate
d within the page is run. This procedure extracts
the RecordID from the URL then runs a select query on the Daily_Log table and
displays the record in tablular format to the user, letting them double check to make
sure it’s the one they wish to delete. I
f the user then still wishes to delete the record
they can press the cmdDelete button located on the page. The page then POSTs back to
itself and the procedure DeleteFromDB() located in CommonFunction.inc is called.
The DeleteFromDB() procedure then dele
tes the record from the table and displays a
notification page to the user signifying that the data has been removed.


AddUser.asp

This page is accessed via a link from the main login page. When accessed it first calls
LoggedOn() to make sure the user has

appropriate rights. If they do then the Main()
procedure is run. It checks to see if the button cmdAdd has a value of “Submit”. If it
doesn’t then a HTML form is displayed where the user can enter data for a new user.
This form is where the button cmd
Add is located. When submitted the form posts the
data back to itself at which point the button cmdAdd has a value of “Submit”. The
Main() procedure then calls the procedure AddRecord() located within the page. This
procedure first check to see whether
the username supplied is already in use. If it is
then the user is asked to try a different one. The procedure then checks to see if the
first and last names already exist in the database, if they do then the user is displayed a
page notifying them that
the person already has a user account set up. If none of the
other conditions are true and the username is original and the first and last name do not
already exist then the new user information is added to the table and a notification page
is displayed l
etting them know that the user account was successfully created.


EditUser.asp

This page is accessed via a link from the main login page. When accessed it first calls
LoggedOn() to make sure the user has appropriate rights. If they do then the Main()
pro
cedure is run. The Main() procedure checks to see if one of two buttons has been
activated. These two buttons are created in other procedure so by them not having a
value it is understood that their corresponding procedures haven’t been run yet and
need
to be. If neither button has a value then the procedure continues to execute and
displays a page listing all users currently in the database. The user then selects which
user they wish to edit and press cmdSubmit. This button then posts the data back to

the
page at which point Main() calls a different procedure, this time EditRecordForm().
This procedure displays in a form the current values associated with the user. The user
can then change these and post the form to itself again. At this point cmdEd
it now has
a value and Main() calls the procedure EditRecord(). This procedure writes the
changes to the table and displays a notification page to the user that the information has
been updated.


DeleteUser.asp

This page is accessed via a link from the ma
in login page. When accessed it first calls
LoggedOn() to make sure the user has appropriate rights. If they do then the Main()
procedure is run. The Main() procedure checks to see if one of two buttons has been
activated. These two buttons are created

in other procedure so by them not having a
value it is understood that their corresponding procedures haven’t been run yet and
need to be. If neither button has a value then the procedure continues to execute and
displays a page listing all users current
ly in the database. The user then selects which
user they wish to delete and press cmdSubmit. This button then posts the data back to
the page at which point Main() calls a different procedure, this time
DeleteRecordForm(). This procedure shows the user

the information that they are
about to delete and makes them double check their decision. When the user presses the
cmdDelete button the page posts back to itself once again and Main() call
DeleteFromDB() located in CommonFunctions.inc


CONCLUSION

This p
roject was successful in that my goals were met. The site is secured and no one
can access it unless they login. Qualified users can enter production data into the
database. Qualified users can manage data in the production database, and the website
adm
inistrator can add, delete, and change user permissions from the website.


This project could be very useful in other situations also. With code modifications the
site can be used with virtually any database to read and write to tables.


REFERENCES

Deitel
, Harvey, Deitel, Paul, Nieto, Tem.
Internet and World Wide Web: How to
Program.

Upper Saddle River, NJ: Prentice Hall, 2001


Hoffman, James.
Introduction to Structured Query Language.

2001

< http://w3.one.net/~jhoffman/sqltut.htm> (10 Sept. 2001).


Rio
rdan, Rebecca.
Microsoft SQL Server 2000 Programming
. Redmond, WA:
Microsoft Press, 2001.


Sussman, David.
ADO 2.6 Programmer’s Reference
. Chicago, IL: Wrox, 2000.


Weissinger, A. Keyton.
ASP In A Nutshell: A Desktop Quick Reference.
Sebastopol,
CA:
O’Reilly, 2000.