ASP and ADO Continued

1
ASP and ADO Continued

• Paging Results Sets and
• Using Action Queries To Maintain Data

2
What is Paging?

• ADO has a feature that easily allows programmers
  to query a database and divide the results into
  equal size pieces that can be retrieved as a set.
  This is called paging.
• ADO Provides the following 3 properties
• PageSize To set how many rows are in each page,
  set before recordset is opened
• PageCount Determined after the recordset is
  opened calculates the total number of pages
  including the partial page of records at the end
• AbsolutePage This is used to set which page is
  brought back to the client. Pages start at 1 and
  goto PageCount.
• In most cases a self referencing form is desirable

3
Title Search Start Page
Title search example start page. One text box and
three check boxes. The default is Title. Check
boxes were chosen as being more intuitive
for users as opposed to a combo box or radio
buttons.
4
Title Search Results
This is the 2nd page notice the URL. This URL
was constructed for the 'Next 2' Hyperlink
5
Opening a recordset for use in paging
set rs server.createobject("ADODB.Recordset")
rs.ActiveConnection cDSN 'Defined
above rs.CursorType adOpenStatic 'Notice
this type rs.LockType adLockReadOnly
'Readonly (for speed) rs.cursorLocation
adUseServer 'so pages can be computed rs.PageSiz
e cRECMAX 'Number of Records/Page rs.Source
mySQL rs.Open 'Now adjust page number to be
correct if PAGE lt 1 then PAGE 1 if PAGE gt
rs.PageCount Then PAGE rs.PageCount 'Then set
the desired page If rs.RecordCount ltgt 0 Then
rs.AbsolutePage PAGE 'Display Records... (See
next slide)
6
Spinning the paged records
If rs.RecordCount 0 Then response.write
"ltpgtNo matching records, lta href'TitleSrch.Asp
'gtSearch Againlt/agtltpgt" Else Response.Write
"lttable width'100' Border'0'
cellspacing'0' cellpadding'4'gt" 'Write
out Column Names '... 'Write out all rows
values For J 1 To cRECMAX 'Catch the last
page where there are fewer then cRECMAX
records if rs.Eof Then exit for end
if 'Write out the row '... rs.Movenext
Next Response.Write("lt/tablegt") End if
7
Setting up the passed parameters
'Notice that we create the local variables from
BOTH the 'querystring() and form() Arguments.
SEARCHTrim(request.form("SEARCH"))
trim(request.querystring("SEARCH")) 'The numeric
ones demonstrate a trick for converting to a
number quickly FLAGcInt("0" Trim(request.form("
FLAG") trim(request.querystring("FLAG")))) PAGE
cInt("0" Trim(request.form("PAGE")
trim(request.querystring("PAGE")))) C1cInt("0"
Trim(request.form("C1")) Trim(request.querystrin
g("C1")) ) C2cInt("0" Trim(request.form("C2"))
Trim(request.querystring("C2")) ) C3cInt("0"
Trim(request.form("C3")) Trim(request.querystrin
g("C3")) ) 'If no flags are selected default to
title. If (C1 C2 C3) 0 then C11 'Create a
QueryString for use in the paging hyperlinks
myRETURN MakeReturn(SEARCH,C1,C2,C3)
'... 'F(X) To make the arguments for the
hyperlinks (called the return in this
example) Function MakeReturn(SearchText, F1, F2,
F3) MakeReturn "ampC1" F1 "ampC2"
F2 "ampC3" F3 "ampSEARCH"
SearchText Exit Function End Function
8
Notes on parameter passing

• Every link to a page needs to include
• Page desired (1 - Page Count)
• All of the search parameters (e.g. Search, C1-C3)
• A flag that indicates the results page is needed
• In this design, a function MakeReturn() creates
  the query string for the search parameters that
  can be appended to each of the hyper links
  generated thus allowing the query to be
  re-executed each time with the same criteria and
  different pages.
• This may seem wasteful, but the operation is
  actually fairly cheap providing the parameters
  used in the query are indexed.

9
Creating Hyperlinks

• We desire a hyperlink as on slide 4 of
• http//www.valtara.com/csc96c/aspsamples/1TitleSrc
  h.Asp?FLAG1Page2C11C21C31SEARCHcomp
• To get that we compose a querystring for the
  hyperlink using code like this
• myReturn MakeReturn()
• Response.Write ". . .lta href'TitleSrch.Asp?FLAG1
  ampPage" cStr(myPage1) myReturn "'gt . .
  ."

1. notice the server expanded the hyperlink in
the code generated when it got sent back to the
client
10
Creating the page navigation
Function DoNav(myReturn,myPage,MaxPage) dim
sret sRet "" sRet sRet "lttable border'0'
cellpadding'3' cellspacing'0'
width'100'gtlttrgt" sRet sRet "lttd
bgcolor'FFCC00' align'left'gtlta
href'TitleSrch.asp?FLAG0'gt ltfont
color'000000'gtNew Searchlt/fontgtlt/agtlt/tdgt" sRet
sRet "lttd bgcolor'FFCC00' align'left'gtlta
href'TOP'gtltfont color'000000'gtTop of
Pagelt/fontgtlt/agtlt/tdgt" if myPage gt 1 then sRet
sRet "lttd bgcolor'FFCC00' align'left'gtlta
href'TitleSrch.asp?FLAG1ampPage1"
myReturn "'gtltfont color'000000'gtFirst
Pagelt/fontgtlt/agtlt/tdgt" else sRet sRet "lttd
bgcolor'FFCC00' align'right'gtnbsplt/tdgt" end
if sRet sRet "lttd bgcolor'FFCC00'
align'left'gtmyPage " myPage " of lta
href'TitleSrch.asp?FLAG1ampPage"
cStr(MaxPage) myReturn "'gtltfont
color'000000'gt" MaxPage "lt/fontgtlt/tdgt" if
myPage gt 1 then sRet sRet "lttd
bgcolor'FFCC00' align'right'gtlta
href'TitleSrch.Asp?FLAG1ampPage"
cStr(myPage-1) myReturn "'gtltfont
color'000000'gtPrevious " cRECMAX
"lt/fontgtlt/agtlt/tdgt" else sRet sRet "lttd
bgcolor'FFCC00' align'right'gtnbsplt/tdgt" end
if if myPage lt MaxPage Then sRet sRet "lttd
bgcolor'FFCC00' align'right'gtlta
href'TitleSrch.Asp?FLAG1ampPage"
cStr(myPage1) myReturn "'gtltfont
color'000000'gtNext " cRECMAX
"lt/fontgtlt/agtlt/tdgt" else sRet sRet "lttd
bgcolor'FFCC00' align'right'gtnbsplt/tdgt" end
if sRet sRet "lt/trgtlt/tablegt" DoNav
sRet exit function End Function
11
Notes about page navigation

• We go through all of that trouble to present the
  user with only the relevant navigation links.
• What is missing is a routine to count the number
  of actual records on the last page and display
  that instead of the number of records per page.
• The page size of 2 is to force paging for the
  example. Obviously, bigger would be better.
• The server side cursor used this was is fairly
  light weight and the technique is highly
  scale-able.

12
Achieving the green bar effect

• Set a variable to the back color attribute
  desired, in this case force the default back
  color to show in each cell
• bColor ""
• As each record is written determine if it is an
  even record and set the back color attribute to
  the desired contrast color by creating an
  attribute string
• if j mod 2 0 then bColor"bgcolor'FFFFCC'"
• Then insert that value into each of the ltTDgts
  written
• Response.write "lttd " bColor " valign

13
Maintaining Databases

• Action queries are our friends

14
Overview

• The web is episodic in nature, so too should be
  our data access
• IIS 4 is built on MTS that provides connection
  pooling. The upshot of this is that connection
  objects normally very expensive to create are
  quite cheap and fast.
• Thus, the connection objects execute method
  provides a good way to modify records in the
  database using the SQL update, insert and delete
  verbs.

15
Authors Table Maintenance Example
Notice we hyperlink the display with the PK of
the table so that when a record is clicked on
the record is passed to the next page.
16
Editing the author record
Again we pass the action in the embedded hyperlink
17
The ADO Connection Object

• Provides a facility to
• send SQL action queries back to the server and
  see how many records were affected
• create fire hose queries
• execute stored procedures
• Created via the usual mechanism
• Set CNServer.CreateObject("ADODB.Connection")
• As usual what is created should be destroyed
• Set CN Nothing

18
The connection object needs the DSN as before.

• For example this is the DSN for this class
• const cDSN "DriverSQL ServerServer130.86.76.1
  69DatabasepubsUIDstudentPWDstudent"
• To open a connection object is very simple
• Set CN Server.CreateObject("ADODB.Connection")
• CN.Open cDSN
• Again, if an object has an OPEN close should be
  applied
• CN.Close

19
Using a parameter to indicate the correct editing
sequence

• In the class examples the 'ACTION' querystring
  parameter indicates what the form should do.
• It is important to carefully check the passed
  parameters and be aware of case sensitivity
  issues.
• The code on the next slide shows the case
  statement that powers up each of the actions.
• Some of the actions are less then intuitive, they
  are written to reflect the episodic nature of the
  web.

20
The select statement for the actions
AUID trim(Request.QueryString("AUID")) if
len(AUID) 0 then 'Redirect method must be used
before lthtmlgt element 'unless buffer property
used first Response.Redirect("AspADO1.Asp") en
d if ACTION ucase(trim(Request.QueryString("ACTI
ON"))) '... Select case ACTION case
"DELETE" 'Delete Record case "NEW" 'Add
Record case "SAVENEW" 'Save Newly Added
Record case "SAVE" 'Save Edited Record
case else 'Show Record To Be Edited End Select
21
Editing a record

• The code on the next slide demos how to take a
  recordset open one specific record and fill a
  form with the existing values.
• Note use of the 'name' and 'value' attributes.
• The code on the page after that, shows how once
  the user hits the UPDATE button what code is
  behind updating the database using the form
  fields.

22
Making of the Editing Form
'Create a form to edit in response.write("ltform
action'AspADO2.asp?AUID" AUID
"ACTIONSAVE' method'POST'gt")
response.write("ltpgtAuthor ID " AUID
"lt/pgt") response.write("ltpgtFirst Name ltinput
type'text' name'First' Value'"
rs.fields("au_fname").value "'gtltbrgt") response.w
rite("Last Name ltinput type'text' name'Last'
Value'" rs.fields("au_lname").value
"'gtlt/pgt") response.write("ltinput type'submit'
name'Submit'gt") response.Write("lt/formgt")
response.write("ltpgtlta href'AspAdo2.asp?AUID"
AUID "ACTIONDELETE'gtDelete This
Record.lt/agt")
23
The "SAVE" code
'Create and open a connection object Set CN
Server.CreateObject("ADODB.Connection") CN.Open
cDSN mySQL "Update Authors Set AU_FNAME '"
Request.Form("FIRST") "', AU_LNAME '"
Request.Form("LAST") "' where (AU_ID '"
AUID "')" CN.Execute mySQL,RA if RA 1 then
Response.Write("ltpgtRecord Saved. Author ID "
AUID "lt/pgt") else Response.Write("ltpgtRecord
NOT Saved, Contact support! Author ID "
AUID "lt/pgt") end if CN.Close Set CN
Nothing
This is an example for how the SAVE mechanism
works. Notice, proper disposal of objects when
done with them.
24
Summary

• Since each time the page is visited the
  connection is re-created the form is stateless
  with respect to the server.
• Statelessness is the most scale-able solution.
• Combined with paging, the overall load on the
  server is minimal.
• This example uses two forms, it could all be
  implemented in one form, but it would be a very
  complex one.
• Ideally, the first form should be a paged search.