Intra Internet Databases

1
Intra / Internet Databases

• Part 5 Inserts

2
Insert1A.asp
3
Insert1A-Step2.asp
4
Insert1A-Step2.asp

• lthtmlgt
• ltheadgt
• lttitlegtInsert 1Alt/titlegt
• ltlink relstylesheet type'text/css'
  href'style.css'gt
• lt/headgt
• ltbodygt
• lt
• Response.Write("lth2gtInsert 1A (Blind Faith
  Version)lt/h2gt")

5

• ' Fetch user input from previous page
• SelectedCheckNumber Request.Form("CheckNumber")
• SelectedPayDate Request.Form("PayDate")
• SelectedEID Request.Form("EID")
• SelectedGrossAmount Request.Form("GrossAmount")
• SelectedNetAmount Request.Form("NetAmount")
• ' Open connection to back-end SQL Server database
• myServer "MISSQL2005"
• myDatabase "MIS325_msbaa001"
• BackEnd "ProviderSQLOLEDB Integrated
  SecuritySSPI
• Persist Security InfoFalse Initial
  Catalog" myDatabase
• " Data Source" myServer
• Set conn Server.CreateObject("ADODB.Connection")
  
• conn.CommandTimeout 15
• conn.ConnectionTimeout 15
• conn.Open BackEnd

6

• ' Insert paycheck (trusting there are no
  errors)
• ' Define action command
• sql "INSERT INTO tblPaycheck (CheckNumber,
  PayDate, EID,
• GrossAmount, NetAmount) VALUES ("
  SelectedCheckNumber
• ", '" SelectedPayDate "', '"
  SelectedEID "', "
• SelectedGrossAmount ", "
  SelectedNetAmount ")"

sql
INSERT INTO tblPaycheck (CheckNumber,
PayDate, EID, GrossAmount, NetAmount) VALUES (
9201, '11/18/2009', 'Jimmy', 7500, 5398.04)
7

• ' Insert paycheck (trusting there are no
  errors)
• ' Define action command
• sql "INSERT INTO tblPaycheck (CheckNumber,
  PayDate, EID,
