locked
Delete Multiple Records From Access Database RRS feed

  • Question

  • User828474466 posted

    Hi,

     

    I wonder if anyone could help, I am trying to delete multiple records from an access databse with ASP.NET VB but I'm doing something very wrong .....

    Here is my code ...

     

        Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As EventArgs)
            Dim atLeastOneRowDeleted As Boolean = False
            Dim Conn1 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\App_Data\DB1.mdb")
            Dim cmd2 As OleDbCommand
            For Each row As GridViewRow In GridView3.Rows
                Dim cb As CheckBox = row.FindControl("chkSelect")
                If cb IsNot Nothing AndAlso cb.Checked Then
                    atLeastOneRowDeleted = True
                    Dim ID As Integer = _
                        Convert.ToInt32(GridView3.DataKeys(row.RowIndex).Value)
    
                    DeleteResults.Text &= String.Format("Delete: {0}<br />", ID)
                    Try
                        Conn1.Open()
                        cmd2 = New OleDbCommand("Delete FROM UserInformation WHERE ID=@ID;", Conn1)
                        If Not String.IsNullOrEmpty(ID) Then cmd2.Parameters.AddWithValue("@id", ID)
                        cmd2.ExecuteNonQuery()
                        Conn1.Close()
                    Catch ex As Exception
                        If Not Conn1.State = ConnectionState.Closed Then Conn1.Close()
                    End Try
    
                End If
            Next
    
            ' Show the Label if at least one row was deleted...
            DeleteResults.Visible = atLeastOneRowDeleted
        End Sub

     

     

    Any help would be great !!

     

    I would also like to know once I get this working how I would delete, additional tables linked to this table by another field not ID ??

    Monday, February 21, 2011 10:05 AM

Answers

  • User3866881 posted

    It seems that your Asset is a foreign key in both of the two tables, isn't it?

    If really, I think the easiest way for you to do now is to set Cascading Deleting settings, something like this:

    Thus you can only delete a UserInformation record, it will delete all the related ones.

    For more you can refer at:

    http://www.codeproject.com/KB/cs/CascadingDeletes_cs.aspx?display=Mobile

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 24, 2011 8:13 PM

All replies

  • User-1412735316 posted

    Please mention the error occurs?

    Is it any foreignkey violation error? then you must delete the records from foreignkey references tables first.

    Monday, February 21, 2011 11:34 AM
  • User828474466 posted

    Hi,

    I don't actually get an error , it just doesn't delete ?

    Cheers

     

    Andy

    Monday, February 21, 2011 1:48 PM
  • User3866881 posted

    I had a quick look at your codes first, found something: (Attention to the bold statements)

       Dim ID As Integer =
                       
    Convert.ToInt32(GridView3.DataKeys(row.RowIndex).Value)
     
     
                   
    DeleteResults.Text &= String.Format("Delete: {0}<br />", ID) 
                   
    Try 
                       
    Conn1.Open() 
                        cmd2
    = New OleDbCommand("Delete FROM UserInformation WHERE ID=@ID;", Conn1) 
                       
    If Not String.IsNullOrEmpty(ID) Then cmd2.Parameters.AddWithValue("@id", ID) 
                        cmd2
    .ExecuteNonQuery() 

    Since you've successfully converted to Integer. How can you use String.IsNullOrEmpty to validate it? String.IsNullOrEmpty can only allow string type instead of integer here.

    My solution is this (Delete If……)

       Dim ID As Integer=
                       
    Convert.ToInt32(GridView3.DataKeys(row.RowIndex).Value) 
     
                   
    DeleteResults.Text &= String.Format("Delete: {0}<br />", ID) 
                   
    Try 
                       
    Conn1.Open() 
                        cmd2
    = New OleDbCommand("Delete FROM UserInformation WHERE ID=@ID;", Conn1) 
                       
    If Not String.IsNullOrEmpty(ID) Then

                        cmd2.Parameters.AddWithValue("@id", ID) 
                        cmd2
    .ExecuteNonQuery() 

    then you should re-read all the values into DataTable and re-databind to the GridView and call DataBind();

    Tuesday, February 22, 2011 10:18 PM
  • User828474466 posted

    Hi thanks for your reply , I did try as you suggested but it still didn't work so I have rewritten the code which now works on deleting several records from 1 table , but I want to delete records from two other tables linked by field ASSET, so it find the first record via id from Table "UserInformation" and  this also gives the asset ref , I then want to check two other tables "Software" and "Build Process" for asset and delete any records from both with this field, any help would be great code so far :

     

       Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As EventArgs)
            Dim productsToDelete As New ArrayList()
    
            For Each row As GridViewRow In GridView1.Rows
                If row.RowType = DataControlRowType.DataRow Then
                    Dim chkDelete As CheckBox = DirectCast(row.Cells(0).FindControl("chkDelete"), CheckBox)
    
                    If chkDelete IsNot Nothing Then
                        If chkDelete.Checked Then
                            Dim productId = row.Cells(2).Text
                            Dim Asset = row.Cells(11).Text
                            Dim ConnString As String = ("Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" & Server.MapPath("app_data/refresh.mdb"))
    
    
                            Dim SqlString As String = "Delete * From UserInformation Where id=? and Asset = ?"
    
                            Using conn As New OleDbConnection(ConnString)
    
                                Using cmd As New OleDbCommand(SqlString, conn)
    
                                    cmd.CommandType = CommandType.Text
    
                                    cmd.Parameters.AddWithValue("ID", productId)
    
                                    cmd.Parameters.AddWithValue("Asset", Asset)
    
                                    conn.Open()
    
                                    cmd.ExecuteNonQuery()
    
                                End Using
    
                            End Using
                        End If
                    End If
                End If
            Next
    
            BindData()
        End Sub
    Wednesday, February 23, 2011 6:00 PM
  • User3866881 posted

    Hey, do you mean "but I want to delete records from two other tables linked by field ASSET"???

    1) Relation between a primary key or a foreign key linked with each other?

    2) Only you mean that in the other two tables you have the same column with the same value, and you want to delete all of them?

    Thx:)

    Please clearify them:))

    Wednesday, February 23, 2011 8:15 PM
  • User1867929564 posted

    I think in msaccess you can create cascade delete.
    if u r successful in doing so then your exisiting code is just ok.
    whenever you delete from UserInformation,related record from other table will get deleted automatically .

    if u r not successful then u hv to write more code 

    Thursday, February 24, 2011 7:23 AM
  • User828474466 posted

    Sorry it was a bit confusing , ill try and explain

    (Main Table) User Information - Contains Fields ID, Asset, Name, etc

    linked to this table are two other tables - Software - contains fields ID, Asset, Software Name, etc

    AND

    Build Process - contains Fields Id, Asset, etc  

    So BuildProcess and Software are linked to User Information by Asset, I just want to delete the main record from User Information and then any records from BuildProcess and Software where they reference the Asset Number

     

    So the tables are linked like this

                USERINFORMATION  

                        ID Asset

               Asset               Asset

    BUILDPROCESS            SOFTWARE

    Cheers

    Andy

    Thursday, February 24, 2011 11:35 AM
  • User3866881 posted

    It seems that your Asset is a foreign key in both of the two tables, isn't it?

    If really, I think the easiest way for you to do now is to set Cascading Deleting settings, something like this:

    Thus you can only delete a UserInformation record, it will delete all the related ones.

    For more you can refer at:

    http://www.codeproject.com/KB/cs/CascadingDeletes_cs.aspx?display=Mobile

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 24, 2011 8:13 PM