Transcript Aspnet3

ASP.NET
Database Connection
ASP.NET Framework

The ASP.NET framework includes the
ADO.NET data access technology for working
with databases and other OLE DB data
sources
 ASP.NET pages that connect to databases
must gain access to the system classes that
provide data access functionality. For working
with Microsoft Access and other databases
that use OLE DB providers, the page must
import the System.Data.OleDb namespace
Database Namespace Directive

System.Data.OleDb namespace can be imported by
including the following directive at the top of the page:
<%@ Import Namespace="System.Data.OleDb" %>

This namespace includes the three basic classes
needed to work with Access databases:
 OleDbConnection - for connecting to a database
 OleDbCommand - for issuing SQL queries against
database tables
 OleDbDataReader - for access to recordsets retrieved
through SQL queries
Product Table
Field Name
Data Type
ItemNumber
Text
ItemType
Text
ItemSupplier
Text
ItemName
Text
ItemDescription Memo
ItemPrice
Currency
ItemQuantity
Number
Field Size
6
20
20
50
Long Integer
Example Data
OS1111
Operating System
Microsoft
Windows XP
Windows XP is
$149.95
20
Opening a DB Connection

A connection to a database is made by
creating an OleDbConnection object
that can be used to access the database.

After the connection is established then,
this connection's Open() method is used
to open the database.
Opening a DB Connection

Dim DBConnection As OleDbConnection
DBConnection = New
OleDbConnection(ConnectionString)
DBConnection.Open()
or
Dim DBConnection = New
OleDbConnection(ConnectionString)
DBConnection.Open()
Opening a DB Connection

DBConnection is a programmer-supplied
reference; the ConnectionString specifies the
OLE DB Provider (the database type) and the
Data Source (the physical path to the
database on the server).
 For Access databases, the Provider is:
"Microsoft.Jet.OLEDB.4.0"
 the Data Source is in the format:
"drive:\folder\folder\...\database.mdb".
 The two clauses are separated by a semicolor
and compose a single string.
Opening a DB Connection – Ex.

<%@ Import Namespace="System.Data.OleDb" %>
<SCRIPT runat="server">
Dim DBConnection As OleDbConnection
Sub Page_Load
'-- Open a database connection
DBConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
End Sub
</SCRIPT>
Selecting Records
Selecting records from a database table
to display or to edit is made through the
OleDbCommand object.
 This selection is normally an SQL
command issued through the
OleDbCommand object against the
database.

Selecting Records

Dim DBCommand As OleDbCommand
DBCommand = New
OleDbCommand(CommandString, DBConnection)
or
Dim DBCommand = New
OleDbCommand(CommandString, DBConnection)

DBCommand is a programmer-supplied reference.
The CommandString is an SQL statement to access a
set of records from the database; the DBConnection is
a reference to the database connection opened
previously.
Selecting Records – Ex.

<%@ Import Namespace="System.Data.OleDb" %>
<SCRIPT runat="server">
Dim DBConnection As OleDbConnection
Dim DBCommand As OleDbCommand
Dim SQLString As String
Sub Page_Load
'-- Open a database connection
DBConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
'-- Create and issue SQL command through the database connection
SQLString = "SELECT * FROM Products"
DBCommand = New OleDbCommand(SQLString, DBConnection)
End Sub
</SCRIPT>
Creating a DataReader


Any of the SQL statement types (SELECT, INSERT,
UPDATE, DELETE, and so forth) can be issued
through the OleDbCommand object. When issuing a
SELECT statement, a set of records (a recordset) is
returned from the database and made available to the
script. In this case a mechanism is needed for iterating
through the recordset and specifying fields of data to
be displayed or otherwise processed.
An OleDbDataReader object represents a stream of
database records returned from a SELECT statement
issued through the OleDbCommand object. A data
reader is created by using the ExecuteReader()
method of the OleDbCommand object
Creating a DataReader

Dim DBReader As OleDbDataReader
DBReader = DBCommand.ExecuteReader()
or
Dim DBReader =
DBCommand.ExecuteReader()

DBReader is a programmer-supplied
reference; DBCommand is a reference to the
OleDbCommand object previously created for
issuing the SQL statement
Creating a DataReader – Ex.

<%@ Import Namespace="System.Data.OleDb" %>
<SCRIPT runat="server">
Dim DBConnection As OleDbConnection
Dim DBCommand As OleDbCommand
Dim DBReader As OleDbDataReader
Dim SQLString As String
Sub Page_Load
'-- Open a database connection
DBConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
'-- Create and issue an SQL command through the database connection
SQLString = "SELECT * FROM Products"
DBCommand = New OleDbCommand(SQLString, DBConnection)
'-- Create a recordset of selected records from the database
DBReader = DBCommand.ExecuteReader()
End Sub
</SCRIPT>
Accessing through DataReader


An OleDbDataReader represents a stream of
database records that are made available to a script
one record at a time. It is a forward-only recordset (it
cannot be read backwards), and individual records are
made available with its Read() method.
When the Read() method is called, two events occur:


First, it returns True if a next record is available in the
recordset, or it returns False if no additional records are
available.
Second, it advances to the next record if one is available.
These pair of events make it very easy to iterate through the
records in the OleDbDataReader.
Accessing through DataReader

While DBReader.Read()
...process database record
End While
 An OleDbDataReader supplies a complete
