BMIS 289: Spring 2002 Gonzaga University

1
BMIS 289 Spring 2002Gonzaga University

• Class 13
• GetCMD, Misc ASP Topics

2
Tonights Agenda

• Final Projects
• Final Exam
• Program 10
• GetCMD
• A structured, centralized method for handling
  stored procedures.
• Misc. ASP Topics

3
Final Project Milestone Dates

• April 24th Second version of your code done.
  Final testing to verify correctness and locate
  any last bugs. Begin working on presentation.
• May 1st Final project presentations.
• If you feel you would like an extra week to
  prepare for we can postpone presentations until
  May 8th at 8pm.
• If we do that, however, we will have to postpone
  all presentations until that date.
• In addition, the final exam will still be due
  back by May 8th.

4
Final Project - Presentation

• Target for about 15-20 minutes.
• You may take longer if needed
• Be sure to cover these points
• Purpose of your web application
• Design of the overall application and its
  database
• Your code
• Challenges, issues you faced when writing this
  application.
• Be organized, polished, and professional
• Visual aides are always nice (e.g., Power Point)
• Be ready for questions.

5
Final Project What is turned in

• A one page summary of your final web application.
• What is its purpose?
• What functionality does it have?
• How did you construct it?
• What challenges did you face when constructing
  it?
• Copies of your code, databases, and any
  additional files that make up your web
  application.
• Please be sure to retain directory structures on
  the items you hand in.
• Include a readme file if there are any special
  installation instructions.
• If everything is too big to fit on one floppy
  then you may email me the files (zip them up).
• Or you could burn a CD.

6
Final Project Getting Help

• Standard warning
• Dont wait until the last minute. Have everything
  lined up and ready to go so you can have a
  polished presentation.
• If you need assistance with anything you can call
  or email me.
• But please keep in mind that my time can be
  limited so I cant deal with 10 huge problems
  that flood in at the last minute.
• Its your responsibility to keep on the ball.

7
Final Exam

• You will be emailed a Word document that contains
  the final exam by next Wednesday.
• Format will be the same as the midterm. Questions
  will cover the entire course contents.
• You may enter your answers into the Word document
  and email it back to me.
• You may also print out the final, fill in the
  answers, and turn the final into the Business
  School secretaries (have them put it in my
  mailbox).
• Either way you must get the final back to me by
  May 8.

8
Program 10

• Requirements
• Create a database with two tables
• Employees
• Jobs
• Generate reports from SQL statements
• Search by employee first and last name.
• List of all employees sorted by job title.
• Listing of all employees within a certain salary
  range.
• Insert, Update, and Delete employee record.

9
Program 10 Company database

• Created in Microsoft Access
• The Employees and Jobs table are linked with each
  other via the JobID field.
• JobID is the primary key for the Jobs table, and
  is also a foreign key in the Employees table.
• In other words, JobID is a foreign key of the
  Employees table.

10
Program 10

• How I organized the program
• Main.asp the main program interface.
• Search.asp performs all record lookups.
• AddEmployee.asp adds a new employee record.
• UpdateDeleteEmp.asp table for updating/deleting
  employee records.
• UpdateEmp.asp Performs update on an employee
  record.
• DeleteEmp.asp Performs deletion of an employee
  record.

11
Program 10 Main.asp

• This page is just a series of HTML forms and
  links to activate the other pages in the
  application.
• In a professional application we would want to
  scale down this page as much as possible.
• Each page should be limited to one or two logical
  functions.
• Ideally a menu bar would run across the top of
  every page.

12
Program 10 Search.asp

• This page performs one of three separate record
  lookups based on the value of a querystring
  variable called id.
• All searches are performed by dynamically
  building a SQL statement and then executing it
  and fetching the records it returns.
• Tip if youre unsure of how a particular SQL
  statement should be formed use Access to build it
  and then look at the SQL it generated.

13
Program 10 AddEmployee.asp

