Delete Multiple Records From Access Database

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 ??

Answer 1

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.

Answer 2

User828474466 posted

Hi,

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

Cheers

 

Andy

Answer 3

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();

Answer 4

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

Answer 5

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:))

Answer 6

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 

Answer 7

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

Answer 8

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