data record (table row) one at a time.
Normally, the interest is in working with
individual data fields within the record. In order
to specify a data field, the following format is
used:
DataReader("FieldName")
 DataReader is a reference to a previously
created OleDbDataReader object. FieldName
is the name of a table column in the database
DataReader - Ex.

<%@ Import Namespace="System.Data.OleDb" %>
<SCRIPT runat="server">
Dim DBConnection As OleDbConnection
Dim DBCommand As OleDbCommand
Dim DBReader As OleDbDataReader
Dim SQLString As String
Sub Page_Load
'-- Open a database connection
DBConnection = New OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
'-- Create and issue an SQL command through the database connection
SQLString = "SELECT * FROM Products"
DBCommand = New OleDbCommand(SQLString, DBConnection)
'-- Create a recordset of selected records from the database
DBReader = DBCommand.ExecuteReader()
'-- Read through the recordset one record at a time
While DBReader.Read()
...process DBReader("ItemNumber")
...process DBReader("ItemName")
...process DBReader("ItemPrice")
End While
End Sub
Closing DB Connection

When access to a database is no longer
required both the data reader and database
connection should be closed.
 Each of these task is accomplished with their
respective Close() methods, as added below
to the continuing script.
 '-- Close the reader and database connections
DBReader.Close()
DBConnection.Close()
Binding DataReader to a Control

Under ASP.NET a typical method of working with a
data reader is to bind it to one of the listing controls:
asp:Repeater, asp:DataList, or asp:DataGrid. In this
case it is not necessary to iterate through the records
in the data reader with a While...End While loop.
Instead, the data source is bound to the control.
 The following script binds the data reader to an
asp:DataGrid control which has the id value of
MyDataGrid:
'-- Bind the recordset to a control
MyDataGrid.DataSource = DBReader
MyDataGrid.DataBind()
<asp:DataGrid id="MyDataGrid" runat="server"/>
Binding DataReader to a Control



For purpose of retrieving and displaying records in the
table, the records can be iterated within a While...End
While loop, giving access to each of the individual
records and their separate data fields.
For purpose of retrieving and displaying records in the
table, the data reader can be bound to one of the
display controls where they are automatically iterated
and bound to the control to produce a complete listing
of the recordset.
The method chosen -- recordset iteration or control
binding -- depends mostly on programmer
preferences and characteristics of the database
application.
Accessing Single Table Value



For certain applications it may not be necessary to
extract a complete set of records from a database
table. For instance, you may wish simply to get a
count of the number of records in the table using a
SELECT statement with, say, a Count function:
SELECT Count(*) FROM Products
In this case a data reader is not required since no
records are return by the query. All that is returned is a
numeric value representing the number of records.
Extracting single values from a table is accomplished
with the OleDbCommand object's ExecuteScalar()
method (rather than its ExecuteReader() method). The
returned value can be assigned to a variable.
Accessing Single Table Value

<%@ Import Namespace="System.Data.OleDb" %>
<SCRIPT runat="server">
Dim DBConnection As OleDbConnection
Dim DBCommand As OleDbCommand
Dim SQLString As String
Dim TheCount As Integer
Sub Page_Load
DBConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
SQLString = "SELECT Count(*) FROM Products"
DBCommand = New OleDbCommand(SQLString, DBConnection)
TheCount = DBCommand.ExecuteScalar()
DBConnection.Close()
End Sub
</SCRIPT>

The ExecuteScalar() method is used with other SQL functions that return single
values such as MIN, MAX, AVG, and others.
Updating a Table


The SQL INSERT, UPDATE, and DELETE statements
are used to edit records in a database table, adding
new records or changing or deleting existing records.
When these statements are issued no recordset is
returned; the affected record is updated in place within
the database.
There is no requirement for a data reader. Instead,
these statements are issued through the command
object's ExecuteNonQuery() method.
Updating a Table

'-- Create and issue an SQL UPDATE
'-- command through the database connection
SQLString = "UPDATE Products _
SET ItemQuantity=0 _
WHERE ItemNumber='BU1111'"
DBCommand = New
OleDbCommand(SQLString, DBConnection)
DBCommand.ExecuteNonQuery()
Contingency Binding


Occasionally, SQL SELECT statements do not return a
recordset that can be bound to an output control. The
recordset is empty because the SQL statement was in
error or because no existing records matched the
selection criteria. This situation may not cause a
processing error, but you may not wish to display a
partial or empty control where a recordset would
otherwise display.
Fortunately, controls that are bound to a recordset are
displayed only when data are bound to them. A
common way of ensuring that a control is displayed
only when it has records to display is by first getting a
count of the records matching the search criteria, then
binding to the control only if the count is greater than
0.
Contingency Binding
SQLString = "SELECT Count(*) FROM Products WHERE ItemType = 'Business'"
DBCommand = New OleDbCommand(SQLString, DBConnection)
If DBCommand.ExecuteScalar() <> 0 Then
SQLString = "SELECT * FROM Products WHERE ItemType = 'Business'"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
MyRepeater.DataSource = DBReader
MyRepeater.DataBind()
DBReader.Close()
End If
DBConnection.Close()
<asp:Repeater id="MyRepeater" runat="server">
</asp:Repeater>


