How to edit existing records?

M

melegant

Well-known member
Joined
Feb 2, 2003
Messages
52
Location
NY
So, I have a table of parts where i want to import some data from an excel sheet but I do not want to add parts that already exist.

I can add rows in ADO.NET just fine, but I cant for the life of me think of an efficent way to go to the table, discover if the record exists then edit that record ..

I have tried using the datatables.SELECT method, but you have to set that equal to an array of datarows....my last attempt was to use the select method without setting it equal to something..which I am not sure works.(the code executes..but the update only works on the first record it finds that already exists..)

thanks for any help>

Code: 
  Public Sub PartImp()

        MyExcel.Workbooks.Open("c:\temp\SoftwareH.xls")
        Dim partcount As Integer
        partcount = 1

        OLEda = New OleDb.OleDbDataAdapter(myOLECmd, OLEcn)
        OLEcn.Open()
        OLEdt = New DataTable()
        OLEda.Fill(OLEdt)
        MyExcel.Sheets("Aug 02 Master").Select()

        Dim test As String
        Dim readcount As Integer
        Dim holdnum As Integer
        Do Until partcount = 664
            OLEcmd = New OleDb.OleDbCommand("Select LineIT,model FROM bomacc WHERE model = " & test & "", OLEcn)
            OLEdr = OLEcmd.ExecuteReader

            While OLEdr.Read()
                readcount = readcount + 1
                holdnum = OLEdr.GetInt32(0)
            End While

            OLEdr.Close()
            OLEdr = Nothing


            If readcount = 1 Then
       
                OLEdt.Select("LineIt =" & holdnum)
                OLEdt.Rows.Item(0).Item("Notes") = "Access Control"
                OLEdt.Rows.Item(0).Item("Catg") = MyExcel.Cells(partcount, "E").Text
                OLEdt.Rows.Item(0).Item("Mfgr") = "Software House"
                OLEdt.Rows.Item(0).Item("Model") = MyExcel.Cells(partcount, "A").Text
                OLEdt.Rows.Item(0).Item("Pnum") = MyExcel.Cells(partcount, "A").Text
                OLEdt.Rows.Item(0).Item("Description") = MyExcel.Cells(partcount, "C").Text
                OLEdt.Rows.Item(0).Item("Vendor") = 594
                OLEdt.Rows.Item(0).Item("V2") = 0
                OLEdt.Rows.Item(0).Item("V3") = 0
                OLEdt.Rows.Item(0).Item("List") = Convert.ToDecimal(MyExcel.Cells(partcount, "D").Text)
                OLEdt.Rows.Item(0).Item("Upd") = Date.Now
                OLEdt.Rows.Item(0).Item("Ptype") = 1
                readcount = 0
                partcount = partcount + 1
            Else
                OLEdrw(0) = OLEdt.NewRow
                OLEdt.Rows.Add(OLEdrw(0))
                OLEdrw(0).Item("Hrs") = 1
                OLEdrw(0).Item("Notes") = "Access Control"
                OLEdrw(0).Item("Catg") = MyExcel.Cells(partcount, "E").Text
                OLEdrw(0).Item("Mfgr") = "Software House"
                OLEdrw(0).Item("Pnum") = MyExcel.Cells(partcount, "A").Text
                OLEdrw(0).Item("Model") = MyExcel.Cells(partcount, "A").Text
                OLEdrw(0).Item("Description") = MyExcel.Cells(partcount, "C").Text
                OLEdrw(0).Item("Vendor") = 594
                OLEdrw(0).Item("V2") = 0
                OLEdrw(0).Item("V3") = 0
                OLEdrw(0).Item("List") = Convert.ToDecimal(MyExcel.Cells(partcount, "D").Text)
                OLEdrw(0).Item("Upd") = Date.Now
                OLEdrw(0).Item("Ptype") = 1
                partcount = partcount + 1
            End If
        Loop
        Dim OLEcbDEL As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(OLEda)
        OLEda.Update(OLEdt)
        OLEcn.Close()
        MyExcel.Workbooks.Close()
        MyExcel.Application.Quit()

    End Sub
 
Last edited by a moderator:
Suggestion (as psudocode)

Move the excel sheet to your database as a table (called TempParts)

Delete From TempParts those values that exist in your Master Parts table

Insert the remaining parts from TempParts into your Master Parts Table.

Delete the TempParts table.
 
Thx for suggestion, however I need to do this programmatically...

once I have solved my dilemma, I can then work on creating the class so it will take any sort of import,.excel csv tab del. etc....
 
Back
Top