• This page actually has two sections, but only one
  section will execute at a given time
• The new employee input form.
• The ASP code to write the new record to the
  database.
• We know which section of code to execute by
  checking to see if a form has been posted to this
  page.
• If a form has been posted then execute the code
  to retrieve the form values and create new
  record.
• Otherwise show the form for adding a new employee
  record.

14
Program 10 UpdateDeleteEmp.asp

• This page generates a table of all the Employee
  records in the database.
• On each row there are hotlinks to delete or
  update that particular record.
• Those hotlinks call either the delete or update
  employee record ASP pages. They also have that
  records employee id appended as a querystring.
• By passing the records employee id (primary key)
  we will know which particular record we want to
  delete or update.

15
Program 10 DeleteEmp.asp

• The first thing this page does is grab the
  employee id from the querystring.
• It then checks to see if a value was indeed
  obtained from the querystring
• If the value was obtained then it attempts to
  delete that record by building a delete SQL
  statement and executing it against a database
  connection.
• If the value was not obtained then an error
  message is outputted.

16
Program 10 UpdateEmp.asp

• Gets the employee id from the querystring (if
  its not there redirect back to
  UpdateDeleteEmp.asp)
• Next, check to see if an Update command was
  issued via the querystring.
• If it was then we assume a form has also been
  posted to this page with new values to update the
  record with.
• If not we assume the user wants to update the
  record so we call a custom sub that outputs an
  HTML form for updating an employee record, with
  the fields pre-filled with the records current
  values.

17
Stored Procedure Recap

• Stored Procedures allow us to store our SQL
  queries inside the actual database, rather than
  hard coding directly into ASP pages.
• This saves on long messy SQL statements and
  executes faster (because SQL is compiled in the
  database).

18
Stored Procedure Recap

• To call a stored procedure from an ASP page
• Create parameter objects for each parameter in
  the stored procedure.
• Attach the parameter object(s) to the command
  object.
• Execute the command object and fetch the records
  it returns.
• In our previous class we had an example that
  pulled employees with salaries gt a certain
  amount.

19
Structuring Commands

• Using stored procedures and command objects leads
  to many very powerful benefits (speed being the
  most obvious) but this can lead to a lot of code.
• Almost as messy as hard coding SQL statements
  directly into an ASP page.
• To combat this we can encapsulate all the
  commands our ASP pages use into a single function
  called GetCmd which is stored in our
  DataStore.asp include file.

20
GetCmd

• GetCmd is a function that returns an ADO Command
  object that is set to a specific stored
  procedure, has its active connection set, and is
  preloaded with parameter values.
• The command object we get back from GetCmd should
  be ready to execute and fetch records.
• Think of GetCmd as a function for building
  command objects.

21
GetCmd

• GetCmd has three arguments
• SP_Name the name of the stored procedure we want
  to build a command object for.
• Param_Array an array that contains all the
  values specific to a certain stored procedure
  (ie the one indicated by SP_Name).
• Conn an ADO Connection object that points to the
  database where the stored procedure in SP_Name is
  stored.

22
Implementing GetCmd

• Based on the function arguments you probably have
  an idea about how this function might be
  implemented.
• The main body of the code is a big SELECT CASE
  statement whose driving value is the SP_Name
  argument.
• When a match is made the the command object is
  initialized with the necessary data for that
  particular stored procedure.

23
Implementing GetCmd

• Initializing the command object consists of two
  primary steps
• Set the CommandText property to the stored
  procedure name (ie the name you matched in the
  case statement previously SP_Name).
• Create and set the parameters for this stored
  procedure and append to the command object.
• Problem This function needs to be capable of
  handling any stored procedure our program
  requires. It is likely that our stored procedures
  will have varying numbers of parameters (0..N).
  How do we pass an unknown number of values into
  this function?

24
Implementing GetCmd

• This problem is solved by passing in all
  parameter values within an array.
• Using an array we can pass in as many values as
  we need to.