In the above example a test is first made of the number of records retrieved by an
SQL statement issued to retrieve records that meet a particular search criteria. If
this count is not 0, then a second SQL statement is issued to retrieve the
matching recordset.
The record count is not assigned to a variable as a way to capture its value. The
direct result of executing the DBCommand.ExecuteScalar() statement is tested.
Contingency Binding




Even though an asp:Repeater is coded on the page it
does not display unless data are bound to it.
If the DataBind() method is issued (when the record
count <> 0), then the Repeater is displayed.
If the DataBind() method is not issued (when the
record count = 0), then the Repeater is not displayed.
The control is displayed only when it has data to
display -- when it is bound to a recordset.
This is the case for all bound controls, and it relieves
the programmer from having to script the visibility of a
control depending on the number of records retrieved.
Display Table Values
<%@ Import Namespace="System.Data.OleDb" %>
<SCRIPT runat="server">
Dim DBConnection As OleDbConnection
Dim DBCommand As OleDbCommand
Dim DBReader As OleDbDataReader
Dim SQLString As String
Sub Page_Load
'-- Display table header
MyTable.Text = "<table border=""1"" style=""border-collapse:collapse"">"
MyTable.Text &= "<tr style=""background-color:#F0F0F0"">"
MyTable.Text &= "<th>No</th>"
MyTable.Text &= "<th>Type</th>"
MyTable.Text &= "<th>Supplier</th>"
MyTable.Text &= "<th>Name</th>"
MyTable.Text &= "<th>Price</th>"
MyTable.Text &= "<th>Qty</th>"
MyTable.Text &= "</tr>"
Display Table Values
DBConnection = New OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\inetpub\wwwroot\eCommerce.mdb")
DBConnection.Open()
SQLString = "SELECT * FROM Products ORDER BY ItemNumber"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
While DBReader.Read()
'-- Display table rows
MyTable.Text &= "<tr>"
MyTable.Text &= "<td>" & DBReader("ItemNumber") & "</td>"
MyTable.Text &= "<td>" & DBReader("ItemType") & "</td>"
MyTable.Text &= "<td>" & DBReader("ItemSupplier") & "</td>"
MyTable.Text &= "<td>" & DBReader("ItemName") & "</td>"
MyTable.Text &= "<td align=""right"">" & DBReader("ItemPrice") & "</td>"
MyTable.Text &= "<td align=""right"">" & DBReader("ItemQuantity") & "</td>"
MyTable.Text &= "</tr>"
End While
DBReader.Close()
DBConnection.Close()
Display Table Values
'-- Display table footer
MyTable.Text &= "</table>"
End Sub
</SCRIPT>
<html>
<body>
<form runat="server">
<asp:Label id="MyTable" runat="server"/>
</form>
</body>
</html>
DisplayTable.aspx
Display Table Values


When the possibility exists that an SQL query will not return a set
of records, it is always a good idea to anticipate and deal with the
possibility that column headings may display but no rows of data
appear.
As was suggested previously, the script is modified to check for a
returned recordset and provide explanation if none were
retrieved.
SQLString = "SELECT Count(*) FROM Products WHERE
ItemType='Business'"
DBCommand = New OleDbCommand(SQLString, DBConnection)
If DBCommand.ExecuteScalar() <> 0 Then
...
Else
MyTable.Text &= "<tr><td colspan=""6"" style=""color:#FF0000"">"
MyTable.Text &= "No matching records"
MyTable.Text &= "</td></tr>"
End If
DisplayTableCheck.aspx
CalculatingTable Values

Since field values from a database table are
available during iteration of the table,
additional processing can be performed to
generate new information based on those
values.
 In the following example the ItemPrice and
ItemQuantity fields are multiplied to derive the
inventory value for each product. These values
are accumulated across all records and
reported in a total line appended to the output
table.
CalculatingTable Values

Sub Page_Load
Dim Amount As Decimal
Dim Total As Decimal = 0
'-- Display table header
MyTable.Text = "<table border=""1"" style=""border- _
collapse:collapse"">"
MyTable.Text &= "<tr style=""background-color:#F0F0F0"">"
MyTable.Text &= "<th>No</th>"
MyTable.Text &= "<th>Type</th>"
MyTable.Text &= "<th>Supplier</th>"
MyTable.Text &= "<th>Name</th>"
MyTable.Text &= "<th>Price</th>"
MyTable.Text &= "<th>Qty</th>"
MyTable.Text &= "<th>Amount</th>"
MyTable.Text &= "</tr>"
CalculatingTable Values

DBConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0; & _
Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
SQLString = "SELECT * FROM Products _
ORDER BY ItemNumber"
DBCommand = New OleDbCommand _
(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
CalculatingTable Values
While DBReader.Read()
'-- Calculate item amount
Amount = DBReader("ItemPrice") * DBreader("ItemQuantity")
Total += Amount
'-- Display table rows
MyTable.Text &= "<tr>“
MyTable.Text &= "<td>" & DBReader("ItemNumber") & "</td>“
MyTable.Text &= "<td>" & DBReader("ItemType") & "</td>“
MyTable.Text &= "<td>" & DBReader("ItemSupplier") & "</td>“
MyTable.Text &= "<td>" & DBReader("ItemName") & "</td>“
MyTable.Text &= "<td align=""right"">" & DBReader("ItemPrice") & "</td>“
MyTable.Text &= "<td align=""right"">" & DBReader("ItemQuantity") & "</td>“
MyTable.Text &= "<td align=""right"">" & _
FormatNumber(Amount) & "</td>“
MyTable.Text &= "</tr>“
End While
CalculatingTable Values

'-- Display table footer
MyTable.Text &= "<tr align=""right"" style=""background-color:#F0F0F0"">"
MyTable.Text &= "<td colspan=""6""><b>Total </b></td>"
MyTable.Text &= "<td>" & FormatCurrency(Total) & "</td>"
MyTable.Text &= "</tr>"
MyTable.Text &= "</table>"
End Sub
</SCRIPT>
<html><body><form runat="server>
<asp:Label id="MyTable" runat="server"/>
</form></body></html>

Two variables are declared at the beginning of the script: Amount holds the calculation
of ItemPrice times ItemQuantity for each product; Total is the accumulator for all the
Amounts and is initialized to 0.
Within the processing loop Amount is calculated as DBReader("ItemPrice") *
DBReader("ItemQuantity") for this product. This calculated Amount is added to the
Total. Within a new table column this Amount is displayed with FormatNumber()
formatting.
At the end of the processing loop variable Total, having accumulated all the individual
Amounts, is displayed in an added table row. It is formatted as a dollar amount.


DisplayTableCalc.aspx
Binding to Data Display Controls


The preferred ASP.NET method to display database
records is to bind the recordset to a list control such as
the asp:Repeater, asp:DataList, or asp:DataGrid
control.
For an asp:Repeater control, templates are provided
to describe output formatting. A table can be used to
display rows and columns of records, with individual
data items bound to the table cells. Also, alternating
row formatting can be specified. A column can be
provided for displaying a calculated amount for each
item, and a row can be added to the bottom of the
table for display of the inventory total.
Binding to a Repeater

<%@ Import _
Namespace="System.Data.OleDb" %>
<SCRIPT runat="server">
Dim DBConnection As OleDbConnection
Dim DBCommand As OleDbCommand
Dim DBReader As OleDbDataReader
Dim SQLString As String
Dim Amount As Decimal
Dim Total As Decimal = 0
Binding to a Repeater

Sub Page_Load
If Not Page.IsPostBack Then
DBConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
SQLString = "SELECT * FROM Products ORDER BY ItemNumber"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
RepeaterOutput.DataSource = DBReader
RepeaterOutput.DataBind()
DBReader.Close()
DBConnection.Close()
End If
End Sub
Binding to a Repeater

Function GetAmount(Price As Decimal, Quantity As Decimal)
Amount = Price * Quantity
Total += Amount
Return Amount
End Function
Function GetTotal()
Return Total
End Function
</SCRIPT>
Binding to a Repeater

<html>
<body>
<form runat="server">
<asp:Repeater id="RepeaterOutput" runat="server">
<HeaderTemplate>
<table border="1" style="border-collapse:collapse">
<tr style="background-color:#A0A0A0; color:#FFFFFF">
<th>No</th>
<th>Type</th>
<th>Supplier</th>
<th>Name</th>
<th>Price</th>
<th>Qty</th>
<th>Amount</th>
</tr>
</HeaderTemplate>
Binding to a Repeater
<ItemTemplate>
<tr>
<td><%# Container.DataItem("ItemNumber") %> </td>
<td><%# Container.DataItem("ItemType") %> </td>
<td><%# Container.DataItem("ItemSupplier") %> </td>
<td><%# Container.DataItem("ItemName") %> </td>
<td align="right"><%# Container.DataItem("ItemPrice")%>
</td>
<td align="right"><%# Container.DataItem("ItemQuantity")%>
</td>
<td align="right">
<%# FormatNumber(GetAmount(Container.DataItem("ItemPrice"), _
Container.DataItem("ItemQuantity"))) %></td>
</tr>
</ItemTemplate>

Binding to a Repeater
<AlternatingItemTemplate>
<tr style="background-color:#F0F0F0">
<td><%# Container.DataItem("ItemNumber") %></td>
<td><%# Container.DataItem("ItemType") %></td>
<td><%# Container.DataItem("ItemSupplier") %></td>
<td><%# Container.DataItem("ItemName") %></td>
<td align="right"><%# Container.DataItem("ItemPrice") %>
</td>
<td align="right"><%# Container.DataItem("ItemQuantity") %>
</td>
<td align="right">
<%# FormatNumber(GetAmount(Container.DataItem("ItemPrice"), _
Container.DataItem("ItemQuantity"))) %></td>
</tr>
</AlternatingItemTemplate>

Binding to a Repeater

<FooterTemplate>
<tr align="right">
<th colspan="6" style="background-color:#A0A0A0; _
color:#FFFFFF">Total</th>
<td><%# FormatCurrency(GetTotal()) %></td>
</tr>
</table>
</FooterTemplate>
</asp:Repeater>
</form>
</body>
</html>
Repeater.aspx
Binding to a Repeater

The script links to the database, extracts a
recordset, and binds the associated data
reader to the Repeater control.
 Scripting is placed inside the If Not
Page.IsPostBack condition because the
control only needs to be populated the first
time the page loads.
 Although it does not occur in this example, the
control would retain its data through the page's
View State if a page postback were made.
Binding to a Repeater
Data values extracted from the Products table are bound to the table
cells with a simple binding expression in the format <%#
Container.DataItem("FieldName") %>. A calculated amount for each
item is given by a function call to GetAmount() which passes the
ItemPrice and ItemQuantity from the associated record:
<%# FormatNumber(GetAmount(Container.DataItem("ItemPrice") _
Container.DataItem("ItemQuantity"))) %>
 The function receives these values as arguments Price and
