Search results for 'microsoft access Walburg'

Programming with Microsoft VB 2017 Diane Zak Chapter 12 Adalene Solution

Open the Adalene Solution.sln file contained in the VB2017\Chap12\Adalene Solution folder. The application is already connected to the Adalene.mdf file, and the AdaleneDataSet has already been created. Start the application to view the records in the dataset and then stop the application. The Adalene Fashions application should allow the user to display all of the information in the dataset, only the information for company-owned stores, or only the information for franchisees. Create the appropriate queries and then use them to code the btnDisplay_Click procedure. Save the solution and then start and test the application.

Programming with Microsoft VB 2017 Diane Zak Chapter 11 Utilities Solution

Open the Utilities Solution.sln file contained in the VB2017\Chap11\Utilities Solution-DataGrid folder.
a. Create a SQL Server database named Utilities.mdf.
b. Add the Bills table definition shown in Figure 11-64 to the database. The Month field's (Is Identity), Identity Increment, and Identity Seed properties are set to True, 1, and 1, respectively. (Recall that you need to expand the Identity Specification property to access these properties.)
c. After defining the table, click the Update button and then click the Update Database button.
d. Open the Data Sources window and start the Data Source Configuration Wizard. Connect the Utilities.mdf file to the application. Include the entire Bills table in the dataset.
e. Set the Utilities.mdf file's Copy to Output Directory property to "Copy if newer".
f. Drag the Bills table to the form. Set the DataGridView control's AutoSizeColumnsMode property to Fill.
g. Open the DataGridView control's task list and click Dock in Parent Container. Click Edit Columns. Change the Month column's AutoSizeMode property to ColumnHeader.
h. Click Electricity in the Edit Columns dialog box, click DefaultCellStyle, click the ... (ellipsis) button, click Format, click the ... (ellipsis) button, click Numeric, and then click the OK button. The Format box now shows N2. Change the Alignment property to MiddleRight and then click the OK button to close the CellStyle Builder dialog box.
i. Now, format the Water and Gas columns using the Numeric setting with two decimal places. Also, align the values in both columns using the MiddleRight setting. When you are finished setting the properties, close the Edit Columns dialog box.
j. Change the form's Size property to 330, 200.
k. Open the Code Editor window and enter an appropriate Try...Catch statement.
l. Save the solution and then start the application. Enter the three records shown in Figure 11-64. (Recall that the Month field is an auto-numbered field. The numbers 1, 2, and 3 will appear when you click the Save Data button.)
m. Stop the application and then start it again to verify that the three records were saved.

Programming with Microsoft VB 2017 Diane Zak Chapter 11 Total Cookie Sales Solution

Open the Total Cookie Sales Solution.sln file contained in the VB2017\Chap11\Total Cookie Sales Solution folder
a. Figure 11-63 shows the Sales table contained in the CookieSales.mdf file. The table contains the numbers of boxes of cookies sold in each of six weeks. The database is already connected to the application and the CookieSalesDataSet has already been created.

b. Open the Data Sources window and then drag the Sales table to the DataGridView control. Change the control's AutoSizeColumnsMode to Fill. Use the control's task list to disable adding, editing, and deleting records. Also, right-align the numbers in the cookie sales columns.

c. Lock the controls on the form. Start the application to verify that the six records appear in the DataGridView control. Stop the application.

d. The Calculate button should display the total sales for each cookie type. Code the btnCalc_Click procedure. (The database does not allow NULLs in any of the fields, so you do not need to check if a field contains the NULL value.)

e. Save the solution and then start and test the application.

Programming with Microsoft VB 2017 Diane Zak Chapter 12 FixIt Solution

Open the VB2017\Chap12\FixIt Solution\FixIt Solution.sln file. Start the application.
Click the Grade radio button, type the letter a in the Grade box, and then click the Display button. The application should display three records in the DataGridView control. It should also display the number 3 in the lblCount control. Notice that the application is not working properly. Fix the application.

Programming with Microsoft VB 2017 Diane Zak Chapter 10 Fence Solution

In this exercise, you create an application that can be used to calculate the cost of installing a fence around a rectangular area. Create a Windows Forms application. Use the following names for the project and solution, respectively: Fence Project and Fence Solution. Save the application in the VB2017\Chap10 folder:

a. Use Windows to copy the Rectangular.vb file from the VB2017\Chap10 folder to the Fence Project folder. Then, use the Project menu to add the file to the project.

b. Modify the Rectangular class to use the Double (rather than Integer) variables.