• The key is that the code passing in the array
  must place the array (parameter) values in the
  correct order.
• There is really no way to programmatically ensure
  this so there should be adequate documentation
  available to the programmer for each stored
  procedure.
• With proper documentation the programmer will
  know how to correctly set that parameter array.

25
Implementing GetCmd

• After the parameters have been set the SELECT
  CASE statement ends.
• Before returning the command object its
  ActiveConnection property should be set to the
  connection objection that was passed into the
  GetCmd function.

26
Implementing GetCmd

• One last consideration we need to make is how the
  names of the stored procedures should be
  referenced.
• It is best to code the stored procedure names as
  constants within the datastore.asp include
  file.
• These constants can then be referenced by GetCmd
  and any other ASP code that includes this file.

27
Implementing GetCmd

• I recommend following a naming convention for
  your stored procedure name constants
• SP_DB_SPName
• SP stored procedure
• DB a shorthand code for the database the stored
  procedure is located in.
• SPName a shorthand code for a particular stored
  procedure.
• Ex SP_EMP_EMP_BY_SALARY

28
Break
29
Misc. ASP Topics

• What follows is a few bread and butter ASP
  tricks and techniques that professional ASP
  developers use on the job.
• They make ASP development quicker, more powerful,
  and a lot easier.

30
Debugging SQL Statements

• Write out any SQL that is giving you problems to
  the browser before its executed
• SQL Response.Write SQLResponse.Endcmd.Execut
  e
• Check your field names
• Make sure you dont use reserved words by your
  DBMS software to name fields (e.g., use
  HireDate instead of Date)
• Enclose field/table names with spaces in
  brackets
• SELECT Emp ID FROM Employee Table
• Common syntax errors
• Not enclosing strings with single-quotes, dates
  with s, and enclosing number fields with
  single-quotes.

31
Getting Records With GetRows

• The ADO Recordset object has a method called
  GetRows
• Getrows reads a recordsets contents into a
  two-dimensional array.
• RecordArray(COL, ROW)
• This is a more efficient way of reading records
  because it is faster and uses less memory.
• However we cannot update the contents in the
  array and expect the contents of the database to
  be updated.
• Example getrows.asp

32
Counting the number of records in a table.

• The quickest and most efficient way to count the
  number of records in a given table is to do it
  through SQL.
• For example
• SELECT count() FROM publishers
• You can use the WHERE clause to qualify which
  records you want to count
• SELECT count() FROM publishers WHERE state'NY'
• Handy for outputting the number of records
  returned by a search.

33
ASP Shopping Carts

• We dont have time to go over an entire shopping
  cart system (they can get fairly complex) but we
  can discuss one simple method
• Most shopping carts are enabled via cookies.
• So when the user adds an item to their shopping
  cart they are really writing a product id to a
  cookie (along with any other information, like
  quantity).
• The checkout page then collects all the record
  ids from the cookie and reads the database to
  pull the product information out.

34
Paging Records

• Frequently, database searches yield a large
  number of results hundreds, thousands,
• Since thousands of records shouldnt be outputted
  all at once many sites break them up into small
  chunks say 10 or 20 per page.
• This is known as database paging.
• Example dbpage_input.htm, dbpage.asp

35
Determining Browser

• In a perfect world all web browsers would produce
  the exact same output for a given block of HTML
  (which is suppose to be an international
  standard).
• Unfortunately, this is seldom the case.
• Often, ASP developers have to resort to writing
  custom code that outputs specific HTML for one
  browser and a different set for another.
• One easy way to determine what kind of browser a
  visitor is using is to use the Browser
  Capabilities component.

36
Determining Browser Cont.

• The browser capabilities component is a built- in
  ASP object that determines what kind of browser a
  person is using.
• It is fairly easy to use in code, but to keep up
  with new browsers a separate .ini configuration
  file must be maintained on the server.