Quantity, and multiplies them to derive the item Amount. At the same
time, this Amount is added to variable Total to accumulate the total
value of inventory. Variables Amount and Total have been declared
as global variables for access by the Repeater and by the function.
The function returns the calculated Amount, which is formatted as a
number with the built-in FormatNumber() function.

Binding to a Repeater

Incidentally, were it not for the fact that the
inventory Total is calculated by accumulating
item Amounts, the function call to GetAmount()
would not be needed.
 If only the item Amount is calculated, it could
be done by including the calculation inside the
Repeater cell:
<%# FormatNumber(Container.DataItem _
("ItemPrice") * Container.DataItem _
("ItemQuantity")) %>
Binding to a Repeater

Still, there is coding consistency in always using
function calls for calculated values. This consistency is
maintained by displaying the inventory Total at the
bottom of the Repeater table by a function call to
GetTotal():
<%# FormatCurrency(GetTotal()) %>

The function simply returns the value of variable Total.
This value could have been displayed without a
function call by embedding the variable itself inside the
binding expression:
<%# FormatCurrency(Total) %>
Binding to a DataGrid

An asp:DataGrid control provides both the easiest and
the most elaborate methods for displaying database
output. On one hand, the control can automatically
generate columns of output data to match the columns
of input data with only minimal specifications. On the
other hand, the control can be altered in numerous
ways to produce specialized output.

In its minimal state the asp:DataGrid control requires
only a single line of code:
<asp:DataGrid id="DataGridOutput" runat="server"/>
Binding to a DataGrid
DBConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0; _
Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
SQLString = "SELECT ItemNumber, ItemType, ItemSupplier, _
ItemName, ItemPrice, ItemQuantity FROM Products _
ORDER BY ItemNumber"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
DataGridOutput.DataSource = DBReader
DataGridOutput.DataBind()
DBReader.Close()
DBConnection.Close()
Datagrid.aspx
Binding to a DataGrid

The control is quick and functional but you
probably wish to have more control over its
display characteristics.
 In the following example, selected columns are
bound to the DataGrid using
<asp:BoundColumn> and <TemplateColumn>
controls. In this case only those specified
columns are displayed. Formatting styles are
also applied to the grid.
Binding to a DataGrid

<%@ Import _
Namespace="System.Data.OleDb" %>
<SCRIPT runat="server">
Dim DBConnection As OleDbConnection
Dim DBCommand As OleDbCommand
Dim DBReader As OleDbDataReader
Dim SQLString As String
Binding to a DataGrid

