ASP.NET

1
ASP.NET

• Database Operations

2
Sorting with Repeater

• For this example, records from the Products table
  are sorted prior to their display in the
  Repeater.
• Sorting is triggered by clicking a button that
  serves as the column heading. That is, when the
  "Name" button is clicked records are shown in
  ascending alphabetic order by product name when
  the "Type" button is clicked, the records are
  ordered alphabetically by product type.

3
Sorting with Repeater

• ltdiv class"head"gtSorted Product Listing - _
  Repeaterlt/divgt
• ltaspRepeater id"RepeaterDisplay"
  runat"server"gt
• ltHeaderTemplategt
• lttable id"RepeaterTable" border"1" _
  style"background-colorF9F9F9"gt
• lttrgt ltthgt
• ltaspButton Text"Number" runat"server" _
  OnCommand"SortRepeater" _ CommandName"ItemNumber
  " _ EnableViewState"False"/gt lt/thgt

4
Sorting with Repeater

• Using Button Command Properties When the
  "Number" button is clicked the OnCommand event
  handler calls the SortRepeater subprogram. An
  identifier, ItemNumber in this case, is also
  passed to the subprogram through the CommandName
  property.
• ltaspButton Text"Number" runat"server" _
  OnCommand"SortRepeater " _
• CommandName"ItemNumber" _
• EnableViewState"False"/gt
• Thus, the called subprogram receives a
  CommandName value that identifies the name of a
  field in the Products table on which sorting is
  to take place. Each of the buttons in the header
  row calls the same subprogram but names a
  different database field for sorting.

5
Sorting with Repeater

• An EnableViewState"False" property setting is
  made for the buttons. This ensures that the
  visual status of a button is not maintained in
  View State.
• A clicked button is "highlighted" with a bolder
  border than other buttons. Unless this emphasis
  is turn off between page postings, previous
  buttons that were clicked remain highlighted,
  making it difficult to tell which button was most
  recently clicked.
• This setting does not affect actual sorting of
  the records it only affects the button's display
  characteristics.

6
Sorting with Repeater

• The Sort Subprogram. Record sorting takes place
  in the SortRepeater subprogram. Sorting requires
  no more than an SQL statement to retrieve records
  from the Products table in a particular order.
  That order is given by the CommandName argument
  passed to the subprogram by a button click.
• Sub SortRepeater (Src As Object, Args As _
  CommandEventArgs)
• SQLString "SELECT FROM Products _
• ORDER BY " _
• Args.CommandName
• DisplayRepeater
• End Sub

7
Sorting with Repeater

• Notice the signature for the subprogram includes
  Args As CommandEventArgs. CommandEventArgs is the
  object through which database fields names are
  passed to the subprogram.
• Args.CommandName is the property containing the
  name of the sort field that is passed. Therefore,
  this property is appended to a SELECT statement
  to return a recordset ordered by the values in
  that field.
• This single subprogram, then, can compose a
  proper SELECT statement for any of the buttons
  that are clicked.

8
Sorting with Repeater

• Issuing the SELECT Statement. Once a proper
  SELECT statement is prepared a "general-purpose"
  DisplayRepeater subprogram can be called to
  retrieve the recordset and rebind it to the
  Repeater.
• Sub DisplayRepeater
• DBConnection New OleDbConnection( _
  "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Data Sourced\Databases\eCommerce.mdb")
  DBConnection.Open()
• DBCommand New OleDbCommand(SQLString,
  DBConnection) DBReader DBCommand.ExecuteReader()
  
• RepeaterDisplay.DataSource DBReader
• RepeaterDisplay.DataBind() DBReader.Close()
• DBConnection.Close()
• End Sub
• In previous examples a SELECT statement was
  included in the database retrieval and binding
  routine. In this case the statement is composed
  in a different subprogram and just being issued
  in this routine.

9
Sorting with Repeater

• Page Loading. All that remains is to code the
  Page_Load portion of the page. The Repeater
  appears when the page loads, so an initial SQL
  statement needs to be issued to populate the
  Repeater this first time subsequently it is
  redisplayed by the sort routine.
• Sub Page_Load
• If Not Page.IsPostBack Then
• SQLString "SELECT FROM Products _
• ORDER BY ItemNumber
• DisplayRepeater
• End If
• End Sub
• SortRepeater.aspx

10
Sorting with Repeater

• On initial page load the SQLString retrieves a
  recordset sorted by ItemNumbers (any field could
  be chosen or none at all). Then the
  DisplayRepeater subprogram is called to extract
  and bind this initial recordset to the Repeater.
• You might note that the DisplayRepeater
  subprogram does not have a signature source and
  argument list. This is because no arguments are
  being passed to it. It is not being called by a
  server control it is "our" subprogram to do with
  as we please.
• Also, since variable SQLString is referenced in
  two different subprograms (SortRepeater and
  DisplayRepeater) it needs to be declared as a
  global variable.

11
DataGrid

• FullDataGrid.aspx

12
Sorting with DataGrid Control

• An aspDataGrid control can automatically use its
  column headings as links to sort routines. Minor
  changes are required to the control, and similar
  subprograms to the Repeater are needed.
• Using Link Command Properties First,
  "permission" to sort the grid is provided by
  adding AllowSorting"True" and OnSortCommand"Sort
  DataGrid" to the control's property list, the
  latter naming the subprogram to call when a link
  is clicked. Then, for those columns on which
  sorting is to take place, a SortExpression"sort
  field" property is added.

13
Sorting with DataGrid Control

• ltdiv class"head"gtSorted Product Listing -
  DataGridlt/divgt
• ltaspDataGrid id"DataGridDisplay" runat"server"
  _ AutoGenerateColumns"False" AllowSorting"True"
  _ OnSortCommand"SortDataGrid" Width"560" _
  BackColor"F9F9F9" HeaderStyle-BackColor"990000
  " _ HeaderStyle-ForeColor"FFFFFF"
  HeaderStyle-Font- _ Bold"True"
  HeaderStyle-HorizontalAlign"Center" ItemStyle- _
  VerticalAlign"Top"gt
• ltColumnsgt
• ltaspBoundColumn
• DataField"ItemNumber"
• SortExpression"ItemNumber"
• HeaderText"No"
• HeaderStyle-Font-Size"8pt"
• ItemStyle-Font-Size"8pt"/gt

14
Sorting with DataGrid Control