c. Add a method named GetPerimeter to the Rectangle class. The method should calculate and return the perimeter of a rectangle. To calculate the perimeter, the method will need to add together the length and width measurements and then multiply the sum by 2.

d. Create the interface shown in Figure 10-41 and then code the application. Save the solution and then start and test the application.

PRG/421 Week 4 Java 4.6 LAB: Zip code and population (generic types)

Define a class StatePair with two generic types (Type1 and type2), a constructor mutators, accessors, and a printinfo() method. Three ArrayLists have been pre-filled with StatePair data in main():
• ArrayList<StatePairInteger, String>> zipCodeState: Contains ZIP code/state abbreviation pairs
• ArrayList<StatePair<string, String>> abbrevstate: Contains state abbreviation/state name pairs
• ArrayList<StatePair<string. Integer>> state Population Contains state name/population pairs

Complete main() to use an input ZIP code to retrieve the correct state abbreviation from the ArrayList ZipCodeState. Then use the state abbreviation to retrieve the state name from the ArrayList abbrevState. Lastly, use the state name to retrieve the correct state name/population pair from the ArrayList state Population and output the pair.

Ex If the input is:
21044

the output is:
Maryland: 6079602

CIS355A Week 6 Lab - Database Connectivity Student Management System

OBJECTIVES
• Programmatic access to a MySQL database to add and display records

PROBLEM: Student Management System
A teacher needs the ability to store and retrieve student data. This includes
• student name;
• three test scores;
• average; and
• letter grade.

FUNCTIONAL REQUIREMENTS
You can code the GUI by hand or use NetBeans GUI builder interface.
Create a GUI which allows for input and display of student data.
It should include buttons to save a record, display all records.

Create a database and table to store student name and three test scores. (Note that average and grade are calculated by app.)

Student class
Create a Student class to manage the student data. It should have private instance variables of
• student name; and
• three test scores.
The class must have the following methods.
• A default and parameterized constructor
• Sets/gets for all instance variables
• A get method to calculate and return the average
• A get method to calculate and return the letter grade
• toString to display the name of the student

StudentDB class
Create a StudentDB class that is used to create a connection and interface with the database.

This class should have two methods.
• getAll - reads data from database, returns data in an arraylist of student objects
• add - writes a record to the database

GUI class
Insert button will take the info from the GUI (student name and three test scores) and insert a record into the table.  Input should be cleared from the textboxes.
Display button will read the data from the database and creates a report in Console window, sample format below.
Name Test1 Test2 Test3 Avg Grade
Bruce Wayne  90  95  98  94.3  A
Clark Kent  65  70  90  75.0  C

RUBRIC
Student class
• Has all required functionality 10
GUI class
• Student record can be saved
• All student data can be displayed 15
StudentDB class
• add method inserts a record into db.
• get method reads all records and returns in arraylist. 15
Code style 5
Lab Report 10
TOTAL 55

CODE STYLE REQUIREMENTS
• Include meaningful comments throughout your code.
• Use meaningful names for variables.
• Code must be properly indented.
• Include a comment header at beginning of each file, example below.
/****************************************************
Program Name: ProgramName.java
Programmer's Name: Student Name
Program Description: Describe here what this program will do
***********************************************************/

DELIVERABLES
Submit as a SINGLE zip folder
• all java files; and
• the Lab report.

Follow assignment specification regarding class/method names.
Note that your Java file name must match class name (DO NOT rename).

WEB460 Lab 7 of 7: Model-View-Controller Application

Scenario/Summary
This lab will introduce you to the ASP.NET MVC application architecture. You will create a controller and views for the controller. In this lab, we will not explore models but with the foundation you gain here, you should be able to follow tutorials you discover online.

TABLE OF CONTENTS
Lab Steps
STEP A: Create a Model-View-Controller Application
MVC applications are not created as Visual Studio Web Sites. Instead we create a Project, similar to what we do for a desktop application. Follow the directions in this week's lecture to create an ASP.NET MVC web application. The application should be named web460_wk7_ilab.
The name is very important because this project uses namespaces. If the namespace in your code files is not web460_wk7_ilab, you can either:
delete the project and recreate a new with the correct name (web460_wk7_iLab), or
adjust the code in the Lab to match the namespace of your application.
When you build and run the default web application, you should see a website similar to the image below that has a responsive web layout. As you adjust the window size, elements move to accommodate the narrower screen size, such as for a mobile device.

