data-driven - 1 © minder chen, 2002-2007 building data-driven web sites in asp.net 2.0
TRANSCRIPT
Data-Driven - 1 © Minder Chen, 2002-2007
Building Data-Driven Web Sites in ASP.NET 2.0
Data-Driven - 2 © Minder Chen, 2002-2007
Codeless Data-Binding Process
Source: http://beta.asp.net/QUICKSTART/aspnet/doc/ctrlref/data/default.aspx
Data-Driven - 3 © Minder Chen, 2002-2007
Data Bound Controls
• DataGrid
• DataList
• GridView
• DetailsView
• FormView • Repeater
New inASP.NET 2.0
Data-Driven - 4 © Minder Chen, 2002-2007
Data-Bound Controls
• <asp:GridView>
• <asp:DataGrid>
• <asp:DetailsView>
• <asp:TreeView>
• <asp:Menu>
• <asp:DataList>
• <asp:Repeater>
• <asp:DropDownList>
• <asp:BulletedList>
• <asp:CheckBoxList>
• <asp:RadioButtonList>
• <asp:ListBox>
• <asp:AdRotator>
Data-Driven - 5 © Minder Chen, 2002-2007
Data Bound Controls and Data Source Controls
Data-Driven - 6 © Minder Chen, 2002-2007
Create a New Data Source
• Drag and drop the AccessDataSource Web server control on to a Web form.
• Click the smart tab of the AccessDataSource1 to select common tasks for the AccessDataSource
• Click on the Configure Data Source… taskSmart tag
icon
Smart tag menu
Data-Driven - 7 © Minder Chen, 2002-2007
Select the Access database
Data-Driven - 8 © Minder Chen, 2002-2007
Define a SQL (Select) statement
SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]
Don't choose any query name if you need to create a data source that will be used to insert, update, or delete data in the database.
Data-Driven - 9 © Minder Chen, 2002-2007
Test Query
Data-Driven - 10 © Minder Chen, 2002-2007
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/northwind.mdb"
SelectCommand="SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]">
</asp:AccessDataSource>
Data-Driven - 11 © Minder Chen, 2002-2007
CategoryList.aspx
Data-Driven - 12 © Minder Chen, 2002-2007
Connecting Data Source and GridView
• Drop and drop a GridView to the Web form • From the GridView smart tag menu, click on the
dropdown listbox -- Choose Data Source and then select AccessDataSource1
Data-Driven - 13 © Minder Chen, 2002-2007
Edit Columns…
Data-Driven - 14 © Minder Chen, 2002-2007
Edit Columns…
Data-Driven - 15 © Minder Chen, 2002-2007
Create a HyperLinkField for the CategoryName Column
• Delete the CategoryName from the Selected Fields list box
• Highlight HyperlinkField and click Add
• Move the HyperLinkField to the position between CategoryID and Description in the Selected Fields list box
• Set up the properties of the HyperlinkField
Data-Driven - 16 © Minder Chen, 2002-2007
Set up HyperlinkField• Set up the Hyperlink field so that it displays CategoryName as the
link words, and links to ProductsByCategory.aspx while passing two QueryString variables cid and cname where cid contains CategoryID and cname contains CategoryName
ProductsByCategory.aspx?cid={0}&cname={1}
CategoryID, CategoryName
CategoryIDCategoryName
Data-Driven - 17 © Minder Chen, 2002-2007
Source Code <asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" DataKeyNames="CategoryID" DataSourceID="AccessDataSource1"> <Columns> <asp:BoundField DataField="CategoryID"
HeaderText="CategoryID" InsertVisible="False"
ReadOnly="True" SortExpression="CategoryID" /> <asp:HyperLinkField
DataNavigateUrlFields="CategoryID, CategoryName" DataNavigateUrlFormatString=
"ProductsByCategory.aspx?cid={0}&cname={1}" DataTextField="CategoryName" HeaderText="Category Name" /> <asp:BoundField DataField="Description" H
HeaderText="Description" SortExpression="Description" /> </Columns> </asp:GridView>
Data-Driven - 18 © Minder Chen, 2002-2007
CategoryList.aspx<%@ Page Language="VB" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <h1> Category List</h1> <p> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CategoryID" DataSourceID="AccessDataSource1"> <Columns> <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" InsertVisible="False" ReadOnly="True" SortExpression="CategoryID" /> <asp:HyperLinkField DataNavigateUrlFields="CategoryID, CategoryName"
DataNavigateUrlFormatString="ProductsByCategory.aspx?cid={0}&cname={1}" DataTextField="CategoryName" HeaderText="Category Name" /> <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" /> </Columns> </asp:GridView> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/northwind.mdb" SelectCommand="SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]"> </asp:AccessDataSource> </p> </div> </form></body></html>
Data-Driven - 19 © Minder Chen, 2002-2007
ProductsByCategory.aspx
Data-Driven - 20 © Minder Chen, 2002-2007
• Choose Data Source
• Edit Data Source
Data-Driven - 21 © Minder Chen, 2002-2007
Define the SQL Statement
SELECT [ProductID], [ProductName], [Discontinued], [UnitPrice] FROM [Products] WHERE ([CategoryID] = ?)
Make sure you choose
Products table
Data-Driven - 22 © Minder Chen, 2002-2007
Add Where Clause
Data-Driven - 23 © Minder Chen, 2002-2007
Parameterized Commands
• Parameter properties – Parameterized database commands
• Example: – Get value for WHERE clause in
SelectCommand from QueryString parameter or item selected in a drop-down list Web server control
• Example: – Get value for WHERE clause in
DeleteCommand from GridView
Data-Driven - 24 © Minder Chen, 2002-2007
Parameters Properties
Name Description
SelectParameters Specifies parameters for SelectCommand
InsertParameters
UpdateParameters
DeleteParameters
FilterParameters Specifies parameters for FilterExpression
Specifies parameters for InsertCommand
Specifies parameters for UpdateCommand
Specifies parameters for DeleteCommand
Data-Driven - 25 © Minder Chen, 2002-2007
Parameter Types
NameName DescriptionDescription
ControlParameter Binds a replaceable parameter to a control property
CookieParameter Binds a replaceable parameter to a cookie value`
FormParameter Binds a replaceable parameter to a form field
ProfileParameter Binds a replaceable parameter to a profile property
QueryStringParameter Binds a replaceable parameter to a query string parameter
Parameter Binds a replaceable parameter to a data field
SessionParameter Binds a replaceable parameter to a session variable
Data-Driven - 26 © Minder Chen, 2002-2007
Test Query
Data-Driven - 27 © Minder Chen, 2002-2007
Data-Driven - 28 © Minder Chen, 2002-2007
AutoFormat
Data-Driven - 29 © Minder Chen, 2002-2007
TemplateField
Data-Driven - 30 © Minder Chen, 2002-2007
Code for TemplateField <asp:TemplateField HeaderText="ProductName" SortExpression="ProductName">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("ProductName") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("ProductName") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
Data-Driven - 31 © Minder Chen, 2002-2007
Edit Template
Data-Driven - 32 © Minder Chen, 2002-2007
Edit Template
Data-Driven - 33 © Minder Chen, 2002-2007
Set Up Text property
Data-Driven - 34 © Minder Chen, 2002-2007
Set up NavigateUrl property
• ProductRecord.aspx?pid={0}
Data-Driven - 35 © Minder Chen, 2002-2007
End Template Editing
Data-Driven - 36 © Minder Chen, 2002-2007
ProductsByCategory.aspx
<%@ Page Language="VB" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server"> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) LabelCategoryID.Text = Request.QueryString("cid") LabelCategoryName.Text = Request.QueryString("cname") End Sub</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Products by a Category</title></head><body> <form id="form1" runat="server"> <div> <strong>CategoryID: </strong> <asp:Label ID="LabelCategoryID" runat="server"></asp:Label><strong> Catgeory Name: </strong> <asp:Label ID="LabelCategoryName" runat="server"></asp:Label><br /> <br />
Data-Driven - 37 © Minder Chen, 2002-2007
Continued…<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductID" DataSourceID="AccessDataSource1" BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4" ><Columns> <asp:BoundField DataField="ProductID" HeaderText="ProductID"
InsertVisible="False" ReadOnly="True" SortExpression="ProductID" /> <asp:TemplateField HeaderText="Product Name" SortExpression="ProductName"> <ItemTemplate> <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# Eval("ProductID", "ProductRecord.aspx?pid={0}") %>' Text='<%# Eval("ProductName") %>'></asp:HyperLink> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server"
Text='<%# Bind("ProductName") %>'></asp:TextBox> </EditItemTemplate></asp:TemplateField>
Data-Driven - 38 © Minder Chen, 2002-2007
Continued…<asp:CheckBoxField DataField="Discontinued"
HeaderText="Discontinued" SortExpression="Discontinued" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" DataFormatString="{0:c}" >
<ItemStyle HorizontalAlign="Right" /> </asp:BoundField></Columns><FooterStyle BackColor="#FFFFCC" ForeColor="#330099" /><RowStyle BackColor="White" ForeColor="#330099" /><PagerStyle BackColor="#FFFFCC" ForeColor="#330099"
HorizontalAlign="Center" /><SelectedRowStyle BackColor="#FFCC66" Font-Bold="True"
ForeColor="#663399" /><HeaderStyle BackColor="#990000" Font-Bold="True"
ForeColor="#FFFFCC" />
</asp:GridView>
Data-Driven - 39 © Minder Chen, 2002-2007
Continued…
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/northwind.mdb" SelectCommand="SELECT [ProductID], [ProductName], [Discontinued], [UnitPrice] FROM [Products] WHERE ([CategoryID] = ?)">
<SelectParameters> <asp:QueryStringParameter DefaultValue="1"
Name="CategoryID" QueryStringField="cid" Type="Int32" /> </SelectParameters> </asp:AccessDataSource> </div> </form></body></html>
Data-Driven - 40 © Minder Chen, 2002-2007
ProductRecord.aspx Runtime
Data-Driven - 41 © Minder Chen, 2002-2007
Define the SQL StatementSELECT [ProductID], [ProductName], [CategoryID], [UnitPrice],
[UnitsInStock], [Discontinued] FROM [Products] WHERE ([ProductID] = ?)
Data-Driven - 42 © Minder Chen, 2002-2007
Add Where Clause
Data-Driven - 43 © Minder Chen, 2002-2007
Use DetailsView (One Record at a Time)
Data-Driven - 44 © Minder Chen, 2002-2007
Format the DetailsView
Data-Driven - 45 © Minder Chen, 2002-2007
ProductRecord.aspx<%@ Page Language="VB" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <h1>Product Record</h1> <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" CellPadding="4" DataKeyNames="ProductID" DataSourceID="AccessDataSource1"
Font-Bold="False" ForeColor="#333333" GridLines="None" Height="50px"> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <CommandRowStyle BackColor="#D1DDF1" Font-Bold="True" /> <RowStyle BackColor="#EFF3FB" /> <FieldHeaderStyle BackColor="#DEE8F5" Font-Bold="True" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <Fields> <asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False" ReadOnly="True" SortExpression="ProductID" />
<asp:BoundField DataField="ProductName" ItemStyle-Width="200px" HeaderText="ProductName" SortExpression="ProductName" />
<asp:BoundField DataField="CategoryID" HeaderText="CategoryID" SortExpression="CategoryID" />
Data-Driven - 46 © Minder Chen, 2002-2007
Continued… <asp:BoundField DataField="UnitPrice" DataFormatString="{0:c}"
HeaderText="UnitPrice" SortExpression="UnitPrice"> </asp:BoundField>
<asp:BoundField DataField="UnitsInStock" DataFormatString="{0} in stock" HeaderText="UnitsInStock"
SortExpression="UnitsInStock" /> <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued"
SortExpression="Discontinued" /> </Fields> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#2461BF" /> <AlternatingRowStyle BackColor="White" /> </asp:DetailsView> <asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/northwind.mdb" SelectCommand="SELECT [ProductID], [ProductName], [CategoryID], [UnitPrice],
[UnitsInStock], [Discontinued] FROM [Products] WHERE ([ProductID] = ?)"> <SelectParameters> <asp:QueryStringParameter Name="ProductID"
QueryStringField="pid" Type="Int32" /> </SelectParameters> </asp:AccessDataSource> </div> </form></body></html>
Data-Driven - 47 © Minder Chen, 2002-2007
Manage Categories
Data-Driven - 48 © Minder Chen, 2002-2007
Data-Driven - 49 © Minder Chen, 2002-2007
Define the SQL Statement
Data-Driven - 50 © Minder Chen, 2002-2007
Use Optimistic Concurrency Control <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/northwind.mdb" DeleteCommand="DELETE FROM [Categories] WHERE [CategoryID] = ? AND
[CategoryName] = ? AND [Description] = ?" InsertCommand="INSERT INTO [Categories] ([CategoryID], [CategoryName], [Description]) VALUES (?, ?, ?)" SelectCommand="SELECT [CategoryID], [CategoryName], [Description] FROM
[Categories]" UpdateCommand="UPDATE [Categories] SET [CategoryName] = ?, [Description] = ?
WHERE [CategoryID] = ? AND [CategoryName] = ? AND [Description] = ?" ConflictDetection="CompareAllValues"> <DeleteParameters> <asp:Parameter Name="original_CategoryID" Type="Int32" /> <asp:Parameter Name="original_CategoryName" Type="String" /> <asp:Parameter Name="original_Description" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="CategoryName" Type="String" /> <asp:Parameter Name="Description" Type="String" /> <asp:Parameter Name="original_CategoryID" Type="Int32" /> <asp:Parameter Name="original_CategoryName" Type="String" /> <asp:Parameter Name="original_Description" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="CategoryID" Type="Int32" /> <asp:Parameter Name="CategoryName" Type="String" /> <asp:Parameter Name="Description" Type="String" /> </InsertParameters>
</asp:AccessDataSource>
Data-Driven - 51 © Minder Chen, 2002-2007
Without Optimistic Concurrency Control<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/northwind.mdb" DeleteCommand="DELETE FROM
[Categories] WHERE [CategoryID] = ?" InsertCommand="INSERT INTO [Categories] ([CategoryID],
[CategoryName], [Description]) VALUES (?, ?, ?)" SelectCommand="SELECT [CategoryID], [CategoryName], [Description]
FROM [Categories]" UpdateCommand="UPDATE [Categories] SET [CategoryName] = ?,
[Description] = ? WHERE [CategoryID] = ?"> <DeleteParameters> <asp:Parameter Name="original_CategoryID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="CategoryName" Type="String" /> <asp:Parameter Name="Description" Type="String" /> <asp:Parameter Name="original_CategoryID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="CategoryID" Type="Int32" /> <asp:Parameter Name="CategoryName" Type="String" /> <asp:Parameter Name="Description" Type="String" /> </InsertParameters> </asp:AccessDataSource>
Data-Driven - 52 © Minder Chen, 2002-2007
CategoryUpdateGridView.aspx<%@ Page Language="VB" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><script runat="server"> </script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <h1>Manage Categories</h1>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CategoryID"
DataSourceID="AccessDataSource1" AllowPaging="True" PageSize="5" AllowSorting="True" >
<Columns> <asp:CommandField ButtonType="Button" HeaderText="Actions"
ShowDeleteButton="True" ShowEditButton="True" /> <asp:BoundField DataField="CategoryID" HeaderText="CategoryID"
InsertVisible="False" ReadOnly="True" SortExpression="CategoryID" /> <asp:BoundField DataField="CategoryName" HeaderText="CategoryName"
SortExpression="CategoryName" /> <asp:BoundField DataField="Description" HeaderText="Description"
SortExpression="Description" /> </Columns> </asp:GridView>
Data-Driven - 53 © Minder Chen, 2002-2007
Continued…<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/northwind.mdb" DeleteCommand="DELETE FROM [Categories] WHERE [CategoryID]
= ? AND [CategoryName] = ? AND [Description] = ?" InsertCommand="INSERT INTO [Categories] ([CategoryID], [CategoryName], [Description]) VALUES (?, ?, ?)" SelectCommand="SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]" UpdateCommand="UPDATE [Categories] SET [CategoryName] = ?, [Description] = ? WHERE [CategoryID] =
? AND [CategoryName] = ? AND [Description] = ?" ConflictDetection="CompareAllValues"> <DeleteParameters> <asp:Parameter Name="original_CategoryID" Type="Int32" /> <asp:Parameter Name="original_CategoryName" Type="String" /> <asp:Parameter Name="original_Description" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="CategoryName" Type="String" /> <asp:Parameter Name="Description" Type="String" /> <asp:Parameter Name="original_CategoryID" Type="Int32" /> <asp:Parameter Name="original_CategoryName" Type="String" /> <asp:Parameter Name="original_Description" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="CategoryID" Type="Int32" /> <asp:Parameter Name="CategoryName" Type="String" /> <asp:Parameter Name="Description" Type="String" /> </InsertParameters> </asp:AccessDataSource> </div> </form></body></html>
Data-Driven - 54 © Minder Chen, 2002-2007
Change Description Field to Template Field
Data-Driven - 55 © Minder Chen, 2002-2007
Choose Edit Template
Data-Driven - 56 © Minder Chen, 2002-2007
Change EditItemTemplate
Data-Driven - 57 © Minder Chen, 2002-2007
<asp:TemplateField> for Description <asp:TemplateField HeaderText="Description"
SortExpression="Description"> <ItemTemplate> <asp:Label ID="Label1" runat="server"
Text='<%# Bind("Description") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server"
Text='<%# Bind("Description") %>'></asp:TextBox> </EditItemTemplate></asp:TemplateField>
Data-Driven - 58 © Minder Chen, 2002-2007
Change TextBox TextMode Property from SingleLine to MultiLine
Data-Driven - 59 © Minder Chen, 2002-2007
CategoryUpdateGridView2.aspx
Data-Driven - 60 © Minder Chen, 2002-2007
CategoryProductMasterDetail.aspx
Data-Driven - 61 © Minder Chen, 2002-2007
1
2
Data-Driven - 62 © Minder Chen, 2002-2007
CategoryProductMasterDetail.aspx<%@ Page Language="VB" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><script runat="server"> Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) LabelCategory.Text = "Category ID selected: " & DropDownList1.SelectedItem.Value LabelCategory.Text &= "<br>Category Name selected: " & DropDownList1.SelectedItem.Text End Sub</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
DataSourceID="AccessDataSource1" DataTextField="CategoryName" DataValueField="CategoryID"
OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"> </asp:DropDownList><br /> <asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/northwind.mdb" SelectCommand="SELECT [CategoryID], [CategoryName] FROM
[Categories]"></asp:AccessDataSource> <br /> <asp:Label ID="LabelCategory" runat="server"></asp:Label> </div> </form></body></html>
Data-Driven - 63 © Minder Chen, 2002-2007
CatgeoryProductMasterDetail2.aspx
Data-Driven - 64 © Minder Chen, 2002-2007
Data-Driven - 65 © Minder Chen, 2002-2007
Data-Driven - 66 © Minder Chen, 2002-2007
CategoryProductMasterDetail3.aspx
Data-Driven - 67 © Minder Chen, 2002-2007
CategoryProductMasterDetail3.aspx<%@ Page Language="VB" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="AccessDataSource1"
DataTextField="CategoryName" DataValueField="CategoryID" AppendDataBoundItems=true>AppendDataBoundItems=true>
<asp:ListItem Text="-- Choose a category --" Value="" /><asp:ListItem Text="-- Choose a category --" Value="" /> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~~/App_Data/northwind.mdb" SelectCommand="SELECT [CategoryID], [CategoryName] FROM
[Categories]" CancelSelectOnNullParameter=false>CancelSelectOnNullParameter=false> </asp:AccessDataSource> <br />
Data-Driven - 68 © Minder Chen, 2002-2007
Continued…<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="ProductID" DataSourceID="AccessDataSource2"> <Columns> <asp:BoundField DataField="ProductID" HeaderText="ProductID"
InsertVisible="False" ReadOnly="True" /> <asp:BoundField DataField="ProductName" HeaderText="ProductName" /> <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" /> <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" /> <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" /> <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" /> </Columns> </asp:GridView> <asp:AccessDataSource ID="AccessDataSource2" runat="server"
DataFile="~~/App_Data/northwind.mdb" SelectCommand="SELECT [ProductID], [ProductName], [SupplierID], [UnitPrice],
[UnitsInStock], [Discontinued] FROM [Products] WHERE ([CategoryID] = ?)"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="CategoryID"
PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:AccessDataSource> </div> </form></body></html>
Data-Driven - 69 © Minder Chen, 2002-2007
ManageProduct.aspx
Data-Driven - 70 © Minder Chen, 2002-2007
Add Edit and New Button
1
2
3
4
Data-Driven - 71 © Minder Chen, 2002-2007
1
2
3Make sure that Insert, Update, Delete statements are generated
Data-Driven - 72 © Minder Chen, 2002-2007
Insert Problem• Using Visual Web Developer 2005 and ASP.NET 2.0
• If you insert an AccessDataSource that use a table for insert, update and delete operations and the table has a primary key that is an autonumber, the code that is generated is shown in the next 3 slides.
• If you want to insert a record (Using a DetailsView object) you get the following error: "You tried to assign the Null value to a variable that is not a Variant data type." .
• To correct the problem, remove the highlighted (Bold & UnderlinedBold & Underlined) code that has been generated.
Source: http://forums.asp.net/1027200/ShowPost.aspx
Data-Driven - 73 © Minder Chen, 2002-2007
ManageProduct.aspx<%@ Page Language="VB" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <strong>Manage Product Information<br /> </strong> <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False" DataKeyNames="ProductID" DataSourceID="AccessDataSource1" Height="50px" > <PagerSettings Mode="NextPreviousFirstLast" /> <Fields> <asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False" ReadOnly="True" SortExpression="ProductID" /> <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" /> <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" SortExpression="CategoryID" /> <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" /> <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" SortExpression="Discontinued" /> <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" /> <asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" /> <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" SortExpression="UnitsInStock" /> <asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" SortExpression="UnitsOnOrder" /> <asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel" />
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
</Fields> </asp:DetailsView>
Data-Driven - 74 © Minder Chen, 2002-2007
Continued…<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/northwind.mdb" DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = ?"
InsertCommand="INSERT INTO [Products]
( [ProductID], [ProductID], [ProductName], [CategoryID], [UnitPrice], [Discontinued], [SupplierID], [QuantityPerUnit], [UnitsInStock], [UnitsOnOrder], [ReorderLevel])
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" SelectCommand="SELECT [ProductID], [ProductName], [CategoryID],
[UnitPrice], [Discontinued], [SupplierID], [QuantityPerUnit], [UnitsInStock], [UnitsOnOrder], [ReorderLevel] FROM [Products]"
UpdateCommand="UPDATE [Products] SET [ProductName] = ?, [CategoryID] = ?, [UnitPrice] = ?, [Discontinued] = ?, [SupplierID] = ?,
[QuantityPerUnit] = ?, [UnitsInStock] = ?, [UnitsOnOrder] = ?, [ReorderLevel] = ? WHERE [ProductID] = ?"> <DeleteParameters> <asp:Parameter Name="original_ProductID" Type="Int32" /> </DeleteParameters>
Data-Driven - 75 © Minder Chen, 2002-2007
Continued…<UpdateParameters> <asp:Parameter Name="ProductName" Type="String" /> <asp:Parameter Name="CategoryID" Type="Int32" /> <asp:Parameter Name="UnitPrice" Type="Decimal" /> <asp:Parameter Name="Discontinued" Type="Boolean" /> <asp:Parameter Name="SupplierID" Type="Int32" /> <asp:Parameter Name="QuantityPerUnit" Type="String" /> <asp:Parameter Name="UnitsInStock" Type="Int16" /> <asp:Parameter Name="UnitsOnOrder" Type="Int16" /> <asp:Parameter Name="ReorderLevel" Type="Int16" /> <asp:Parameter Name="original_ProductID" Type="Int32" /> </UpdateParameters> <InsertParameters>
<asp:Parameter Name="ProductID" Type="Int32" /><asp:Parameter Name="ProductID" Type="Int32" /> <asp:Parameter Name="ProductName" Type="String" /> <asp:Parameter Name="CategoryID" Type="Int32" /> <asp:Parameter Name="UnitPrice" Type="Decimal" /> <asp:Parameter Name="Discontinued" Type="Boolean" /> <asp:Parameter Name="SupplierID" Type="Int32" /> <asp:Parameter Name="QuantityPerUnit" Type="String" /> <asp:Parameter Name="UnitsInStock" Type="Int16" /> <asp:Parameter Name="UnitsOnOrder" Type="Int16" /> <asp:Parameter Name="ReorderLevel" Type="Int16" /> </InsertParameters> </asp:AccessDataSource> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> </div> </form></body></html>
Delete!!!
Data-Driven - 76 © Minder Chen, 2002-2007
Data Binding In Templates
• Goal: Simpler data expression syntax for both simple and complex expressions
• V1 Databinding syntax– <%# DataBinder.Eval(Container.DataItem, "field" [, formatString]) %>
• New Syntax– <%# Eval("field") %> // one-way databinding
– <%# Bind("field") %> // two-way databinding
Data-Driven - 77 © Minder Chen, 2002-2007
Data Binding in Templates
• Two-way data bindings – Enable templated controls to retrieve input
values to use as parameters
– Allow templated controls to perform automatic updates/deletes
– GridView and DetailsView both support two-way data bindings using TemplateField
– New FormView control allows completely templated rendering of DetailsView
– Future ListView control would provide this behavior for DataList functionality
Data-Driven - 78 © Minder Chen, 2002-2007
Use Template for Editing CategoryID
Data-Driven - 79 © Minder Chen, 2002-2007
CategoryID Template …..<asp:BoundField DataField="ProductName"
HeaderText="ProductName" SortExpression="ProductName" /> <asp:TemplateField HeaderText="CategoryID"> <ItemTemplate> <asp:Label ID="Label1" runat="server"
Text='<%# Bind("CategoryID") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server"
Text='<%# Bind("CategoryID") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="TextBox1" runat="server"
Text='<%# Bind("CategoryID") %>'></asp:TextBox> </InsertItemTemplate> </asp:TemplateField> <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice"
SortExpression="UnitPrice" />…..
Data-Driven - 80 © Minder Chen, 2002-2007
Data-Driven - 81 © Minder Chen, 2002-2007
Set SelectedValue Properties
Data-Driven - 82 © Minder Chen, 2002-2007
Modified Template <asp:TemplateField HeaderText="CategoryID"> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("CategoryID") %>'></asp:Label>
</ItemTemplate> <EditItemTemplate> <asp:DropDownList ID="DropDownList1" runat="server"
DataSourceID="AccessDataSource1" DataTextField="CategoryName" DataValueField="CategoryID"
SelectedValue='<%# Bind("CategoryID") %>'> </asp:DropDownList>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/northwind.mdb"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories]">
</asp:AccessDataSource> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%#
Bind("CategoryID") %>'></asp:TextBox> </InsertItemTemplate></asp:TemplateField>
Data-Driven - 83 © Minder Chen, 2002-2007
Indirect Events
• Indirect events can be raised by ASP.NET internally because of some action taken by the user.
DeletingDeleting
Is deleting allowed
Cancel the evente.Cancel = True
DeletedDeleted
No
Yes Run the event' Handle exception gracefullyIf e.Exception IsNot Nothing Then
AccessDataSource1_DeletedAccessDataSource1_Deleting
Data-Driven - 84 © Minder Chen, 2002-2007
Handle Exception (Partial Code)<%@ Page language="VB" %><script runat="server"> Sub CustomersGridView_RowDeleted(ByVal sender As Object, ByVal e As GridViewDeletedEventArgs) ' Use the Exception property to determine whether an exception ' occurred during the delete operation. If e.Exception Is Nothing Then ' Use the AffectedRows property to determine whether the ' record was deleted. Sometimes an error might occur that ' does not raise an exception, but prevents the delete ' operation from completing. If e.AffectedRows = 1 Then Message.Text = "Record deleted successfully." Else Message.Text = "An error occurred during the delete operation." End If Else ' Insert the code to handle the exception. Message.Text = "An error occurred during the delete operation." ' Use the ExceptionHandled property to indicate that the ' exception is already handled. e.ExceptionHandled = True End If End Sub</script>
Data-Driven - 85 © Minder Chen, 2002-2007
<html> <body> <form runat="server"> <h3>GridViewDeletedEventArgs Example</h3> <asp:label id="Message" forecolor="Red" runat="server"/> <br/> <!-- The GridView control automatically sets the columns --> <!-- specified in the datakeynames property as read-only. --> <!-- No input controls are rendered for these columns in edit mode. -->
<asp:gridview id="CustomersGridView"
datasourceid="CustomersSqlDataSource" autogeneratecolumns="true"
autogeneratedeletebutton="true" datakeynames="CustomerID"
onrowdeleted="CustomersGridView_RowDeleted" runat="server">
</asp:gridview> <!-- This example uses Microsoft SQL Server and connects --> <!-- to the Northwind sample database. Use an ASP.NET --> <!-- expression to retrieve the connection string value from the Web.config file. --> <asp:sqldatasource id="CustomersSqlDataSource" selectcommand="Select [CustomerID], [CompanyName], [Address], [City],
[PostalCode], [Country] From [Customers]" deletecommand="Delete from Customers where CustomerID = @CustomerID" connectionstring = "<%$ ConnectionStrings:NorthWindConnectionString%>" runat="server"> </asp:sqldatasource> </form> </body></html>
Data-Driven - 86 © Minder Chen, 2002-2007
Insert Issues
• Not pass an identity column during an insert to the database?
– Set InsertVisible property of control to false
• After the insert, retrieve the newly inserted record, complete with identity column?
– Uses Inserted event of DataSource control and grab the value of the identity column using e.Command.Parameters collection
Data-Driven - 87 © Minder Chen, 2002-2007
Insert, Update, Delete Issues – Keep Controls in Sync
• Refresh one control when another changes?• Call DataBind() method of second control from
first control’s ItemInserted,ItemUpdated, or ItemDeleted event
Protected Sub FormView1_ItemInserted(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs)
If e.Exception IsNot Nothing Then LabelMsg.Text = "Insert failed. Reason: " & e.Exception.Message
Else LabelMsg.Text = "Insert succeeded!" FormView1.Visible = False
GridView1.DataBind() End If End Sub
Data-Driven - 88 © Minder Chen, 2002-2007
Set a default value of a control for an insert
• Use DataBound event, checking the CurrentMode property
Protected Sub FormView1_DataBound(ByVal sender As Object, _
ByVal e As System.EventArgs)
' Set default value of the supplier ID to the current supplier ID
If (FormView1.CurrentMode = FormViewMode.Insert) Then CType(FormView1.FindControl("SUPIDLabel"), Label).Text = _
LabelSupplierID.Text
End If
End Sub