• ltaspTemplateColumngt
• ltHeaderTemplategt Description lt/HeaderTemplategt
• ltItemTemplategt
• ltdiv class"box"gt lt Container.DataItem("ItemDesc
  ription") gt
• lt/divgt
• lt/ItemTemplategt
• lt/aspTemplateColumngt
• ltaspBoundColumn
• DataField"ItemPrice"
• SortExpression"ItemPrice"
• HeaderText"Price"
• HeaderStyle-Font-Size"8pt"
• ItemStyle-Font-Size"8pt"
• ItemStyle-HorizontalAlign"Right"/gt

15
Sorting with DataGrid Control

• ltaspBoundColumn
• DataField"ItemQuantity
• SortExpression"ItemQuantity"
• HeaderText"Qty"
• HeaderStyle-Font-Size"8pt"
• ItemStyle-Font-Size"8pt"
• ItemStyle-HorizontalAlign"Right"/gt
• ltaspTemplateColumngt
• ltHeaderTemplategt Picture lt/HeaderTemplategt
• ltItemTemplategt
• ltimg src"Pictures/ltContainer.DataItem("ItemNumb
  er")gt _
• .jpg" style"height50px cursorhand"
  title"Click for larger image" _
  onClick"ShowPicture('lt _ container.DataItem("It
  emNumber")gt.jpg')"/gt
• lt/ItemTemplategt lt/aspTemplateColumngt
• lt/Columnsgt lt/aspDataGridgt

16
Sorting with DataGrid Control

• The Sort Subprogram. The SortDataGrid subprogram
  has a slightly different signature from the one
  used for the Repeater plus, Args.SortExpression
  identifies the sort field passed through the link
  click.
• Sub SortDataGrid (Src As Object, Args As _
  DataGridSortCommandEventArgs)
• SQLString "SELECT FROM Products _
• ORDER BY " Args.SortExpression
• DisplayDataGrid
• End Sub
• A SELECT statement is composed by appending the
  passed field name then subprogram
  DisplayDataGrid is called.

17
Sorting with DataGrid Control

• Issuing the SELECT Statement. Once a SELECT
  statement is composed, subprogram DisplayDataGrid
  is called to retrieve the recordset and bind it
  to the DataGrid. This subprogram is identical to
  the one used for the Repeater except for the
  binding statements.
• Sub DisplayDataGrid
• DBConnection New OleDbConnection( _
  "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Data Sourced\Databases\eCommerce.mdb")
  DBConnection.Open()
• DBCommand New OleDbCommand(SQLString,
  DBConnection)
• DBReader DBCommand.ExecuteReader()
  DataGridDisplay.DataSource DBReader
  DataGridDisplay.DataBind()
• DBReader.Close()
• DBConnection.Close()
• End Sub

18
Sorting with DataGrid Control

• Page Loading. As is done for the Repeater, an
  initial SQL command is composed and issued
  through the DisplayDataGrid subprogram so that
  the DataGrid is initially populated with records
  when the page first opens.
• Sub Page_Load
• If Not Page.IsPostBack Then
• SQLString "SELECT FROM Products _
• ORDER BY ItemNumber"
• DisplayRepeater
• DisplayDataGrid
• End If
• End Sub
• Since variable SQLString is referenced in two
  different subprograms (SortDataGrid and
  DisplayDataGrid) it needs to be declared as a
  global variable.
• SortDataGrid.aspx

19
DataList

• FullDataList.aspx

20
Sorting with DataList Control

• The aspDataList control does not have a built-in
  sorting feature. Radio buttons are added to
  select a sort field and to indicate ascending or
  descending sequence.
• A button is added to call the SortDataList
  subprogram.
• Coding Sort Buttons A table to format the radio
  buttons is added immediately below the caption
  for the DataList. The first set of buttons
  permits choice of a sort field the second set is
  for choosing the direction of sort.
• A standard button calls the SortDataList
  subprogram.

21
Sorting with DataList Control

• ltdiv class"head"gtSorted Product Listing -
  DataListlt/divgt
• lttable border"1" width"560" style"border-collap
  secollapse"gt
• lttr valign"bottom"gt lttdgt ltbgt Order By lt/bgtltbrgt
• ltaspRadioButtonList id"SortButtons"
  runat"server" _ RepeatDirection"Horizontal"
  RepeatLayout"Flow"gt
• ltaspListItem Text"Number " Value"ItemNumber" _
  Selected"True"/gt
• ...
• ltaspListItem Text"Quantity " Value"ItemQuantity
  "/gt
• lt/aspRadioButtonListgt lt/tdgt
• lttdgt ltbgtDirection lt/bgtltbrgt
• ltaspRadioButtonList id"DirectionButtons"
  runat"server" _ RepeatDirection"Horizontal"
  RepeatLayout"Flow"gt
• ltaspListItem Text"ASC" Value"ASC"
  Selected"True"/gt
• ltaspListItem Text"DESC" Value"DESC"/gt
• lt/aspRadioButtonListgt lt/tdgt
• lttdgt ltaspButton Text"Sort" OnClick"SortDataList
  " runat"server"/gt lt/tdgt
• lt/trgt lt/tablegt

22
Sorting with DataList Control

• ltaspDataList id"DataListDisplay" runat"server"
  _
• Width"560" _
• CellSpacing"3" _
• CellPadding"5" _
• RepeatColumns"2" _
• RepeatDirection"Horizontal" _
• GridLines"Both" ItemStyle- _
• BackColor"F9F9F9" _
• ItemStyle-Font-Size"8pt"gt

23
Sorting with DataList Control

• ltItemTemplategt
• ltimg src"Pictures/ltContainer.DataItem("ItemNumb
  er")gt _
• .jpg" style"width50px floatleft
  margin-right15px _
• margin-bottom20px cursorhand" _
• title"Click for larger image"
  onClick"ShowPicture('lt _ Container.DataItem("It
  emNumber")gt.jpg')"/gt
• ltbgtNumber lt/bgtlt Container.DataItem("ItemNumber"
  ) gtltbrgt
• ltbgtQuantitylt/bgtlt Container.DataItem("ItemQuanti
  ty") gt ltbrgt ltbgtDescription lt/bgtltbrgt
• ltdiv style"width260px height55px
  overflowauto"gt
• lt Container.DataItem("ItemDescription") gt
  lt/divgt
• lt/ItemTemplategt lt/aspDataListgt

24
Sorting with DataList Control

• Coding the Sort Routine As in previous examples
  the SortDataList subprogram creates an SQL
  statement that can be executed by the
  DisplayDataList routine.
• In this case two values are appended to the
  statement. The field name is given by the value
  of the checked radio button in the first set the
  value ASC or DESC is given by the second set.
• The resulting SQL statement is in the format
• SELECT FROM Products _
• ORDER BY 'field' ASC (or DESC).

25
Sorting with DataList Control

• Sub SortDataList (Src As Object, Args As
  EventArgs)
• SQLString "SELECT FROM Products _
• ORDER BY " _ SortButtons.SelectedItem.Value _
• " " _ DirectionButtons.SelectedItem.Value
• DisplayDataList
• End Sub
• Sub DisplayDataList
• DBConnection New OleDbConnection( _
  "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Data Sourced\Databases\eCommerce.mdb")
• DBConnection.Open()
• DBCommand New OleDbCommand(SQLString,
  DBConnection)
• DBReader DBCommand.ExecuteReader()
• DataListDisplay.DataSource DBReader
• DataListDisplay.DataBind()
• DBReader.Close()
• DBConnection.Close()
• End Sub

26
Sorting with DataList Control

• An initial SQL command is composed and issued
  through the DisplayDataList subprogram when the
  page first opens.
• Sub Page_Load
• If Not Page.IsPostBack Then
• SQLString "SELECT FROM Products _
• ORDER BY ItemNumber"
• DisplayDataList
• End If
• End Sub
• Since variable SQLString is referenced in two
  different subprograms (SortDataList and
  DisplayDataList) it needs to be declared as a
  global variable.
• SortDataList.aspx

27
Display Selected Records

• When displaying records from a database table it
  is often convenient to be able to select
  particular records for display. This involves
  specifying some criterion value for one of the
  fields and then extracting only those records
  which meet that criterion for example, select
  only those records where the quantity in stock is
  greater than 100.
• The following Repeater is based on the previous
  control which permits record sorting. Selections
  are provided to choose subsets of records from
  the Products table which meet specified search
  criteria. For instance, the following subset
  shows all software for which the ItemType field
  equals "Database", sorted in ascending sequence
  by ItemPrice.

28
Display Selected Records

• In order to make the selection and sorting an SQL
  statement must be composed to read
• SELECT FROM Products WHERE ItemType'Database'
  ORDER BY ItemPrice ASC
• Additional controls are added to the Repeater to
  collect information to create this statement. A
  drop-down list permits selection of a field name,
  a second drop-down list selects a conditional
  operator, and a textbox provides the value for
  comparison. These three controls permit creation
  of an SQL WHERE clause giving the criterion for
  selecting records from the Products table. A set
  of radio buttons is also added to specify their
  sort order.

29
Display Selected Records

• ltdiv class"head"gt
• Selected Product Listing - Repeaterlt/divgt
• ltaspPanel id"FieldPanel" BackColor"F0F0F0"
  Width"560" _
• runat"server"gt
• ltbgt Select Field lt/bgt
• ltaspDropDownList id"FieldName" runat"server"gt
• ltaspListItem Value"ItemNumber" Text"Item
  Number"/gt ltaspListItem Value"ItemType"
  Text"Item Type"/gt ltaspListItem
  Value"ItemSupplier" Text"Item Supplier"/gt
  ltaspListItem Value"ItemName" Text"Item Name"/gt
  ltaspListItem Value"ItemDescription" Text"Item
  Description"/gt ltaspListItem Value"ItemPrice"
  Text"Item Price"/gt ltaspListItem
  Value"ItemQuantity" Text"Item Quantity"/gt
• lt/aspDropDownListgt

30
Display Selected Records

• ltaspTextBox id"FieldValue" _
• Text"Database" runat"server"/gt
• ltaspRadioButtonList id"DirectionButtons _
• runat"server" RepeatDirection"Horizontal" _
• RepeatLayout"Flow"gt
• ltaspListItem Text"ASC" Value"ASC" _
• Selected"True"/gt
• ltaspListItem Text"DESC" Value"DESC"/gt
• lt/aspRadioButtonListgt lt/aspPanelgt

31
Display Selected Records

• ltaspRepeater id"RepeaterDisplay" _
• runat"server"gt
• ltHeaderTemplategt
• lttable id"RepeaterTable" border"1 _
• style"background-colorF9F9F9"gt
• lttrgt ltthgt
• ltaspButton Text"Number" runat"server _
• OnCommand"SortRepeater _
• CommandName"ItemNumber" _
• EnableViewState"False"/gt lt/thgt
• ...
• lt/HeaderTemplategt

32
Display Selected Records

• ltItemTemplategt
• lttrgt
• lttd class"center"gt
• lt Container.DataItem("ItemNumber") gtlt/tdgt
• lttdgt ltdiv class"box"gt
• lt Container.DataItem("ItemDescription") gt
• lt/divgt lt/tdgt
• lttd class"right"gt
• lt Container.DataItem("ItemPrice") gt
• lt/tdgt
• lt/trgt
• lt/ItemTemplategt

33
Display Selected Records

• Field name selection is made through an
  aspDropDownList supplying field names in the
  Products table. The actual names are given in the
  Value properties.
• ltbgtSelect Field lt/bgt
• ltaspDropDownList id"FieldName" runat"server"gt
• ltaspListItem Value"ItemNumber" Text"Item
  Number"/gt
• ltaspListItem Value"ItemType" Text"Item
  Type"/gt
• ltaspListItem Value"ItemSupplier" Text"Item
  Supplier"/gt
• ltaspListItem Value"ItemName" Text"Item
  Name"/gt
• ltaspListItem Value"ItemDescription" Text"Item
  Description"/gt
• ltaspListItem Value"ItemPrice" Text"Item
  Price"/gt
• ltaspListItem Value"ItemQuantity" Text"Item
  Quantity"/gt
• lt/aspDropDownListgt

34
Display Selected Records

• Conditional operators are selected from a second
  DropDownList whose Values are the actual
  conditional operators and whose Text properties
  are verbal equivalents.
• ltaspDropDownList id"Operator" runat"server"gt
• ltaspListItem Value" LIKE " Text"Contains"/gt
• ltaspListItem Value" Not LIKE " Text"Does Not
  Contain"/gt
• ltaspListItem Value" lt " Text"Less Than"/gt
• ltaspListItem Value" " Text"Equal To"/gt
• ltaspListItem Value" gt " Text"Greater Than"/gt
• ltaspListItem Value" ltgt " Text"Not Equal To"/gt
• lt/aspDropDownListgt
• Finally, the search criterion is given in an
  aspTextBox control. Values applied to SQL
  statements are not case sensitive, so either
  lower-case, upper-case, or mixed-case characters
  can be entered.
• ltaspTextBox id"FieldValue" Text"Database"
  runat"server"/gt

35
Composing SQL String

• The values from the three controls can be used to
  construct an SQL WHERE clause for selecting
  records that meet the criterion. For instance,
  when "ItemType" is selected from the FieldName
  list, and " " is selected from the Operator
  list, and "Database" is entered in the FieldValue
  textbox, then concatenating the values
• " WHERE " FieldName.SelectedItem.Value _
• Operator.SelectedItem.Value "'"
  FieldValue.Text "'
• produces the string
• WHERE ItemType 'Database'
• This string can be plugged into a SELECT
  statement to extract records based on this
  condition test.
• Composition of the SELECT statement takes place
  in the SortRepeater subroutine.

36
Display Selected Records

• Sub SortRepeater (Src As Object, Args As
  CommandEventArgs)
• SQLString "SELECT FROM Products"
• If FieldValue.Text ltgt "" Then
• If Operator.SelectedItem.Value " LIKE " _ OR _
• Operator.SelectedItem.Value " Not LIKE " Then
  
• '-- "Contains" comparison, e.g.,
• '-- WHERE field LIKE 'value'
• SQLString " WHERE " FieldName.SelectedItem.Va
  lue _
• _ Operator.SelectedItem.Value "'"
  FieldValue.Text _
• "'"
• Else If FieldName.SelectedItem.Value ltgt
  "ItemPrice" AND _ FieldName.SelectedItem.Value ltgt
  "ItemQuantity" Then
• '-- Alphanumeric comparising, e.g.,
• '-- WHERE field 'value'

37
Display Selected Records

• SQLString " WHERE " FieldName.SelectedItem.Va
  lue _ Operator.SelectedItem.Value "'"
  FieldValue.Text "'"
• Else
• '-- Numeric comparison, e.g.,
• '-- WHERE field value
• SQLString " WHERE " FieldName.SelectedItem.V
  alue _
• Operator.SelectedItem.Value FieldValue.Text
  
• End If
• End If
• End If
• SQLString " ORDER BY " Args.CommandName
• SQLString " " DirectionButtons.SelectedItem.V
  alue
• DisplayRepeater
• End Sub

38
Display Selected Records

• Composing the WHERE clause depends on the user
  having entered a search criterion value in the
  textbox. If the textbox is empty this part of the
  subprogram is not run and no WHERE clause is
  appended to the SQLString.
• The format of the WHERE clause differs slightly
  when specifying string versus numeric
  comparisons. When the comparison value is a
  string, it must be enclosed in single quotes
  (apostrophes) when the comparison value is a
  number, no quotes are used. For example,
• WHERE ItemSupplier 'Microsoft'
• WHERE ItemPrice gt 100
• So, the script supplies different SQL coding for
  the ItemNumber, ItemType, ItemSupplier, ItemName,
  and ItemDescription fields (which are strings
  whose comparison values are enclosed in
  apostrophes) versus the ItemPrice and
  ItemQuantity fields (which are numbers whose
  comparison values are not enclosed in single
  quotes).

39
Display Selected Records

• When using LIKE or  Not LIKE comparisons, all
  values are treated as strings and no
  differentiation in coding is made. The script
  uses the general field search  LIKE 'value' to
  locate the entered value anywhere in the field.
• After the WHERE clause is composed and appended
  to the SQLString variable, the ORDER BY clause is
  added.
• SQLString " ORDER BY " Args.CommandName As
  before, the sort field is given by the
  CommandName associated with the button clicked in
  the column header. Next, either ASC or DESC is
  appended to the SQLString depending on which of
  the radio buttons is checked.
• SQLString " " DirectionButtons.SelectedItem.V
  alue Finally, the completed SQLString is issued
  by calling the DisplayRepeater subprogram. The
  Repeater is re-bound with records matching the
  search criterion and sorted according to the
  button click.
• SelectRepeater.aspx

40
Display Records w/Paging

• When displaying records from a database you need
  to be cautious about the length of output
  produced. The Products table includes only 20
  records and easily fits on a single Web page. But
  consider a database table containing hundreds or
  thousands of records. It would be impractical to
  display all records at one time. The need is to
  display only a few records at a time and to
  provide a paging mechanism for looking through
  the complete table.
• In the following example, four records at a time
  from the Products table are displayed in a
  Repeater. Buttons call up the next set of records
  for viewing.

41
Display Records w/Paging

• Example uses buttons to display subsets of
  records from the Products table. Two issue
  surround these buttons. First, there needs to be
  a way to associate a particular button with a
  particular subset of records from the table.
  Second, the buttons must be created dynamically,
  under script control. They cannot be hard coded
  on the page because it is not known in advance
  how many buttons are needed nor which subset of
  records each is associated with.
• As records are added to the table, more buttons
  are needed as records are deleted, fewer buttons
  are needed. The number of records in the table
  and, therefore, the number of buttons needed
  cannot be known until the table is first accessed
  in a script.
• Although button controls cannot be coded directly
  on the page, an area can be set aside as a
  "placeholder" where a script can place the
  buttons when it creates them. The aspPlaceHolder
  control is designed for just this purpose. It
  reserves space on the Web page within which
  dynamically created controls can be placed. It
  has a very simple general format
• ltaspPlaceHolder id"value" runat"server"/gt

42
Display Records w/Paging

• ltdiv class"head"gtProduct Listing -
  Repeaterlt/divgt
• ltaspRepeater id"RepeaterDisplay"
  runat"server"gt
• ltHeaderTemplategt
• lttable id"RepeaterTable" border"1"gt
• lttrgt
• ltthgtNolt/thgt
• ltthgtTypelt/thgt
• ltthgtSupplierlt/thgt
• ltthgtNamelt/thgt
• ltthgtDescriptionlt/thgt
• ltthgtPricelt/thgt
• ltthgtQtylt/thgt
• ltthgtPicturelt/thgt
• lt/trgt
• lt/HeaderTemplategt

43
Display Records w/Paging

• ltItemTemplategt
• lttrgt
• lttd class"center"gtlt Container.DataItem("ItemNum
  ber") gtlt/tdgt
• lttdgtlt Container.DataItem("ItemType") gtlt/tdgt
• lttdgtlt Container.DataItem("ItemSupplier")
  gtlt/tdgt
• lttdgtlt Container.DataItem("ItemName") gtlt/tdgt
• lttdgt ltdiv class"box"gt
• lt Container.DataItem("ItemDescription")
  gtlt/divgt lt/tdgt
• lttd class"right"gtlt Container.DataItem("ItemPric
  e") gtlt/tdgt
• lttd class"right"gtlt Container.DataItem("ItemQuan
  tity") gtlt/tdgt
• lttdgt ltimg src"Pictures/ltContainer.DataItem("Ite
  mNumber")gt _
• .jpg" style"height50px cursorhand"
  title"Click for larger image" _
• onClick"ShowPicture('lt Container.DataItem("Ite
  mNumber")gt.jpg')"/gt lt/tdgt
• lt/trgt
• lt/ItemTemplategt
• ltFooterTemplategt lt/tablegt lt/FooterTemplategt
• lt/aspRepeatergt
• Page ltaspPlaceHolder id"PageButtons"
  runat"server"/gt

44
Display Records w/Paging

• Initial Repeater Display The number of records
  per page to display is an arbitrary choice. In
  this example four records per page is chosen.
  When the page opens the first subset of records
  needs to be retrieved for display in the
  Repeater. This is done in the following portion
  of the script. Variable PageSize is declared to
  hold the number of records per page.
• SELECT TOP n FROM table ... to retrieve the
  first PageSize number of records from the
  Products table and bind them to the Repeater.
  This initial retrieval takes place only the first
  time the page loads.

45
Display Records w/Paging

• ltSCRIPT runat"server"gt
• Dim DBConnection As OleDbConnection
• Dim DBCommand As OleDbCommand
• Dim DBReader As OleDbDataReader
• Dim SQLString As String
• Dim PageSize As Integer 4

46
Display Records w/Paging

• Sub Page_Load
• If Not Page.IsPostBack Then
• '-- Display the first PageSize number of records
  
• DBConnection New OleDbConnection( _
  "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Data Sourced\Databases\eCommerce.mdb")
• DBConnection.Open()
• SQLString "SELECT TOP " PageSize " FROM
  Products " _
• "ORDER BY ItemNumber"
• DBCommand New OleDbCommand(SQLString,
  DBConnection)
• DBReader DBCommand.ExecuteReader()
• RepeaterDisplay.DataSource DBReader
• RepeaterDisplay.DataBind()
• DBReader.Close()
• DBConnection.Close()
• End If ...create paging buttons
• End Sub

47
Display Records w/Paging

• Dynamic Paging Buttons Since paging buttons are
  created in script they do not take part in the
  page's View State. They need to be created each
  time the page loads, within the script's
  Page_Load subroutine.
• Each paging button needs to represent a
  particular subset of records. When it calls a
  subprogram, say DisplayRepeater, to redisplay the
  Repeater and bind that subset of records it must
  pass along to the subprogram information about
  which subset to display. Here is an opportunity,
  then, to use the CommandName property of a button
  to pass this information to the subprogram.
• Records from the Products table are retrieved in
  ascending order by ItemNumber. Therefore, the
  information needed by subprogram DisplayRepeater
  is the first ItemNumber in the subset and the
  last ItemNumber in the subset.
• Assume for instance that the subset of records to
  be displayed begins with ItemNumber "GR1111" and
  ends with ItemNumber "GR4444". A command button
  containing this information could be coded as
  follows
• ltaspButton runat"server" Text"label"
  OnCommand"DisplayRepeater" CommandName"GR1111GR
  4444" /gt

48
Display Records w/Paging

• That is, the button's OnCommand property calls
  subprogram DisplayRepeater, and its CommandName
  property is a string containing the first and
  last item numbers to be retrieved. These item
  numbers are separated by a "pipe" character ()
  although any delimiter character could be used so
  long as it were not part of the values in the
  list. When the subprogram is called it could
  parse the CommandArgument argument to determine
  the range of item numbers to extract from the
  Products table.
• This is precisely the way the example Repeater
  works. The Page_Load script builds a set of
  buttons, each containing the starting and ending
  ItemNumbers for a subset of records. These item
  numbers are given in the CommandName property of
  the buttons.

49
Display Records w/Paging

• Determining Subsets of Records The first task in
  creating the paging buttons is to retrieve the
  full set of item numbers from the Products table
  and to assign particular subsets of numbers to
  particular buttons.
• For this purpose an ArrayList is created and
  loaded with item numbers from the table. It is
  easier to work with a fully stocked ArrayList
  than try to create buttons while iterating
  through a recordset.

50
Display Records w/Paging

• Sub Page_Load
• If Not Page.IsPostBack Then
• ...display initial Repeater
• End If
• '-- Load array ItemNumberList() with item numbers
  
• Dim ItemNumberList New ArrayList()
• DBConnection New OleDbConnection( _
  "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Data Sourced\Databases\eCommerce.mdb")
• DBConnection.Open()
• SQLString "SELECT ItemNumber FROM Products _
• ORDER BY ItemNumber"

51
Display Records w/Paging

• DBCommand New OleDbCommand(SQLString, _
• DBConnection)
• DBReader DBCommand.ExecuteReader()
• While DBReader.Read()
• ItemNumberList.Add(DBReader("ItemNumber"))
• End While
• DBReader.Close()
• DBConnection.Close()
• ItemNumberList.TrimToSize()
• ...
• End Sub

52
Display Records w/Paging

• At this point ArrayList ItemNumberList contains,
  in sequence, all of the item numbers from the
  Products table. Now it is a matter of indexing
  through the ArrayList, determining the beginning
  and ending item numbers of each subset of
  records, and creating a button for each subset.

53
Display Records w/Paging

• '-- Create Paging Buttons
• Dim StartIndex As Integer
• Dim EndIndex As Integer
• Dim StartKey As String
• Dim EndKey As String
• Dim i As Integer
• StartIndex 0 For i 1 To _
• Math.Ceiling(ItemNumberList.Count / PageSize)
• '-- Determine starting and ending array indexes
• EndIndex StartIndex PageSize - 1
• If EndIndex gt RecordCount - 1 Then
• EndIndex RecordCount - 1
• End If

54
Display Records w/Paging

• '-- Assign starting and ending item numbers
• StartKey ItemNumberList(StartIndex)
• EndKey ItemNumberList(EndIndex)
• '-- Create a button and assign to placeholder
• Dim PageButton As Button
• PageButton New Button()
• PageButton.Text i
• PageButton.id "P" i
• PageButton.CommandArgument StartKey ""
  EndKey
• PageButton.Style("width") "20px"
• PageButton.Style("background-color") "F0F0F0"
• AddHandler PageButton.Command, AddressOf
  DisplayRepeater
• PageButtons.Controls.Add(PageButton)
• StartIndex PageSize
• Next

55
Display Records w/Paging

• The number of paging buttons needed is given by
  dividing the total number of item numbers in the
  array (ItemNumberList.Count) by the number of
  records per page (PageSize). More accurately, the
  Math.Ceiling() method must be applied to the
  formula to always "round up" to the next whole
  number of buttons
• Math.Ceiling(ItemNumberList.Count / PageSize)
• A loop running from 1 to Math.Ceiling(ItemNumberLi
  st.Count / PageSize) produces the correct number
  of buttons to create. Each iteration of the loop
  produces one of those buttons.

56
Display Records w/Paging

• Each button requires a CommandName property
  composed of the first and last item numbers for
  its particular subset. The ArrayList index of
  these item numbers occurs in multiples of the
  PageSize. That is, for a Products table of 20
  records retrieved 4 at a time the ArrayList
  locations of the beginning and ending item
  numbers for 5 buttons can be shown as 1 (0)-(3),
  2 (4)-(7), 3(8)-(11), 4 (12)-(15)
• The first portion of script within the loop
  determines these ArrayList indexes of the item
  numbers to assign to a button and extracts them
  from the array for assignment to variables
  StartKey and EndKey.

57
Display Records w/Paging

• StartIndex 0
• For i 1 To Math.Ceiling(ItemNumberList.Count /
  PageSize)
• '-- Determine starting and ending array indexes
• EndIndex StartIndex (PageSize - 1)
• If EndIndex gt ItemNumberList.Count - 1 Then
• EndIndex ItemNumberList.Count - 1
• End If

58
Display Records w/Paging

• '-- Assign starting and ending item numbers
• StartKey ItemNumberList(StartIndex)
• EndKey ItemNumberList(EndIndex)
• ...
• StartIndex EndIndex 1
• Next
• The StartIndex for the array (the index of the
  beginning item number for the first button) is 0.
  The EndIndex (the index of the ending item number
  for the first button) is StartIndex (PageSize -
  1). For the first button StartIndex 0 and
  EndIndex 3, spanning the four array elements
  containing the item numbers associated with this
  first button. Using these indexes, StartKey is
  assigned the item number in ItemNumberList(0) and
  EndKey is assigned the item number in
  ItemNumberList(3).

59
Display Records w/Paging

• At the end of the loop the StartIndex is moved
  forward to point to the array element following
  the previous EndIndex element (StartIndex
  EndIndex 1). During the next iteration of the
  loop StartIndex 4 and EndIndex 7, and the
  item numbers in array elements 4 and 7 get
  assigned to the second button. This indexing
  continues until all five buttons are created.
• Depending on the number of records in the
  Products table there is a good possibility that
  the last button retrieves fewer records than the
  other buttons. For example, if the table contains
  only 18 records then the last button retrieves
  only two records (4 4 4 4 2). This is why
  an "end-of-array" check is made when calculating
  the EndIndex for a button.

60
Display Records w/Paging

• EndIndex StartIndex (PageSize - 1)
• If EndIndex gt ItemNumberList.Count - 1 Then
• EndIndex ItemNumberList.Count - 1
• End If
• If the calculation produces an index value that
  is beyond the upper limit of the array, then the
  EndIndex is set to that last element.
• In all of the calculations an index value is
  always one less than a count (ItemNumberList.Count
  - 1 and PageSize - 1). These adjustments are
  needed because arrays are indexed beginning with
  0 and counts begin with 1.

61
Display Records w/Paging

• Creating Buttons with Scripts The last section
  of code in the loop creates a new aspButton
  control containing a CommandName property with a
  range of items numbers to be retrieved for
  display.
• '-- Create a button and assign to placeholder
• Dim PageButton As Button
• PageButton New Button()
• PageButton.Text i
• PageButton.id "P" i
• PageButton.CommandArgument StartKey ""
  EndKey
• PageButton.Style("width") "20px"
• PageButton.Style("background-color") "F0F0F0"
• AddHandler PageButton.Command, AddressOf
  DisplayRepeater
• PageButtons.Controls.Add(PageButton)

62
Display Records w/Paging

• A button is created programmatically by assigning
  it to a reference variable with variable New
  Button(), where variable is the
  programmer-supplied reference to the new button.
  Here, PageButton is used as this reference.
• Once the button is created, its properties can be
  assigned. Its Text property is set to the loop
  index i to provide a label showing the page
  number. It is given an id property by appending
  the loop index to the character "P", creating
  buttons P1, P2, P3, P4, and P5. There is nothing
  significant about the id it is simply a unique
  identifier for the button.
• The button's CommandArgument property is now set
  to a string composed of the beginning (StartKey)
  and ending (EndKey) item numbers determined for
  this button, concatenated with the separator
  character "". This, finally, is what all the
  previous work was about. The button is also style
  with a width and background color so that all
  buttons have the same size and appearance.

63
Display Records w/Paging

• To be activated, a button needs an event handler.
  It is supplied with an (on) Command handler to
  call the DisplayRepeater subroutine.
• AddHandler PageButton.Command, AddressOf
  DisplayRepeater
• An AddHandler statement is in the general format
• AddHandler object.event, AddressOf subprogram
• The Visual Basic AddHandler procedure adds a
  named event handler to an object, specifying the
  AddressOf subroutine to call when the event
  handler is invoked.
• With the button fully defined it is added to the
  placeholder created earlier as the location for
  paging buttons
• PageButtons.Controls.Add(PageButton)
• The button is added to the placeholder's
  Controls collection through the collection's
  Add() method as
• controlscollection.Add(object)
• In this case a new PageButton object is added to
  the PageButtons.Controls collection. When the
  processing loop finishes, the full complement of
  buttons will have been added to the placeholder
  and appear on the page.

64
Display Records w/Paging

• Displaying the Repeater The paging buttons call
  the DisplayRepeater subroutine to retrieve and
  display the subset of product records indicated
  in their CommandName property.
• The subprogram needs to access this property,
  parse the beginning and ending item numbers from
  the string, and display those records.
• The signature of the following subprogram
  requires a call from a command button.

65
Display Records w/Paging

• Sub DisplayRepeater (Src As Object, Args As
  CommandEventArgs)
• Dim Keys() As String
• Keys Split(Args.CommandName, "")
• '-- Bind the Repeater
• DBConnection New OleDbConnection( _
• "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Data Sourced\Databases\eCommerce.mdb")
• DBConnection.Open()
• SQLString "SELECT FROM Products WHERE " _
• "ItemNumber gt '" Keys(0) "' AND " _
• "ItemNumber lt '" Keys(1) "' "ORDER BY
  ItemNumber
• DBCommand New OleDbCommand(SQLString,
  DBConnection)
• DBReader DBCommand.ExecuteReader()
• RepeaterDisplay.DataSource DBReader
• RepeaterDisplay.DataBind()
• DBReader.Close()
• DBConnection.Close()

66
Display Records w/Paging

• '-- Highlight clicked button
• Dim Item As Button
• Dim ThisButton As Button
• For Each Item in PageButtons.Controls
• ThisButton CType(Item, Button)
• ThisButton.Style("background-color")
  "F0F0F0
• ThisButton.Style("color") "000000"
• Next
• ThisButton CType(PageButtons.FindControl(Src.id)
  , _
• Button)
• ThisButton.Style("background-color") "990000
• ThisButton.Style("color") "FFFFFF"
• End Sub

67
Display Records w/Paging

• Extraction of the item numbers from the passed
  CommandArgument uses the Visual Basic Split()
  statement to parse the string Args.CommandArgument
  into elements of array Keys, splitting the
  string at the "" character. As a result, the
  beginning item number is in Keys(0) and the
  ending item number is in Keys(1). Other string
  methods could be used, but the Split() method is
  automatic and easy.
• Now, an appropriate SQL statement can be composed
  to retrieve these records
• Taking the first button as an example, the SQL
  statement becomes
• SELECT FROM Products WHERE _
• ItemNumber gt 'BU1111' AND _
• ItemNumber lt 'DB1111'
• The statement is issued against the Products
  table and four records are retrieved and bound to
  the Repeater.

68
Display Records w/Paging

• Finding Scripted Controls The last statements in
  the subprogram highlight the clicked button for
  visual emphasis, changing its background and text
  colors. First, though, the previously highlighted
  button needs to be un-highlighted. The script
  loops through the placeholder's Controls
  collection (PageButtons.Controls) converting each
  control to a button object (CType(Item, Button))
  and setting its background and foreground colors
  to normal.
• Dim Item As Button
• Dim ThisButton As Button
• For Each Item in PageButtons.Controls
• ThisButton CType(Item, Button)
• ThisButton.Style("background-color") "F0F0F0
• ThisButton.Style("color") "000000"
• Next

69
Display Records w/Paging

• The button that is clicked needs to be found
  among the several buttons appearing in the
  placeholder so that its background and foreground
  colors can be set.
• To locate script-generated controls on a page the
  FindControl() method of the Controls collection
  is used. Its general format is shown below
• controlscollection.FindControl("id")

70
Display Records w/Paging

• The identify of the button that is clicked is
  given by the Src.id argument passed to the
  subprogram when it is called. Therefore, the
  PageButtons collection is searched to locate this
  control and to convert it to a button object.
  Then its background and foreground colors can be
  set.
• As one final touch, the first button in the group
  should be highlighted when the page first loads
  and the Repeater displays the first subset of
  records. Therefore, a routine to do this is added
  at the end of the Page_Load script.

71
Display Records w/Paging

• Sub Page_Load
• If Not Page.IsPostBack Then
• ...display initial Repeater
• End If
• ...create paging buttons
• If Not Page.IsPostBack Then
• Dim FirstButton As Button
• FirstButton CType(PageButtons.FindControl("P1"),
  Button)
• FirstButton.Style("background-color") "990000
• FirstButton.Style("color") "FFFFFF"
• End If
• End Sub

72
Display Records w/Paging

• The first button has id"P1" because of the
  naming convention chosen, so this is the control
  that is found and styled.
• Note that is routine is not part of the previous
  If Not Page.IsPostBack routine within which the
  Repeater is initially displayed. This first
  button cannot be highlighted until after the
  Page_Load script finishes creating all the
  buttons. You can, though, package the Repeater
  display and this routine together as long as they
  both appear last in the Page_Load script.
• As mentioned previously, the paging techniques
  presented here are adaptable to any of the
  information display controls -- aspRepeater,
  aspDataGrid, and aspDataList. The reason these
  paging buttons can be used for any of the
  controls is that they are not part of the
  controls themselves. They reside separately
  inside an aspPlaceHolder control that can be
  displayed along side any of the information
  display controls. Also, when applied to other
  database tables, the range of keys associated
  with the buttons can be easily determined from
  the values in the data field used to identify
  subsets of records.
• PagedRepeater.aspx

73
Adding Records

• Records are added to a database table through a
  form presenting input areas for entering the
  fields of information.
• A button then calls a subroutine to write the new
  information to the table with an SQL INSERT
  command.

74
Add Form

• An add form is formatted in a table with server
  controls for data input areas. With the exception
  of the aspDropDownList control for the ItemType
  field, all controls are aspTextBox controls.
• Associated with these input controls are
  aspLabel controls for displaying error messages
  resulting from data entry problems. These message
  areas have their EnableViewState properties set
  to "False" to keep previous messages from
  reappearing on form postings.

75
Add Form

• ltdiv class"head"gtProduct Addlt/divgt
• lttable id"AddTable" border"1" rules"rows"gt
• lttrgt
• ltthgt Item Number lt/thgt
• lttdgtltaspTextBox id"ItemNumber" runat"server"
  _
• Columns"6 MaxLength"6"/gtlt/tdgt
• lttdgtltaspLabel id"ItemNumberMessage"
  runat"server" _
• ForeColor"FF0000" EnableViewState"False"/gtlt/t
  dgt
• lt/trgt
• lttrgt
• ltthgt Item Type lt/thgt
• lttdgtltaspDropDownList id"ItemType"
  runat"server"/gtlt/tdgt
• lttdgtlt/tdgt
• lt/trgt

76
Add Form

• lttrgt
• ltthgt Item Supplier lt/thgt
• lttdgtltaspTextBox id"ItemSupplier"
  runat"server" _
• Columns"40" MaxLength"50"/gtlt/tdgt
• lttdgtltaspLabel id"ItemSupplierMessage"
  runat"server" _
• ForeColor"FF0000" EnableViewState"False"/gtlt/t
  dgt
• lt/trgt lttrgt
• ltthgt Item Name lt/thgt
• lttdgtltaspTextBox id"ItemName" runat"server" _
• Columns"40" MaxLength"50"/gtlt/tdgt
• lttdgtltaspLabel id"ItemNameMessage"
  runat"server" _
• ForeColor"FF0000" EnableViewState"False"/gtlt/t
  dgt
• lt/trgt
• lttrgt
• ltthgt Item Description lt/thgt
• lttdgtltaspTextBox id"ItemDescription"
  runat"server" _
• TextMode"MultiLine" Columns"45"
  rows"3"/gtlt/tdgt
• lttdgtltaspLabel id"ItemDescriptionMessage"
  runat"server" _
• ForeColor"FF0000" EnableViewState"False"/gtlt/t
  dgt

77
Add Form

• lttrgt
• ltthgt Item Price lt/thgt
• lttdgt ltaspTextBox id"ItemPrice" runat"server"
  _
• Columns"7" MaxLength"6"/gtlt/tdgt
• lttdgtltaspLabel id"ItemPriceMessage"
  runat"server" _
• ForeColor"FF0000" EnableViewState"False"/gtlt/t
  dgt
• lt/trgt
• lttrgt
• ltthgt Item Quantity lt/thgt
• lttdgtltaspTextBox id"ItemQuantity"
  runat"server" _
• Columns"3" MaxLength"3"/gtlt/tdgt
• lttdgtltaspLabel id"ItemQuantityMessage"
  runat"server" _
• ForeColor"FF0000" EnableViewState"False"/gtlt/t
  dgt
• lt/trgt
• lt/tablegt

78
Add Form

• ltbrgt
• ltaspButton Text"Add Record" _
• OnClick"AddRecord" runat"server"/gt
• ltaspButton Text"Clear Form" _
• OnClick"ClearForm" runat"server"/gt
• ltaspLabel id"AddRecordMessage" _
• runat"server" ForeColor"FF0000" _
• EnableViewState"False"/gt

79
Add Form

• When defining text input areas for data to be
  written to a database, it is particularly
  important that the length of the entered data
  does not exceed the size of the field in the
  database. This mismatch in field sizes causes
  execution errors.
• Therefore, all of the aspTextBox controls which
  have restricted sizes in the database have their
  MaxLength sizes set to the size of the field in
  the database table so that no more than this
  number of characters can be entered.

80
Loading the Product Types

• The product type input field is an
  aspDropDownList supplying the valid product
  types (one way of reducing the possibility of
  input errors).
• This list is created when the page is first
  loaded by populating the control with ItemType
  values from the Products table.

81
Loading the Product Types

• Sub Page_Load
• If Not Page.IsPostBack Then
• '-- Load drop-down list with item types
• DBConnection New OleDbConnection( _
• "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Data Sourced\Databases\eCommerce.mdb")
• DBConnection.Open()
• SQLString "SELECT DISTINCT ItemType FROM
  Products _
• ORDER BY ItemType"
• DBCommand New OleDbCommand(SQLString,
  DBConnection)
• DBReader DBCommand.ExecuteReader()
• ItemType.DataSource DBReader
• ItemType.DataTextField "ItemType"
• ItemType.DataValueField "ItemType"
• ItemType.DataBind()
• DBReader.Close()
• DBConnection.Close()
• End If
• End Sub

82
Checking Entered Data

• When the "Add Record" button is clicked, the
  AddRecord subroutine is called to add the entered
  data as a new record in the Products table.
• Before writing a record, though, the entered data
  needs to be validated as best possible.
• The first part of the AddRecord subroutine puts
  the entered data through a series of editing
  checks.

83
Checking Entered Data

• Sub AddRecord (Src As Object, Args As EventArgs)
• '-- CHECK FOR VALID RECORD ---
• Dim ValidRecord As Boolean True
• '-- Check for valid ItemNumber
• If Len(ItemNumber.Text) ltgt 6 Then
• ItemNumberMessage.Text "Invalid Item Number
  length"
• ValidRecord False
• ElseIf Not IsNumeric(Right(ItemNumber.Text,4))
  Then
• ItemNumberMessage.Text "Invalid Item Number
  format"
• ValidRecord False
• Else
• ItemNumber.Text UCase(ItemNumber.Text)
• End If

84
Checking Entered Data

• '-- Check for missing Item Supplier
• If ItemSupplier.Text "" Then
• ItemSupplierMessage.Text "Missing Item
  Supplier"
• ValidRecord False
• End If
• '-- Check for missing Item Name
• If ItemName.Text "" Then
• ItemNameMessage.Text "Missing Item Name"
• ValidRecord False
• End If
• '-- Check for missing Item Description
• If ItemDescription.Text "" Then
• ItemDescriptionMessage.Text "Missing Item
  Description"
• ValidRecord False
• End If

85
Checking Entered Data

• '-- Check for valid Item Price
• If Not IsNumeric(ItemPrice.Text) Then
• ItemPriceMessage.Text "Invalid Item Price
  format"
• ValidRecord False
• End If
• '-- Check for valid Item Quantity
• If Not IsNumeric(ItemQuantity.Text) Then
• ItemQuantityMessage.Text "Invalid Item
  Quantity format"
• ValidRecord False
• End If
• If ValidRecord True Then
• ...continue...
• End If
• End Sub

86
Checking Entered Data

• An edit flag -- variable ValidRecord -- is
  initialized as True, and at the end of the
  editing routines indicates whether or not an
  error was discovered in the entered data. If its
  value remains True, the script can continue the
  process of adding the new record to the table.
• The editing routines look for missing data in a
  field, a sufficient number of characters in the
  field, or, in the case of the price and quantity
  fields, numeric characters only. If an error is
  discovered, ValidRecord is set to False and an
  appropriate error message is written to the
  message label accompanying the field.

87
Checking for an Existing Record

• If the entered data passes all the editing
  checks, it is still necessary to make sure that
  the record being added does not have the same
  ItemNumber value as an existing record in the
  table.
• The ItemNumber is the unique record "key," and
  duplicates are not allowed.

88
Checking for an Existing Record

• If ValidRecord True Then
• '--- CHECK FOR DUPLICATE RECORD ---
• DBConnection New OleDbConnection( _
• "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Data Sourced\Databases\eCommerce.mdb")
• DBConnection.Open()
• SQLString "SELECT Count() FROM Products " _
• WHERE ItemNumber '" ItemNumber.Text "'"
• DBCommand New OleDbCommand(SQLString,
  DBConnection)
• If DBCommand.ExecuteScalar() ltgt 0 Then
• AddRecordMessage.Text "Duplicate Item Number.
  Record _
• not added."
• ValidRecord False
• End If
• DBConnection.Close()
• End If

89
Checking for an Existing Record

• A check for a duplicate record is made by getting
  a count of the number of records in the table
  with the same ItemNumber as was entered in the
  data entry form. The following SQL statement is
  issued against the database
• "SELECT Count() FROM Products _
• WHERE ItemNumber '" _
• ItemNumber.Text "'"

90
The ExecuteScalar() Method

• To return a count of matching records the
  ExecuteScalar() method of the Command object is
  used. This method is used when returning a single
  value from an SQL query rather than a recordset
  requiring a data reader. In this case the single
  returned value is a count of the number of
  records matching the input item number that can
  be assigned to a variable and tested
• Dim RecordCount As Integer
• RecordCount DBCommand.ExecuteScalar()
• If RecordCount ltgt 0 Then
• AddRecordMessage.Text "Duplicate Item _
• Number. Record not added."
• ValidRecord False
• End If

91
Adding a Table Record

• If all editing checks are passed and there is not
  a duplicate record in the Products table, then
  the new record can be added to the table.
• The database access script is placed inside a
  Try...Catch structure to trap for any remaining
  errors that could cause script execution problems.

92
Adding a Table Record

• '-- ADD A NEW RECORD
• Try
• DBConnection New OleDbConnection( _
• "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Data Sourced\Databases\eCommerce.mdb")
• DBConnection.Open()
• SQLString "INSERT INTO Products " _
• "(ItemNumber, ItemType, ItemSupplier, ItemName,
  " _
• "ItemDescription, ItemPrice, ItemQuantity) "
  _
• "VALUES (" "'" ItemNumber.Text "', " _
  
• "'" ItemType.SelectedItem.Value "', " _
• "'" Replace(ItemSupplier.Text, "'", "''")
  "', " _
• "'" Replace(ItemName.Text, "'", "''") "', "
  _
• "'" Replace(ItemDescription.Text, "'", "''")
  "', " _
• ItemPrice.Text ", " ItemQuantity.Text ")"

93
Adding a Table Record

• DBCommand New OleDbCommand(SQLString,
  DBConnection)
• DBCommand.ExecuteNonQuery
• DBConnection.Close()
• AddMessage.Text "Record added
• Catch
• AddMessage.Text "Update problem. _
• Record not added. " SQLString
• End Try

94
Adding a Table Record

• The entered data values are inserted into the
  table with an SQL INSERT statement. An example of
  the format of this statement when data values are
  added is
• INSERT INTO Products (ItemNumber, _
• ItemType, ItemSupplier, ItemName, _
• ItemDescription, ItemPrice, ItemQuantity) _
• VALUES ('BU5555', 'Business Office', _
• 'Microsoft', 'Visio', 'Description of
  product...', _
• 399.99, 10)

95
Adding a Table Record

• The SQL statement is composed by concatenating
  literal text and input values, making sure to
  surround string values with single quotes
  (apostrophes) and to separate values with commas.
  For instance, the input item number is
  concatenated to the statement with
• "'" ItemNumber.Text "', "

96
Adding a Table Record

• A single quote is concatenated with the input
  value and with a closing single quote to produce,
  say, 'BU5555' as the value to be written to the
  ItemNumber field of the database.
• Whether a string or numeric value is formatted
  depends on the field type in the database.

97
Replacing Apostrophes in Tex