Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

FREE 12 month online training for ASP.NET & MS Expression Studio and a Free copy of MS Expression Web with Windows Server Purchase
How to insert and update database records using ListView control in ASP.NET

Author: Richard Wand

This article will explain how we can insert and update database records using ListView control in ASP.NET.

 

In one of my previous article on DevASP.NET, I have explained about ListView control and some of its templates. I have also demonstrated that how we can bind data to ListView control using SqlDataSource and from server side coding. Like GridView control, ListView control can also be used for editing updating and deleting records. In addition to GridView control, ListView control also has built-in support for inserting records to database. This can be easily done using SqlDataSource and you can also insert and update database records using server side coding with ListView control. ListView control contains EditItemTemplate and InsertItemTemplate for update and insert respectively. When we use SqlDataSource to insert and update records in SQL database Server, we have to select option for generate INSERT, UPDATE and DELETE statements and we also need to enable editing, inserting and deleting.
 
  1. Create new website
  2. Add ListView control and SqlDataSource
  3. Configure SqlDataSource to get data from stores table of PUBS Sample database. Remember, you have to select option for generate INSERT, UPDATE and DELETE statements in Advanced part of the Configure select statement.
  4. Now Choose data source for ListView control and enable editing, inserting and deleting in configure ListView option.
  5. You will see following HTML code in aspx page of your web form

    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
     
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
     
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:ListView ID="ListView1" runat="server" DataSourceID="SqlDataSource1"
                DataKeyNames="stor_id" InsertItemPosition="LastItem">
            <LayoutTemplate>
                    <table runat="server">
                        <tr runat="server">
                            <td runat="server">
                                <table ID="itemPlaceholderContainer" runat="server" border="0" style="">
                                    <tr runat="server" style="">
                                        <th runat="server">
                                        </th>
                                        <th runat="server">
                                            Store ID</th>
                                        <th runat="server">
                                            Store Name</th>
                                        <th runat="server">
                                            Store Address</th>
                                        <th runat="server">
                                            City</th>
                                        <th runat="server">
                                            State</th>
                                        <th runat="server">
                                            Zip</th>
                                    </tr>
                                    <tr ID="itemPlaceholder" runat="server">
                                    </tr>
                                </table>
                            </td>
                        </tr>
                        <tr runat="server">
                            <td runat="server" style="">
                            </td>
                        </tr>
                    </table>
                </LayoutTemplate>
                <ItemTemplate>
                    <tr style="">
                        <td>
                            <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" />
                            <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" />
                        </td>
                        <td>
                            <asp:Label ID="stor_idLabel" runat="server" Text='<%# Eval("stor_id") %>' />
                        </td>
                        <td>
                            <asp:Label ID="stor_nameLabel" runat="server" Text='<%# Eval("stor_name") %>' />
                        </td>
                        <td>
                            <asp:Label ID="stor_addressLabel" runat="server" Text='<%# Eval("stor_address") %>' />
                        </td>
                        <td>
                            <asp:Label ID="cityLabel" runat="server" Text='<%# Eval("city") %>' />
                        </td>
                        <td>
                            <asp:Label ID="stateLabel" runat="server" Text='<%# Eval("state") %>' />
                        </td>
                        <td>
                            <asp:Label ID="zipLabel" runat="server" Text='<%# Eval("zip") %>' />
                        </td>
                    </tr>
                </ItemTemplate>
                <AlternatingItemTemplate>
                    <tr style="">
                        <td>
                            <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" />
                            <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" />
                        </td>
                        <td>
                            <asp:Label ID="stor_idLabel" runat="server" Text='<%# Eval("stor_id") %>' />
                        </td>
                        <td>
                            <asp:Label ID="stor_nameLabel" runat="server" Text='<%# Eval("stor_name") %>' />
                        </td>
                        <td>
                            <asp:Label ID="stor_addressLabel" runat="server" Text='<%# Eval("stor_address") %>' />
                        </td>
                        <td>
                            <asp:Label ID="cityLabel" runat="server" Text='<%# Eval("city") %>' />
                        </td>
                        <td>
                            <asp:Label ID="stateLabel" runat="server" Text='<%# Eval("state") %>' />
                        </td>
                        <td>
                            <asp:Label ID="zipLabel" runat="server" Text='<%# Eval("zip") %>' />
                        </td>
                    </tr>
                </AlternatingItemTemplate>
                <EditItemTemplate>
                    <tr style="">
                        <td>
                            <asp:Button ID="UpdateButton" runat="server" CommandName="Update"
                                Text="Update" />
                            <asp:Button ID="CancelButton" runat="server" CommandName="Cancel"
                                Text="Cancel" />
                        </td>
                        <td>
                            <asp:Label ID="stor_idLabel1" runat="server" Text='<%# Eval("stor_id") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="stor_nameTextBox" runat="server" Text='<%# Bind("stor_name") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="stor_addressTextBox" runat="server" Text='<%# Bind("stor_address") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="cityTextBox" runat="server" Text='<%# Bind("city") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="stateTextBox" runat="server" Text='<%# Bind("state") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="zipTextBox" runat="server" Text='<%# Bind("zip") %>' />
                        </td>
                    </tr>
                </EditItemTemplate>
                <EmptyDataTemplate>
                    <table runat="server" style="">
                        <tr>
                            <td>
                                No data was returned.
                            </td>
                        </tr>
                    </table>
                </EmptyDataTemplate>
                <InsertItemTemplate>
                     <tr style="">
                        <td>
                            <asp:Button ID="InsertButton" runat="server" CommandName="Insert" Text="Insert" />
                            <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Clear" />
                        </td>
                        <td>
                            <asp:TextBox ID="stor_idTextBox" runat="server" Text='<%# Bind("stor_id") %>'></asp:TextBox>
                        </td>
                        <td>
                            <asp:TextBox ID="stor_nameTextBox" runat="server" Text='<%# Bind("stor_name") %>'></asp:TextBox>
                        </td>
                        <td>
                            <asp:TextBox ID="stor_addressTextBox" runat="server" Text='<%# Bind("stor_address") %>'></asp:TextBox>
                        </td>
                        <td>
                            <asp:TextBox ID="cityTextBox" runat="server" Text='<%# Bind("city") %>'></asp:TextBox>
                        </td>
                        <td>
                            <asp:TextBox ID="stateTextBox" runat="server" Text='<%# Bind("state") %>'></asp:TextBox>
                        </td>
                        <td>
                            <asp:TextBox ID="zipTextBox" runat="server" Text='<%# Bind("zip") %>' />
                        </td>
                    </tr>
                </InsertItemTemplate>
                <SelectedItemTemplate>
                    <tr style="">
                        <td>
                            <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" />
                            <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" />
                        </td>
                        <td>
                            <asp:Label ID="stor_idLabel" runat="server" Text='<%# Eval("stor_id") %>' />
                        </td>
                        <td>
                            <asp:Label ID="stor_nameLabel" runat="server" Text='<%# Eval("stor_name") %>' />
                        </td>
                        <td>
                            <asp:Label ID="stor_addressLabel" runat="server" Text='<%# Eval("stor_address") %>' />
                        </td>
                        <td>
                            <asp:Label ID="cityLabel" runat="server" Text='<%# Eval("city") %>' />
                        </td>
                        <td>
                            <asp:Label ID="stateLabel" runat="server" Text='<%# Eval("state") %>' />
                        </td>
                        <td>                            <asp:Label ID="zipLabel" runat="server" Text='<%# Eval("zip") %>' />
                        </td>
                    </tr>
                </SelectedItemTemplate>
            </asp:ListView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="<%$ ConnectionStrings:C:\SQL SERVER 2000 SAMPLE DATABASES\PUBS.MDFConnectionString %>"
                DeleteCommand="DELETE FROM [stores] WHERE [stor_id] = @stor_id"
                InsertCommand="INSERT INTO [stores] ([stor_id], [stor_name], [stor_address], [city], [state], [zip]) VALUES (@stor_id, @stor_name, @stor_address, @city, @state, @zip)"
                SelectCommand="SELECT * FROM [stores]"
                UpdateCommand="UPDATE [stores] SET [stor_name] = @stor_name, [stor_address] = @stor_address, [city] = @city, [state] = @state, [zip] = @zip WHERE [stor_id] = @stor_id">
                <DeleteParameters>
                    <asp:Parameter Name="stor_id" Type="String" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="stor_id" Type="String" />
                    <asp:Parameter Name="stor_name" Type="String" />
                    <asp:Parameter Name="stor_address" Type="String" />
                    <asp:Parameter Name="city" Type="String" />
                    <asp:Parameter Name="state" Type="String" />
                    <asp:Parameter Name="zip" Type="String" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="stor_name" Type="String" />
                    <asp:Parameter Name="stor_address" Type="String" />
                    <asp:Parameter Name="city" Type="String" />
                    <asp:Parameter Name="state" Type="String" />
                    <asp:Parameter Name="zip" Type="String" />
                    <asp:Parameter Name="stor_id" Type="String" />
                </UpdateParameters>
            </asp:SqlDataSource>
        </div>
        </form>
    </body>
    </html>
     
  6. Now start debugging and see the ListView control. Try to insert, update and delete records.

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to create a Login page in ASP.NET

Disclaimer - Privacy
© 2002-2014 DevASP.net