Because URLs are very important in determining the classes and actions called in an MVC application, you should note the URL and port number IIS Express is using to run the application. localhost refers to your own computer, and the number following is the port. In the image below, the port is 51071.
In the examples shown in the lab steps below, replace the port number (51071) with the port number on your computer.

STEP B: Adding a Controller
In this step, we will build a controller that returns HTML directly to the browser. This will help us begin with a simple example before adding more features. In the next step we will have the controller call a View( ) to create a full web page.
1. Right-click on the Controllers folder and select Add => Controller... from the menu.
2. Name the controller HelloEarthController and select the Empty MVC controller template.
3. You should see HelloEarthController.cs in the Controllers folder in the Solution Explorer pane. Double-click the file to open it and examine the code. The index action method is
the default action method for each controller. Replace the HelloEarthController Index( ) action method with the following code:
Index Action Method in HelloEarthController.cs
// Add your comments here
public string Index()
{
return "This is the <strong>default action</strong> for this web site ...!";
}
4. Run the application and append HelloEarth to the base URL:
http://localhost:51071/HelloEarth
You should see the following:
5. We just called the HelloEarth Controller. The URL routing logic in ASP.NET MVC applications is a format like this to determine what code to invoke:
/[Controller]/[ActionName]/[Parameters]
Add the following Welcome action method to the HelloEarth Controller:
Welcome Action Method in HelloEarthController.cs
// Add your comments here
public string Welcome()
{
return "<h2>Welcome to Earth visitor!</h2>";
}
6. Run the application and append HelloEarth/Welcome to the base URL:
http://localhost:51071/HelloEarth/Welcome
You should see the following:
7. Now, let's pass information in arguments to the Welcome method. Change the Welcome method to the following:
Revised Welcome Action Method in HelloEarthController.cs
// Add your comments here
public string Welcome(string visitor, int numTimes = 1)
{
return HttpUtility.HtmlEncode("Hello " + visitor + "! Your number is " + numTimes);
}
8. Run the application and again append HelloEarth/Welcome to the base URL:
http://localhost:51071/HelloEarth/Welcome
You should see the default values for visitor and numTimes:
9. Run the application and again append query string parameters to the base URL:
http://localhost:51071/HelloEarth/Welcome?visitor=Jose&numTimes=5
MVC will bind the query string values to the action method parameters. You should