• Example browsecap.asp, browscap.ini
• Browser Capabilities cannot tell if certain
  features are turned on or not (in everything up
  to IIS 5.0)

37
Sending Email From ASP

• To be able to send email from an ASP page you
  need to have a component installed on your server
  that supports it.
• There are several out there (both for free and
  pay), but if you have NT Server 4 or higher then
  you probably already have CDONTS installed, which
  supports email through ASP
• Example codemail.asp (note this does not work on
  NT Workstation,Win 98, XP Pro, or 2000 Pro)

38
Retrieving ID of new record

• Frequently, we have the need to retrieve the
  record id of a record we just created in order to
  perform further processing.
• In Access 2000 and SQL Server there is a reserved
  SQL word (_at__at_Identity) that we can use to retrieve
  a new records primary key.
• The trick when using Access 2000 is that we must
  remember to use OLE-DB in our connection string,
  otherwise _at__at_Identity wont work.
• Example newrecid.asp

39
Server Variables

• Server variables are individual bits of
  information that are passed by HTTP packets when
  a browser and server communicate with each other.
• Some server variables may not be available on
  particular server software/browser combinations.
• Example servervar.asp

40
Dynamic Includes

• One common mistake most new ASP programmers make
  is trying to do things like this
• lt IF PageNum1 THEN gt lt!-- include
  file1.htm --gtltElSEIF PageNum2 THENgt lt!--
  include file2.htm --gtltEND IFgt
• The problem with this is that the includes are
  processed BEFORE the ASP so it is not possible to
  use conditional logic to process them.
• On Windows 2000 (IIS 5.0) there are methods
  called Server.Execute and Server.Transfer that
  let us get around this.

41
ASP Alternatives

• JSP
• Java Server Pages
• An extension of the Java programming language,
  written by SUN.
• PHP
• Pre-HTML Processor
• A free, open-source dynamic web language. Similar
  to Java.
• Runs on multiple platforms.
• CGI
• The very first technology available to
  dynamically generate web pages. Often used with
  the PERL programming language.
• A lot of web sites still use CGI to this day.

42
Advanced ASP Topics

• SQL Server
• An enterprise strength RDBMS.
• Site Server
• Used on Intranets
• Commerce Server
• Used to more rapidly build ecommerce sites.
• Microsoft has about a million more XYZ Server
  products aimed at specific web applications.
• Custom ASP Components

43
ASP.Net

• Next generation of ASP
• In the future you will likely encounter this
  version of ASP with any company/client you work
  for.
• The ASP we are learning now will be known as
  classic ASP.
• If you understand basic programming concepts and
  ASP then moving to ASP.Net is not that difficult.
• The first version of ASP.Net (1.0) is already
  available.

44
ASP.Net Cont.

• ASP.Net is part of a larger Microsoft initiative
  called .Net (DotNet).
• DotNet is a new development framework that is
  centered (partly) around the idea of Web
  Services.
• The idea being that all programs should be easily
  and readily available via the Internet.
• In many respects, ASP.Net is completely different
  than classic ASP.

45
ASP.Net Cont.

• There are many new benefits that go along with
  ASP.Net
• True, compiled, object-oriented programming
  languages (VisualBasic.Net, C, etc.)
• You can actually use any programming language
  that DotNet supports to code your ASP pages.
• I once saw an example that had a COBOL ASP page.
• Event-driven programming model
• Significant performance enhancements
• ADO.Net
• Extensive library of functions (full access to
  Windows API).
• Enterprise applications like Amazon.com can now
  be written by a relatively few number of
  programmers and with minimal code (well see how
  true this turns out to be)

46
ASP Resources

• LearnASP.com
• www.learnasp.com
• Good for finding small, concise lessons.
• ASP 101
• www.asp101.com
• Many beginner and advanced topics here. Articles
  are larger than LearnASP
• Ask ASP
• www.askasp.com
• Tons of ASP questions answered here.
• MSDN
• msdn.microsoft.com
• Microsoft programming resources.

47
END