Wednesday, January 27, 2010

Sub Queries

Here are the samples for subqueries:

/************************************
This is the beginning of Subqueries
*************************************/

use MagazineSubscription

--simple subquery to see which magazine
--has the highest price
Select MagID, subscriptionPrice
From MagazineDetail
Where subscriptionPrice=
(Select Max(subscriptionPrice)
From magazineDetail)

--inner join and sub query
Select MagName, subscriptionPrice
From Magazine m
Inner Join MagazineDetail md
On m.MagID=md.Magid
Where subscriptionPrice=
(Select Max(subscriptionPrice)
From magazineDetail)

--which magazines are greater than average in price
Select MagID, subscriptionprice
From MagazineDetail
Where SubscriptionPrice >
(Select Avg(SubscriptionPrice)
From MagazineDetail)

--sub query in the select
Select MagID, subscriptionprice,
(Select Avg(subscriptionPrice) From magazineDetail) as Average
From MagazineDetail
Where SubscriptionPrice >
(Select Avg(SubscriptionPrice)
From MagazineDetail)

Select MagID, subscriptionprice,
(Select Avg(subscriptionPrice) From magazineDetail) as Average
From MagazineDetail
Where SubscriptionPrice <
(Select Avg(SubscriptionPrice)
From MagazineDetail)


--correlated subqueries
--see exists

--using in
Select CustLastName, CustfirstName
From customer
Where custID in
(Select Custid from subscription
Where MagDetID in
(Select MagDetID from MagazineDetail
Where SubscriptionPrice=
(Select MIN(subscriptionPrice)
From MagazineDetail)))


--any all

--all means to use the comparitive to all values
--in the subquery; the query below lists only
--those subscriptionprices that are greater than
--or equal to all other values. This is
--equivalent to getting the max value
Select MagDetID, subscriptionPrice
from MagazineDetail
Where SubscriptionPrice >= All
(Select subscriptionPrice
From MagazineDetail)

