Use asp:SqlDataSource and asp:GridView to edit database table : GridView « Data Binding « ASP.NET Tutorial





Each column can be any of several column types
The order of your column tags determines the left-to-right order of columns in the GridView.

Class                  Description
BoundField             text
ButtonField            button
CheckBoxField          check box
                       Its used automatically for true/false fields (in SQL Server, these are fields that use the bit data type).
CommandField           provides selection or editing buttons.
HyperLinkField         a hyperlink.
ImageField             image data from a binary field.
TemplateField          specify multiple fields, custom controls, and arbitrary HTML



<%@ Page Language="VB" AutoEventWireup="false"%>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            DeleteCommand="DELETE FROM [Books] WHERE [BookID] = @BookID" 
            InsertCommand="INSERT INTO [Books] ([Title], [Author], [YearPublished], [Price], [LastReadOn], [PageCount]) VALUES (@Title, @Author, @YearPublished, @Price, @LastReadOn, @PageCount)"
            SelectCommand="SELECT * FROM [Books]" 
            UpdateCommand="UPDATE [Books] SET [Title] = @Title, [Author] = @Author, [YearPublished] = @YearPublished, [Price] = @Price, [LastReadOn] = @LastReadOn, [PageCount] = @PageCount WHERE [BookID] = @BookID">
            <DeleteParameters>
                <asp:Parameter Name="BookID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="Title" Type="String" />
                <asp:Parameter Name="Author" Type="String" />
                <asp:Parameter Name="YearPublished" Type="Int32" />
                <asp:Parameter Name="Price" Type="Decimal" />
                <asp:Parameter Name="LastReadOn" Type="DateTime" />
                <asp:Parameter Name="PageCount" Type="Int32" />
                <asp:Parameter Name="BookID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="Title" Type="String" />
                <asp:Parameter Name="Author" Type="String" />
                <asp:Parameter Name="YearPublished" Type="Int32" />
                <asp:Parameter Name="Price" Type="Decimal" />
                <asp:Parameter Name="LastReadOn" Type="DateTime" />
                <asp:Parameter Name="PageCount" Type="Int32" />
            </InsertParameters>
        </asp:SqlDataSource>
    
    </div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="BookID" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="BookID" HeaderText="Book ID" InsertVisible="False" ReadOnly="True"
                    SortExpression="BookID" />
                <asp:TemplateField HeaderText="Title" SortExpression="Title">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Title") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("Title") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="Author" HeaderText="Author" SortExpression="Author" />
                <asp:BoundField DataField="YearPublished" HeaderText="Published" SortExpression="YearPublished" />
                <asp:TemplateField HeaderText="Price" SortExpression="Price">
                    <EditItemTemplate>
                        $<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Price") %>' Columns="10"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox2"
                            Display="Dynamic" ErrorMessage="You must enter a price."></asp:RequiredFieldValidator>
                        <asp:CompareValidator ID="CompareValidator1" runat="server" ControlToValidate="TextBox2"
                            Display="Dynamic" ErrorMessage="You must enter a valid numeric value greater than or equal to zero."
                            Operator="GreaterThanEqual" Type="Double" ValueToCompare="0"></asp:CompareValidator>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("Price", "{0:c}") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="LastReadOn" HeaderText="LastReadOn" SortExpression="Last Read" ApplyFormatInEditMode="True" DataFormatString="{0:d}" HtmlEncode="False" />
                <asp:BoundField DataField="PageCount" HeaderText="PageCount" SortExpression="Pages" />
            </Columns>
            <RowStyle BackColor="#F7F7DE" />
            <SelectedRowStyle Font-Bold="True"/>
            <PagerStyle HorizontalAlign="Right" />
            <HeaderStyle Font-Bold="True"/>
            <AlternatingRowStyle BackColor="White" />
            <FooterStyle BackColor="#CCCC99" />
            <RowStyle BackColor="#F7F7DE" />
            <SelectedRowStyle Font-Bold="True" ForeColor="White" />
            <PagerStyle HorizontalAlign="Right" />
            <HeaderStyle Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
        &nbsp;
    </form>
</body>
</html>


File: Web.config

<?xml version="1.0"?>
<configuration>
    <connectionStrings>
        <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>










19.17.GridView
19.17.1.Set RowStyle and HeaderStyle for GridView
19.17.2.GridView DataBind
19.17.3.GridView with template
19.17.4.Using Programmatic DataBinding
19.17.5.Use asp:SqlDataSource and asp:GridView to edit database table
19.17.6.Pageable asp:GridView
19.17.7.Sortable GridView
19.17.8.DataBinding to GridView (C#)
19.17.9.Assign data source to GridView
19.17.10.Editing Data
19.17.11.Prevent concurrency conflict
19.17.12.Displaying a message when no records match.
19.17.13.Displaying a template when no records match.
19.17.14.Formatting the GridView Control
19.17.15.Using Fields with the GridView Control
19.17.16.Using the PagerStyle and PagerSettings objects in the GridView control
19.17.17.Using the Null value