• GrossAmount, NetAmount) VALUES ("
  SelectedCheckNumber
• ", '" SelectedPayDate "', '"
  SelectedEID "', "
• SelectedGrossAmount ", "
  SelectedNetAmount ")"
• ' Execute action command (no recordset generated)
• Set ActionCommand Server.CreateObject("ADODB.Com
  mand")
• Set ActionCommand.ActiveConnection conn
• ActionCommand.CommandType 1
• ActionCommand.CommandText sql
• ActionCommand.Execute

8

• ' Release action command object
• Set ActionCommandNothing
• ' Display success message
• Response.Write("lth3gtSUCCESSlt/h3gt")
• Response.Write("ltpgtThe paycheck has been inserted
  into the database.lt/pgt")
• Response.Write("ltpgtReturn to previous page to
  insert another paycheck.lt/pgt")
• ' Close database connection
• conn.Close
• Set connNothing
• gt
• lt/bodygt
• lt/htmlgt

9
Insert1B.asp
10
Insert1B-Step2.asp
11
Insert1B.asp
12
Insert1B-Step2.asp
13
Data Validation Rules

• CheckNumber non-blank, unique number
• CheckNumber is the primary key
• PayDate non-blank, legal date
• EID non-blank, real EID
• EID is a foreign key
• GrossAmount non-blank, positive number
• NetAmount non-blank, positive number
• NetAmount lt GrossAmount

14
Insert1B-Step2.asp

• ' Fetch user input from previous page
• SelectedCheckNumber Request.Form("CheckNumber")
• SelectedPayDate Request.Form("PayDate")
• SelectedEID Request.Form("EID")
• SelectedGrossAmount Request.Form("GrossAmount")
• SelectedNetAmount Request.Form("NetAmount")
• ' Sanitize user input
• SelectedCheckNumber Trim(SelectedCheckNumber)
• SelectedPayDate Trim(SelectedPayDate)
• SelectedEID Replace(SelectedEID," ","")
• SelectedGrossAmount Trim(SelectedGrossAmount)
• SelectedNetAmount Trim(SelectedNetAmount)

15

• ' Validate user input
• Errors0
• ErrorMessage""
• ' Validation CheckNumber (non-blank, unique
  number)
• If Len(SelectedCheckNumber)0 Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The check
  number cannot be blank.ltbrgt"
• ElseIf Not IsNumeric(SelectedCheckNumber) Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The check
  number must be numeric.ltbrgt"

16

• Else
• ' Lookup CheckNumber in tblPaycheck (PK cannot
  already exist)
• sql"SELECT CheckNumber FROM tblPaycheck
  WHERE
• CheckNumber" SelectedCheckNumber
• Set rstServer.CreateObject("ADODB.Recordset"
  )
• rst.Open sql, conn
• If Not rst.EOF Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The check
  number ("
• SelectedCheckNumber ") already
  exists in tblPaycheck.ltbrgt"
• End If
• rst.Close
• Set rstNothing
• End If

17

• ' Validation PayDate (non-blank, legal date)
• If Len(SelectedPayDate)0 Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The date cannot
  be blank.ltbrgt"
• ElseIf Not IsDate(SelectedPayDate) Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The date is not
  valid.ltbrgt"
• End If

18

• ' Validation EID (non-blank, real EID)
• If Len(SelectedEID)0 Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The employee
  EID cannot be blank.ltbrgt"
• Else
• ' Lookup EID in tblEmployee (FK must exist)
• sql"SELECT EID FROM tblEmployee WHERE
  EID'" SelectedEID "'"
• Set rstServer.CreateObject("ADODB.Recordset"
  )
• rst.Open sql, conn
• If rst.EOF Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The
  employee EID (" SelectedEID
• ") does not exist in
  tblEmployee.ltbrgt"
• End If
• rst.Close
• Set rstNothing
• End If

19

• ' Validation GrossAmount (non-blank, positive
  number)
• If Len(SelectedGrossAmount)0 Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The gross
  amount cannot be blank.ltbrgt"
• ElseIf Not IsNumeric(SelectedGrossAmount) Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The gross
  amount must be numeric.ltbrgt"
• ElseIf SelectedGrossAmountlt0 Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The gross
  amount must be positive.ltbrgt"
• End If

20

• ' Validation NetAmount (non-blank, positive
  number)
• If Len(SelectedNetAmount)0 Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The net amount
  cannot be blank.ltbrgt"
• ElseIf Not IsNumeric(SelectedNetAmount) Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The net amount
  must be numeric.ltbrgt"
• ElseIf SelectedNetAmountlt0 Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The net amount
  must be positive.ltbrgt"
• End If

21

• ' Validation NetAmountltGrossAmount
• If IsNumeric(SelectedGrossAmount) And
  IsNumeric(SelectedNetAmount)
• And SelectedNetAmountgtSelectedGrossAmount
  Then
• ErrorsErrors1
• ErrorMessageErrorMessage "The net
  amount cannot exceed the
• gross amount.ltbrgt"
• End If

22

• ' Insert paycheck if there are no errors
• If Errorsgt0 Then
• ' Display error message
• Response.Write("lth3gtERRORSlt/h3gt")
• Response.Write("ltpgt" ErrorMessage
  "lt/pgt")
• Response.Write("ltpgtReturn to the previous
  page to correct the errors.lt/pgt")
• Else
• ' Define action command
• sql"INSERT INTO tblPaycheck (CheckNumber,
  PayDate, EID,
• GrossAmount, NetAmount) VALUES ("
  SelectedCheckNumber
• ", '" SelectedPayDate "', '"
  SelectedEID "', "
• SelectedGrossAmount ", "
  SelectedNetAmount ")"
• ' Execute action command (no recordset generated)
• Set ActionCommandServer.CreateObject("ADODB.
  Command")
• ( same as the first example )

23

• ' Insert paycheck if there are no errors
• If Errorsgt0 Then
• ' Display error message
• Response.Write("lth3gtERRORSlt/h3gt")
• Response.Write("ltpgt" ErrorMessage
  "lt/pgt")
• Response.Write("ltpgtReturn to the previous
  page to correct the errors.lt/pgt")
• Else
• ' Define action command
• sql"INSERT INTO tblPaycheck (CheckNumber,
  PayDate, EID,
• GrossAmount, NetAmount) VALUES ("
  SelectedCheckNumber
• ", '" SelectedPayDate "', '"
  SelectedEID "', "
• SelectedGrossAmount ", "
  SelectedNetAmount ")"
• ' Execute action command (no recordset generated)
• Set ActionCommandServer.CreateObject("ADODB.
  Command")
• ( same as the first example )

INSERT INTO tblPaycheck (CheckNumber,
PayDate, EID, GrossAmount, NetAmount) VALUES (
9202, '11/18/2009', 'Bert', 3258.50, 2438.65)
sql