Slide 7.16: Embedding SQL in ASP.NET (cont.)

Embedding SQL in ASP.NET (Cont.)

The example below shows a GridView control associated to a SqlDataSource control, which is intended to replace the ADO.NET code. Because the data queries are specified directly as properties of the data source control, this is sometimes called a two-tier model instead of a three-tier one, because the data queries are still maintained in page code. Other than the GridView, the following two controls are also included in this application:

DropDownList, which allows the user to select a single item from a drop-down list, and
DetailsView, which displays the values of a single record from a data source in a table, where each data row represents a field of the record.

Like the GridView, the two controls can be declaratively bound by SqlDataSource by using the DataSourceID property.

^†If you encounter the error after configuring the SqlDataSource:

System.Data.OleDb.OleDbException: No value given for one or more required parameters.

make sure the elements CommandField, SelectParameters, and ControlParameter are not removed.

C:\ASP.NET-workspace\WebSite2\WebSite2\Default.aspx

001<%@ Page Language="C#" AutoEventWireup="false" CodeFile="Default.aspx.cs"
002  Inherits="_Default" %>
003 
004<!DOCTYPE html>
005<html xmlns="http://www.w3.org/1999/xhtml">
006 <head id="Head1" runat="server">
007  <title>GridView DetailsView Master-Details (Insert)</title>
008 </head>
009 <body>
010  <form id="form1" runat="server">
011   <b>Choose a state: </b>
012   <asp:DropDownList ID="DropDownList1" DataSourceID="SqlDataSource1"
013     AutoPostBack="True" DataTextField="state" runat="server"
014     OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" />
015   <asp:SqlDataSource ID="SqlDataSource1" runat="server"
016     SelectCommand="SELECT DISTINCT state FROM authors"
017     ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
018     ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>" />
019   <table bgcolor="lightgreen">
020    <tr>
021     <td valign="top">
022 
023      <asp:GridView ID="GridView1" AllowSorting="True" AllowPaging="True"
024        runat="server" DataSourceID="SqlDataSource2" DataKeyNames="au_id"
025        AutoGenerateColumns="False" Width="500px" SelectedIndex="0"
026        OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
027        OnPageIndexChanged="GridView1_PageIndexChanged"
028        OnRowDeleted="GridView1_RowDeleted" OnSorted="GridView1_Sorted">
029       <Columns>
030        <asp:CommandField ShowSelectButton="true" ShowDeleteButton="true" />
031        <asp:BoundField DataField="au_id"    HeaderText="au_id" ReadOnly="True"
032          SortExpression="au_id" />
033        <asp:BoundField DataField="au_lname" HeaderText="au_lname"
034          SortExpression="au_lname" />
035        <asp:BoundField DataField="au_fname" HeaderText="au_fname"
036          SortExpression="au_fname" />
037        <asp:BoundField DataField="state"    HeaderText="state"
038          SortExpression="state" />
039       </Columns>
040      </asp:GridView>
041 
042      <asp:SqlDataSource ID="SqlDataSource2" runat="server"
043        SelectCommand  = "SELECT au_id, au_lname, au_fname, state
044          FROM authors where state=@state"
045        DeleteCommand  = "DELETE FROM authors WHERE au_id = @au_id"
046        ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"
047        ProviderName="<%$ ConnectionStrings:ConnectionString2.ProviderName %>" >
048       <SelectParameters>
049        <asp:ControlParameter ControlID="DropDownList1" Name="state"
050          PropertyName="SelectedValue" Type="String" />
051       </SelectParameters>
052      </asp:SqlDataSource>
053     </td>
054 
055     <td valign="top">
056      <asp:DetailsView AutoGenerateRows="False" DataKeyNames="au_id"
057        DataSourceID="SqlDataSource3" HeaderText="Author Details"
058        ID="DetailsView1" runat="server" Width="275px"
059        OnItemUpdated ="DetailsView1_ItemUpdated"
060        OnItemInserted="DetailsView1_ItemInserted"
061        OnDataBound   ="DetailsView1_DataBound" >
062 
063       <Fields>
064        <asp:BoundField DataField="au_id"    HeaderText="au_id" ReadOnly="True"
065          SortExpression="au_id"  />
066        <asp:BoundField DataField="au_lname" HeaderText="au_lname"
067          SortExpression="au_lname" />
068        <asp:BoundField DataField="au_fname" HeaderText="au_fname"
069          SortExpression="au_fname" />
070        <asp:BoundField DataField="phone"    HeaderText="phone"
071          SortExpression="phone" />
072        <asp:BoundField DataField="address"  HeaderText="address"
073          SortExpression="address" />
074        <asp:BoundField DataField="city"     HeaderText="city"
075          SortExpression="city" />
076        <asp:BoundField DataField="state"    HeaderText="state"
077          SortExpression="state" />
078        <asp:BoundField DataField="zip"      HeaderText="zip"
079          SortExpression="zip" />
080        <asp:CheckBoxField DataField="contract"
081          HeaderText="contract" SortExpression="contract" />
082        <asp:CommandField ShowEditButton="True" ShowInsertButton="True" />
083       </Fields>
084      </asp:DetailsView>
085 
086      <asp:SqlDataSource ID="SqlDataSource3" runat="server"
087        SelectCommand  = "SELECT au_id, au_lname, au_fname, phone, address,
088          city, state, zip, contract FROM authors WHERE au_id=@au_id"
089        UpdateCommand  = "UPDATE authors SET au_lname = @au_lname, au_fname = @au_fname,
090          phone = @phone, address = @address, city = @city, state = @state, zip = @zip,
091           contract = @contract  WHERE au_id=@au_id"
092        InsertCommand  = "INSERT INTO authors ( au_id, au_lname, au_fname, phone,
093          address, city, state, zip, contract) VALUES( @au_id, @au_lname, @au_fname,
094          @phone, @address, @city, @state, @zip, @contract )"
095        ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>"
096        ProviderName="<%$ ConnectionStrings:ConnectionString3.ProviderName %>" >
097     
098       <SelectParameters>
099        <asp:ControlParameter ControlID="GridView1" Name="au_id"
100          PropertyName="SelectedValue" Type="String" />
101       </SelectParameters>
102 
103       <UpdateParameters>
104        <asp:Parameter Name="au_id"    Type="Int16" />
105        <asp:Parameter Name="au_lname" Type="String" />
106        <asp:Parameter Name="au_fname" Type="String" />
107        <asp:Parameter Name="phone"    Type="String" />
108        <asp:Parameter Name="address"  Type="String" />
109        <asp:Parameter Name="city"     Type="String" />
110        <asp:Parameter Name="state"    Type="String" />
111        <asp:Parameter Name="zip"      Type="String" />
112        <asp:Parameter Name="contract" Type="Boolean" />
113       </UpdateParameters>
114 
115       <InsertParameters>
116        <asp:Parameter Name="au_id"    Type="Int16" />
117        <asp:Parameter Name="au_lname" Type="String" />
118        <asp:Parameter Name="au_fname" Type="String" />
119        <asp:Parameter Name="phone"    Type="String" />
120        <asp:Parameter Name="address"  Type="String" />
121        <asp:Parameter Name="city"     Type="String" />
122        <asp:Parameter Name="state"    Type="String" />
123        <asp:Parameter Name="zip"      Type="String" />
124        <asp:Parameter Name="contract" Type="Boolean" />
125       </InsertParameters>
126 
127      </asp:SqlDataSource>
128     </td>
129    </tr>
130   </table>
131   <asp:Label ID="ErrorMessageLabel" EnableViewState="false" runat="server" />
132  </form>
133 </body>
134</html>