Cant get ado.net to 'update' to a database and delete records

[ElectronikBean]

Hello, Thanks for reading my post

This is the first time that I have attempted to write anything using ado.net, I am used to classic ado.

I have been pulling my hair out with this one... I have written a simple function that removes duplicates from a database table. (Oh, I thought, this will be easy )

It iterates through the recordset (sorry, DataSet/DataTable), and, when it finds a duplicate, it is supposed to remove the offending row.

Only, I have had some trouble trying to get the system to use the adapter.update method (or whatever) to finalise deletition of the row. It runs through the code, completes without exception and then laughs at me when I find that it has not actually deleted any rows at all. Having a book and using M$ documentation, I am still in confusion as to why it does not actually update/delete??? They all seem to use similar logic to what I have used here. No doubt I am missing somthing.

Someone that has been doing this longer than I have will probably look at the below code and see exactly what is wrong:-

[blue]
Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class Form1


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'Removes duplicates...

        Dim sql As String
        sql = "select * from table"
        Dim cn_Str As String
        cn_Str = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\test.mdb;"

        Using cnl As New OleDbConnection(cn_Str)
            '---------------ado.net setup
            Dim cmd As OleDbCommand = cnl.CreateCommand()
            cmd.CommandText = sql
            Dim adapter As New OleDbDataAdapter(sql, cn_Str)
            Dim in_use As New DataSet
            adapter.Fill(in_use)
            '---------------other declarations
            Dim PreviousID As Int32 = 0 'PreviousID is previous ID
            Dim row As DataRow
            'Dim rows As DataRow = in_use.Tables(0).Rows
            Dim iRow As Int32 = 0

            Dim deleteCommand As OleDbCommand = cnl.CreateCommand
            deleteCommand.CommandType = CommandType.TableDirect
            'deleteCommand.Connection = cnl
            Dim table As DataTable = in_use.Tables(0)

            While iRow < table.Rows.Count 'in_use.Tables(0).Rows.Count
                'row = in_use.Tables(0).Rows(iRow)
                row = table.Rows(iRow)

                'Console.WriteLine("Viewing >" & row("PartID").ToString)
                Console.Write("Examining >" & row("PartID").ToString)
                If row("PartID") = PreviousID Then 'this row is duplicated!
                    Console.WriteLine("<Duplicate Detected... removing duplicate...")
                    deleteCommand.CommandText = "Delete * from tbl_Instrument_Usage where IU_ID = " & row("IU_ID")
                    Console.WriteLine(deleteCommand.CommandText)
                    row.Delete()
                    row.AcceptChanges()
                    adapter.DeleteCommand = deleteCommand
                    adapter.UpdateCommand = deleteCommand
                    'adapter.Update(in_use)
                    adapter.Update(table.Select(Nothing, Nothing, _
  DataViewRowState.Deleted))


                Else
                    Console.WriteLine("<No duplicate found with last record!")
                    PreviousID = row("PartID")
                End If

                iRow += 1
            End While



        End Using
    End Sub
End Class

[/blue]

As you can see, I have experimented with this. I am thankful for any help given

 

[ElectronikBean]

Just a note, I am still hoping for some help on doing this in ADO.net.

Here is my solution in classic ado & vb. It works well, and the code is much more simple... it seems to me that the power of .net is offset by it's complexity :-/

Option Explicit
Public Con As ADODB.Connection
Private rs As ADODB.Recordset


Function RemoveDuplicates()
Dim PreviousID As Integer
Dim cn_Str As String
cn_Str = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\dbtest.mdb;"

Set Con = New ADODB.Connection
Set rs = New ADODB.Recordset

Con.Open cn_Str

rs.Open "select * from MyTable", Con, adOpenDynamic

While Not rs.EOF
  
  If rs.Fields("PartID") = PreviousID Then 'this row is duplicated!
                    Debug.Print rs.Fields("PartID"); "<Duplicate Detected... removing duplicate..."
                    Con.Execute ("Delete * from MyTable where IU_ID = " & rs.Fields("IU_ID"))
                Else
                    Debug.Print rs.Fields("PartID"); "<No duplicate found with last record!"
                    PreviousID = rs.Fields("PartID")
                End If
  
  rs.MoveNext
Wend

End Function