Sub Page_Load
If Not Page.IsPostBack Then
DBConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0; _
Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
SQLString = "SELECT * FROM Products _
ORDER BY ItemNumber"
DBCommand = New OleDbCommand _
(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
DataGridOutput.DataSource = DBReader
DataGridOutput.DataBind()
DBReader.Close()
DBConnection.Close()
End If
End Sub
Binding to a DataGrid

<html>
<body>
<form runat="server">
<asp:DataGrid id="DataGridOutput" runat="server" _
AutoGenerateColumns="False" _
CellPadding="2" _
GridLines="Horizontal" BorderWidth="1" _
HeaderStyle-BackColor="#A0A0A0" _
HeaderStyle-ForeColor="#FFFFFF" _
HeaderStyle-Font-Bold="True" _
HeaderStyle-HorizontalAlign="Center" _
ItemStyle-VerticalAlign="Top" _
AlternatingItemStyle-BackColor="#F0F0F0">
Binding to a DataGrid

<Columns>
<asp:BoundColumn
DataField="ItemNumber"
HeaderText="No"/>
<asp:BoundColumn
DataField="ItemType"
HeaderText="Type"/>
<asp:BoundColumn
DataField="ItemSupplier"
HeaderText="Supplier"/>
<asp:BoundColumn
DataField="ItemName"
HeaderText="Name"/>
Binding to a DataGrid

<asp:BoundColumn
DataField="ItemPrice"
HeaderText="Price"
ItemStyle-HorizontalAlign="Right"/>
<asp:BoundColumn
DataField="ItemQuantity"
HeaderText="Qty"
ItemStyle-HorizontalAlign="Right"/>
<asp:TemplateColumn
HeaderText="Description"
ItemStyle-VerticalAlign="Top">
Binding to a DataGrid

<ItemTemplate>
<div style="width:170px; height:40px; _
font-size:8pt; line-height:8pt; _
overflow:auto">
<%# Container.DataItem("ItemDescription") %>
</div>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</form>
</body>
</html>
DataGrid2.aspx
Binding to a DataGrid


Notice that the specification
AutoGenerateColumns="False" is coded for the
DataGrid so that display columns are not automatically
generated.
One of the database fields, ItemDescription, is an
Access Memo field containing free-form text. A Memo
field can hold over 65,000 characters. When setting up
the DataGrid, special provision needs to be made for
this field; otherwise its entire contents would display,
making for a very long and cumbersome output
display. An <asp:TemplateColumn> is added to the
DataGrid to handle this field.
Binding to a DataGrid
<asp:TemplateColumn HeaderText="Description“ _
ItemStyle-VerticalAlign="Top">
<ItemTemplate>
<div style="width:170px; height:40px; font-size:8pt; _
line-height:8pt; overflow:auto">
<%# Container.DataItem("ItemDescription") %>
</div>
</ItemTemplate>
</asp:TemplateColumn>
Binding to a DataGrid

Within the <ItemTemplate> an HTML division
is included with width and height settings to
restrict its size. Also, overflow:auto is applied
to the division that displays a vertical scroll bar
on the container if its contents cannot be fully
displayed. Using this setting permits display of
the ItemDescription field without it taking up
too much real estate on the page.
 When using template columns in a DataGrid a
binding expression is in the format:
<%# Container.DateItem("FieldName") %>
Adding Calculation to a DataGrid

The above example does not display a
calculated column or an inventory total as in
the previous Repeater. However, these can be
easily added to the DataGrid using the same
techniques as used for the Repeater.
 Of course, a template column, rather than a
bound column, is added to the DataGrid since
bound columns only map to existing recordset
fields, not to calculated values.
Binding to a DataList with
Calculations
The asp:DataList control gives a different
look to the output since all data items for
a single record appear in a single table
cell rather than being arranged in a grid.
 It is based on the use of templates to
describe the output format, and
embedded binding expressions show the
locations of displayed data.

Binding to a DataList with
Calculations
Dim Amount As Decimal
Dim Total As Decimal = 0
Sub Page_Load
If Not Page.IsPostBack Then
DBConnection = New OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0; _
Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
SQLString = "SELECT * FROM Products ORDER BY ItemNumber"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
DataListOutput.DataSource = DBReader
DataListOutput.DataBind()
DBReader.Close()
DBConnection.Close()
End If
End Sub
Binding to a DataList with
Calculations
Function GetAmount(Price As Decimal, _
Quantity As Decimal)
Amount = Price * Quantity
Total = Total + Amount
Return Amount
End Function
Function GetTotal()
Return Total
End Function
Binding to a DataList with
Calculations
</SCRIPT>
<html> <body> <form runat="server">
<asp:DataList id="DataListOutput" runat="server" GridLines="Both" _
CellPadding="3" RepeatColumns="2" RepeatDirection="Horizontal">
<ItemTemplate>
<img src="../Pictures/<%# Container.DataItem("ItemNumber") %>.jpg" _
width="70" align="left">
<table border="0" cellpadding="0" cellspacing="0">
<tr><td><b>Number: </b></td>
<td><%# Container.DataItem("ItemNumber") %> </td>
</tr>
<tr><td><b>Type: </b></td>
<td><%# Container.DataItem("ItemType") %> </td>
</tr>
Binding to a DataList with
Calculations
<tr><td><b>Supplier: </b></td>
<td><%# Container.DataItem("ItemSupplier") %></td></tr>
<tr><td><b>Title: </b></td>
<td><%# Container.DataItem("ItemName") %> </td></tr> <tr><td><b>Price:
</b></td><td>
<%# Container.DataItem("ItemPrice") %> </td></tr> <tr><td><b>Quantity:
</b></td><td>
<%# Container.DataItem("ItemQuantity") %></td></tr> <tr><td><b>Amount:
</b></td><td><b>
<%# FormatNumber(GetAmount(Container.DataItem("ItemPrice"),
Container.DataItem("ItemQuantity"))) %> </b></td></tr>
</table>
</ItemTemplate>
<FooterTemplate> <b>Inventory Total: <%# FormatCurrency(GetTotal())
%></b>
</FooterTemplate>
</asp:DataList> </form> </body> </html>
FullDataListCalc.aspx
Binding to a DataList with
Calculations


Binding to the DataList is virtually the same as for the
Repeater and DataList controls. In this case a product
picture is added to the display with an <img/> tag
whose src attribute links the ItemNumber from the
database with the graphic file name. That is, image file
BU1111.jpg is referenced by ItemNumber BU1111 with
".jpg" appended. Information inside the template cells
of the DataList are arranged in its own table to control
alignment.
There are numerous ways to display server-generated
data on a Web page. No one method is the "right" way.
Much will depend on how much trouble you wish to
spend in producing the displays or how much control
you want over the process.
Adding Calculation to a DataGrid
<asp:TemplateColumn
ItemStyle-VerticalAlign="Top"
ItemStyle-HorizontalAlign="Right">
<HeaderTemplate> Amount </HeaderTemplate>
<ItemTemplate>
<%# FormatNumber(GetAmount _
(Container.DataItem("ItemPrice"), _
Container.DataItem("ItemQuantity"))) %>
</ItemTemplate>
<FooterTemplate>
<%# FormatCurrency(GetTotal()) %>
</FooterTemplate>
</asp:TemplateColumn>
Binding to a DropDownList

The most popular server control for making
selections from databases is the
DropDownList.
 It is populated with record identifiers for
choosing which of the records in the table to
display or otherwise take action on.
 In the following example a list of ItemNumbers
