Trouble Deleting Access Records

[Jamblaster]

I am having a problem getting this code to work in deleting records from a database. The path to the database is correct and I have another couple of event handlers that work fine using this same datbase so I know that the path to the Db is good. Any advice would be great, thanks. I marked where the error occurs at debugging. I know what line breaks the program, but I don't know why.

Private Sub radRemoveWords_Click(sender As System.Object, e As System.EventArgs) Handles radRemoveWords.Click


        Dim _intUpperBound As Integer = 1000 'dummy value
        Dim str_newWords(_intUpperBound) As String

        ' make the database connection to update the table

        Dim frmSpellingTest As New frmSpellingTest
        Dim _strFileLocation2 As String = frmSpellingTest._strFileLocation
        Dim _intUpperBound2 As Integer = frmSpellingTest._intUpperBound

        Try

            ' Array to use in handling incoming data from Db
            Dim strWords(_intUpperBound2) As String ' frmSpellingTest._intUpperBound

            ' SQL string to grab spelling words from the database table (argument 1 to data adapter creation)
            Dim strSQL As String = "SELECT * FROM spelling_list"
            ' User can supply location of word list (default will be displayed for ease of use)
            ' Path to the database table (argument 2 to data adapter creation)
            Dim location As String = _strFileLocation2
            Dim strPath As String = "Provider=Microsoft.ACE.OLEDB.12.0 ;" & "Data Source=" & location
            ' Use the SQL statement and path to create a data adapter to database
            Dim odaSpellingList2 As New OleDb.OleDbDataAdapter(strSQL, strPath)
            ' Create an DataTable to hold the data retrieved
            Dim datWords As New DataTable
            ' Counter to use in filling the ListBox object
            Dim intCount As Integer = 0
            ' Fill the DataTable with the data in the DataAdapter
            odaSpellingList2.Fill(datWords)



            ' command builder will build a SQL command to use
            Dim cb2 As New OleDb.OleDbCommandBuilder(odaSpellingList2)

            ' this is the dataset to work with to update the database with
            Dim spelling2 As New spelling_listDataSet

            ' this is how many items are in the listbox object
            Dim size As Integer = lstEditWords.Items.Count
            Dim X As Integer = 0

            For X = 1 To size  ' from 0 to however many spelling words there are
                If (lstEditWords.GetItemChecked(X - 1)) Then

                    spelling2.Tables("spelling_list").Rows(X).Delete()   'ERROR IS HERE
                                                                         ' BUT WHY?
                    odaSpellingList2.Update(spelling2, "spelling_list")
                    lstEditWords.Items.RemoveAt(X)

                End If

            Next
            ' close the Db connection now
            odaSpellingList2.Dispose()

        Catch exception As SystemException
            MsgBox("File Not Found", 16, "Missing Spelling List File")
            Me.Close()

        End Try

    End Sub

[GeekByChoiCe]

Hello,

If you have added the type of exception then you could expect a bit better help.So first of all I would advice you to add a Try/Catch block around the line which causes the error and read the exception message.
For now I assume you get an "ArgumentOutOfRangeException".

Try this code block instead:

Dim size As Integer = lstEditWords.Items.Count -1

For X As Integer = 0 To size ' from 0 to however many spelling words there are
If (lstEditWords.GetItemChecked(X)) Then
spelling2.Tables("spelling_list").Rows(X).Delete() 'ERROR IS HERE
' BUT WHY?
odaSpellingList2.Update(spelling2, "spelling_list")
End If
Next

lstEditWords.Items.clear

lstEditWords.Items.RemoveAt(X) Will raise another error as you actually try to manipulate this collection while you loop through it.
To avoid that you have two possibilities:
1. clear items when the loop is done
2. create a copy of this collection, loop through this copy, remove items from the original collection and clear the copy after the looping is done.

If this does not solve your problem, please do the Try/catch and post more details about the error ;)

[Jamblaster]

I just commented out the ListBox removal line and then I added some lines to the try-catch routine. Below you can see what I put in. When I run the record deletion event handler it bypasses every one of the new catches and the system exception box pops up.

            Catch exception As ArgumentNullException
                MsgBox("ARGUMENT NULL EXCEPTION", 16, "ERROR")

            Catch exception As AccessViolationException
                MsgBox("ACCESS VIOLATION EXCEPTION", 16, "ERROR")

            Catch exception As AppDomainUnloadedException
                MsgBox("APP DOMAIN UNLOADED EXCEPTION", 16, "ERROR")

            Catch exception As ArgumentOutOfRangeException
                MsgBox("ARGUMENT OUT OF RANGE EXCEPTION", 16, "ERROR")

            Catch exception As DuplicateWaitObjectException
                MsgBox("DUPLICATE WAIT OBJECT EXCEPTION", 16, "ERROR")

            Catch exception As ArgumentException
                MsgBox("ARGUMENT EXCEPTION", 16, "ERROR")

            Catch exception As DivideByZeroException
                MsgBox("DIVIDE BY ZERO EXCEPTION", 16, "ERROR")

            Catch exception As ArithmeticException
                MsgBox("ARITHMETIC EXCEPTION", 16, "ERROR")

            Catch exception As ArrayTypeMismatchException
                MsgBox("ARRAY TYPE MISMATCH EXCEPTION", 16, "ERROR")

            Catch exception As DeletedRowInaccessibleException
                MsgBox("DELETED ROW INACCESSIBLE EXCEPTION", 16, "ERROR")

            Catch exception As DuplicateNameException
                MsgBox("DUPLICATE NAME EXCEPTION", 16, "ERROR")

            Catch exception As VersionNotFoundException
                MsgBox("VERSION NOT FOUND EXCEPTION", 16, "ERROR")

            Catch exception As DataException
                MsgBox("DATA EXCEPTION", 16, "ERROR")

            Catch exception As DataMisalignedException
                MsgBox("DATA MISALIGNED EXCEPTION", 16, "ERROR")

            Catch exception As DBConcurrencyException
                MsgBox("DB CONCURRENCY EXCEPTION", 16, "ERROR")

            Catch exception As ObjectDisposedException
                MsgBox("OBJECT DISPOSED EXCEPTION", 16, "ERROR")



            Catch exception As SystemException
                MsgBox("SYSTEM EXCEPTION", 16, "ERROR")
                Me.Close()

[GeekByChoiCe]

Holy hell :o

Just do:

Try
     For X = 1 To size  ' from 0 to however many spelling words there are
                If (lstEditWords.GetItemChecked(X - 1)) Then

                    spelling2.Tables("spelling_list").Rows(X).Delete()  
                    odaSpellingList2.Update(spelling2, "spelling_list")
                    lstEditWords.Items.RemoveAt(X)

                End If

            Next
Catch ex as Exception
    msgbox(ex.message)
End try

This will show you whats wrong.

[Jamblaster]

I already scrapped that module and created a new class which loads the data into an array and then loads the array into a listbox and then uses data-linked objects to add and delete records with. It isn't pretty and it isn't directly programmatically controlled, but it works and I spent too much time debugging that one line of code already.