locked
Whole gridview was deleted when trying to delete only 1 row RRS feed

  • Question

  • User-1061466964 posted

    Hi all. I am trying to delete a row of records in a gridview. 

    The selectMethod works perfectly fine, but the DeleteMethod doesn't. When I delete a row, only 1 row of records should be deleted, but the whole gridview was deleted! (I have about 3 -5 rows of data). I am using ObjectDataSource.


    Here's my code if it would help.

    Data Access Layer

    ' Retrieve all uploads for administrator
    
        Public Function RetrieveUploads() As System.Data.DataSet
            Dim connectionString As String = My.Settings.dbConnection
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
            Dim queryString As String = "SELECT * FROM Uploads"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection
            Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
            dataAdapter.SelectCommand = dbCommand
            Dim dataSet As System.Data.DataSet = New System.Data.DataSet
            dataAdapter.Fill(dataSet)
            Return dataSet
        End Function
    
    
    ' Delete uploads (admin)
      Public Function DeleteUploads(ByVal username As String, ByVal filedir As String, ByVal keyhash As String) As Int32
            Dim connectionString As String = My.Settings.dbConnection
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
            '  Dim queryString As String = "DELETE FROM Uploads WHERE filedir ='" + filedir + "'"
            Dim queryString As String = "DELETE FROM Uploads"
            Dim dbCommand As SqlCommand = New SqlCommand(queryString, dbConnection)
            Dim rowsAffected As Integer = 0
            dbConnection.Open()
            Try
                rowsAffected = dbCommand.ExecuteNonQuery
            Finally
                dbConnection.Close()
            End Try
            Return rowsAffected
        End Function

    Business Logic Layer

       Public Function getAllUploads() As System.Data.DataSet
            Dim ds As Data.DataSet = UserDA.RetrieveUploads()
            Return ds
        End Function
    
    
        Public Function deleteUploads(ByVal username As String, ByVal filedir As String, ByVal keyhash As String) As Integer
            Dim ds As Integer = UserDA.DeleteUploads(username, filedir, keyhash)
            Return ds
        End Function


    Presentation Layer

    Design:

    <%@ Page Title="" Language="VB" MasterPageFile="~/PreLogin.master" AutoEventWireup="false" CodeFile="AdministrateUploads.aspx.vb" Inherits="AdministrateUploads" %>
    
    
    <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
        <table class="style5">
              
            <tr>
                <td colspan="2" class="style25" style="width: 725px">
                    Welcome,
                    <asp:Label ID="lb_user" runat="server"></asp:Label>
     <asp:LinkButton ID="LinkButton1" runat="server">Log Out</asp:LinkButton>
                    <br />
                    <br />
                    <asp:Label ID="Label1" runat="server" Font-Names="Aharoni" Font-Size="X-Large" 
                        Text="Administrate Uploads"></asp:Label>
                    <br />
                     <br />
                   
                </td>
            </tr>
            <tr>
                <td colspan="2" class="style28" style="width: 725px">
                         
                    </td>
            </tr>
            <tr>
                <td style="width: 725px">
                    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                        DataKeyNames="username" DataSourceID="ObjectDataSource2">
                        <Columns>
                            <asp:CommandField ShowDeleteButton="True" />
                            <asp:TemplateField HeaderText="username" SortExpression="username">
                                <EditItemTemplate>
                                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("username") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("username") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="filedir" SortExpression="filedir">
                                <EditItemTemplate>
                                    <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("filedir") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label2" runat="server" Text='<%# Bind("filedir") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="keyhash" SortExpression="keyhash">
                                <EditItemTemplate>
                                    <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("keyhash") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label3" runat="server" Text='<%# Bind("keyhash") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                        </Columns>
                    </asp:GridView>
                    <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" 
                        DeleteMethod="deleteUploads" SelectMethod="getAllUploads" 
                        TypeName="BusinessLogic.UserBLL">
                        <DeleteParameters>
                            <asp:Parameter Name="username" Type="String" />
                            <asp:Parameter Name="filedir" Type="String" />
                            <asp:Parameter Name="keyhash" Type="String" />
                        </DeleteParameters>
                    </asp:ObjectDataSource>
                    <br />
                </td>
            </tr>
            </table>
    </asp:Content>
    


    Presentation Layer

    Code Behind:

    Partial Class AdministrateUploads
        Inherits System.Web.UI.Page
        Dim username As String
        Dim filedir As String
        Dim keyhash As String
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            lb_user.Text = Session("username")
    
        End Sub
    
        Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    
            Dim UserController As New BusinessLogic.UserBLL()
            
            Dim ds As Integer = UserController.deleteUploads(username, filedir, keyhash)
    
            GridView1.DataBind()
        End Sub


    Monday, January 10, 2011 7:18 PM

Answers

  • User187056398 posted

    It's possible that your WHERE clause matched every record in the table.

    Make sure the table has a primary key.

    When you build DataSource using the wizard, the correct delete statement will be made.

    When you attach the DataSource to the GridView, you'll get a property like this:  DataKeyNames="ID"  where ID is the primary key of the table.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 11, 2011 9:12 AM

All replies

  • User187056398 posted

    "DELETE FROM Uploads"

    Your SQL delete statement has no WHERE clause.  So it deletes everything.

    Try rebuilding a DataSource from scratch and see what the generated code looks like so you can fix your DataSource. 

    Monday, January 10, 2011 9:19 PM
  • User-1061466964 posted

    "DELETE FROM Uploads"

    Your SQL delete statement has no WHERE clause.  So it deletes everything.

    Try rebuilding a DataSource from scratch and see what the generated code looks like so you can fix your DataSource. 

     

    I tried a WHERE clause in the delete statement, but the whole gridview was still deleted.

    This is what I've tried:

    "DELETE FROM Uploads WHERE filedir ='" + filedir + "'"

    and also

    "DELETE FROM Uploads WHERE username ='" + username + "'"

     

    Where am I doing wrong in here?

    Monday, January 10, 2011 10:11 PM
  • User187056398 posted

    It's possible that your WHERE clause matched every record in the table.

    Make sure the table has a primary key.

    When you build DataSource using the wizard, the correct delete statement will be made.

    When you attach the DataSource to the GridView, you'll get a property like this:  DataKeyNames="ID"  where ID is the primary key of the table.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 11, 2011 9:12 AM
  • User-1061466964 posted

    Thanks!

    I don't think it would be appropriate to have a primary key in the database, as the Uploads table must record duplicates of the username, with duplicated file directory, and a different key hash. This web form is for the Administrator use. I've tried putting key hash as the primary key, but I still have the same problem.

    I'm actually thinking of using a checkbox, where the admin can select the checkbox and delete that particular row of records.

    I know how to add the checkbox into the gridview, but I am stuck with the codes.

    I'm not sure if it's suitable though.

    Tuesday, January 11, 2011 9:42 AM
  • User187056398 posted

    I don't think it would be appropriate to have a primary key in the database,
     

    I don't think you understand what a primary key is.  You need to add an auto-incrementing integer field that uniquely identifies each record.

    Every table should have one.

    Tuesday, January 11, 2011 9:53 AM
  • User-1061466964 posted

    I know and understand what a primary key is...

    Thanks anyway, you've been such a great help!

    Tuesday, January 11, 2011 10:06 AM