from the Products table is displayed. The
selection retrieves the associated record.
Binding to a DropDownList
<asp:DropDownList id="ItemNumber" runat="server"/>
<asp:Button Text="Select" OnClick="Display_Product" runat="server"/>
<asp:Repeater id="ProductDisplay" runat="server">
<HeaderTemplate>
<table border="1" cellpadding="3" style="border-collapse:collapse">
<tr style="background-color:#F0F0F0">
<th>No</th>
<th>Type</th>
<th>Supplier</th>
<th>Title</th>
<th>Price</th>
<th>Qty</th>
<tr>
</HeaderTemplate>
Binding to a DropDownList
<ItemTemplate>
<tr>
<td align="center"><%# Container.DataItem("ItemNumber") %></td>
<td><%# Container.DataItem("ItemType") %></td>
<td><%# Container.DataItem("ItemSupplier") %></td>
<td><%# Container.DataItem("ItemName") %></td>
<td align="right"><%# Container.DataItem("ItemPrice") %></td>
<td align="right"><%# Container.DataItem("ItemQuantity") %></td>
</tr>
<tr>
<td colspan="6"><%# Container.DataItem("ItemDescription") %>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
Binding to a DropDownList
Sub Page_Load
If Not Page.IsPostBack Then
DBConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
SQLString = "SELECT ItemNumber FROM Products _
ORDER BY ItemNumber"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
ItemNumber.DataSource = DBReader
ItemNumber.DataTextField = "ItemNumber"
ItemNumber.DataBind()
DBReader.Close()
DBConnection.Close()
End If
End Sub
DropDownList.aspx
Binding to a DropDownList



The ItemNumber field is retrieved from all records in
the Products table, and the recordset is made
available in ascending order.
Binding this set of ItemNumbers to the DropDownList
requires the statements:
ItemNumber.DataSource = DBReader
ItemNumber.DataTextField = "ItemNumber"
ItemNumber.DataBind()
The DataSource property identifies the set of records
to be used. In the case of a recordset retrieved as a
data reader from a database, it is the data reader that
is the source of data for the control.
Binding to a DropDownList



The DataTextField property identifies the field from
the recordset that displays as items in the list. In this
example the ItemNumber field is displayed, being the
only field retrieved from the table. (Optionally, the
DataValueField could be specified if using a different
value from the ItemNumber. In this case the
ItemNumber is used as both the Text and Value
properties of the list.)
Finally, the DataBind() method is called to bind the
ItemNumbers to the DropDownList.
When the "Select" button is clicked, the
Display_Product subroutine is called to display
information about the selected product
Binding to a DropDownList

Sub Display_Product (Src As Object, Args As EventArgs)
DBConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
SQLString = "SELECT * FROM Products " & _
"WHERE ItemNumber = '" ItemNumber.SelectedItem.Text & "'"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
ProductDisplay.DataSource = DBReader
ProductDisplay.DataBind()
DBReader.Close()
DBConnection.Close()
End Sub
 The statement to notice is the SQL command:
SQLString = "SELECT * FROM Products " & _
"WHERE ItemNumber = '" & ItemNumber.SelectedItem.Text & "'"
which uses the Text property of the selection from the DropDownList to
retrieve all fields from the associated Products record.
Composing of SQL Strings

You need to become familiar with this method of
inserting values inside SQL statements. It can appear
confusing, but it's rather straight-forward if you
concentrate. The statement simply inserts a scriptgenerated value inside literal strings of SQL text to
arrive at an SQL statement in the format:
SELECT * FROM PRODUCTS WHERE ItemNumber = 'value'
So, the literal string "SELECT * FROM PRODUCTS
WHERE ItemNumber = '" is concatenated with the
value of the selected item
ItemNumber.SelectedItem.Text along with a final
closing single-quote string "'".
Binding to a DropDownList


A DataValueField, in addition to the DataTextField, can
be defined when a Value property for a drop-down
selection needs to be different from its Text property.
For instance, to provide a listing of ItemNames from
which to choose, still using the ItemNumber to retrieve
the associated record, binding of both Text and Value
properties is required.
The binding statements include both DataTextField
and DateValueField bindings:
ItemNumber.DataSource = DBReader
ItemNumber.DataTextField = "ItemName"
ItemNumber.DataValueField = "ItemNumber"
ItemNumber.DataBind()
Binding to a DropDownList


The SQL statement to supply these values to the
DropDownList includes retrieval of both fields and
ordering by the names:
SQLString = "SELECT ItemNumber, ItemName _
FROM Products ORDER BY ItemName"
In the Display_Product subprogram the SQL statement
to retrieve a record identifies the Value property of the
selection, rather than the Text property, as the value to
use in retrieving the matching record:
SQLString = "SELECT * FROM Products " & _
"WHERE ItemNumber = '" & _
ItemNumber.SelectedItem.Value & "'“
DropDownList2.aspx
Binding to a ListBox

These same binding techniques used for a
DropDownList apply to the asp:ListBox control,
where multiple items can be selected from the
list.
<asp:ListBox id="ItemNumbers" _
SelectionMode="Multiple" runat="server"/>

Use Ctrl-Click or Shift-Click to select one or
more items from the following ListBox.
Binding to a ListBox

When retrieving selected records from the database the SQL
statement must use multiple conditions within the WHERE clause
to match against the multiple selections. For example, if the first
three items in the list are selected, then the SQL statement would
have to be:
SELECT * FROM Products WHERE ItemNumber='DB1111' _
OR ItemNumber='GR3333' OR ItemNumber='WB4444'

The multiple conditions need to be appended to the SQL
statement for as many item as are selected. This is accomplished
within a For Each...Next loop that iterates through all the items in
the list and concatenates a condition test to the SQL statement
for each selected item.
Binding to a ListBox

