CIS407A Week 4 iLab Web Forms with Database Interaction

Description

iLAB OVERVIEW
Scenario/Summary
In this lab, we will start with the form we created in Week 2 (frmPersonnel) and add functionality to INSERT records into a database table and SELECT records for display to the user. We will create a typed dataset, a Data Layer class, several functions to access the data, and a connection to a database. We also will add a search form to allow the user to search records in the database and display the results of that search.
Instructions for Week 4 iLab: Web Forms with Database Interaction
Deliverables
All files are located in the subdirectory of the project. The project should function as specified: When you press the Submit button in frmPersonnel, a record should be saved in the tblPersonnel table having the FirstName, LastName, PayRate, StartDate, and EndDate you entered on the form. Add a search feature to the project. Update your main navigation page with the new options. Once you have verified that it works, save your website, zip up all files, and submit it in the Dropbox.
iLAB STEPS
STEP 1: Data Layer (10 points)
1. Open Microsoft Visual Studio.NET 2008.
2. Click the ASP.NET project called PayrollSystem to open it.
3. Open the clsDataLayer class and add the following function:
// This function saves the personnel data public static bool SavePersonnel(string Database, string FirstName, string LastName, string PayRate, string StartDate, string EndDate) { bool recordSaved; try { // Add your comments here OleDbConnection conn = new OleDbConnection(“PROVIDER=Microsoft.Jet.OLEDB.4.0;” + “Data Source=” + Database); conn.Open(); OleDbCommand command = conn.CreateCommand(); string strSQL; // Add your comments here strSQL = “Insert into tblPersonnel ” + “(FirstName, LastName, PayRate, StartDate, EndDate) values (‘” + FirstName + “‘, ‘” + LastName + “‘, ” + PayRate + “, ‘” + StartDate + “‘, ‘” + EndDate + “‘)”; // Add your comments here command.CommandType = CommandType.Text; command.CommandText = strSQL; // Add your comments here command.ExecuteNonQuery(); // Add your comments here conn.Close(); recordSaved = true; } catch (Exception ex) { recordSaved = false; } return recordSaved; }
4. In the frmPersonnelVerified form, go to the Page_Load() event and add the following code after the existing code (but in the Page_Load event handler):
// Add your comments here if (clsDataLayer.SavePersonnel(Server.MapPath(“PayrollSystem_DB.mdb”), Session[“txtFirstName”].ToString(), Session [“txtLastName”].ToString(), Session [“txtPayRate”].ToString(), Session [“txtStartDate”].ToString(), Session [“txtEndDate”].ToString())) { txtVerifiedInfo.Text = txtVerifiedInfo.Text + ”
The information was successfully saved!”; } else { txtVerifiedInfo.Text = txtVerifiedInfo.Text + ”
The information was NOT saved.”;
5. Add comments for all code containing // Add your comments here.
6. Test your work to make sure no errors occur! (Make sure to put in valid date values for the date data entry fields).
STEP 2: Data Display and Search (10 points)
7. Using the skills you learned in Week 3, create a new DataSet for the tblPersonnel table (called the DataSet dsPersonnel).
8. Using the skills you learned in Week 3, create a new function called GetPersonnel in the clsDataLayer class. This function should retrieve all data from the tblPersonnel table and return it in the form of a dsPersonnel DataSet. Use the GetUserActivity function as an example.
9. Create a new Web form called frmViewPersonnel.
10. Using the skills you learned in Week 3, add a GridView control (called grdViewPersonnel) to the form. This GridView control will be used to display data from the tblPersonnel table. Add the CoolBiz logo at the top of the page and make sure it links back to frmMain.
11. Add the following code to the Page_Load() function in frmViewPersonnel:
if (!Page.IsPostBack) { // Declare the DataSet dsPersonnel myDataSet = new dsPersonnel(); // Fill the dataset with what is returned from the function myDataSet = clsDataLayer.GetPersonnel(Server.MapPath(“PayrollSystem_DB.mdb”)); // Set the DataGrid to the DataSource based on the table grdViewPersonnel.DataSource = myDataSet.Tables[“tblPersonnel”]; // Bind the DataGrid grdViewPersonnel.DataBind(); }
12. Return to the frmPersonnel Web form and add a button ((ID) = btnViewPersonnel, Text = View Personnel) which, when clicked, will display form frmViewPersonnel.
13. Using the skills you learned in Week 3, open the frmPersonnelVerified form and add a button ((ID) = btnViewPersonnel, Text = View Personnel) which, when clicked, will display form frmViewPersonnel. NOTE: This is the same button with the same functionality that you added to form frmPersonnel in the previous step. Also add a new link and linked image to frmMain called View Personnel that will go to the new frmViewPersonnel page you created.
14. You will now add a search feature to allow the user to find and display data. The user will enter a last name and the web application will display the grid of employees with all employees that match that last name.
15. Create a new web form called frmSearchPersonnel. Add the hyperlinked Cool Biz logo to this page. Also add a new item on frmMain (with a link button and image button) called Search Personnel.
16. On the frmSearchPersonnel form, add a label that displays “Search for employee by last name:”. Next to the label, add a text box with an ID of txtSearchName. Add a button with an ID of btnSearch and set the text of the button to “Search”.
17. When the frmSearchPersonnel Search button is pressed, the frmViewPersonnel is displayed. At this point, no searching is actually happening, but you have the forms you need and the navigation working. Now you can focus on the coding you will need to do to have the grid only display matching employees.
18. Before calling the GetPersonnel method you added previously in the lab, get the value that is in the Request[“txtSearch”] item. When the form posts the search page results to the frmViewPersonnel, the name value pair for the search value is passed as part of the Request object. Assign this value to a string variable.
19. Modify the GetPersonnel method you added to include a new parameter called strSearch of type string. This parameter needs to be after the Database string parameter that is already in the method.
20. When calling the GetPersonnel method, pass the value you received to the strSearch parameter.
21. In the GetPersonnel method, you now need to use the passed in strSearch parameter value as part of the SQL string being used to retrieve data. You also need to add logic so that, if strSearch is empty or has no value, all employees are returned in the query.
22. Test the search so that when you enter a last name, employees with that last name are returned. Make sure that when you access frmViewPersonnel and you are not searching, all employees are returned.
23. Lab Hints:
Make sure you re-establish your database connection if you copied the files from a previous lab.
Before you pass the search value into the GetPersonnel method, make sure you check to see if the Request item is null. If it is, you need to pass an empty string into the method or check for null inside the method. If you don’t do this, you will get a server error. To check to see if an object is null, you compare the object to the keyword null.
To create an SQL statement that will search for a given last name in the tblPersonnel table, you can do the following (assume that the search variable was called strSearch).
“select * from tblPersonnel where LastName = ‘” + strSearch + “‘”
24. Add the new Search option and the View Employees option to your main navigation page.
STEP 3: Test and submit (10 points)
Run your project and test it as follows:
The frmMain form should be displayed first (set this form as your start page).
Homepage
Click on the Add New Employee hyperlink to go to the frmPersonnel data entry form. Click the View Personnel button on this form. The frmViewPersonnel form should be displayed in the browser, but at this point, there should not be any personnel listed (because you haven’t entered any yet).
Use the Back button in your web browser to return to the frmPersonnel form and enter some personnel data, similar to the following:
Entering Personnel Data
Now click the Submit button. The frmPersonnelVerified form should be displayed, showing the data you entered, and you should get a message saying that the data was successfully saved, like this:
Verifying Personnel Data
Finally, click the View Personnel data button on this form. The frmViewPersonnel form should be displayed and should show the personnel record you just entered, retrieved from the database, like this:
Viewing Personnel Data
You also should be able to view the employee records by clicking the link on the home page.
Test the search feature and make sure that entering no search string returns all the data and that typing in a last name will return all employees with the same last name.
Search
Search Results
NOTE: Make sure you include comments in the code provided where specified (where the ” // Your comments here” line appears) and for any code you write, or else a five point deduction per item (form, class, function) will be made.
SCREENSHOTS
SOLUTION
PAYMENT
ENTIRE COURSE
The solution includes a Visual Studio ASP.NET project
Attachments [Move over files to preview content of those files]
CIS407A_Week_4_iLab.zip (361.82 KB)
CIS407A-Week4-iLab-mainFrm-Screenshot.png
CIS407A-Week4-iLab-Search-Screenshot.png
CIS407A-Week4-iLab-View-Personnel-Screenshot.png
PayrollSystem
PayrollSystem
App_Code
clsDataLayer.cs
dsPersonnel.xsd
dsPersonnel.xss
dsUserActivity.xsd
dsUserActivity.xss
App_Data
PayrollSystem_DB.mdb
Backup
PayrollSystem
PayrollSystem.sln
PayrollSystem.suo
Default.aspx
Default.aspx.cs
frmMain.aspx
frmMain.aspx.cs
frmPersonalVerified.aspx
frmPersonalVerified.aspx.cs
frmPersonnel.aspx
frmPersonnel.aspx.cs
frmSalaryCalculator.aspx
frmSalaryCalculator.aspx.cs
frmSearchPersonnel.aspx
frmSearchPersonnel.aspx.cs
frmUserActivity.aspx
frmUserActivity.aspx.cs
frmViewPersonnel.aspx
frmViewPersonnel.aspx.cs
images
AddEmployee.png
calculator.png
search.png
Thumbs.db
ViewPersonnel.png
ViewUserActivity.png
PayrollSystem_DB.mdb
UpgradeLog.XML
web.config
_UpgradeReport_Files
UpgradeReport.css
UpgradeReport.xslt
UpgradeReport_Minus.gif
UpgradeReport_Plus.gif
PayrollSystem.sln
PayrollSystem.suo
PayrollSystem.v12.suo
Preview clsDataLayer.cs
xxxxx xxxxxx.xxx.xxxx;
// xxxxxxxxx xxxxxxxxxx xx xxxxxxx xx xxxxx xxxxxxxx
xxxxx xxxxxx.xxxx.xxxxx;
xxxxx xxxxxx.xxx;
///
/// This class will get the user activity and store it in the database table tblUserActivity ///
public class clsDataLayer { // This function gets the user activity from the tblUserActivity public static dsUserActivity GetUserActivity(string Database) { // creating OLEDB Dataset, OLEDB connection and Data adapter objects dsUserActivity DS; OleDbConnection sqlConn; OleDbDataAdapter sqlDA;
//xxxxxx x xxx xxxxxxxxxx xxx xxxxxxx xxxxxxxxxx xxxxxx xx xxx xxxxxxxxxx xxxxxx
xxxxxxx = xxx xxxxxxxxxxxxxxx(“xxxxxxxx=xxxxxxxxx.xxx.xxxxx.4.0;” +
“xxxx xxxxxx=” + xxxxxxxx);
// xxxxxx x xxx xxxx xxxxxxx xxx xxxxxxx xxxxx xx xxx xxxx xxxxxxx
xxxxx = xxx xxxxxxxxxxxxxxxx(“xxxxxx * xxxx xxxxxxxxxxxxxxx”, xxxxxxx);
// xxxxxxxx xxx xxxx xxx
Preview Default.aspx.cs
xxxxx xxxxxx;
xxxxx xxxxxx.xxxxxxxxxxx.xxxxxxx;
xxxxx xxxxxx.xxxx;
xxxxx xxxxxx.xxx;
xxxxx xxxxxx.xxx.xx;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) {
}
}
Preview frmMain.aspx.cs
xxxxx xxxxxx;
xxxxx xxxxxx.xxxxxxxxxxx.xxxxxxx;
xxxxx xxxxxx.xxxx;
xxxxx xxxxxx.xxx;
xxxxx xxxxxx.xxx.xx;
using System.Web.UI.WebControls;
public partial class frmMain : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // This statement will Save the current user activity to tblUserActivity table
xxxxxxxxxxxx.xxxxxxxxxxxxxxxx(xxxxxx.xxxxxxx(“xxxxxxxxxxxxx_xx.xxx”), “xxxxxxxxxxxx”);
}
}
Preview frmPersonalVerified.aspx.cs
xxxxx xxxxxx;
xxxxx xxxxxx.xxxxxxxxxxx.xxxxxxx;
xxxxx xxxxxx.xxxx;
xxxxx xxxxxx.xxx;
xxxxx xxxxxx.xxx.xx;
using System.Web.UI.WebControls;
public partial class frmPersonalVerified : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { //So here we are initializing text property of the textbox “txtVerifiedInfo” after fetching the
//xxxxxx xxxx xxx xxxxxxx xxxxxx
xxxxxxxxxxxxxxx.xxxx = xxxxxxx[“xxxxxxxxxxxx”].xxxxxxxx() +
“x” + xxxxxxx[“xxxxxxxxxxx”].xxxxxxxx() +
“x” + xxxxxxx[“xxxxxxxxxx”].xxxxxxxx() +
“x” + xxxxxxx[“xxxxxxxxxxxx”].xxxxxxxx() +
Preview frmPersonnel.aspx.cs
{
}
xxxxxxxxx xxxx xxxxxxxxx_xxxxx(xxxxxx xxxxxx, xxxxxxxxx x)
{
if (ValidateFields()) //if Validate fields method has returned true { //Add all text boxes values to the session object. Session.Add(“txtFirstName”, txtFirstName.Text);//Add First Name to Session Object Session.Add(“txtLastName”, txtLastName.Text); //Add Last Name to Session Object Session.Add(“txtPayRate”, txtPayRate.Text); //Add Pay Rate to Session Object Session.Add(“txtStartDate”, txtStartDate.Text);//Add Start Date to Session Object Session.Add(“txtEndDate”, txtEndDate.Text); //Add End Date to Session Object Server.Transfer(“frmPersonalVerified.aspx”);//Redirect to frmPersonalVerified page }
}
xxxxxxxxx xxxx xxxxxxxxxxxxxx()
{
xxxxxxxxxxxx.xxxxxxxxx = xxxxxx.xxxxxxx.xxxxx.xxxxx;
Preview frmSalaryCalculator.aspx.cs
xxxxx xxxxxx;
xxxxx xxxxxx.xxxxxxxxxxx.xxxxxxx;
xxxxx xxxxxx.xxxx;
xxxxx xxxxxx.xxx;
xxxxx xxxxxx.xxx.xx;
using System.Web.UI.WebControls;
public partial class frmSalaryCalculator : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) {
}
xxxxxxxxx xxxx xxxxxxxxxxxxxxxxxx_xxxxx(xxxxxx xxxxxx, xxxxxxxxx x)
{
xxxxxx xxxxxxxxxxxxxx = xxxxxxxxxxxxxx.xxxx; //xxxxxxx xxx xxxxx xx xxxxxx xxxxx xxxx xxx xxxxxxx xxxx x xxxxxx
xxxxxx xxxxxxxxxxx = xxxxxxx.xxxxxxxx(xxxxxxxxxxxxxx); //xxxxxxxxxx xxx xxxxxx xxxxx xxxxxxxxxxxxxx xx xxxxxx xxx xxxxxx xx xx x xxxxxx xxxxxxxx
Preview frmSearchPersonnel.aspx.cs
xxxxx xxxxxx;
xxxxx xxxxxx.xxxxxxxxxxx.xxxxxxx;
xxxxx xxxxxx.xxxx;
xxxxx xxxxxx.xxx;
xxxxx xxxxxx.xxx.xx;
using System.Web.UI.WebControls;
public partial class frmSearchPersonnel : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) {
}
}
Preview frmUserActivity.aspx.cs
xxxxx xxxxxx;
xxxxx xxxxxx.xxxxxxxxxxx;
xxxxx xxxxxx.xxxxxxxxxxxxx;
xxxxx xxxxxx.xxxx;
xxxxx xxxxxx.xxxx;
using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq;
xxxxxx xxxxxxx xxxxx xxxxxxxxxxxxxxx : xxxxxx.xxx.xx.xxxx
{
xxxxxxxxx xxxx xxxx_xxxx(xxxxxx xxxxxx, xxxxxxxxx x)
{
xx (!xxxx.xxxxxxxxxx)
Preview frmViewPersonnel.aspx.cs
xxxxx xxxxxx;
xxxxx xxxxxx.xxxxxxxxxxx.xxxxxxx;
xxxxx xxxxxx.xxxx;
xxxxx xxxxxx.xxx;
xxxxx xxxxxx.xxx.xx;
using System.Web.UI.WebControls;
public partial class frmViewPersonnel : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack)
{
// xxxxxxx xxx xxxxxxx
xxxxxxxxxxx xxxxxxxxx = xxx xxxxxxxxxxx();
xxxxxx xxxxxxxxx=””;
//xxxx xxxxxx xxxx xxxx xxx xxxxxxx xxxxxx xxxx xxx xxxxxxxx xxxx’x xxxx xxx
Preview UpgradeReport.css
xxxx-xxxxxx: “xxxxxxx”, xxxx-xxxxx;
xxxx-xxxx: 70%;
xxxx-xxxxxx: 12xx;
xxxxxx-xxxxxx: 0xx;
xxxxxx-xxxx: 10xx;
MARGIN-TOP: 10px } .note { BACKGROUND-COLOR: #ffffff; COLOR: #336699; FONT-FAMILY: “Verdana”, sans-serif; FONT-SIZE: 100%; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 0px; MARGIN-TOP: 0px; PADDING-RIGHT: 10px
}
.xxxxxxxxx
{
xxxxxxxxxx-xxxxx: #x0x0x0;
xxxxxx-xxxxxx: #xxxxxx 0xx xxxxx;
xxxxxx-xxxxxxxx: xxxxxxxx;
Price: $9
Buy Now
Checkout
Added to cart
Buy More Save More
Buy at least TWO items & save up to 30% OFF your ENTIRE order!
Rack up instant rebates in your shopping cart. Simply add items to your cart, and see the savings add up.
Discounts will automatically be applied on eligible orders.
CIS407A Week 4 iLab Web forms with database interaction – $9.00
CIS407A Week 5 iLab Transaction Processing – $12.00
CIS407A Week 6 iLab Login and Security Levels – $12.00
CIS407A Week 7 iLab Error Notification via E-Mail – $12.00
Add to Cart
Checkout
Added to cart
FLASH SALE
$57
$72
Save $15
CIS407A Entire Course
Get Entire Course
You May Also Like:
CIS407A Entire Course
CIS407A Week 1 iLab Annual Salary Calculator ASP.NET Web Application
CIS407A Week 2 iLab User Input Web Pages
CIS407A Week 3 iLab User Activity Monitoring
CIS407A Week 5 iLab Transaction Processing
CIS407A Week 6 iLab Login and Security Levels
CIS407A Week 7 iLab Error Notification via E-Mail