Tuesday, January 31, 2012
Dentist Appointment ERD
Here is the ERD (Entity Relation Diagrom) for the Dentist office we did in class:
Monday, January 30, 2012
SubQueries
Use MagazineSubscription
Select MagID From MagazineDetail
Where SubscriptionPrice =
(Select MAX(SubscriptionPrice) From MagazineDetail)
Select MagID, SubscriptionPrice,
(Select AVG(SubscriptionPrice) From MagazineDetail) as Average
From MagazineDetail
Where SubscriptionPrice >
(Select AVG(SubscriptionPrice) From MagazineDetail)
Insert into Customer(CustLastName, CustFirstName,
CustAddress, CustCity, CustState, CustZipcode, CustPhone)
Values('John', 'Larry', '100 somewhere st','Seattle','Wa','98000',null)
Select CustID, CustLastName, CustFirstName
From Customer
Where CustID Not in (Select CustID from Subscription)
Select MagName
From Magazine
Where MagID not in
(Select MagID
from MagazineDetail
Where MagDetID in
(Select MagDetID
from Subscription))
Select MagID
from MagazineDetail
Where exists
(Select MagDetID
from Subscription
where SubscriptionPrice > 100)
Select MagdetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice >= all
(Select SubscriptionPrice
From MagazineDetail)
--any says that the subcription price must be
--greater than any one of the other prices
--the effect is to return all but the smallest price
Select MagdetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice > any
(Select SubscriptionPrice
From MagazineDetail)
Select SubscriptTypeID, AVG(SubscriptionPrice) as Average
From MagazineDetail
Group by SubscriptTypeID
--this is a correlated subquery
--that means the subquery is dependent
--on a value from the main query
--for its completion
--it is equivalent to a recursive function
--in programming
--Note that like a self join the same table
--is aliased with two different aliases
--treating it like two tables
Select md.SubscriptTypeID, MagDetID, SubscriptionPrice
From magazineDetail md
Where SubscriptionPrice >=
(Select AVG(SubscriptionPrice)
From magazinedetail amd
where md.SubscripttypeID = amd.SubscripttypeID)
Select * from SubscriptionType
--exists returns a boolean does it exist
--in the subset or not
--a bit more efficient than in
Select Magname
From Magazine
Where Exists
(Select MagID
from MagazineDetail
Where SubscriptTypeID=5)
Use CommunityAssist
Select Distinct (Select MAX(hireDate) from Employee) as "Newest",
(Select MIN(HireDate) From Employee) as "Oldest",
DateDiff(yy, (Select MIN(HireDate) From Employee),
(Select MAX(hireDate) from Employee) ) as "Range"
From Employee
Friday, January 27, 2012
ITC 222 Assignment 7
Creating and altering tables
Automart has decided to implement a new inspection process where they go to each location at random times. They want to add the results of these inspections to the database. This involves adding three tables. One look up table that contains the various types of infractions or problems. One for the inspection date and location and another for the details of each inspection
The Problems
Create the following tables. Each table should be in the Schema "Employee".
InspectionCategory
It will contain the column CategoryID which will be an identity starting at 1 and incrementing by ones. This column will also be the primary key. The next column will be CategoryName which will provide the descriptive name of the category such as "cleanliness" or " waiting area" etc.
Inspection
This table will contain an InspectionID which is the primary key and and identity starting with 1 and incrementing by 1s. It will contain an InspectionDate and a LocationID which is a foreign key relating to the table location.
Inspection Detail
This table will contain the columns InspectionDetailID, an Identity and primary key, InspectionID, a foreign key, CategoryID a foreign key related to InspectionCategory and a Description column which will contain xml.
We also are going to make some changes to existing tables
Alter the Customer.RegisteredCustomer table to make emails unique. Do the same for passwords.
Also the CustomerSurvey table has check constraint on the column "rating" that limits it to a number between 1 and 10. The constraint name is CK__CustomerS__Ratin__25869641. Drop it and add a new constraint that limits it to numbers between 1 and 5.
We are also going to correct a mistake in the current database. The vehicleID in Employee.VehicleService should be a foreign key related to the VehicleID in Customer.Vehicle. Add the Foreign Key constraint to correct this oversite.
What I am looking for
I want to see the code for the three tables and the alter tables. I am especially looking at the primary key and foreign key constraints for those tables
Calculator Examples
I am posting two examples of Calculator code that work. They both have different but effective approaches. The first is From Victoria Bailey. I have only included the code from the form.
Victoria's only works with binary operations, that is [number] [operator] [number], for example 4 + 2, 3 * 5, etc. It doesn't work with examples like 4 + 3 * 2 / 2. Christian's does
Default.aspx
<form id="form1" runat="server">
<div>
<asp:Label ID="lblScreen" runat="server" Text=""></asp:Label><br />
<asp:Button ID="Button1" runat="server" Text="1"
onclick="btnNumber_Click" /><asp:Button ID="Button2"
runat="server" Text="2" onclick="btnNumber_Click" />
<asp:Button ID="Button3" runat="server" Text="3" onclick="btnNumber_Click" /><br />
<asp:Button ID="Button4" runat="server"
Text="4" onclick="btnNumber_Click" /><asp:Button ID="Button5"
runat="server" Text="5" onclick="btnNumber_Click" />
<asp:Button ID="Button6" runat="server" Text="6" onclick="btnNumber_Click" /><br />
<asp:Button ID="Button7" runat="server"
Text="7" onclick="btnNumber_Click" /><asp:Button ID="Button8"
runat="server" Text="8"
onclick="btnNumber_Click" />
<asp:Button ID="Button9" runat="server" Text="9"
onclick="btnNumber_Click" /><br />
<asp:Button ID="Button10" runat="server" Text="0"
onclick="btnNumber_Click" /><br />
<asp:Button ID="Button11" runat="server" Text="+"
onclick="btnOperation_Click" />
<asp:Button ID="Button12" runat="server" Text="-"
onclick="btnOperation_Click" />
<asp:Button ID="Button13" runat="server" Text="*"
onclick="btnOperation_Click" />
<asp:Button ID="Button14" runat="server" Text="/"
onclick="btnOperation_Click" />
<br /><br />
<asp:Button ID="btnCalculate"
runat="server" Text="Calculate" onclick="btnCalculate_Click" /><br /><br />
<asp:Label ID="lblAnswer" runat="server" Text="Answer: "></asp:Label>
<asp:Label ID="lblCalculation" runat="server" Text=""></asp:Label><br /><br />
</div>
</form>
Code Behind:
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnCalculate_Click(object sender, EventArgs e)
{
string op = Session["operation"].ToString();
double number = 0;
double secondNumber = 0;
double total = 0;
if (lblScreen.Text != String.Empty)
{
secondNumber = double.Parse(lblScreen.Text);
}
if ((Session["firstNumber"] != null) && (Session["firstNumber"].ToString() != ""))
{
number = double.Parse(Session["firstNumber"].ToString());
}
switch (op)
{
case "+":
total = number + secondNumber;
break;
case "-":
total = number - secondNumber;
break;
case "*":
total = number * secondNumber;
break;
case "/":
total = number / secondNumber;
break;
default:
break;
}
lblCalculation.Text = total.ToString();
}
protected void btnNumber_Click(object sender, EventArgs e)
{
Button button = (Button)sender;
lblScreen.Text += button.Text;
}
protected void btnOperation_Click(object sender, EventArgs e)
{
Session["firstNumber"] = lblScreen.Text;
Button button = (Button)sender;
Session["operation"] = button.Text;
lblScreen.Text = String.Empty;
}
The second example is from Christian Roehr. It uses a very different approach. It is also well commented. Again I have only included the source from the form
>
<form id="form1" runat="server">
<div>
<table>
<tbody>
<tr>
<td colspan="4"></td>
<asp:TextBox ID="txtDisplay" runat="server"></asp:TextBox>
</tr>
<tr>
<td>
<asp:Button ID="Button1" runat="server" Text="7"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button2" runat="server" Text="8"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button3" runat="server" Text="9"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="btnAdd" runat="server" Text="+"
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
</tr>
<tr>
<td>
<asp:Button ID="Button5" runat="server" Text="4"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button6" runat="server" Text="5"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button7" runat="server" Text="6"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="btnSubtract" runat="server" Text="-"
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
</tr>
<tr>
<td>
<asp:Button ID="Button9" runat="server" Text="1"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button10" runat="server" Text="2"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button11" runat="server" Text="3"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="btnMult" runat="server" Text="*"
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
</tr>
<tr>
<td>
<asp:Button ID="btnDel" runat="server" Text="Del" ToolTip="Use the <Del> button to clear the textbox in order to correct a value before clicking one of the operator buttons. The intermediate result and last operator are still kept in memory." CssClass="buttonstyle"
onclick="btnDel_Click"/></td>
<td>
<asp:Button ID="Button14" runat="server" Text="0"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button15" runat="server" Text="."
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="btnDiv" runat="server" Text="/"
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
</tr>
<tr>
<td>
<asp:Button ID="btnClear" runat="server" Text="CL" ToolTip="Use the <CL> button to reset the calculator to its original state. Any prior values or operators are lost." CssClass="buttonstyle" OnClick="btnCl_Click"/></td>
<td colspan="3">
<asp:Button ID="btnEqual" runat="server" Text="=" CssClass="buttonstyle" OnClick="Operator_Click"/></td>
</tr>
</tbody>
</table>
</div>
</form>
</body>
</html>
======================== default.aspx.cs
using System;
using System.Diagnostics; // needed for "Debug" class
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
// define unique integers to identify one of
// {+,-,*,/,=}
protected enum OperatorTypes
{
UNKNOWN=0,
ADD = 1,
SUBTRACT = 2,
MULT = 3,
DIVIDE = 4,
EQUALS = 5
};
protected const double m_initialValue = 0.0;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) // wonna do it when page loads but not every time when we push a button
{
InitializeCalculator();
}
}
protected void InitializeCalculator()
{
// START: define the session variables
//
// Here is why the operator is initially "+".
// When the user clicks any operator
// op_new = one of {+,-,*,/,=}
// then the evaluation of the
// _previous_ operator (op_prev ) happens and _not_ the one that was just clicked (op_new).
// Thus, if we want everything to work the same and if we want to save ourselves
// too much extra specialized initialization-specific code, we can just assume that
// the very first op_prev was "+" and that the previous result (result_prev) was 0.0.
// Then , when op_new is clicked, we evaluate
// result_new = execute(result_prev, op_prev, number);
// Where "number" is the number the user typed in prior to pressing op_new. "number"
// got parsed right when we clicked op_new.
// We could just as well have selected Session["result"] = 1.0 and Session["operator"] = "*"
// but "+" is simpler and somehow cleaner, avoiding possible rounding errors.
//
// In any future discussion, i will call the session variables
// the "stack", since that is what we kind of do when we
// store the value on the server side and update it or retrieve it.
Session["result"] = m_initialValue; // this writes it to the server and gives it a value of 0.0
Session["operator"] = OperatorTypes.ADD;
// END: define the session variables
// in order to facilitate the very first parsing of a number
// down the line, let's put a "0" into the text field.
// That is how most calculators work and it also
// puts it in sync with what is in our initial session "result" value.
InitializeTextDisplay();
}
protected void InitializeTextDisplay()
{
txtDisplay.Text = m_initialValue.ToString();
}
protected void EmptyTextDisplayIfContainsInitialValue()
{
if (txtDisplay.Text == m_initialValue.ToString())
{
txtDisplay.Text = "";
}
}
protected void InputNumber_Click(object sender, EventArgs e)
{
// avoid this scenario from happening:
// after entering a number, get something like 06, 0555, 000, 0024
// We dont want such numbers since they may not be parsable, or even if
// they are, they dont look pretty.
EmptyTextDisplayIfContainsInitialValue();
// the button that is sending the message
Button b = (Button)sender;
txtDisplay.Text += b.Text;
}
protected void Operator_Click(object sender, EventArgs e)
{
// the button that is sending the message
Button b = (Button)sender;
// we could use the ID or we could use the text
// START: determine which operator was clicked
//
// FYI: Equals() operator more optimized to work with strings, but could have done '==' too
//
OperatorTypes op_new = OperatorTypes.UNKNOWN;
if (b.ID.Equals("btnAdd"))
{
op_new = OperatorTypes.ADD;
}
else if (b.ID.Equals("btnSubtract"))
{
op_new = OperatorTypes.SUBTRACT;
}
else if (b.ID.Equals("btnMult"))
{
op_new = OperatorTypes.MULT;
}
else if (b.ID.Equals("btnDiv"))
{
op_new = OperatorTypes.DIVIDE;
}
else if (b.ID.Equals("btnEqual"))
{
op_new = OperatorTypes.EQUALS;
}
else
{
// this should not happen anyways if we wrote
// the code correctly.
op_new = OperatorTypes.UNKNOWN;
}
// for debugging only
Debug.Write(String.Format("op_new={0}\n", (int)op_new));
if (op_new == OperatorTypes.UNKNOWN)
{
Exception ex = new Exception("Invalid operator");
throw ex;
}
// END: determine which operator was clicked
// 1. Parse the number which user had entered up to
// the point of clicking one of the operator buttons.
double operandNumber = GetNumber();
// 2. Get the last result from the stack.
double result_prev = (double)Session["result"];
// 3. Get the operator from the stack.
// This operator is _not_ the one we just got, but the one
// entered before user entered <operandNumber>
OperatorTypes op_prev = (OperatorTypes)Session["operator"];
Debug.WriteLine("op_prev={0}\n", (int)op_prev);
// 4. evaluate the new result based
// on <result_prev>, <op_prev>, <operandNumber>
// For instance, if op_prev is "*" (multiplication), then
//
// result_new = result_prev * operandNumber
double result_new = ExecuteOperator(result_prev, op_prev, operandNumber);
Debug.WriteLine("result_new={0}\n", result_new);
// test if the number is wellformed. That can be for one of the following reasons
// a) divide-by-zero
// b) overflow (two very large numbers multiplied or a very small one in the denominator)
// c) perhaps some other reason
if (double.IsInfinity(result_new) || double.IsNaN(result_new))
{
// first reset the calculator since you cannot utilize the result for any future operation
InitializeCalculator();
// throw an exception
string numberString = string.Format("resulting number = {0} .", result_new);
Exception ex = new Exception(numberString+ "You either divided by zero or the operands resulted in an out-of-range number");
throw ex;
}
// 5. put new result on the stack
Session["result"] = result_new;
// 6. put new operator on the stack
Session["operator"] = op_new;
// 7. display the new result
if (op_new == OperatorTypes.EQUALS)
{
// return result to text box
txtDisplay.Text = result_new.ToString();
}
else
{
// as shown in the programming assignment specification,
// ("and clear it to make it ready for the next entry")
// clear the text (even though that is not the typical
// calculator behavior i think. the typical calculator
// shows the intermittent result and only clears after
// you press the first digit of
// the new number you are about to enter)
InitializeTextDisplay();
}
}
protected double GetNumber()
{
// double number = double.Parse(txtDisplay.Text);
double number = 0;
bool isDouble = double.TryParse(txtDisplay.Text, out number);
if (isDouble == false)
{
txtDisplay.Text = "";
Exception ex = new Exception("Only enter valid numbers");
throw ex;
}
return number;
}
// Perform the operation
// returnvalue = result_old op num
// e.g. if op=OperatorTypes.ADD
// you will get
// returnvalue = result_old + num
protected double ExecuteOperator(
double result_old,
OperatorTypes op,
double num
)
{
double result = result_old; // default in case of an invalid operator
switch (op)
{
case OperatorTypes.ADD:
result = result_old + num;
break;
case OperatorTypes.SUBTRACT:
result = result_old - num;
break;
case OperatorTypes.MULT:
result = result_old * num;
break;
case OperatorTypes.DIVIDE:
result = result_old / num;
break;
case OperatorTypes.EQUALS:
result = result_old;
break;
default:
result = result_old;
break;
}
return result;
}
protected void btnDel_Click(object sender, EventArgs e)
{
// as specified in the assignment
// have a "clear" button to clear the textbox.
InitializeTextDisplay();
}
protected void btnCl_Click(object sender, EventArgs e)
{
// if we dont have a CLEAR button to reset everything,
// I found that funky things happen where the operators
// stop doing the right things after clicking a certain
// combination of equal sign and operators etc.
// Thus we must have a way to clear all memory and reset
// the stack to the beginning of what it was when the calculator
// first started.
InitializeCalculator();
}
}
Wednesday, January 25, 2012
Joins
use CommunityAssist Select LastName, Firstname, Street, [State], City, Zip From Person Inner Join PersonAddress ON Person.PersonKey=PersonAddress.PersonKey -- Select LastName, Firstname, Street, [State], City, Zip, ContactInfo From Person, PersonAddress, personContact WHERE Person.PersonKey=PersonAddress.PersonKey AND Person.PersonKey=PersonContact.PersonKey Select p.PersonKey, LastName, Firstname, Street, [State], City, Zip From Person p Inner Join PersonAddress pa ON p.PersonKey=pa.PersonKey Select LastName, Firstname, Hiredate, Dependents, Street, City, [State], Zip From Person p inner join Employee e on p.PersonKey=e.PersonKey inner join PersonAddress pa on p.PersonKey=pa.PersonKey select LastName, City From Person Cross join PersonAddress Select ServiceName, GrantKey From [Service] s left outer join ServiceGrant sg On s.ServiceKey=sg.ServiceKey Where GrantKey is Null Select ServiceName, GrantKey From [Service] s inner join ServiceGrant sg on sg.GrantKey > s.ServiceKey --a self join joins a table with itself --here is a little script to show --an example Create Database sample GO Use sample Go Create table Employee ( EmployeeID int Primary key, lastName varchar(30) not null, Supervisior int ) GO Insert into Employee Values(1,'Smith', 3) Insert into Employee Values(2,'Rogers', 3) Insert into Employee Values(3,'Johnson', null) Insert into Employee Values(4,'Larson', 2) Insert into Employee Values(5,'Standish', 3) Select * from Employee --the self join Select e.LastName as employee, s.Lastname as supervisor From employee e Inner join employee s on s.employeeid=e.supervisior
Tuesday, January 24, 2012
ADO
The database scripts can be found on the syllabus. Here is a description of how to run the scripts with SQL Server Express through visual Studio
Running SQL scripts through Visual Studio
Default.Aspx
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server">
</asp:DropDownList>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click"
Text="Get Donations" />
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlConnection connect = new SqlConnection
("Data Source=localhost;initial catalog=communityassist;integrated security=true");
//SqlConnection connect = new SqlConnection
("Data Source=localhost;initial catalog=communityassist;user=manager;password=p@ssw0rd1");
//SqlConnection connect2 = new SqlConnection();
//SqlConnectionStringBuilder builder=new SqlConnectionStringBuilder();
//builder.DataSource = "localhost";
//builder.InitialCatalog = "community assist";
string sql = "Select Distinct DonationDate from donation";
SqlCommand cmd = new SqlCommand(sql, connect);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "Donations");
DropDownList1.DataSource = ds.Tables["Donations"];
DropDownList1.DataTextField= "DonationDate";
DropDownList1.DataBind();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
//ado objects
SqlConnection connect = new SqlConnection
("Data Source=localhost;initial catalog=communityassist;integrated security=true");
//SqlConnection connect2 = new SqlConnection();
//SqlConnectionStringBuilder builder=new SqlConnectionStringBuilder();
//builder.DataSource = "localhost";
//builder.InitialCatalog = "community assist";
string sql = "Select DonationDate, DonationAmount from donation Where DonationDate=@date";
SqlCommand cmd = new SqlCommand(sql, connect);
cmd.Parameters.AddWithValue("@date", DateTime.Parse(DropDownList1.SelectedItem.ToString()));
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds,"Donations");
GridView1.DataSource = ds.Tables["Donations"];
GridView1.DataBind();
}
}
Monday, January 23, 2012
Aggregate Functions
Here is the in-class code for Aggregate Functions
Here is a solution to the substring problem where it wouldn't return single worded entries. The problem was it was looking for a space and none existed. The solution, if inelegant is to concatenate in a space within the charIndex function so that every column has a trailing space.
Use CommunityAssist --aggregate function Select COUNT(PersonKey) "Number of Donations" From Donation --DISTINCT will return only unique values -- in the following --it returns only unique persons for donors Select COUNT(Distinct PersonKey) [Number of Donors] From Donation Select SUM(donationAmount) [total donations] From Donation Select Avg(donationAmount) "Average" From Donation Select MAX(donationAmount) "Highest" From Donation Select MiN(donationAmount) "Highest" From Donation --if you have an aggregate function in the SELECT --any other column in the SELECT not a part of an --aggregate function must be contained in a --GROUP BY clause Select EmployeeKey, COUNT(DonationKey) "Count", sum(DonationAmount) as Total From Donation Group by EmployeeKey --this does several things: --MONTH and YEAR are scalar functions --that means they operate on one row at a time --SUM is an aggreagate function operating on --multiple rows--so the MONTH and YEARfunctions --must be contained in a group by clause --the aliases don't work, you must do the functions --themselves --if you have an aggergate function in your --criteria you must use HAVING instead of WHERE --you can use both in a query as in the example below --the WHERE comes after the FROM --the HAVING comes after the GROUP BY Select MONTH(DonationDate) [Month], Year(DonationDate) [Year],SUM(donationAmount) total From Donation Where YEAR(DonationDate)=2010 Group by Year(donationDate), Month(DonationDate) having SUM(donationAmount) > 3000
Here is a solution to the substring problem where it wouldn't return single worded entries. The problem was it was looking for a space and none existed. The solution, if inelegant is to concatenate in a space within the charIndex function so that every column has a trailing space.
Select SUBSTRING(VehicleMake, 1, CHARINDEX(' ', VehicleMake+' ', 1))
From Customer.Vehicle
Thursday, January 12, 2012
Calculator with Addition
Here is the calculator. I want to mention that when you do multiplication and division you will have to use a somewhat different pattern. There is only a zero in the session variable when you click the operator. That results in the final answer also equaling zero or infinity. Try to figure out how to do these, if you can and we will look at it again on Tuesday.
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Calculator</title>
<link href="calculator.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tbody>
<tr>
<td colspan="4">
<asp:TextBox ID="txtDisplay" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>
<asp:Button ID="Button1" runat="server" Text="7"
CssClass="buttonstyle" OnClick="InputNumber_Click" /></td>
<td>
<asp:Button ID="Button2" runat="server" Text="8"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button3" runat="server" Text="9"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="btnAdd" runat="server" Text="+"
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
</tr>
<tr>
<td>
<asp:Button ID="Button5" runat="server" Text="4"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button6" runat="server" Text="5"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button7" runat="server" Text="6"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="btnSubtract" runat="server" Text="-"
CssClass="buttonstyle" OnClick="Operator_Click" /></td>
</tr>
<tr>
<td>
<asp:Button ID="Button9" runat="server" Text="1"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button10" runat="server" Text="2"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button11" runat="server" Text="3"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="btnMult" runat="server" Text="*"
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
</tr>
<tr>
<td>
<asp:Button ID="btnDel" runat="server" Text="Del"
CssClass="buttonstyle"/></td>
<td>
<asp:Button ID="Button14" runat="server" Text="0"
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="Button15" runat="server" Text="."
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
<td>
<asp:Button ID="btnDiv" runat="server" Text="/"
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
</tr>
<tr>
<td colspan="4">
<asp:Button ID="btnEqual" runat="server" Text="="
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
</tr>
</tbody>
</table>
</div>
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Session["result"] = 0.0;
}
}
protected void InputNumber_Click(object sender, EventArgs e)
{
Button b = (Button)sender;
txtDisplay.Text += b.Text;
}
protected void Operator_Click(object sender, EventArgs e)
{
Button b = (Button)sender;
if (b.ID.Equals("btnAdd"))
{
Addition();
Session["operator"] = "+";
}
if(b.ID.Equals("btnEqual"))
{
Calculate();
}
}
protected void Addition()
{
try
{
double num = GetNumber();
double numResult = (double)Session["result"];
Session["result"] = num + numResult;
txtDisplay.Text = "";
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
}
protected double GetNumber()
{
double number =0;
bool isDouble = double.TryParse(txtDisplay.Text, out number);
if (isDouble == false)
{
txtDisplay.Text = "";
Exception ex = new Exception("Only enter valid numbers");
throw ex;
}
return number;
}
protected void Calculate()
{
double number = (double)Session["result"];
double number2 = GetNumber();
if (Session["operator"].ToString().Equals("+"))
{
txtDisplay.Text = (number + number2).ToString();
}
}
}
Calculator.css
body {
}
.buttonstyle
{
width:50px;
}
#txtDisplay
{
text-align:right;
}
Wednesday, January 11, 2012
SQL Functions
--concatination
Select (Lastname + ', ' + firstname) as [Person Name]
From Person
-- Operators
Select DonationAmount, DonationAmount * .85 as [Charity]
From Donation
Select * From Donation
Select YEAR(DonationDate) as [Year] From Donation
Select MONTH(DonationDate) as [Month] from Donation
Select Distinct Day(DonationDate) as [Day] from Donation
Select DATEPART(MM,DonationDate) From Donation
Select DatePart(hh,DonationDate) as [Month] from Donation
Select DATEDIFF(DD,'12/31/2011', GETDATE())
Select DateAdd(YY, 5, GETDATE())
Select * from PersonAddress
Select CHARINDEX(Street, ' ') From PersonAddress
Select SUBSTRING(Street, 1, 10)
From PersonAddress
Select SUBSTRING(Street, 1, charindex(' ', Street))
From PersonAddress
Select * From Donation
Select '$' +CAST( DonationAmount as Varchar) as Amount from Donation
Select '(' + SUBSTRING(ContactInfo, 1,3) + ')'
From PersonContact
Where ContactTypeKey !=6
Select '$' +CAST(( DonationAmount * .85) as Varchar) as CharityAmount from Donation
Monday, January 9, 2012
SQL SELECT
Use CommunityAssist Select Lastname, Firstname From Person; Select * From Person Select lastname as [Last Name], Firstname as [First Name] From Person --if there are no special characters or spaces --you don't need brackets Select lastname Surname, Firstname Name From Person /*this is a multi line comment */ Select Distinct PersonKey from Donation Order by PersonKey Desc Select * from Donation Order by donationAmount Desc Select * from Donation Order by 3 Desc Select Top 10 DonationKey, DonationAmount From Donation Order by DonationAmount Desc Select DonationKey, donationAmount From Donation Where DonationAmount > 1000 -- > < >= <= != <> Select * From PersonAddress Where City='Seattle' Select * From PersonAddress Where not City='Seattle' Select * from Donation Where DonationDate='2/2/2010' Select * from Donation Where DonationDate between '2/2/2010' And '2/6/2010' Select * From PersonAddress Where City='Kent' or City='Bellevue' Select * From PersonContact Where ContactInfo LIKE '306%' -- _ for a single character ITC22_ 220 222 224 226 Select * From Donation Where EmployeeKey in (3, 4, 5) Select * from PersonAddress Where Apartment is Not Null
Sunday, January 8, 2012
ITC 222 assignment six
Insert, Update, Delete
Problems
1-5 Insert the following new customer, his vehicle, and the services provided. He is not going to become a registered customer at this time. (This will require multiple insert statements. You will also need to look up the automatically generate keys to use as foreign keys.)
Jay Russell, license BFM349, Ford Fusion, 2011, at the new Tacoma shop. He got an oil change and new windshield wipers.
6. Registered customer 7 has a new email address. Change it to "laura26@yahoo.com" change the password to "moonshine"
7. Change the price of auto service 12 to $110.90
8. Delete AutoserviceDetailID 80 from vehicle service detail.
What I am Looking for
The insert requires some effort. Not only do you have to do multiple insert statements, you also have to look up vales, and the inserts must be done in the correct order. 1through 5 are wort 7 points. The others are worth 1 point each, though they are not without their dangers
To Turn In
Copy the SQL code to a Google doc and share it with spconger@gmail.com
Saturday, January 7, 2012
ITC 222 Assignment Five
Sub Queries
Problems
For each of these problems use subqueries for all parts of the problem, even though joins may make more sense to you.
1. List the name and price of the service with the maximum price.
2. Get a list of the first and last names of every person who is an employee.
3. Get a list of all the employee supervisors.
4. List the name of every person who is not a registered customer.
5. Get the license make and year of every vehicle that has been served. Some may appear more than once. You can use DISTINCT to not show duplicates.
6. Get the same information as in number five but only for those vehicles that have had service 12 completed on them.
7. Now repeat the query above, but include the service name in the select clause. (Remember, no inner joins).
8. Get the service price, the average service price and the difference between the two for every service. Sort them in descending order by service price. (This is one that can only be done with subqueries).
What I am looking for
I want only sub queries. 1 through 6 are worth 1 point each. 7 and 8 are worth 2 points each.
To Turn in
Post the SQL in a Google doc. Share it with spconger@gmail.com
ITC 222 Assignment 4
Joins
Inner Joins
1. Return the names (first and last) and emails of every registered user.
2. Do the same query based on the old style "where" based syntax.
3. List the customer names, the license plate, the vehicle make and year of every registered customer.
4. List the customer name, the license plate, the vehicle make and year and the services provided for the Vehicle service with the ID of 12.
5. List the location names and the names of all the employees that work at those locations.
Cross Joins
6. Do a cross join between Person and registered customer. Use the cross Join syntax.
7. Do a cross join using the older syntax
Outer Joins
8. List the customers people that do not have a registered customer account.
9. List any location names that has never provided a service for any car
What I am looking for
I want to see the SQL that would provide the data to the query. Each one is worth 1 pt. with one point for free for a total of 10 points.
To Turn in
Copy the SQL into a Google doc. Share it with spconger@gmail.com
Thursday, January 5, 2012
Stuf for the first Assignment
Default.aspx
Default.aspx.cs
Confirmation.aspx
Confirmation.aspx.cs
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="_Default" Trace="false" ViewStateMode="Enabled"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Stuff</h1>
<asp:DropDownList ID="DropDownList1" runat="server">
</asp:DropDownList>
<p><asp:Label ID="Label1" runat="server" Text="Enter Name"></asp:Label>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox></p>
<asp:Calendar ID="Calendar1" runat="server"></asp:Calendar>
<asp:Button ID="btnConfirm" runat="server" Text="Confirm"
onclick="btnConfirm_Click" />
</div>
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string[] colors = new string[5];
colors[0] = "Blue";
colors[1] = "Green";
colors[2] = "Red";
colors[3] = "Yellow";
colors[4] = "Teal";
if (!IsPostBack)
{
DropDownList1.DataSource = colors;
DropDownList1.DataBind();
}
//DropDownList1.Items.Add("Red");
//DropDownList1.Items.Add("Green");
}
protected void btnConfirm_Click(object sender, EventArgs e)
{
string name = txtName.Text;
string date = Calendar1.SelectedDate.ToShortDateString();
string chosenColor = DropDownList1.SelectedItem.ToString();
//Response.Redirect("Confirmation.aspx?name="
// + name+"&date="
// +date+"&color="+chosenColor);
Session["name"] = name;
Session["color"]=chosenColor;
Session["date"] = date;
Response.Redirect("Confirmation.aspx");
}
}
Confirmation.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Confirmation.aspx.cs" Inherits="Confirmation" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Confirmation Page</h1>
</div>
<asp:Label ID="lblName" runat="server" Text="Label"></asp:Label><br />
<asp:Label ID="lblColor" runat="server" Text="Label"></asp:Label><br />
<asp:Label ID="lblDate" runat="server" Text="Label"></asp:Label>
</form>
</body>
</html>
Confirmation.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Confirmation : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//lblColor.Text = Request.QueryString["color"];
//lblName.Text = Request.QueryString["name"];
//lblDate.Text = Request.QueryString["date"];
if (Session["color"] != null)
{
lblColor.Text = Session["color"].ToString();
lblDate.Text = Session["date"].ToString();
lblName.Text = Session["name"].ToString();
}
else
{
Response.Redirect("Default.aspx");
}
}
}
Subscribe to:
Posts (Atom)