Sub Display_Product (Src As Object, Args As EventArgs)
SQLString = "SELECT * FROM PRODUCTS WHERE "
Dim Item As ListItem
For Each Item in ItemNumbers.Items
If Item.Selected = True Then
SQLString &= "ItemNumber = '" & Item.Value _
& "' OR "
End If
Next
SQLString = Left(SQLString, Len(SQLString) - 4)
Binding to a ListBox
DBConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
DBCommand = New OleDbCommand_
(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
ProductDisplay.DataSource = DBReader
ProductDisplay.DataBind()
DBReader.Close()
DBConnection.Close()
End Sub
Binding to a ListBox

The SQL statement is composed by first assigning the literal text string
"SELECT * FROM Products WHERE " to the SQLString variable.
 Now, a For Each...Next loop checks whether an item in the list has been
selected. If so, then that item's Value (its associated ItemNumber) is
concatenated to the SQLString as a selection criterion:
SQLString &= "ItemNumber='" & Item.Value & "' OR ".
 If, for example, the first item in the list is selected, then SQLString
contains, at this point,
SELECT * FROM Products WHERE ItemNumber='DB1111' OR
 Continuing through the loop, all selected items are appended to
SQLString as a selection criterion. Thus, if the first three items are
selected, SQLString contains the following string at completion of the
loop:
SELECT * FROM Products WHERE ItemNumber='DB1111' OR _
ItemNumber='GR3333' OR ItemNumber='WB4444' OR
Listbox.aspx
Binding to a ListBox

The string continues to be built in this fashion for as many items
as are selected. At completion, though, the SQL statement is not
in valid format because it has an extra " OR " on the end of the
string. Therefore, these extra four characters (counting the blank
spaces) need to be removed:
SQLString = Left(SQLString, Len(SQLString) - 4)


The left-most (length - 4) characters of SQLString are reassigned
to the variable. Now a valid SQL statement is stored in
SQLString.
The remainder of the script is identical to before. The SQL
statement is issued to retrieve the matching records and the
resulting data reader is bound to the Repeater control.
Binding to a RadioButtonList

A RadioButtonList control (but not a
RadioButton control) can bind to data from a
database.
 This control has DataTextField and
DataValueField properties, the former serving
as the text label for the button and the latter as
the value for the button.
 If DataValueField is not defined, then the
DataTextField serves as both the label and the
value.
Binding to a RadioButtonList

In the following example, the ItemType field from the
Products table is used as the data source for the list of
radio buttons. Clicking the "Select" button displays all
the products of that type.

The RadioButtonList and accompanying button are
defined with the controls:
<asp:RadioButtonList id="RadioList" runat="server"/>
<asp:Button Text="Select“ _
OnClick="Display_Products" runat="server"/>
with binding script similar to that used for the dropdown list and which is run when the page is first
loaded.
Binding to a RadioButtonList

DBConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
SQLString = "SELECT DISTINCT ItemType FROM Products _
ORDER BY ItemType"
DBCommand = New OleDbCommand(SQLString,
DBConnection)
DBReader = DBCommand.ExecuteReader()
RadioList.DataSource = DBReader
RadioList.DataTextField = "ItemType"
RadioList.DataBind()
DBReader.Close()
DBConnection.Close()
Binding to a RadioButtonList

The SQL statement used to retrieve button labels and values
selects only the unique ItemType values from the field (multiple
products have the same ItemType values; only one occurence of
each type is retrieved with the DISTINCT selector):
SQLString = "SELECT DISTINCT ItemType FROM Products _
ORDER BY ItemType"

The SQL statement is composed to select all records from the
database where ItemType matches the
RadioList.SelectedItem.Text value of the checked button. That
subset of records is bound to the Repeater. Notice that the
binding statements are surrounded by a test for a button
selection. Data are retrieved and bound to the Repeater only if a
button has been clicked. Of course, this test is not necessary if
any of the buttons is preselected.
Binding to a RadioButtonList

Sub Display_Products (Src As Object, Args As EventArgs)
If RadioList.SelectedIndex <> -1 Then
DBConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\Databases\eCommerce.mdb")
DBConnection.Open()
SQLString = "SELECT * FROM Products " & _
"WHERE ItemType = '" & RadioList.SelectedItem.Text & "'"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
ProductDisplay4.DataSource = DBReader
ProductDisplay4.DataBind()
DBReader.Close()
DBConnection.Close()
End If
End Sub
RadioButton.aspx
Binding to a RadioButtonList

Recall that radio buttons themselves can trigger a
subroutine call without having to provide a separate
"Select" button. Code the RadioButtonList with
AutoPostBack="True" and
OnSelectedIndexChanged="subprogram" properties.
<asp:RadioButtonList id="RadioList" runat="server"
AutoPostBack="True"
OnSelectedIndexChanged="Display_Product"/>

Coding of the Display_Products subprogram is
identical to where a selection is made with a separate
"Submit" button, with the exception that it is not
necessary to test for a checked button.
Binding to a CheckBoxList
Binding to and selecting from an
asp:CheckBoxList control operate in the
same fashion as for a ListBox control.
 Since more than one item can be
checked, you need to iterate though all
the boxes to discover those that are
checked, and build an appropriate SQL
statement as a concatenation of multiple
selection criteria.

Repeater
FullRepeater.aspx