--any means that at least one value meets the criteria
--what the query below means is that the subscriptionprice
--listed is greater than or equal to at least one value
--in the sub query (which really applies to every record
--since it is at least equal to itself

Select MagDetID, subscriptionPrice
from MagazineDetail
Where SubscriptionPrice >= Any
(Select subscriptionPrice
From MagazineDetail)

--exists. Notice also that this is a
--correlated query where the inner query
--depends on the outer
Select MagName
From Magazine m
Where exists
(select *
from MagazineDetail md
Where m.magid=md.magid
And subscriptTypeID=3)

--same with not exists
Select MagName
From Magazine m
Where Not exists
(select *
from MagazineDetail md
Where m.magid=md.magid
And subscriptTypeID=3)

Tuesday, January 26, 2010

LINQ Examples

Here is the code from the class. Rember, for any of this to work, you must use the server Explorer window, create a connection to Community Assist Database. Add a new LINQ to SQL Classes object to your project and drag the Service, Person and PersonGrant tables into the designer.


Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
Dim db As New CAssistDataContext
Dim caService = (From s In db.Services _
Order By s.ServiceName _
Select s.ServiceKey, s.ServiceName).Distinct


DropDownList1.DataSource = caService
DropDownList1.DataTextField = "ServiceName"
DropDownList1.DataValueField = "ServiceKey"
DropDownList1.DataBind()
End If
End Sub

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged

Dim db As New CAssistDataContext
Dim caGrant = From g In db.ServiceGrants _
Order By g.GrantDate _
Where g.ServiceKey = DropDownList1.SelectedValue _
Select g.Person.LastName, g.Person.FirstName, g.GrantDate, g.GrantAmount


DataList1.DataSource = caGrant
DataList1.DataBind()



End Sub
End Class


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" 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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
</asp:DropDownList>
</div>
<asp:DataList ID="DataList1" runat="server">
<ItemTemplate>
<hr />
<p>
<asp:Label ID="lblLastname" runat="server" Text='<%#Eval("LastName") %>'></asp:Label>,
<asp:Label ID="lblFirstName" runat="server" Text='<%#Eval("FirstName") %>'></asp:Label>
<br />
<asp:Label ID="lblDate" runat="server" Text='<%#Eval("GrantDate") %>'></asp:Label>
<br />
<asp:Label ID="lblGrantAmount" runat="server" Text='<%#Eval("GrantAmount") %>'></asp:Label>
</p>
<hr />
</ItemTemplate>
</asp:DataList>
</form>
</body>
</html>

Thursday, January 21, 2010

Watermarks

If anyone has completed the watermark extra credit assignment and wants to share how they didi it, I would appreciate it. I haven't had time to test teh book's code. I know that you have to provide a different prefix--something other than "asp"-- to the ajax extension controls.

If you post the asp, ajax, or xhtml code in the blog you must escape all < brackets with &lt; Otherwise the blog will attempt to process the contents.

Wednesday, January 20, 2010

Joins

Here are the samples for todays lecture on Joins:

/************************************
this script provides samples for
Various types of Joins between tables
***************************************/

Use MagazineSubscription
--Inner Joins

--basic inner join two tables
Select CustLastName, CustFirstName , subscriptionID, SubscriptionStart
From Customer c
Inner join Subscription s
On c.CustID=s.custID

-- Join and inner join are equivalent, but inner join is preferable
--because it is more descriptive of what you are doing

Select CustLastName, CustFirstName ,subscriptionID, SubscriptionStart
From Customer c
join Subscription s
On c.CustID=s.custID
where custLastName='Able'

/*You will also be expected to know the equi-join
equivalents to the join statements especially since
some older DBMSs don't support the Join syntax. You should
use the Join syntax where possible because it is
clearer about what you are actually doing */

--equi-join equivalent

Select CustLastName, CustFirstName ,subscriptionID, SubscriptionStart
From Customer c, Subscription s
Where c.CustID=s.custID
And custLastName='Able'

--Multiple inner joins. this would answer the question
--What Magazines has Tina Able subscribed to

Select CustLastName, MagName, s.SubscriptionID,
SubscriptionStart, SubscriptionEnd
From Customer c
Inner Join Subscription s
On c.CustID=s.custID
Inner Join MagazineDetail md
On md.magDetID = s.MagDetID
Inner Join Magazine m
On m.MagID=md.MagID
Where CustLastName='Able'

--equi-Join equivalent

Select CustLastName, MagName, s.SubscriptionID,
SubscriptionStart, SubscriptionEnd
From Customer c, MagazineDetail md, Subscription s, Magazine m
Where c.custID=s.custID
And md.MagDetID=s.MagDetID
AND m.MagID=md.MagID
And CustLastName='Able'

--Inner Join with Aggregate Functions. This query
--answers the question How much has each customer
--paid for their subscriptions

Select CustLastName, Sum(SubscriptionPrice) as "Total Paid"
From Customer c
Inner Join Subscription s
On c.custID=s.custID
Inner Join MagazineDetail md
on md.magDetID=s.magdetID
Group by CustLastName
Order by CustLastName

--equi Join equivalent
Select CustLastName, Sum(SubscriptionPrice) as "Total Paid"
From Customer c, Subscription s, MagazineDetail md
Where c.custID=s.custID
And md.MagDetID=s.MagDetID
Group by CustLastName
Order by CustLastName

--Cross Join
Select CustLastName, s.SubscriptionID
From Customer
Cross Join Subscription s

--equijoin equivalent

Select CustLastName, s.SubscriptionID
From Customer, Subscription s

--Outer Joins
--Left outer join Returns all customers and
--any matching Magazines. If no
--subscriptions then Null
Select CustLastName, SubscriptionID
From Customer c
Left Outer Join Subscription s
On c.CustID=s.custID

--a right outer join is exactly the same just reverse the order of the tables
Select CustLastName, SubscriptionID
From Subscription s
right Outer Join customer c
On c.CustID=s.custID


--SQL server 2005 no longer supports the non
--ans *= and =* syntax

--To locate all customers that don't have a subscription
Select CustLastName, SubscriptionID
From Customer c
Left Outer Join Subscription s
On c.CustID=s.custID
Where s.SubscriptionID is Null

--To locate all magazines that have never been
--subscribed to
--first I see what is subscribe to
Select Distinct MagName
From Magazine m
Inner Join MagazineDetail md
On m.MagID=md.MagID
Inner Join Subscription s
On md.magdetid=s.magdetid

--then what is not. You might be able
--to find a more elegant solution
Select MagName, Count(s.subscriptionID) as Orders
From Magazine m
Inner Join MagazineDetail md
On m.MagID=md.MagID
Left Outer Join Subscription s
On md.magDetID=s.MagDetID
Group by magName
Having count(s.subscriptionID) < 1


--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', 2)
Insert into Employee
Values(3,'Johnson', null)
Insert into Employee
Values(4,'Larson', 2)
Insert into Employee
Values(5,'Standish', 3)

--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 19, 2010

Ajax Samples 2

Here is the code we did for class today. It has two Ajax Update Panels, one that does the tip calculator and one that reveals or hides a panel with a table in it. Remember to add a Script manager, and remember that all the controls in an Update Panel must be inside a control template element.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" 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 id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Tip Calculator</h1>
<p>
 </p>

<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<p> Enter the Meal AmountEnter the Meal AmountEnter the Meal Amount
<asp:TextBox ID="TxtMeal" runat="server"></asp:TextBox>
 </p>
<asp:Label ID="Label2" runat="server" Text="Choose the tip Percent"></asp:Label>
<asp:RadioButtonList ID="RadioButtonList1" runat="server">
</asp:RadioButtonList>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" />
<br />
<asp:Label ID="Label3" runat="server" Text="Your tip will be"></asp:Label>
<asp:Label ID="lbltip" runat="server" Text=""></asp:Label> <br />
<asp:Label ID="Label4" runat="server" Text="Your total will be"></asp:Label>
<asp:Label ID="lbltotal" runat="server" Text=""></asp:Label>
</ContentTemplate>
</asp:UpdatePanel>
</div>
<asp:UpdatePanel ID="UpdatePanel2" runat="server">
<ContentTemplate>
<asp:RadioButtonList ID="RadioButtonList2" runat="server" AutoPostBack="True"
onselectedindexchanged="Unnamed1_SelectedIndexChanged">
<asp:ListItem Text="Show" Value="1"></asp:ListItem>
<asp:ListItem Text="Hide" Value="0"></asp:ListItem>
</asp:RadioButtonList>
<asp:Panel ID="Panel1" runat="server" BackColor="Beige" Visible="False">
<asp:Table ID="Table1" runat="server">
<asp:TableRow>
<asp:TableCell>FirstName</asp:TableCell>
<asp:TableCell><asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox></asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>LastName</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox></asp:TableCell>
</asp:TableRow>

</asp:Table>

</asp:Panel>
</ContentTemplate>
</asp:UpdatePanel>
</form>
</body>
</html>


Option Explicit On
Partial Class _Default
Inherits System.Web.UI.Page


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Create an array of percents
If Not IsPostBack Then
Dim percents() As String = {"10 Percent", "15 Percent", "20 Percent"}
RadioButtonList1.DataSource = percents
RadioButtonList1.DataBind()
End If

'Dim x As Integer


End Sub

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

Dim mealAmount As Double
If txtMeal.Text = Nothing Then
Response.Write("Enter Something")
Return
End If
'If IsNumeric(txtMeal.Text) Then
' mealAmount = Double.Parse(txtMeal.Text)
'Else
' lbltip.Text = "please enter a valid numeric amount for the meal"
' Return
'End If

Dim testResult As Boolean
testResult = Double.TryParse(txtMeal.Text, mealAmount)
If testResult = False Then
lbltip.Text = "please enter a valid numeric amount for the meal"
lbltotal.Text = ""
Return
End If

Dim tip As Double
Dim total As Double

If RadioButtonList1.SelectedItem.Text = "10 Percent" Then
tip = mealAmount * 0.1
ElseIf RadioButtonList1.SelectedItem.Text = "15 Percent" Then
tip = mealAmount * 0.15
Else
tip = mealAmount * 0.2
End If

total = mealAmount + tip

lbltip.Text = tip.ToString("$#,##0.00")
lbltotal.Text = total.ToString("$#,##0.00")

End Sub


Protected Sub Unnamed1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If RadioButtonList2.SelectedValue = 1 Then
Panel1.Visible = True
Else
Panel1.Visible = False
End If
End Sub
End Class

Friday, January 15, 2010

Class sample code from Jan, 14

First we looked at how to check that the input was numeric. You can use one of two methods. The first is based on an old VB function called "IsNumeric." The second uses the TryParse method of the Double type.

Here is an example of the first way:

Dim mealAmount As Double

If IsNumeric(txtMeal.Text) then
mealAmount = Double.Parse(txtMeal.Text)
Else
lblError.Text="Please Enter the meal as a numeric amount"
Return
End If


Here is an example of the second way. If the text in the text box parses, the value
is assigned to the mealAmount variable

Dim mealAmount as double
Dim result as Boolean

Result=Double.TryParse(txtMeal.Text, mealAmount)

If result=False then
lblError.Text="Please Enter the meal as a numeric amount"
Return
End If


Next we covered simple Ajax in ASP. The first thing you must do is add an scriptmanager object to your web page.

<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>


Next you add an Update Panel. Inside the Update panel you put all the controls that you want to post back separately from the main web page. We put in a panel control (not another update panel, just a simple panel that is used to contain other controls. We did it so that we could make the controls invisible and then make them visible with a button click.


<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<!--this is a button to make the panel visible-->
<asp:Button ID="Button3" runat="server" Text="Show time?" />
<asp:Panel ID="Panel1" runat="server" BackColor="BlanchedAlmond" Visible="false">
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<br />
<asp:Button ID="Button2" runat="server" Text="Get Time" />
</asp:Panel>
</ContentTemplate>
</asp:UpdatePanel>


I left out the calendar and the other page elements to keep this simple. Here is the code for the two buttons:

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Label1.Text = TimeOfDay().ToShortTimeString
End Sub

Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
Panel1.Visible =True
End Sub


Have a good long weekend

Wednesday, January 13, 2010

Grading

I have not graded any of the assignments you have turned in yet. I hope to get to them this long weekend.

Aggregate Functions

Here are the examples from class today. All use the MagazineSubscription database.

/******************************
This document provides examples
of aggregate functions, that is
fuctions that operate on more than
one row at a time
*******************************/

use MagazineSubscription

--the basic functions

SELECT Count(*) "Total Subscriptions"
from Subscription

Select Max(SubscriptionPrice) as "Most"
From MagazineDetail

Select Min(SubscriptionPrice) as Least
From MagazineDetail

--not a particularly meaninful number
Select Sum(SubscriptionPrice) as "so?"
From MagazineDetail

--more meaningful
Select AVG(SubscriptionPrice) as "Average Price"
From MagazineDetail

--With distinct (eliminates duplicates before
--computing average
Select AVG(Distinct SubscriptionPrice) as "Average Price"
From MagazineDetail


--more meaningful yet
Select SubscriptTypeID, AvG(SubscriptionPrice) "Average Price"
From MagazineDetail
Group by SubscripttypeID
Order by "Average Price" DESC

--using the having clause

Select SubscriptTypeID, AvG(SubscriptionPrice) "Average Price"
From MagazineDetail
Group by SubscripttypeID
having Avg(SubscriptionPrice) > 50
Order by "Average Price" DESC


--Count how many subscriptions each customer who has more than 2 has
Select CustID, Count(SubscriptionID)"Total Subscriptions"
From Subscription
Group by CustID
having Count(SubscriptionID) > 2



Further note: The last assignment in Assignment3 on Aggregate functions is not doable without subqueries which I have not shown you yet. Don't worry about it. I will show you how to do it Wednesday. (Monday is a Holiday)

Monday, January 11, 2010

SQL Scalar Functions

Here is the sample code for SQL Functions. All the code uses the magazine Subsription Database.

/*********************************
this script provides examples
for the chapter on operators
and functions. It uses the
MagazineSubscription Database
*********************************/

Use MagazineSubscription

--operators and calculations
Select 3 * 4 + 12 / 3 as "Simple Order"
Select ((3 * 4) + 12) / 3 as "Simple Order"

--If shipping is 5% of the price of a magazine,
--what is the shipping cost of each magazine

Select MagID, SubscriptionPrice, SubscriptionPrice * .05 as Shipping
From MagazineDetail

/******************************************
String functions
*******************************************/
--Concatinating strings
Select CustLastName + ', ' + custFirstName "Name",
CustAddress + ', ' + CustCity + ', ' + CustZipcode "Address"
From Customer

--Substring get the distinct area codes from the customer table
Select Distinct substring(CustPhone, 0,4) as Prefix
From Customer

--Return the length of customer addresses
--Notice that you can sort by the Alias

Select Len(CustAddress) "Length"
From Customer
Order by Length DESC

--Throw all lastnames to Upper Case
--Lower is identical in syntax
Select Upper(CustLastName) As "Last Name"
from Customer

--Cast from one type to another
--Multiply each zip code by 100(no good reason)
Select Cast(custZipcode as Int) * 100 "Silliness"
From customer

--use charindex to locate a space and substring to return
--all the characters up to that space
Select substring(MagName,0,Charindex(' ',Magname)) as "First Word"
From Magazine

/*Here is a really complex set of functions
that uses Concatination, cast and substring to produce
a more formatted output */
Select MagID "Magazine", '$' + Cast(SubscriptionPrice As Char(6)) "Price",
'$' + substring(Cast(SubscriptionPrice * .05 as char),0,5) "Shipping"
From MagazineDetail

/****************************************
Date Time functions
****************************************/
--Get the current date and time

Select GetDate() "Now"
Select Current_TimeStamp "Now"

--Select Parts
Select DatePart("yyyy",Current_TimeStamp)
Select DatePart("m", Current_timeStamp)
Select DatePart("hh", Current_timestamp)
Select DatePart("mm", GetDate())
Select Year(GetDate())
Select Month(GetDate())
Select Day(SubscriptionStart)
From Subscription


--DateDiff
Select SubscriptionStart, SubscriptionEnd,
dateDiff("m",SubscriptionStart,SubscriptionEnd) as "Length in Months"
From Subscription

--dateAdd plus some casting and substrings
--notice what happens when you cast a date to char
Select Substring(Cast(SubscriptionEnd as char),0,12)"Subscription End",
Substring(Cast(DateAdd("m",3,SubscriptionEnd) as char),0,12)
AS "Three month bonus"
From Subscription

/*******************************************
Case structures
******************************************/

Select Magname, MagType,
Case MagType
When 'Monthly'
Then 'Six Months'
When 'Quarterly'
Then 'One Year'
When 'Weekly'
Then 'Three months'
Else 'Unknown'
End
As "Minimum Subscription"
From Magazine
Order by MagName

--Nulls, Coalesce
--First insert a customer with no phone
Insert into Customer (custLastName, CustfirstName, CustAddress,
CustCity,CustState,Custzipcode)
Values('Ansi', 'Norm','1000 Coop Blvd','Olympia','WA','98320')

Select CustLastName, custcity, Coalesce(custPhone, 'Unknown')"Phone"
From Customer
Where custcity ='Olympia'

Thursday, January 7, 2010

Intro to Visual Basic

Here is the code we did in class today. first I give you the source for the xhtml and the asp.net controls.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" 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>Untitled Page</title>
</head>
<body>

<form id="form1" runat="server">
<div>
<h1>Tip Calculator</h1>
<p>
<asp:label id="Label1" runat="server" cssclass="">Enter the Meal Amount</asp:label> <asp:textbox id="txtMeal" runat="server"></asp:textbox>
</p>
<asp:label id="Label2" runat="server" text="Choose the tip Percent"></asp:label>
<asp:radiobuttonlist id="RadioButtonList1" runat="server">
</asp:radiobuttonlist>
<asp:button id="btnSubmit" runat="server" text="Submit"></asp:button>


<asp:label id="Label3" runat="server" text="Your tip will be"></asp:label>
<asp:label id="lbltip" runat="server" text=""></asp:label>

<asp:label id="Label4" runat="server" text="Your total will be"></asp:label>
<asp:label id="lbltotal" runat="server" text=""></asp:label>
</div>
</form>
</body>
</html>



Next here is the Visual basic code. In the visual basic, in the page load event, we created an array and bound it to the radiolist controls. The If then statement checks to make sure it is not a "postback" event. That is, it is not a trip back from the server. In the button we create variables to store our values. The mealAmount value comes from the textbox on the form. The others are the result of calculations. An if then else statement checks to see which of the radiobuttons was checked and calculates the tips appropriatly. The results are displayed in label controls and formatted to currency.


Option Explicit On
Partial Class _Default
Inherits System.Web.UI.Page


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Create an array of percents
If Not IsPostBack Then
Dim percents() As String = {"10 Percent", "15 Percent", "20 Percent"}
RadioButtonList1.DataSource = percents
RadioButtonList1.DataBind()
End If

'Dim x As Integer


End Sub





Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

If txtMeal.Text = Nothing Then
Response.Write("Enter a meal amount")
Return
End If
Dim mealAmount As Double = Double.Parse(txtMeal.Text)
Dim tip As Double
Dim total As Double


If RadioButtonList1.SelectedItem.Text = "10 Percent" Then
tip = mealAmount * 0.1
ElseIf RadioButtonList1.SelectedItem.Text = "15 Percent" Then
tip = mealAmount * 0.15
Else
tip = mealAmount * 0.2
End If


total = mealAmount + tip


lbltip.Text = tip.ToString("$#,##0.00")
lbltotal.Text = total.ToString("$#,##0.00")


End Sub
End Class

Wednesday, January 6, 2010

Select Statements

Today, after the initial chaos of setting up Sql Server and assigning permissions, we looked at a few select statements:

First make sure you are in the correct database.

use CommunityAssist

The following statement selects all the records from the table Person.

Select * from Person;

/*sort by lastname z to a and firstname a-z*/
Select Firstname, lastname from Person
order by Lastname Desc, firstname;

Order by allows you to sort the results as you like. DESC lets you sort in reverse order. If you have more than one column listed for sorting, the leftmost column is the primary sort, and then it sorts left to right.

Below are some examples of the WHERE clause with various criteria. Note the use of quotes for string (char, varchar, nchar, nvarchar)and dates. Number values are not quoted. Semicolons are optional in SQL Server. The last two statements are equivalent.

Select * from PersonAddress
Where City = 'Kent';

Select * from donation
Where DonationAmount <= 100;

Select * from Donation
Where DonationDate > '3/1/2010'
And DonationDate < '4/1/2010'

Select * from Donation
Where DonationDate not between '3/1/2010' and '4/1/2010'


Nulls are special cases. They are not a value--not a zero or empty string. They represent an unknown value. As such they cannot be equal to or greater than or less than a value. Therefore SQL uses the IS keyword with Nulls.

Select * from PersonAddress
where Apartment is not null

You can do calculations with Columns. Columns can be aliased to give them more meaningful names

Select 45 * 4 + 3 /2

Select GrantAmount OldAmount, GrantAmount * 1.1 as NewAmount
from ServiceGrant

One that we didn't look at in class is the LIKE operator. It lets you look for patterns in character type data. the % is a wildcard that say look for any number of characters. the underscore is the wild card for a single character. The first one returns all the last names beginning with 'S'. The second one returns any course that start 'MIC 12'. For instance it would return 'MIC 121,' 'MIC 123,' etc.

Select * from Person where Lastname like '$%'

Select * from course wher coursenumber like 'MIC 12_'



--

Tuesday, January 5, 2010

Book Logins

I have entered the logins to access the textbook for ITC 220. Let me know if you have any trouble.

Sunday, January 3, 2010

Creating a Database

Creating a database is like putting together a complex puzzle. You have to consider the business purpose of the database--what is it supposed to do. You have to consider what transactions the database is supposed to store. What information do you need to capture? You have to think about how that information is processed. What are the business rules? What questions is the database supposed to answer? Who are the users of the database? Who should have permissions to see what data? You have to fit all that into a database model of related tables, and you need to to that while keeping the database flexible enough to react to changing business situations and needs. You have to figure out how all the different parts fit together.

To make the puzzle even more complicated, most of the time you aren't given the parts ahead of time. You have to ask questions, research and dig to discover the parts. And if you forget any of them the puzzle won't go together properly. The database won't do what it was intended to do.

Some people love this kind of puzzle. The challenge can be exhilarating. Others find it frustrating in the extreme.

This class is about solving the puzzle. It will lead you through the process and show you some of the steps and methods you can use to succeed.

BASIC and ASP.NET

History of Basic

The programming language Basic is almost as old as the PC itself. The first PCs shipped with DOS and Basic as their only software. You had to learn to program if you wanted to do anything with your computer. The original basic was unstructured. A program consisted of a maximum of 65000 numbered lines. To reuse code you had to create a "GOTO" statement that sent the execution back to a particular line, and then another GOTO to move the execution forward again. This made for very unreadable code. Quick Basic was an improvement on this first Basic. It allowed you to break code into subroutines, lose the line numbering, and broke the 65000 line limit.

When Windows 3.1 was shipped, Basic became Visual Basic. It included a designer that provided the programmer with the an empty Windows form on which he or she could drag various controls. It was event driven, meanining it responded to the users actions, such as clicking on a button, or double clicking a text.

Visual Basic 4 was important because it introduced ADO. ADO was a "Wrapper" around "OLEDB" -- as set of libraries for talking to various databases. A wrapper is a set of programs that provide a simplified interface to a complex set of actions. OLEDB was difficult and required high level programming expertise. ADO made it easy to include data access in a program, and Visual Basic became the language of Choice for data driven programs.

Visual Basic.Net made huge changes in VB. For the first time, Visual Basic was a fully object oriented language with classes, inheritance and polymorphism. For the first time it had all the power of C# and most of the power of C++.

ASP, ASP.Net
Because of ADO VB became a popular language for scripting web pages. ASP stands for Active Server Pages. Instead of using Java Script or PHP, you could write your code in VB Script. This gave you the power of VB, but had some serious drawbacks. For one, it would only run on computers running Windows and Internet Explorer.

ASP.Net changed that. Now you could use VB and have your web page run on any operating system and in any browser. Additionally, it was no longer script, but fully compiled programming code with full object oriented capabilities.

Elements of ASP.Net
ASP.Net is difficult because it brings together a number of different skills. You need to be able to program in VB or C#. You need a good knowledge of xhtml and CSS. You also should have some expertise with xml.

We will look at all these elements in this class

Saturday, January 2, 2010

SQL Overview

SQL is the language relational databases use for all their activities.

SQL is often divided into two broad categories: Data Definition Language(DDL) and Data Manipulation Language (DML). DDL is used for creating and altering database objects such as tables, views, stored procedures and logins. DML is for selecting, inserting, updating or deleting data.

Anything that can be done in a DBMS can be coded as SQL.

SQL is also embedded in web or Windows (Microsoft, Linux, Mac, etc.)applications that are driven by underlying databases.

Any database professional is expected to be proficient in SQL

SQL is considered a fourth generation language. That means that instead of writing code that tells the computer how to do something, step by step, the way you do with C# or C++, you instead tell the computer what you want to do. It is a "declarative" rather than a "procedural" language.

For a history of SQL look at http://en.wikipedia.org/wiki/SQL