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

Slide 7.15: Embedding SQL in ASP.NET (cont.)
Slide 7.17: Embedding SQL in ASP.NET (cont.)
Home

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

<%@ Page Language="C#" AutoEventWireup="false" CodeFile="Default.aspx.cs"
  Inherits="_Default" %>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
 <head id="Head1" runat="server">
  <title>GridView DetailsView Master-Details (Insert)</title>
 </head>
 <body>
  <form id="form1" runat="server">
   <b>Choose a state: </b>
   <asp:DropDownList ID="DropDownList1" DataSourceID="SqlDataSource1"
     AutoPostBack="True" DataTextField="state" runat="server"
     OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" />
   <asp:SqlDataSource ID="SqlDataSource1" runat="server"
     SelectCommand="SELECT DISTINCT state FROM authors"
     ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
     ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>" />
   <table bgcolor="lightgreen">
    <tr>
     <td valign="top">

      <asp:GridView ID="GridView1" AllowSorting="True" AllowPaging="True"
        runat="server" DataSourceID="SqlDataSource2" DataKeyNames="au_id"
        AutoGenerateColumns="False" Width="500px" SelectedIndex="0"
        OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
        OnPageIndexChanged="GridView1_PageIndexChanged"
        OnRowDeleted="GridView1_RowDeleted" OnSorted="GridView1_Sorted">
       <Columns>
        <asp:CommandField ShowSelectButton="true" ShowDeleteButton="true" />
        <asp:BoundField DataField="au_id"    HeaderText="au_id" ReadOnly="True"
          SortExpression="au_id" />
        <asp:BoundField DataField="au_lname" HeaderText="au_lname"
          SortExpression="au_lname" />
        <asp:BoundField DataField="au_fname" HeaderText="au_fname"
          SortExpression="au_fname" />
        <asp:BoundField DataField="state"    HeaderText="state"
          SortExpression="state" />
       </Columns>
      </asp:GridView>

      <asp:SqlDataSource ID="SqlDataSource2" runat="server"
        SelectCommand  = "SELECT au_id, au_lname, au_fname, state
          FROM authors where state=@state"
        DeleteCommand  = "DELETE FROM authors WHERE au_id = @au_id"
        ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"
        ProviderName="<%$ ConnectionStrings:ConnectionString2.ProviderName %>" >
       <SelectParameters>
        <asp:ControlParameter ControlID="DropDownList1" Name="state"
          PropertyName="SelectedValue" Type="String" />
       </SelectParameters>
      </asp:SqlDataSource>
     </td>

     <td valign="top">
      <asp:DetailsView AutoGenerateRows="False" DataKeyNames="au_id"
        DataSourceID="SqlDataSource3" HeaderText="Author Details"
        ID="DetailsView1" runat="server" Width="275px"
        OnItemUpdated ="DetailsView1_ItemUpdated"
        OnItemInserted="DetailsView1_ItemInserted"
        OnDataBound   ="DetailsView1_DataBound" >

       <Fields>
        <asp:BoundField DataField="au_id"    HeaderText="au_id" ReadOnly="True"
          SortExpression="au_id"  />
        <asp:BoundField DataField="au_lname" HeaderText="au_lname"
          SortExpression="au_lname" />
        <asp:BoundField DataField="au_fname" HeaderText="au_fname"
          SortExpression="au_fname" />
        <asp:BoundField DataField="phone"    HeaderText="phone"
          SortExpression="phone" />
        <asp:BoundField DataField="address"  HeaderText="address"
          SortExpression="address" />
        <asp:BoundField DataField="city"     HeaderText="city"
          SortExpression="city" />
        <asp:BoundField DataField="state"    HeaderText="state"
          SortExpression="state" />
        <asp:BoundField DataField="zip"      HeaderText="zip"
          SortExpression="zip" />
        <asp:CheckBoxField DataField="contract" 
          HeaderText="contract" SortExpression="contract" />
        <asp:CommandField ShowEditButton="True" ShowInsertButton="True" />
       </Fields>
      </asp:DetailsView>

      <asp:SqlDataSource ID="SqlDataSource3" runat="server"
        SelectCommand  = "SELECT au_id, au_lname, au_fname, phone, address,
          city, state, zip, contract FROM authors WHERE au_id=@au_id"
        UpdateCommand  = "UPDATE authors SET au_lname = @au_lname, au_fname = @au_fname,
          phone = @phone, address = @address, city = @city, state = @state, zip = @zip,
           contract = @contract  WHERE au_id=@au_id"
        InsertCommand  = "INSERT INTO authors ( au_id, au_lname, au_fname, phone,
          address, city, state, zip, contract) VALUES( @au_id, @au_lname, @au_fname,
          @phone, @address, @city, @state, @zip, @contract )"
        ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>"
        ProviderName="<%$ ConnectionStrings:ConnectionString3.ProviderName %>" >
    
       <SelectParameters>
        <asp:ControlParameter ControlID="GridView1" Name="au_id"
          PropertyName="SelectedValue" Type="String" />
       </SelectParameters>

       <UpdateParameters>
        <asp:Parameter Name="au_id"    Type="Int16" />
        <asp:Parameter Name="au_lname" Type="String" />
        <asp:Parameter Name="au_fname" Type="String" />
        <asp:Parameter Name="phone"    Type="String" />
        <asp:Parameter Name="address"  Type="String" />
        <asp:Parameter Name="city"     Type="String" />
        <asp:Parameter Name="state"    Type="String" />
        <asp:Parameter Name="zip"      Type="String" />
        <asp:Parameter Name="contract" Type="Boolean" />
       </UpdateParameters>

       <InsertParameters>
        <asp:Parameter Name="au_id"    Type="Int16" />
        <asp:Parameter Name="au_lname" Type="String" />
        <asp:Parameter Name="au_fname" Type="String" />
        <asp:Parameter Name="phone"    Type="String" />
        <asp:Parameter Name="address"  Type="String" />
        <asp:Parameter Name="city"     Type="String" />
        <asp:Parameter Name="state"    Type="String" />
        <asp:Parameter Name="zip"      Type="String" />
        <asp:Parameter Name="contract" Type="Boolean" />
       </InsertParameters>

      </asp:SqlDataSource>
     </td>
    </tr>
   </table>
   <asp:Label ID="ErrorMessageLabel" EnableViewState="false" runat="server" />
  </form>
 </body>
</html>

Slide 7.15: Embedding SQL in ASP.NET (cont.)
Slide 7.17: Embedding SQL in ASP.NET (cont.)
Home