ASP Production Data Entry and Management
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 Active Server Page. 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 programming 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 would 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 determined 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).
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 anonymous 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 Windows 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 database 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 information 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 whether 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 everything 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 object 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
When 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 login.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 cookie 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 what 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 form 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 different 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 failed 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 is 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 message 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 URL 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 specified 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 receives 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 procedure 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 page 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 “PoppetDateEntry”. 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 itself 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 the 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 initialized 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.asp 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 sends 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 Recordset. 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.