How to update,delete and edit in datalist control using asp.net

Hi..

In this article we discuss about to update, delete and edit data in datalist control. In an previous article we discuss about update, delete in datagrid.for your reference I mention link below…

http://vbdotnetaddict.blogspot.com/2011/02/groid-view-load-and-update-sql-data.html

Here first you can add a datalist control in an client seide..Check the below code for your refrence ..

The main difference datagrid and datalist.in datalist we can design table as per our own style…we can bind data horizontal or vertical, we can bind column repeated direction..this are all we can’t do in datagrid..Now we come to datalist updation…

<div>     <table cellpadding ="0" cellspacing ="0" width="1000">     <tr>     <td align="center">         <asp:DataList ID="DataList1" runat="server" DataKeyField ="Number" class="list">         <HeaderTemplate >         <table class="list">         <tr>         <td>         Number         </td>         <td>         Name         </td>         <td>         Address         </td>         <td>         City         </td>         <td>         State         </td>         <td>         operation         </td>         </tr>          </HeaderTemplate>          <ItemTemplate >          <tr>          <td>          <%#Eval("Number")%>                   </td>          <td>          <%#Eval("Name")%>          </td>          <td>          <%#Eval("Address")%>                   </td>          <td>          <%#Eval("City")%>          </td>          <td>          <%#Eval("State")%>          </td>          <td>           <asp:LinkButton ID="LinkButton4" runat="server" CommandName ="edit">edit</asp:LinkButton>                 <asp:LinkButton ID="LinkButton2" runat="server" CommandName ="delete">Delete</asp:LinkButton>          </td>           </tr>          </ItemTemplate>          <EditItemTemplate>          <tr>         <td>             <asp:TextBox ID="TextBox1" runat="server" Text=' <%#Eval("Number")%>' ReadOnly ="true" ></asp:TextBox>                 </td>         <td>                     <asp:TextBox ID="TextBox2" runat="server" Text='<%#Eval("Name")%>'></asp:TextBox>         </td>         <td>                             <asp:TextBox ID="TextBox3" runat="server" Text='  <%#Eval("Address")%>'></asp:TextBox>         </td>         <td>                                     <asp:TextBox ID="TextBox4" runat="server" Text=' <%#Eval("State")%>'></asp:TextBox>         </td>          <td>                                             <asp:TextBox ID="TextBox5" runat="server" Text='<%#Eval("City")%>'></asp:TextBox>              <asp:LinkButton ID="LinkButton1" runat="server" CommandName ="update">Update</asp:LinkButton>           <asp:LinkButton ID="LinkButton3" runat="server" CommandName ="Cancel">Cancel</asp:LinkButton>          </td>          </tr>                            </EditItemTemplate>         <FooterTemplate >          </table>         </FooterTemplate>         </asp:DataList>     </td>     </tr>     </table>     </div>

Then follow the below code for an server side coding for datalist control

Sub bind()         Dim query As String         query = "select Number,Name,Address,City,State from phone"         sqlcmd = New SqlCommand(query, sqlcon)         sqlcon.Open()         da = New SqlDataAdapter(sqlcmd)         sqlcon.Close()         da.Fill(dt)         If dt.Rows.Count > 0 Then             DataList1.DataSource = dt             DataList1.DataBind()         End If     End Sub     Protected Sub DataList1_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles DataList1.CancelCommand         DataList1.EditItemIndex = -1         bind()     End Sub     Protected Sub DataList1_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles DataList1.DeleteCommand         Dim strqry As String         Dim number As String         number = DataList1.DataKeys(e.Item.ItemIndex)         strqry = "Delete from phone where number='" & number & "'"         sqlcmd = New SqlCommand(strqry, sqlcon)         sqlcmd.CommandType = CommandType.Text         sqlcon.Open()         sqlcmd.ExecuteNonQuery()         sqlcon.Close()         bind()     End Sub     Protected Sub DataList1_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles DataList1.EditCommand         DataList1.EditItemIndex = e.Item.ItemIndex         bind()     End Sub     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load         If IsPostBack = False Then             bind()         End If     End Sub     Protected Sub DataList1_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles DataList1.UpdateCommand         Dim number, name, address, city, state As TextBox         number = CType(e.Item.FindControl("TextBox1"), TextBox)         name = CType(e.Item.FindControl("TextBox2"), TextBox)         address = CType(e.Item.FindControl("TextBox3"), TextBox)         city = CType(e.Item.FindControl("TextBox4"), TextBox)         state = CType(e.Item.FindControl("TextBox5"), TextBox)         Dim sqlstr As String         sqlstr = "update phone set Name=@name,Address=@address,City=@city,State=@state where Number=@number"         sqlcmd = New SqlCommand(sqlstr, sqlcon)         sqlcmd.Parameters.Add("@name", SqlDbType.VarChar).Value = name.Text         sqlcmd.Parameters.Add("@address", SqlDbType.VarChar).Value = address.Text         sqlcmd.Parameters.Add("@number", SqlDbType.BigInt).Value = number.Text         sqlcmd.Parameters.Add("@city", SqlDbType.VarChar).Value = city.Text         sqlcmd.Parameters.Add("@state", SqlDbType.VarChar).Value = state.Text         sqlcon.Open()         sqlcmd.ExecuteNonQuery()         sqlcon.Close()         DataList1.EditItemIndex = -1         bind()     End Sub

I hope this one helpful to all..

Plz post your valuable comments…..