locked
How to delete row from database RRS feed

  • Question

  • User1717218719 posted

    I have the followingcode which deletes row from repeater, however it doesnt delete row from database. any help/sample code as to how I would code to delete row from database would be great

     '------CHECKBOX DELETE SELECTED 
                        Case "DeleteSelected"
    
                            'Call DeleteDataBaseRow()
    
                            Dim PKs = ""
    
                            For Each item As RepeaterItem In Repeater1.Items
                                Dim chb As CheckBox = TryCast(item.FindControl("CheckBox1"), CheckBox)
                                Dim txtid As TextBox = TryCast(item.FindControl("txtbx"), TextBox)
    
                                If chb.Checked Then
    
                                    PKs += txtid.Text & ","
    
                                End If
                            Next
    
                            Dim dt As DataTable = GetComItms()
    
                            Dim results = (From myRow In dt.AsEnumerable() Where PKs.IndexOf(myRow.Field(Of String)("x")) > -1 Select myRow).ToList()
    
                            For Each row As DataRow In results
                                dt.Rows.Remove(row)
                            Next
    
                            '-- rebind after delete
                            Me.Repeater1.DataSource = dt
                            Me.Repeater1.DataBind()
       		    End Select
    

    Tuesday, July 9, 2019 1:49 PM

Answers

  • User288213138 posted

    Hi E.RU,

    According to your description, I wrote a demo for you as reference.

    In my demo, I find the CustomerId of the row to delete, and then use the sql delete statement to delete the data in the database based on this CustomerId.

    The code:

    aspx:
    <asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand">
        <HeaderTemplate>
            <table id="tblContacts" border="1" >
                <tr>
                    <th>
                    </th>
                    <th>
                    </th>
                    <th>
                        CustomerId
                    </th>
                    <th>
                        Name
                    </th>
                    <th>
                        Country
                    </th>
                </tr>
        </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td>
                    <asp:LinkButton ID="LinkButton1" runat="server" CommandName="DeleteRow" CommandArgument='<%# Bind("CustomerId") %>'>Delete</asp:LinkButton>
                </td>
                <td>
                    <asp:CheckBox ID="CheckBox1" runat="server" />
                </td>
                <td>
                    <%#Eval("CustomerId") %>
                </td>
                <td>
                    <%#Eval("Name") %>
                </td>
                <td>
                    <%#Eval("Country") %>
                </td>
            </tr>
        </ItemTemplate>
        <FooterTemplate>
            </table>
        </FooterTemplate>
    </asp:Repeater>
    Aspx.cs:
    Class SurroundingClass
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If Not IsPostBack Then
                Bind()
            End If
        End Sub
    
        Private str As String = ConfigurationManager.ConnectionStrings("constr527").ConnectionString
    
        Public Sub Bind()
            Using con As SqlConnection = New SqlConnection(str)
                Dim sql As String = "Select * from Customer"
    
                Using adapter As SqlDataAdapter = New SqlDataAdapter(sql, con)
                    Dim dt As DataTable = New DataTable()
                    adapter.Fill(dt)
                    Repeater1.DataSource = dt
                    Repeater1.DataBind()
                End Using
            End Using
        End Sub
    
        Protected Sub Repeater1_ItemCommand(ByVal source As Object, ByVal e As RepeaterCommandEventArgs)
            For item As Integer = 0 To Repeater1.Items.Count - 1
                Dim chb As CheckBox = TryCast(Repeater1.Items(item).FindControl("CheckBox1"), CheckBox)
    
                If chb.Checked Then
    
                    If e.CommandName = "DeleteRow" Then
                        Dim CustomerId As Integer = Convert.ToInt32(e.CommandArgument)
                        Dim con As SqlConnection = New SqlConnection(str)
                        Dim cmdText As String = "DELETE FROM Customer WHERE CustomerId=@CustomerId"
                        Dim cmd As SqlCommand = New SqlCommand(cmdText, con)
                        cmd.Parameters.AddWithValue("@CustomerId", CustomerId)
                        con.Open()
                        cmd.ExecuteNonQuery()
                        con.Close()
                        Bind()
                    End If
                End If
            Next
        End Sub
    End Class

    The result:

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 10, 2019 5:53 AM

