SQL 2

today's SQL
--this is an inner join
--list all the columns regardless of table
--list one of tables in the From clause
--use the inner join keyword to add another
--table
--use the on keyword to show how the tables join

Select LastName, Firstname,
DonationDate, DonationAmount
From Person p
Inner Join Donation d
ON p.PersonKey=d.PersonKey
Order by Lastname

--matches every record in one table
--with every record in the second table
Select LastName, Firstname,
DonationDate, DonationAmount
From Person p
Cross Join Donation d

--equi join
Select LastName, Firstname,
DonationDate, DonationAmount
From Person p, donation d
Where p.PersonKey=d.PersonKey

Select Firstname, LastName, ContactInfo
From Person p
Inner Join PersonContact pc
on p.PersonKey=pc.PersonKey
Where ContactTypeKey=6

Insert into Person (LastName, Firstname)
Values ('Depp', 'Johnny')

Insert into Donation (DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values('2/25/2010',5,52,1)

Select lastName, firstname, DonationDate, DonationAmount
From Person p
inner join Donation d
on p.Personkey=d.personkey
Where lastname='Depp'

Update Person
Set LastName='Jackson',
Firstname='Janet'
Where PersonKey=1

Select * from Person

Begin tran

Update Person
Set LastName='Jackson'

Commit Tran

Delete from Person
Where PersonKey=52

Rollback tran

Begin tran

Delete from Donation

Select * from Donation

Rollback tran

Magazine Subscription Stored Proc

Here is the stored procedure we did in class

Alter Procedure usp_NewSubscription
@CustLastName varchar(30),
@CustFirstName varchar(25),
@CustAddress varchar(100),
@CustCity varchar(50),
@CustState char(2),
@CustZipcode char(11),
@CustPhone char(10),
@Magazine varchar(100),
@SubscriptTypeID int,
@StartDate DateTime
As
Begin tran
--the as keyword starts the body of the procedure
--The first thing we will do is insert the customer
Begin try
--declare the customerid as a variable
Declare @CustID int
--test to see if customer exists by matching all the fields
if exists
(Select CustLastName, CustFirstName, CustAddress, CustCity, CustState, CustZipcode, CustPhone
From Customer
Where CustLastName=@CustLastName
And CustFirstName=@CustFirstName
And CustAddress=@CustAddress
And CustCity= @CustCity
And CustState= @CustState
And CustZipcode= @CustZipcode
And CustPhone=@CustPhone)
Begin --if customer does exist
--get the customer id of the existing customer
Select @CustID=Custid
From Customer
Where CustLastName=@CustLastName
And CustFirstName=@CustFirstName
And CustAddress=@CustAddress
And CustCity= @CustCity
And CustState= @CustState
And CustZipcode= @CustZipcode
And CustPhone=@CustPhone
End
Else --if it doesn't exist
Begin
--insert the customer
Insert into Customer(CustLastName, CustFirstName, CustAddress, CustCity, CustState, CustZipcode, CustPhone)
Values(
@CustLastName,
@CustFirstName,
@CustAddress,
@CustCity,
@CustState,
@CustZipcode,
@CustPhone)
--the user has provided all these values as parameters
--now we get the new CustID created by the insert

Set @CustID=@@Identity
End --end of else
--Next we look for the MagID using the magazine Name

Declare @MagID int
Select @MagID=Magid from Magazine where MagName=@Magazine

--with the magid and the subscription type (which was provided as a parameter
--we can look up the magdetid which is what we need for
--the insert into subscription

Declare @magDetID int
Select @magDetID=MagDetId
From MagazineDetail
where MagID=@MagID
And SubscriptTypeID=@SubscriptTypeID
--test to see if subscription exists or not
If not exists
(Select SubscriptionID from Subscription
Where MagDetID=@MagDetID
And CustID=@CustID
And SubscriptionEnd < GetDate())
Begin
--if it doesn't already exit insert the new subscription
Insert into Subscription
(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values
(@CustID, @MagDetID, @StartDate,
dbo.func_SubscriptionEndDate(@MagDetID, @StartDate))
end
--this will happen if there are no errors above
Commit tran

End try
Begin Catch
--if there are errors do this
Rollback tran
print error_message()
End Catch

SQL

Here is the SQL we did in class with CommunityAssist::

Use CommunityAssist
/*******************
script from 2/23/2010
********************/
Select Lastname, firstname
From Person

Select * From Person

--this only returns unique values
Select Distinct PersonKey from Donation
Order by PersonKey Desc

Select DonationAmount, DonationAmount * .9 As [to Charity]
From Donation

Select * From PersonAddress
Where Not City='Seattle'

Select *
from PersonAddress
Where Apartment
is not Null

Select * from Employee
Where Hiredate > '1/1/2003'

Select * from Employee
Where Dependents >2

Select top 3 Lastname from Person
Order by LastName

Select LastName from Person
Where Lastname LIKE '_a%'

--aggregate functions
Select Count(*) from Donation

Select sum(DonationAmount) From Donation

Select Max(DonationAmount) from Donation

Select MIN(DonationAmount) from Donation

Select Round(Avg(DonationAmount),2)
from Donation

Select Month(DonationDate) as [Month],
Sum(DonationAmount) as Total
From Donation
Group by Month(DonationDate)

Select EmployeeKey,
Sum(DonationAmount) as Total
From Donation
Group by EmployeeKey

State (Cookies, sessions etc.)

Here is the code on the form side (Where you enter the data)with all three different methods


Partial Class Default2
Inherits System.Web.UI.Page

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
' QueryString()
'SessionMethod()
CookieMethod()
End Sub

Protected Sub QueryString()
Response.Redirect("Default3.aspx?Firstname=" & txtFirstName.Text & "&Lastname=" & txtLastName.Text)
End Sub

Protected Sub SessionMethod()
Session("First") = txtFirstName.Text
Session("Last") = txtLastName.Text
Response.Redirect("Default3.aspx")
End Sub

Protected Sub CookieMethod()
Dim chocolateChip As New HttpCookie("sugar")
chocolateChip("first") = txtFirstName.Text
chocolateChip("last") = txtLastName.Text
chocolateChip.Path = "C:\Sugar.txt"
Response.Cookies.Add(chocolateChip)

Response.Redirect("Default3.aspx")
End Sub
End Class

Here is the code from the receiving side:


Partial Class Default3
Inherits System.Web.UI.Page


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'GetQueryString()
'GetSession()
ReadCookie()
End Sub

Protected Sub GetQueryString()
lblFirstName.Text = Request.QueryString("FirstName")
lblLastName.Text = Request.QueryString("LastName")
End Sub

Protected Sub GetSession()
If Session("First") <> Nothing Then
lblFirstName.Text = Session("First")
lblLastName.Text = Session("Last")
End If
End Sub

Protected Sub ReadCookie()
Dim cookie As HttpCookie = Request.Cookies("C:\sugar.txt")
lblFirstName.Text = cookie("first")
lblLastName.Text = cookie("last")
End Sub
End Class

Master pages Example

I attached the Master Pages example as a Zip file to the ITC 172 Syllabus. You can download it extract it and then open it to view the files and code

Stored Procedure

First of all DON"T PANIC! stored procedures do have a bit of a learning curve. They are more like traditional programming. The first thing to do (always) is to be clear about what it is you are trying to do with the procedure. In our procedure we want to Add a new subscription to do this we must

• Get all the parameters (values) we need to insert into customer


• Get the Magazine name and the subscription type (is it for one year or 5 months etc.


• Insert the new customer


• Get the new customerId. It is an identity and we can use the built in variable
  @@Identity


• Use the magazine name to get the magID for the magazine


• Use the magid and the subscription type to get the MagDetID from the magazineDetail table


• use the Information we got to insert the Subscription. (I also used the func_EndDate to get the end date for the subscription

So here is the procedure: First Name the procedure and provide all the parameters you need. You can get them by looking at the columns each table needs for the inserts

Create Procedure usp_NewSubscription
@CustLastName varchar(30),
@CustFirstName varchar(25),
@CustAddress varchar(100),
@CustCity varchar(50),
@CustState char(2),
@CustZipcode char(11),
@CustPhone char(10),
@Magazine varchar(100),
@SubscriptTypeID int,
@StartDate DateTime
As
--the as keyword starts the body of the procedure
--The first thing we will do is insert the customer
Insert into Customer(CustLastName, CustFirstName, CustAddress, CustCity, CustState, CustZipcode, CustPhone)
Values(
@CustLastName,
@CustFirstName,
@CustAddress,
@CustCity,
@CustState,
@CustZipcode,
@CustPhone)
--the user has provided all these values as parameters
--now we get the new CustID created by the insert
Declare @CustID int
Set @CustID=@@Identity

--Next we look for the MagID using the magazine Name

Declare @MagID int
Select @MagID=Magid from Magazine where MagName=@Magazine

--with the magid and the subscription type (which was provided as a parameter
--we can look up the magdetid which is what we need for
--the insert into subscription

Declare @magDetID int
Select @magDetID=MagDetId
From MagazineDetail
where MagID=@MagID
And SubscriptTypeID=@SubscriptTypeID

--now we can do the acutal insert into subscription
--I use the function we created in class earlier
--for the end date

Insert into Subscription
(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values
(@CustID, @MagDetID, @StartDate,
dbo.func_SubscriptionEndDate(@MagDetID, @StartDate))

--this is the end of the procedure so far

So, as is, this isn't much of a procedure. We still need to couch it in a transaction
so that it either all happens or non of it happens. To do that we will need to add a try catch structure. We will also modify it to check if the customer already exists. If they do we will only write the subscription, if not we will write both the customer and the subscription, But as I said at the top of the post. DONT PANIC. We will work through it slowly, step by step.

DVD ERD

Here is the ERD for the DVD database we did in class

Views, Alter tables and Indexes

Here is the stuff on Views and altering tables


Create table test
(
testID int identity(1,1) primary key,
test1 nchar(20) not null unique,
testGrade decimal(10,2),
--Constraint test1_unique unique(test1),
Constraint ck_Grade check(testGrade between 0 and 4)
)

Alter table test
Add Constraint test1_unique unique(test1)

Alter table Test
Add Constraint ck_Grade check(testGrade between 0 and 4)

Insert into Test(test1, testGrade)
Values('stuff', 3.9)

Insert into Test(test1, testGrade)
Values('And more stuff', 3.9)

Alter Table Test
Drop Constraint UQ__test__0BC6C43E

Select * from Test

Delete from test

Drop table test


Create View vw_Subscriptions
As
Select CustLastName as [Last Name],
CustFirstName as [First Name],
Magname as [Magazine],
SubscriptionStart as [Start Date],
SubscriptionEnd as [End Date]
From Customer c
inner join Subscription s
On c.custid=s.custid
inner join MagazineDetail md
on s.magdetID=md.magdetid
inner join Magazine m
on m.magid=md.magid



Select * from vw_subscriptions
Where magazine='IT Toys'
order by [last name]

Select * from vw_Subscriptions
Where custlastname='able'


Create Index ix_lastname on Customer (CustLastName, CustFirstName)

Create unique index ix_unique on Customer(CustPhone)

Drop index ix_unique on Customer

Create clustered index ix_clustered on Customer(CustLastName)

More functions

Select dbo.func_cube(SubscriptTypeID) from MagazineDetail

Alter function func_SubscriptionEndDate
--parameters entered by the user
(@MagDetID int, @StartDate datetime)
Returns datetime --return type
As
Begin
Declare @type int --declare a variable to get the subscription type
Select @type = SubscriptTypeID from MagazineDetail
Where magDetID=@magDetID --query magazinedetail table to get subscription type
Declare @enddate Datetime --declare variable for end date
Select @enddate =
Case --choose end date based on subscription type
When @type=1 then dateadd(mm,6,@startdate)
When @type=2 then dateadd(yy,1,@startDate)
When @type=3 then dateadd(mm,3,@startDate)
When @type=4 then dateadd(yy,2,@startDate)
When @type=5 then dateadd(yy,5,@startDate)
When @type=6 then dateadd(yy,3,@startDate)
Else GetDate()
end
Return (@EndDate)--return the resulting endate
End

Select dbo.func_SubscriptionEndDate(1, GetDate())

Select * from MagazineDetail

Insert into Subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values(2,6,GetDate(), dbo.func_SubscriptionEndDate(6,GetDate()))

Select * from Subscription where custid=2


Alter function func_SalesTax
(@saleprice money, @rate decimal(6,3))
--rate as decimal
returns money
As
Begin
if @rate >=1
Begin
set @rate=@rate/100
End
Declare @tax money
set @Tax=@salePrice * @rate
Return @Tax
end



Select SubscriptionPrice, dbo.func_SalesTax(SubscriptionPrice, 1212.00) as tax from MagazineDetail

Functions

Here is a script for creating functions:

/***********************************************
SCALAR FUNCTIONS
**********************************************/

use MagazineSubscription

/***********PARAMETERS AND VARIABLES********************
all parameters and variables in SQL server begin with the @
@var, @leasenumber, @total
Then they are given a data type
@var int
Parameters are values that must be passed to the function or procedure
when it is called
SELECT dbo.func_SubscriptionEndDate(5,'4/4/2007')
Variables are internal to functions and procedures.
They are not provided by the user
Internal variables are declared with the 'DECLARE' keyword
DECLARE @end datetime
They can be assigned values either with "SET" or "SELECT"
SET @end =GetDate()
SELECT @end=startdate from Subscription
*********************************************************/

--here is a simple function that cubes an integer
Create Function func_cube
(@num int) --parameter
returns Int --return type
As --start definition
Begin --begin function body
Declare @cube int --dclare an internal variable
Set @cube = @num *@num *@num --assign it a value
Return (@cube) --return it
End --end function body

--use the function
Select dbo.func_cube(23)

/*Here is a function that takes the subscription type
and the beginning date and returns an end date
for the subscription. The first time you run a function or procedure
you "CREATE" it, Aftwards, When you make changes you "ALTER" it */
Create function func_SubscriptionEndDate
(@type int, @start DateTime) --parameters of the function
Returns Datetime --return type
As --start function definition
Begin --begin body of the function
Declare @end datetime --declare an internal variable
/* the following case structure uses the dateadd function
to add the appropriate units to the original date */
Select @end=
case
When @type =1 then dateAdd(mm,6,@start)
when @type =2 then dateAdd(yy,1,@start)
When @type =3 then dateAdd(mm,3,@start)
when @type =4 then dateadd(yy,2,@start)
when @type =5 then dateAdd(yy,5, @start)
when @type =6 then dateAdd(yy,3,@start)
Else GetDate()
end --end the case
return (@end)
End -- end the function

--use the function with literal values you must
--always specify the schema with a user defined function
--the default is dbo database owner
Select dbo.func_SubscriptionEndDate(5,'4/4/2007')


--use the function in a real query
Select SubscriptionID, CustID, SubscriptionStart,
dbo.func_SubscriptionEndDate(SubscriptTypeID, Subscriptionstart)as EndDate
From Subscription s
Inner Join MagazineDetail md
on s.magdetid=md.magdetid

Here is a function to determine the end date of a subscription

USE [MagazineSubscription]
GO
/****** Object: UserDefinedFunction [dbo].[func_SubscriptionEndDate] Script Date: 02/23/2009 11:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[func_SubscriptionEndDate]
(@type int, @start DateTime) --parameters of the function
Returns Datetime --return type
As --start function definition
Begin --begin body of the function
Declare @end datetime --declare an internal variable
/* the following case structure uses the dateadd function
to add the appropriate units to the original date */
Select @end=
case
When @type =1 then dateAdd(mm,6,@start)
when @type =2 then dateAdd(yy,1,@start)
When @type =3 then dateAdd(mm,3,@start)
when @type =4 then dateadd(yy,2,@start)
when @type =5 then dateAdd(yy,5, @start)
when @type =6 then dateAdd(yy,3,@start)
Else GetDate()
end --end the case
return (@end)
End

Donation Site code

Here is the code for the Donation site with comments. First the web form. There are no changes here:

<%@ 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>
<p>Enter your information</p>
<asp:Table ID="Table1" runat="server" Width="248px">
<asp:TableRow>
<asp:TableCell>Last Name</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>First Name</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>Address</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>City</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>State</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtState" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>Zip code</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtZipcode" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>

<asp:TableRow>
<asp:TableCell>Home Phone</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtHomePhone" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>

<asp:TableRow>
<asp:TableCell>Email</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>

<asp:TableRow>
<asp:TableCell>Donation Amount</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtDonation" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>

<asp:TableRow>
<asp:TableCell> </asp:TableCell>
<asp:TableCell>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" />
</asp:TableCell>
</asp:TableRow>

</asp:Table>
</div>
</form>
</body>
</html>

Here is the code for Default.aspx.vb

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Try
Dim dude As New Person
dude.PersonFirstName = txtFirstName.Text
dude.PersonLastName = txtLastName.Text
dude.PersonAddress = txtAddress.Text
dude.PersonCity = txtCity.Text
dude.PersonState = txtState.Text
dude.PersonZipcode = txtZipcode.Text
dude.PersonHomePhone = txtDonation.Text
dude.PersonEmail = txtEmail.Text

Dim gift As New Donation
gift.DonationDate = DateTime.Now
gift.DonationAmount = Double.Parse(txtDonation.Text)

Dim data As New DataLayer(dude, gift)

Response.Write("thanks for the donation")
Catch ex As Exception
Response.Write(ex.Message)
End Try

End Sub
End Class

Now here is the Person class--no changes here:

Imports Microsoft.VisualBasic

'*****************************************
'This class ecnapsulates a person, including
'the address and contacts
'it consists only of fields (private class
'variables and properties that expose those
'variables to other classes
'****************************************
Public Class Person

'private class level variables (fields)
Private firstName As String
Private lastName As String
Private address As String
Private city As String
Private state As String
Private zipcode As String
Private homePhone As String
Private email As String
Private PKey As Integer

'public properties

Public Property PersonFirstName() As String
Get
Return FirstName
End Get
Set(ByVal value As String)
FirstName = value
End Set
End Property

Public Property PersonLastName() As String
Get
Return LastName
End Get
Set(ByVal value As String)
LastName = value
End Set
End Property

Public Property PersonAddress() As String
Get
Return address
End Get
Set(ByVal value As String)
address = value
End Set
End Property

Public Property PersonCity() As String
Get
Return city
End Get
Set(ByVal value As String)
city = value
End Set
End Property

Public Property PersonState() As String
Get
Return state
End Get
Set(ByVal value As String)
'in this porpery I validated the input
'if it is longer than 2 characters
'throw an error message back
'to the calling method
If value.Length > 2 Then
Dim ex As New Exception("use two letters for a state")
Throw ex
Else
state = value
End If
End Set
End Property

Public Property PersonZipcode() As String
Get
Return zipcode
End Get
Set(ByVal value As String)
zipcode = value
End Set
End Property

Public Property PersonHomePhone() As String
Get
Return homePhone
End Get
Set(ByVal value As String)
homePhone = value
End Set
End Property

Public Property PersonEmail() As String
Get
Return email
End Get
Set(ByVal value As String)
email = value
End Set
End Property

Public Property PersonKey() As Integer
Get
Return PKey
End Get
Set(ByVal value As Integer)
PKey = value
End Set
End Property


End Class

Here is the donation class--also no changes

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel

'**************************************
'this class encapsulates a donation
'it consists of three fields and
'three properties
'*************************************
Public Class Donation

Private amount As Double
Private donDate As Date
Private Pkey As Integer

Public Property DonationAmount() As Double
Get
Return amount
End Get
Set(ByVal value As Double)
amount = value
End Set
End Property

Public Property DonationDate() As Date
Get
Return donDate
End Get
Set(ByVal value As Date)
donDate = value
End Set
End Property

Public Property DonationPersonKey() As Integer
Get
Return Pkey
End Get
Set(ByVal value As Integer)
Pkey = value
End Set
End Property
End Class

Here is the datalayer class. The one thing that has changed is the WriteDonation method is called in the constructor. As noted in the previous post, this is where the error was.

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data 'import generic data objects
Imports System.Data.SqlClient 'import sql server specific objects

Public Class DataLayer

Private pers As Person
Private don As Donation
Private connect As SqlConnection

Public Sub New(ByRef p As Person, ByRef d As Donation)
'assign the passed objects to the internal fields
pers = p
don = d
'instantiate the sqlconnection string to enable
'connections to the database
connect = New SqlConnection("Data source=.\sqlexpress;initial catalog=communityAssist;integrated security=true;")
'call the method that actually writes the data
writeDonation()

End Sub

Sub writeDonation()
'*******************************************
'this method takes the values stored in the
'person and donation objects and writes them
'to the database. If you are clever you could
'break this method into smaller methods
'each of which does just one thing
'here we did it together so the pattern is
'cleare
'*******************************************

'this will insert into person
'first set up the sql insert statement. the @last, etc are
'sql variables
Dim PersonSql As String = "Insert into Person(lastName, Firstname) Values(@last, @first)"
'instantiate the command object and pass the sql and connection to its constructor
Dim personCommand As New SqlCommand(PersonSql, connect)
'Create parameters to store and assign values to the variables in the sql script
personCommand.Parameters.AddWithValue("@Last", pers.PersonLastName)
personCommand.Parameters.AddWithValue("@first", pers.PersonFirstName)

'will get the new PersonKey
'the @@identiy is a built in SQL Server variable
'that returns the last identity (autonumber)
'generated by the server--this will be the personkey
'from the person table where we just inserted a record
'we need the personkey to tie the address,
'contact information, and donation to the person
Dim personKeySql As String = "Select @@identity"
Dim personKeyCommand As New SqlCommand(personKeySql, connect)

'inserts into PersonAddress
Dim addressSQL As String = "Insert into PersonAddress(street, city, state, zip, personKey) Values(@street, @city, @state,@zip, @personKey)"
Dim addressCommand As New SqlCommand(addressSQL, connect)
addressCommand.Parameters.AddWithValue("@street", pers.PersonAddress)
addressCommand.Parameters.AddWithValue("@City", pers.PersonCity)
addressCommand.Parameters.AddWithValue("@state", pers.PersonState)
addressCommand.Parameters.AddWithValue("@zip", pers.PersonZipcode)
addressCommand.Parameters.Add("@PersonKey", SqlDbType.Int)

'insert into contact for home phone
Dim homePhoneSql As String = "Insert into PersonContact(ContactInfo, personKey, contactTypeKey)Values(@ContactInfo, @personKey, 1)"
Dim homephoneCommand As New SqlCommand(homePhoneSql, connect)
homephoneCommand.Parameters.AddWithValue("@ContactInfo", pers.PersonHomePhone)
homephoneCommand.Parameters.Add("@PersonKey", SqlDbType.Int)

'inserts into contact for email
Dim emailsql As String = "Insert into PersonContact(ContactInfo, personKey, contactTypeKey)Values(@ContactInfo, @personKey, 6)"
Dim emailCommand As New SqlCommand(emailsql, connect)
emailCommand.Parameters.AddWithValue("@ContactInfo", pers.PersonEmail)
emailCommand.Parameters.Add("@PersonKey", SqlDbType.Int)

'inserts into donation
Dim donationSql As String = "insert into donation(donationdate, donationamount,Personkey)Values(@date, @amount, @PersonKey)"
Dim donationCommand As New SqlCommand(donationSql, connect)
donationCommand.Parameters.AddWithValue("@Date", don.DonationDate)
donationCommand.Parameters.AddWithValue("@Amount", don.DonationAmount)
donationCommand.Parameters.Add("@PersonKey", SqlDbType.Int)

'now that everything is set up we will open
'the connection to the database.
'if there are any problems with the connection string
'the error will fall here
connect.Open()

Dim pk As Integer 'to store the personKey

'the transaction object is used to group
'all the statements into one thing
'either all of them happen or none
'of them happen
Dim tran As SqlTransaction
tran = connect.BeginTransaction

'here we assign the new transaction
'to all the sqlcommands
personCommand.Transaction = tran
personKeyCommand.Transaction = tran
addressCommand.Transaction = tran
homephoneCommand.Transaction = tran
emailCommand.Transaction = tran
donationCommand.Transaction = tran

'try the following code and catch any errors
Try
'first we insert the person
'ExecuteNonQuery is for any command
'that doesn't return results
personCommand.ExecuteNonQuery()

'now we get the person key. ExecuteScaler
'is used for queries that will return only
'a single value. the result an integer
'so we cast it CInt to an integer
pk = CInt(personKeyCommand.ExecuteScalar)

'Now we assign the value of personkey
'to the parameters of the
'various commands
addressCommand.Parameters("@PersonKey").Value = pk
homephoneCommand.Parameters("@PersonKey").Value = pk
emailCommand.Parameters("@PersonKey").Value = pk
donationCommand.Parameters("@PersonKey").Value = pk

'Now we execute each of the remaining commands
addressCommand.ExecuteNonQuery()
homephoneCommand.ExecuteNonQuery()
emailCommand.ExecuteNonQuery()
donationCommand.ExecuteNonQuery()

'if we don't have an error by now
'commit them all
tran.Commit()
Catch ex As Exception
'if there is an error undo it all
tran.Rollback()
'throw the error back to the form
Throw ex
Finally
'no matter what, close the connections
connect.Close()
End Try


End Sub


End Class

It all works on my machine

The ADO Example Fix

Speak of brain farts. . . I figured out why the code we did in class didn't work and why it didn't throw any errors. We forgot to call the method that actually writes the data to the database. I added a call to the method in the constructor of the DataLayer class. Here is the code for the constructor now.

Public Sub New(ByRef p As Person, ByRef d As Donation)
pers = p
don = d
connect = New SqlConnection("Data source=.\sqlexpress;initial catalog=communityAssist;integrated security=true;")
'we forgot to call the method that actually writes the data
writeDonation()

End Sub

I will post the rest of the code before the weekend is over.

VB Classes

Here is what we did with the form. We created a table with text fields and a button. We will use this eventually to insert data and populate the People and Donation classes:

<%@ 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>
<p>Enter your information</p>
<asp:Table ID="Table1" runat="server" Width="248px">
<asp:TableRow>
<asp:TableCell>Last Name</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>First Name</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>Address</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>City</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>State</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtState" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>Zip code</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtZipcode" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>

<asp:TableRow>
<asp:TableCell>Home Phone</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtHomePhone" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>

<asp:TableRow>
<asp:TableCell>Email</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>

<asp:TableRow>
<asp:TableCell>Donation Amount</asp:TableCell>
<asp:TableCell>
<asp:TextBox ID="txtDonation" runat="server"></asp:TextBox>
</asp:TableCell>
</asp:TableRow>

<asp:TableRow>
<asp:TableCell> </asp:TableCell>
<asp:TableCell>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" />
</asp:TableCell>
</asp:TableRow>

</asp:Table>
</div>
</form>
</body>
</html>

Now here is the Person class. It consists soley of private fields and public properties. Its purpose it to store the data for a new person:

Imports Microsoft.VisualBasic

Public Class Person

Private firstName As String
Private lastName As String
Private address As String
Private city As String
Private state As String
Private zipcode As String
Private homePhone As String
Private email As String
Private PKey As Integer



Public Property PersonFirstName() As String
Get
Return FirstName
End Get
Set(ByVal value As String)
FirstName = value
End Set
End Property

Public Property PersonLastName() As String
Get
Return LastName
End Get
Set(ByVal value As String)
LastName = value
End Set
End Property

Public Property PersonAddress() As String
Get
Return address
End Get
Set(ByVal value As String)
address = value
End Set
End Property

Public Property PersonCity() As String
Get
Return city
End Get
Set(ByVal value As String)
city = value
End Set
End Property

Public Property PersonState() As String
Get
Return state
End Get
Set(ByVal value As String)
If value.Length > 2 Then
Dim ex As New Exception("use two letters for a state")
Throw ex
Else
state = value
End If
End Set
End Property

Public Property PersonZipcode() As String
Get
Return zipcode
End Get
Set(ByVal value As String)
zipcode = value
End Set
End Property

Public Property PersonHomePhone() As String
Get
Return homePhone
End Get
Set(ByVal value As String)
homePhone = value
End Set
End Property

Public Property PersonEmail() As String
Get
Return email
End Get
Set(ByVal value As String)
email = value
End Set
End Property

Public Property PersonKey() As Integer
Get
Return PKey
End Get
Set(ByVal value As Integer)
PKey = value
End Set
End Property


End Class


Now we have the Donation Class:

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel

Public Class Donation

Private amount As Double
Private donDate As Date
Private Pkey As Integer

Public Property DonationAmount() As Double
Get
Return amount
End Get
Set(ByVal value As Double)
amount = value
End Set
End Property

Public Property DonationDate() As Date
Get
Return donDate
End Get
Set(ByVal value As Date)
donDate = value
End Set
End Property

Public Property DonationPersonKey() As Integer
Get
Return Pkey
End Get
Set(ByVal value As Integer)
Pkey = value
End Set
End Property
End Class

Finally we have just the beginnings of the DataLayer Class. All we have done so far is import the Data and Data.SqlClient namespaces, added to object fields to recieve a person and a donation, and created a constructor with which to pass them to the class

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient

Public Class DataLayer

Private pers As Object
Private don As Object


Public Sub New(ByRef p As Person, ByRef d As Donation)
pers = p
don = d

End Sub



End Class

Create Tables

Here is a script for Creating tables. To look at all the data types supported by SQL Server you can go to MSDN Help Data Types

/*********************************
First we will create a database to store our new tables
*************************************/
Create database Gradebook

/*now use that database*/
Use Gradebook

/*Below are the create table commands
I have shown many alternate ways to create constraints,
both within and outside the tables */
Create table Student
(
StudentID nchar(9) Primary key,
StudentLastName nvarchar(255) not null,
StudentFirstName nvarchar(255) null
)



Create table Course
(
CourseNumber nchar(7) not null,
CourseName nvarchar(255) not null,
CourseCredits int default 5,
Constraint pk_Course primary Key (CourseNumber)

)

Create table Session
(
SessionID int identity(1,1) not null,
CourseNumber nchar(7) Foreign Key references Course(courseNumber),
SessionQuarter nchar(7) check (SessionQuarter in ('Fall', 'Winter', 'Spring', 'Summer')),
SessionYear nchar(4) not null,
Section int default 1
)

Alter table Session
Add Constraint PK_Session Primary Key(SessionID)

Create Table SessionStudent
(
SessionID int not null,
CourseNumber nchar(7) not null,
SessionStudentGrade decimal(2,1) null,
SessionStudentNotes xml

)

/*various alter table commands*/
Alter table SessionStudent
Add Constraint PK_SessionStudent primary key(SessionID, CourseNumber)

Alter Table SessionStudent
Add Constraint FK_Session foreign key (SessionID) references Session(SessionID)

Alter Table SessionStudent
Add Constraint FK_Course Foreign Key(CourseNumber) References Course(CourseNumber)

Alter table SessionStudent
Add Constraint chk_Grade Check(SessionStudentGrade between 0 and 4)


Alter table SessionStudent
Drop Constraint chkGrade

Alter table Session Student
Drop column SessionStudentNotes

Alter table SessionStudent
Add SessionStudentNotes xml

Inserts

Here is the code for the inserts and the last subquery problem

Begin tran
Insert into Customer(CustLastName, CustFirstName, CustAddress, CustCity, CustState, CustZipcode, CustPhone)
Values ('Bateman', 'Py', '1000 Somewhere Street', 'Seattle', 'WA', '98122','2065551245')
Declare @Custid int
Set @CustID=@@identity
Insert into Subscription (CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values(@custID, 16, '2/1/2010', dateAdd("yy",3, '1/1/2010'))
Commit tran
Create table Contact
(
Firstname varchar(25),
Lastname varchar(30),
Phone char(10)
)
Insert into Contact(Firstname, LastName, Phone)
(Select CustFirstName, CustLastName, CustPhone from Customer)
Select * from Contact
Select * from Customer
Select * from Subscription

Select * from Magazine
Select * From MagazineDetail
Select * from SubscriptionType
Begin tran
Update Contact
Set Lastname='Smith',
FirstName='Joe'
Where Firstname='Steve'
And lastname='Conger'
Select * from MagazineDetail
Update MagazineDetail
Set subscriptionPrice = subscriptionPrice *1.1
Select * from Contact
rollback tran
Begin tran
Delete from Contact
Where Firstname='Steve'
and Lastname='conger'
Delete from Customer
Where Custid=1

Select month(DonationDate) as "Month",
sum(donationAmount) as "Monthly",
(Select Sum(donationAmount) from Donation) as "Total",
(sum(DonationAmount) / (Select Sum(donationAmount) from Donation))*100 as "Percent"
From Donation
Group By month (donationDate)