STEP C: Creating a View
In this step we'll create a View that displays default information when the site visitor goes to /HelloEarth.
1. Return to the HelloEarth Controller action method and return it to the original version (don't forget to change the return type back to ActionResult):
Original Index Action Method in EarthController.cs
// Add your comments here
public ActionResult Index()
{
return View();
}
2. To add a View for the Index method, right-click inside the Index method and select Add View...
3. In the Add View dialog, the view name should be Index and the view engine should be ASPX (C#). Also be sure the Use layout or master page is checked and Site.Master is selected as the master page.
4. You should now see a HelloEarth folder containing Index.aspx under the Views folder.
5. Double-click HelloEarth/Index.aspx to examine the code and you should see four content areas from the master page referenced. In the Content1 container, enter the following (this is the page title):
Hello Earth Index
6. In the Content2 container, enter the following (this is the main content heading):
<h2>Welcome to the Earth Index!</h2>
7. In the Content3 container, enter the following:
This web application is your introduction Earth culture.
8. Run the application and append HelloEarth to the base URL:
http://localhost:51071/HelloEarth
You should see the following:

STEP D: Passing To the View From the Controller
MVC uses an object called the ViewBag to communicate between the controller and views. The ViewBag is a dynamic object that can have data fields added to it on the fly (as the program is running). We will use the ViewBag to send the Welcome View data to display on the web page.
1. Return to the HelloEarth Controller and change the Welcome action method to look like the code below (don't forget to change the return type back to ActionResult). We are setting the VisitorName and NumTimes data fields of the ViewBag object. Those data fields are not part of the ViewBag object but will be added when these statements are executed. In our example, we simply pass the parameters to the View, but we could also pass computed values or values retrieved from a Model.
Second Revision of Welcome Action Method in HelloEarthController.cs
// Add your comments here
public ActionResult Welcome(string visitor, int numTimes = 1)
{
ViewBag.VisitorName = visitor;
ViewBag.NumTimes = numTimes;
return View();
}
2. Right-click on the Welcome action method and select Add View... just as we did for the Index action method above. The %> name should be Welcome, use the ASPX (C#) view engine and the master page Site.Master. After clicking OK, you should see the file Welcome.aspx inside the HelloEarth folder under Views.
3. To access the ViewBag data fields in the View, we enclose it in <%: and %> as an ASPX directive. Open Welcome.aspx for editing. Inside the Content2 container, place the following:
<h2>Welcome to Earth <%: ViewBag.VisitorName %>!</h2>
4. Let's use ASPX Page directives along with the ViewBag data to repeatedly print our the visitor's name. Add the code below to the Content3 container:
Add to Content3 Content Container
<ul>
<% for (int i = 0; i < ViewBag.NumTimes; i++ )
{ %>
<li><%: ViewBag.VisitorName%></li>
<% } %>
</ul>
5. Run the application and again append query string parameters to the base URL:
http://localhost:51071/HelloEarth/Welcome?visitor=Jose&numTimes=5
MVC will bind the query string values to the action method parameters. The Controller will then pass those to the View. You should see:

STEP E: Finalize the Lab
1. Save your work!
2. Test it! Make changes as appropriate until it works.
3. Remember to add comments for each step being performed.
4. Please zip and submit the entire web project folder.

WEB460 Lab 5 of 7: Refactoring and Security

Scenario/Summary
In this week's Lab, we refactor our web application and add a secure login feature.

Part I: Refactoring
As applications grow and change with added features and fixed bugs, they can become unwieldy and very difficult to maintain. When an application is difficult to maintain, even a small change to the app can result in hard-to-find bugs. The original design of the application may no longer support the features and functionality present in the current application version.
The solution to this situation is refactoring the application code to better distribute the workload among the classes in the application. Refactoring does not change the external functionality of software; it changes the internal structure by reorganizing it. After refactoring our application, it should look the same to users but execute more efficiently and be easier to maintain.
pgCheckOut.aspx.cs has become fairly large, making it difficult to maintain. The reason for this is that we have the pgCheckOut class doing much of the work that belongs in the business layer and data layer. For this week, the goals of our refactoring are as follows.
1. Correctly distributing application functionality
The presentation layer (pgCheckOut) manages user interaction through the ASP form. It makes requests of the business layer for data to populate form fields, responds to button
presses, and sends form data to the business layer for processing.
The business layer (clsBusinessLayer) validates data, catches exceptions, and corrects them if possible. This layer handles communication between our application and others (through an XML file) and retrieves data from the data layer.
The data layer (clsDataLayer) interacts with database and responds to requests formthe business layer. When we are finished with our refactoring, pgCheckOut.aspx.cs should not need any knowledge or interaction with the data layer (clsDataLayer.cs)

2.Replace repeated or duplicate code with methods to improve maintainability We want to replace code that is duplicated in several places with either a method when it is a sequence of statements, or a data field when it is a reused piece of data, such as the data directory's Server.MapPath. We already reduce duplication on our forms by using master pages. This step allows us to reduce duplication in our code.

Part II: Secure Login
Because our application manages customer information, we should provide a secure login so that only authorized users can view and manipulate customer information. Our secure login page accepts a username and password. If the same user fails to correctly enter his or her password three times, that user's account is locked and the person must contact the system admin to unlock it. If there is a total of six failed login attempts during the session, no matter the user, the application locks by hiding the login/submit button.

Here is an outline of this lab:
PART 0: Prepare Your Project
Start your project as a copy of the Week 4 Lab

PART I: STEP A: Move the GetAllCustomers Functionality to the Business Layer
Add a method to clsBusinessLayer.cs and modify a method in pgCheckOut.aspx.cs.

PART I: STEP B: Move Update and Insert Customer Functionality to the Business Layer
Add two methods to clsBusinessLayer.cs, add a new method to pgCheckOut.aspx.cs, and modify two other methods in pgCheckOut.aspx.cs.

PART I: STEP C: Move Retrieving a Single Customer's Data to the Business Layer
Add a method to clsBusinessLayer.cs and modify a method in pgCheckOut.aspx.cs.

PART II: STEP D: Data Layer Functionality: Validating and Locking Users
Add two methods to clsDataLayer.cs.

Lab Steps
PART II: STEP E: Implement the Business Layer Functionality to Verify User Credentials
Add one method to clsBusinessLayer.cs.

PART II: STEP F: Create the Login Form
Create a form, user the site master page and add controls.

PART II: STEP G: Implement Login Functionality
Validate user credentials and redirect to pgCheckOut.aspx.

PART II: STEP H: Harden Your Application (Optional)
Optionally add features to obscure passwords and prevent SQL injection.

PART II: STEP I: Finalize the Lab
Deliverables
Please zip and submit the entire web project folder.

PART 0: Prepare Your Project
To begin this week's lab, create an Empty Web Site and copy the files from the Week 4 Lab into your website folder.
Test your application. It should function the same as it did at the end of the Week 4 Lab.

PART I: STEP A: Move the GetAllCustomersFunctionality to the Business Layer
We begin with an easy change. In pgCheckOut, the method BindCustomerGridView calls the clsDataLayer method GetAllCustomers. Calling this method should be done in the businesslayer (clsBusinessLayer) because the presentation layer (pgCheckOut) should not interact with the data layer. To fix this, we need to take two actions:
Create a method SelectAllCustomers in clsBusinessLayer that calls the clsDataLayer method GetAllCustomers.
Modify BindCustomerGridView to call SelectAllCustomers in clsBusinessLayer.

1. In clsBusinessLayer, add the SelectAllCustomers method:
SelectAllCustomer Method in clsBusinessLayer.cs
public dsAccounts SelectAllCustomers(){
return myDataLayer.GetAllCustomers();
}

2. In the BindCustomerGridView method in pgCheckOut.aspx.cs, change the line where GetAllCustomers is called to be:
dsAccounts customerListing = myBusinessLayer.SelectAllCustomers();

3. Test your application. It should function as it did at the end of the Week 4 Lab.

PART I: STEP B: Move Update and Insert CustomerFunctionality to the Business Layer
The btnAdd_Click and btnUpdate_Click methods in pgCheckOut have very similar functionality. They pass data from the form to the data layer and then handle any exceptions and errors that may occur. After that, both methods clear the form, display a message to the user, and bind the GridView. There are several optimizations we can make with these:
- Move the calls to the data layer and all exception handling into the business layer(clsBusinessLayer) where they belong. The business layer should also handle any exceptions or errors that could arise, relieving the presentation layer of this responsibility.
- Have the business layer methods return a string that indicates success or failure and can be displayed to the user.
- Place the duplicate code that clears the form, binds the GridView, and sets the feedback message in a private method that can be called from the btnAdd_Click and btnUpdate_Click methods. This should make maintaining the application easier.

1.Create the method UpdateCustomer in clsBusinessLayer.cs:
UpdateCustomer Method in clsBusinessLayer.cs
public string UpdateCustomer ( string firstName, string lastName, string street, string city, string state, string phoneNumber, int customerID)
{
// Add your comments here
string resultMessage = "Customer Updated Successfully.";
// Add your comments here
try
{
myDataLayer.UpdateCustomer(firstName,lastName,street,city,state,phoneNumber,customerID);
}
catch(Exception error)
{
resultMessage = "Error updating customer, please check form data. ";
resultMessage = resultMessage+error.Message;
}
return resultMessage;
}

2. Create the method InsertCustomer in clsBusinessLayer.cs:
InsertCustomer Method in clsBusinessLayer.cs
public string InsertCustomer ( string firstName, string lastName, string street, string city, string state, string phoneNumber)
{
// Add your comments here
string resultMessage = "Customer Added Successfully.";
// Add your comments here
try
{
// Add your comments here
myDataLayer.InsertCustomer(firstName,lastName,street,city,state,phoneNumber);
}
catch(Exception error)
{
// Add your comments here
resultMessage = "Error adding customer, please check form data.";
resultMessage = resultMessage + error.Message;
}
return resultMessage;
}

3. Create the private method updateForm in pgCheckOut.aspx.cs that clears the form, displays the message sent as a parameter, and binds the GridView:
updateForm Method in pgCheckOut.aspx.cs
private void updateForm(string results)
{
// Add your comments here
ClearInputs(Page.Controls);
// Add your comments here
Master.UserFeedBack.Text = results;
// Add your comments here
BindCustomerGridView();
}

4. Modify btnUpdate_Click in pgCheckOut.aspx.cs so that it calls UpdateCustomer in clsBusinessLayer and updateForm. The final version of the method should appear as below:
Revised btnUpdate_Click Method in pgCheckOut.aspx.cs
protected void btnUpdate_Click( object sender, EventArgs e)
{
// Add your comments here
string results = myBusinessLayer.UpdateCustomer(txtFirstName.Text,txtLastName.Text,txtStreet.Text,txtCity.Text,txtState.Text,txtPhone.Text,Convert.ToInt32(customerID.Text));
// Add your comments here
updateForm(results);
}

5. Modify btnAdd_Click in pgCheckOut.aspx.cs so that it calls InsertCustomer in clsBusinessLayer and updateForm. The final version of the method should appear as below:
Revised btnAdd_Click Method in pgCheckOut.aspx.cs
protected void btnAdd_Click (object sender,EventArgs e)
{
// Add your comments here
string results = myBusinessLayer.InsertCustomer(txtFirstName.Text,txtLastName.Text,txtStreet.Text,txtCity.Text,txtState.Text,txtPhone.Text);
// Add your comments here
updateForm(results);
}

6. Test your application. It should function the same is it did at the end of the Week 4 Lab.

PART I: STEP C: Move Retrieving a Single Customer's Data to the Business Layer
In this step, we move the functionality that accesses the data layer and performs exception handling from pgCheckOut to clsBusinessLayer as in the previous steps. Generally, fetching records from the database is safer than updating and inserting. If there is a problem, a DataSet object is still created but it contains no records. Our presentation layer can test for that and display a message to the user that "No records were found", just as our current method does.
If a record is returned from the database, empty fields in the record may contain the value DBNull, which represents a NULL value in the database. Because of this, an exception may occur when a field's value is retrieved so it can be placed in a TextBox on the form. We want to prevent any exceptions from occurring in the presentation layer, so our business layer must find a way to fix fields that have a value of DBNull before the DataSet is passed to the presentation layer for display on the form.

1. Here is the code for the FindCustomer method in clsBusinessLayer. Notice that before returning the DataSet to the presentation layer, it checks each field for DBNull. If a field has that value, it is set to the empty string, which is safe for a form to display.
FindCustomer Method in clsBusinessLayer.cs
public dsAccounts FindCustomer (string LastName)
{
// Add your comments here
dsAccounts dsFoundCustomer = myDataLayer.FindCustomer(LastName);
// Add your comments here
if(dsFoundCustomer.tblCustomers.Rows.Count>0)
{
// Add your comments here
System.Data.DataRow customerRecord = dsFoundCustomer.tblCustomers.Rows[0];
if( customerRecord["FirstName"]==DBNull.Value)
customerRecord["FirstName"]=string.Empty;
if(customerRecord["LastName"]==DBNull.Value)
customerRecord["LastName"]=string.Empty;
if(customerRecord["Street"]==DBNull.Value)
customerRecord["Street"]=string.Empty;
if(customerRecord["City"]==DBNull.Value)
customerRecord["City"]=string.Empty;
if(customerRecord["State"]==DBNull.Value)
customerRecord["State"]=string.Empty;
if(customerRecord["PhoneNumber"]==DBNull.Value)
customerRecord["PhoneNumber"]=string.Empty;
}
return dsFoundCustomer;
}

2. Because the business layer handles all the problems, our presentation layer can simply take the work of the business layer and display it for the user. Here is the final version of the modified btnFindLastName_Click method in pgCheckOut.aspx.cs:
Revised btnFindLastName_Click Method in pgCheckOut.aspx.cs
protected void btnFindLastName_Click (object sender, EventArgs e)
{
// Add your comments here
dsAccounts dsFindLastName = myBusinessLayer.FindCustomer(txtLastName.Text);
// Add your comments here
if(dsFindLastName.tblCustomers.Rows.Count>0)
{
// Add your comments here
txtFirstName.Text=dsFindLastName.tblCustomers[0].FirstName;
txtLastName.Text=dsFindLastName.tblCustomers[0].LastName;
txtStreet.Text=dsFindLastName.tblCustomers[0].Street;
txtCity.Text=dsFindLastName.tblCustomers[0].City;
txtState.Text=dsFindLastName.tblCustomers[0].State;
txtPhone.Text=dsFindLastName.tblCustomers[0].PhoneNumber;
customerID.Text=dsFindLastName.tblCustomers[0].CustomerID.ToString();
Master.UserFeedBack.Text="Record Found";
}
else
{
// Add your comments here
Master.UserFeedBack.Text="No records were found!";
}
}

3. Test your application. It should function the same is it did at the end of the Week 4 Lab. You should also scan through your code to see if there are any other optimizers or clean-ups you can make, such as removing statement setting tempPath because we no longer need it.
Refactored Code
At this point, even though your application has no new features compared to the Week 4 Lab, it is much easier to maintain and modify.
As you scroll through pgCheckOut.aspx.cs, you'll see the class has been greatly simplified and the code performs two main tasks in keeping with the rules of the presentation layer:
Passing data from the form to the business layer for processing
Receiving data from the business layer to display on the form
The data layer code in clsDataLayer.cs is only for interacting with the database. It doesn't validate or display data.
The business layer, clsBusinessLayer.cs ,is the workhorse layer. It communicates data requests from the presentation layer to the data layer, handles errors, and passes clean data back to the presentation layer. It also handles communication with outside applications through XML files. In the next part of the lab, we'll see how we can apply business rules
in the business layer also.

PART II: STEP D: Data Layer Functionality: Validating and Locking Users
As we implement our secure login, we'll begin at the lowest tier and work our way up. The Microsoft Access database Accounts.mdb has two tables. tblCustomers, which we have been working with so far, contains the site customers. The second table, tblUsers, has user account information, such as login credentials. Our site users are company employees and are not customers.

1. To validate a user's login credentials, we need to match the username and password to a username and password in the database. We can do that with a simple select that returns a record if the username and password match someone in the database. We then return a bool to indicate whether the credentials were valid.
ValidateUser Method in clsDataLayer.cls
public bool ValidateUser (string username, string passwd)
{
// Add your comments here
dbConnection.Open();
// Add your comments here
string sqlStmt = "SELECT * FROM tblUsers WHERE UserID = @id AND Pwd = @passwd AND Locked = FALSE";
// Add your comments here
OleDbCommand dbCommand = new OleDbCommand(sqlStmt,dbConnection);
// Add your comments here
dbCommand.Parameters.Add(new OleDbParameter("@id",username));
dbCommand.Parameters.Add(new OleDbParameter("@passwd",passwd));
// Add your comments here
OleDbDataReader dr = dbCommand.ExecuteReader();
//Add your comments here
Boolean isValidAccount = dr.Read();
//Add your comments here
dbConnection.Close();
returnis ValidAccount;
}

2. If the business layer has decided a user needs to be locked out of the application, the data layer can oblige this request by setting the LOCKED field for the user to TRUE. Because the SELECT in the previous method would only return a record if the LOCKED field was FALSE, setting LOCKED to TRUE prevents the user's record from ever being found, effectively locking them out.
LockUserAccount Method in clsDataLayer
public void LockUserAccount(string username)
{
// Add your comments here
dbConnection.Open();
// Add your comments here
string sqlStmt = "UPDATE tblUsers SET Locked = True WHERE UserID = @id";
// Add your comments here
OleDbCommand dbCommand=new OleDbCommand(sqlStmt,dbConnection);
// Add your comments here
dbCommand.Parameters.Add(new OleDbParameter("@id",username));
//Add your comments here
dbCommand.ExecuteNonQuery();
//Add your comments here
dbConnection.Close();
}

PART II: STEP E: Implement the Business Layer Functionality to Verify User Credentials
The business layer calls the data layer to validate login credentials, but must also implement our business rules restricting the number of login attempts. We have two rules that must be implemented:
If the same user fails to correctly enter his or her password three times, that user's account is locked and the person must contact the system admin to unlock it.
If there is a total of six failed login in attempts during the session, no matter the user, the application locks by hiding the login/submit button.
To accomplish these tasks, clsBusinessLayer needs access to the Session variable. This is only directly available on forms, so our presentation layer form will need to pass that to the business layer as an argument.
Here is the method we need to add to our business layer to validate the user's credentials and possibly lock the user's account:
CheckUserCredentials Method in clsBusinessLayer
public bool CheckUserCredentials(System.Web.SessionState.HttpSessionState currentSession, string username, string passwd)
{
// Add your comments here
currentSession["LockedSession"] = false;
// Add your comments here
int totalAttempts = Convert.ToInt32(currentSession["AttemptCount"])+1;
currentSession["AttemptCount"]=totalAttempts;
// Add your comments here
int userAttempts=Convert.ToInt32(currentSession[username])+1;
currentSession[username]=userAttempts;
// Add your comments here
if((userAttempts>3)||(totalAttempts>6))
{
currentSession["LockedSession"]=true;
myDataLayer.LockUserAccount(username);
}
return myDataLayer.ValidateUser(username,passwd);
}

PART II: STEP F: Create the Login Form
Now that the backend processing code is complete, we need to create a login form.

1. Create a new Web Form named pgLogin.aspx.
2. Edit the form to use the master page we created in Week 2, Web460Store.master.
3. In the left content area of the form, add the following fields:
Control Type ID / Name Value
Label lblUserID User ID:
TextBox txtUserID
Label lblPassword Password:
TextBox txtPassword **Should have TextMode set to Password
Button btnLogin Login
4. When you are finished, the form in design view should look similar to this image:

PART II: STEP G: Implement Login functionality
1. Last, we'll implement the code for the pgLogin form to tie together all the previous work in this lab. In the Click handler for btnLogin, we'll pass the login credentials to the business layer for verification. If the user is a valid user, we'll redirect to pgCheckOut.aspx.cs. If the account is locked, we display the appropriate message and hide the Login
button to prevent further login attempts. If the user simply entered an incorrect password or username, we'll display a message indicating the mistake.
btnLogin_Click Method in pgLogin.aspx.cs
protected void btnLogin_Click(object sender,EventArgs e)
{
// Add your comments here
// Please verify that your data directory path is correct!!!!
clsBusinessLayer myBusinessLayer = new clsBusinessLayer(Server.MapPath("~/App_Data/"));
// Add your comments here
bool isValidUser = myBusinessLayer.CheckUserCredentials(Session,txtUserID.Text,txtPassword.Text);
// Add your comments here
if(isValidUser)
{
// Add your comments here
Response.Redirect("~/pgCheckOut.aspx");
}
else if(Convert.ToBoolean(Session["LockedSession"]))
{
Master.UserFeedBack.Text="Account is disabled. Contact System Administrator";
// Hide login button :-)
btnLogin.Visible=false;
}
else
{
// Add your comments here
Master.UserFeedBack.Text="The User ID and/or Password supplied is incorrect. Please try again!";
}
}

2. We can modify the Page_Load method in pgLogin to prompt the user for their credentials.
Revised Page_Load Method in pgLogin.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
Master.UserFeedBack.Text="Please enter username and password.";
}

3. Set pgLogin.aspx as your Start Page and test your application. You should be able to log in with the username jsmith and password password1 or jdoe and password2. Also test the validation and lockout code.

PART II: STEP H: Harden Your Application (Optional)
This is an optional step that you can complete on your own. Below are two actions that can make our web application even more secure.
SQL Injection Prevention
Our application is vulnerable to an SQL injection attack since ValidateUser only checks to see if a record is found and not if the found record is the correct user record. Attackers can fool our application into always returning records by sending carefully crafted SQL as the username or password on our login form. The ValidateUser method will then return true
as if the correct username and password were given.
A quick fix for this is to modify our Data Layer ValidateUser method to verify that the username in the record returned matches the one we were sent as an argument. To implement this, you need to change the query to be similar to the query used in the FindCustomer method. Then compare the UserID field in the record returned from the database to the username
sent as an argument.
Encrypting Passwords in the Database
Our application stores user passwords in plaintext. If an attacker gains access to our server through a vulnerability in our application or possibly another application on the same server,user account passwords are exposed and easily compromised. The passwords should be stored as SHA1 hashes which will make them nearly impossible to reverse engineer.
Unless you have a hashing application available, it is easiest to create new user accounts and store the passwords for these new users as the SHA1 hash. Once you have new users created with hashed passwords stored in the database, you can delete the current user accounts that have unencrypted passwords.
Here is an overview of the steps needed to implement securely stored passwords:
- On the form pgLogin.aspx, add fields (User ID,Password,Verify Password) and a button to Create New User Account.
- Verify the two password fields match.
- Create a new record in tblUsers for this user and store the SHA1 hash of the password instead of the password.
- When a user logs in, search for the username in the database.
-- If a record is found, compute the SHA1 hash of the password the user supplied during the login attempt. Compare the SHA1 of the password entered by the user to the SHA1 password hash stored in the database. If they match, we have a valid user.
The following code uses the ASP .NET class SHA1CryptoServiceProvider to create theSHA1 hash of the variable userPassword and store it in the variable hashedPassword:
UnicodeEncoding encoding = new UnicodeEncoding();
SHA1 sha1Hasher = new SHA1CryptoServiceProvider();
byte[] hashedPassword = new byte[encoding.GetByteCount(userPassword)];
hashedPassword=sha1Hasher.ComputeHash(hashedPassword);
You can make your application even more secure by using salt with a SHA1 password hash, but that is beyond the scope what we can cover here.

1. Optionally add features to obscure passwords and prevent SQL injection
2. Finalize the Lab

PART II: STEP I: Finalize the Lab
1. Save your work!
2. Test it! Make changes as appropriate until it works. Try adding new records to the database.
3. Remember to add comments for each step being performed.
4. Please zip and submit the entire web project folder.