All replies

  • User475983607 posted

    There's no SQL DELETE command shown.  The common pattern is to delete a row, query the table, then rebind server control.  Anyway, this pattern has been around a long time in Web Forms and there is lot of sample code on the internet.

    https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.gridview.deleterow?view=netframework-4.8

    Please try reading the openly published documentation. 

    Tuesday, July 9, 2019 2:02 PM
  • User753101303 posted

    Hi,

    It's not that easy. You can use Delete to mark a row as being deleted and then use AcceptChanges to send back changes to your DataTable to the server...Details are found at https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/updating-data-sources-with-dataadapters

    It is not widely discussed as it is few years old compared with the 20 year old Framework but if you want to use Web Forms and are new I would suggest to check https://docs.microsoft.com/en-us/aspnet/web-forms/overview/presenting-and-managing-data/model-binding/retrieving-data

    IIt should be cleaner, quite simpler while you get past the initial learning curve and it is similar to what you'll find in MVC for example....

    Tuesday, July 9, 2019 3:13 PM
  • User288213138 posted

    Hi E.RU,

    According to your description, I wrote a demo for you as reference.

    In my demo, I find the CustomerId of the row to delete, and then use the sql delete statement to delete the data in the database based on this CustomerId.

    The code:

    aspx:
    <asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand">
        <HeaderTemplate>
            <table id="tblContacts" border="1" >
                <tr>
                    <th>
                    </th>
                    <th>
                    </th>
                    <th>
                        CustomerId
                    </th>
                    <th>
                        Name
                    </th>
                    <th>
                        Country
                    </th>
                </tr>
        </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td>
                    <asp:LinkButton ID="LinkButton1" runat="server" CommandName="DeleteRow" CommandArgument='<%# Bind("CustomerId") %>'>Delete</asp:LinkButton>
                </td>
                <td>
                    <asp:CheckBox ID="CheckBox1" runat="server" />
                </td>
                <td>
                    <%#Eval("CustomerId") %>
                </td>
                <td>
                    <%#Eval("Name") %>
                </td>
                <td>
                    <%#Eval("Country") %>
                </td>
            </tr>
        </ItemTemplate>
        <FooterTemplate>
            </table>
        </FooterTemplate>
    </asp:Repeater>
    Aspx.cs:
    Class SurroundingClass
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If Not IsPostBack Then
                Bind()
            End If
        End Sub
    
        Private str As String = ConfigurationManager.ConnectionStrings("constr527").ConnectionString
    
        Public Sub Bind()
            Using con As SqlConnection = New SqlConnection(str)
                Dim sql As String = "Select * from Customer"
    
                Using adapter As SqlDataAdapter = New SqlDataAdapter(sql, con)
                    Dim dt As DataTable = New DataTable()
                    adapter.Fill(dt)
                    Repeater1.DataSource = dt
                    Repeater1.DataBind()
                End Using
            End Using
        End Sub
    
        Protected Sub Repeater1_ItemCommand(ByVal source As Object, ByVal e As RepeaterCommandEventArgs)
            For item As Integer = 0 To Repeater1.Items.Count - 1
                Dim chb As CheckBox = TryCast(Repeater1.Items(item).FindControl("CheckBox1"), CheckBox)
    
                If chb.Checked Then
    
                    If e.CommandName = "DeleteRow" Then
                        Dim CustomerId As Integer = Convert.ToInt32(e.CommandArgument)
                        Dim con As SqlConnection = New SqlConnection(str)
                        Dim cmdText As String = "DELETE FROM Customer WHERE CustomerId=@CustomerId"
                        Dim cmd As SqlCommand = New SqlCommand(cmdText, con)
                        cmd.Parameters.AddWithValue("@CustomerId", CustomerId)
                        con.Open()
                        cmd.ExecuteNonQuery()
                        con.Close()
                        Bind()
                    End If
                End If
            Next
        End Sub
    End Class

    The result:

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 10, 2019 5:53 AM