Access Database not updating

[shootsnlad]

Ok, I'm back. I have a program that half works. It has a connection to an Access database with 2 data adapters. Each data adapter fills a dataset. I then dim a datatable from each dataset. I'll post the code and then explain what the problem is. Here is the code:

Private Sub btnLoadParcels_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadParcels.Click
        Dim reccount, whereiam, whereiam2, count2, addcount As Integer
        Dim textline, formfield, adddisp As String
        Dim tempdate As DateTime
        Dim firsttime, foundparcel, foundowner As Boolean
        firsttime = True
        Dim dsparcel As New DataSet()
        Dim dsparcelowner As New DataSet()
        DaParcel.Fill(dsparcel)
        DaParcelOwner.Fill(dsparcelowner)
        Dim dtparcel As DataTable = dsparcel.Tables(0)
        Dim dtparcelowner As DataTable = dsparcelowner.Tables(0)
        FileOpen(1, "\\INFORMIX\GISDATA\cnci.txt", OpenMode.Binary, OpenAccess.Read, OpenShare.Shared)
        While Not EOF(1)
            count2 = count2 + 1
            textline = LineInput(1)
            Dim fieldarray() As String = textline.Split("|")
            If firsttime = True Then
            Else
                reccount = 0
                formfield = fieldarray(1)
                formfield = Mid(formfield, 1, 2) + "." + Mid(formfield, 3, 2) + "." + Mid(formfield, 5, 3) + "." + Mid(formfield, 8, 3)
                Do Until (reccount = dtparcel.Rows.Count)
                    If dtparcel.Rows(reccount).Item("parcel-number") = formfield Then
                        foundparcel = True
                        whereiam = reccount
                        reccount = dtparcel.Rows.Count - 1
                    Else
                        foundparcel = False
                    End If
                    reccount = reccount + 1
                Loop
                reccount = 0
                Do Until (reccount = dtparcelowner.Rows.Count)
                    If dtparcelowner.Rows(reccount).Item("parcel_no") = formfield Then
                        foundowner = True
                        whereiam2 = reccount
                        reccount = dtparcelowner.Rows.Count - 1
                    Else
                        foundowner = False
                    End If
                    reccount = reccount + 1
                Loop
                If foundparcel = True And foundowner = True Then
                    dtparcelowner.Rows(whereiam2).Item("owner-name") = fieldarray(2)
                    dtparcelowner.Rows(whereiam2).Item("owner-addr1") = fieldarray(3)
                    dtparcelowner.Rows(whereiam2).Item("owner-addr2") = fieldarray(4)
                    dtparcelowner.Rows(whereiam2).Item("owner-addr3") = fieldarray(5)
                    dtparcelowner.Rows(whereiam2).Item("owner-zip5") = fieldarray(6)
                    dtparcelowner.Rows(whereiam2).Item("owner-zip4") = fieldarray(7)
                    dtparcelowner.Rows(whereiam2).Item("township") = fieldarray(0)
                    dtparcel.Rows(whereiam).Item("addr-number") = fieldarray(8)
                    dtparcel.Rows(whereiam).Item("addr-street") = fieldarray(9)
                    dtparcel.Rows(whereiam).Item("parcel-lgl-desc") = fieldarray(18) + " " + fieldarray(19) + " " + fieldarray(20) + " " + fieldarray(21)
                    dtparcel.Rows(whereiam).Item("parcel-township") = fieldarray(0)
                    dtparcel.Rows(whereiam).Item("parcel-status") = fieldarray(25)
                    If fieldarray(24) = "" Then
                        tempdate = #1/1/1000#
                    Else
                        tempdate = CDate(fieldarray(24))
                    End If
                    dtparcel.Rows(whereiam).Item("update-date") = tempdate
                    Try
                        DaParcel.Update(dtparcel)
                        DaParcelOwner.Update(dtparcelowner)
                    Catch ex As DBConcurrencyException
                    End Try
                ElseIf foundparcel = True And foundowner = False Then
                ElseIf foundparcel = False And foundowner = True Then
                ElseIf foundparcel = False And foundowner = False Then
                    Dim newrow As DataRow = dtparcelowner.NewRow
                    newrow("parcel_no") = formfield
                    newrow("owner-name") = fieldarray(2)
                    newrow("owner-addr1") = fieldarray(3)
                    newrow("owner-addr2") = fieldarray(4)
                    newrow("owner-addr3") = fieldarray(5)
                    newrow("owner-zip5") = fieldarray(6)
                    newrow("owner-zip4") = fieldarray(7)
                    newrow("township") = fieldarray(0)
                    dtparcelowner.Rows.Add(newrow)
                    dtparcelowner.AcceptChanges()
                    Dim newrow2 As DataRow = dtparcel.NewRow
                    newrow2("addr-number") = fieldarray(8)
                    newrow2("addr-street") = fieldarray(9)
                    newrow2("parcel-lgl-desc") = fieldarray(18) + " " + fieldarray(19) + " " + fieldarray(20) + " " + fieldarray(21)
                    newrow2("parcel-number") = fieldarray(1)
                    newrow2("parcel-township") = fieldarray(0)
                    newrow2("parcel-status") = fieldarray(25)
                    If fieldarray(24) = "" Then
                        tempdate = #1/1/1000#
                    Else
                        tempdate = CDate(fieldarray(24))
                    End If
                    newrow2("update-date") = tempdate
                    dtparcel.Rows.Add(newrow2)
                    DaParcel.Update(dsparcel)
                    DaParcelOwner.Update(dsparcelowner)
                    addcount = addcount + 1
                End If
            End If
            firsttime = False
            txtNumber.Text = count2
            txtNumber.Refresh()
        End While
        FileClose(1)
        MsgBox("Done", MsgBoxStyle.OKOnly, "Message")
        adddisp = addcount & " parcel records added."
        MsgBox(adddisp, MsgBoxStyle.OKOnly, "Added Count")
    End Sub

I have 2 problems:
1. The first condition, where foundparcel and foundowner are true does not happen. It goes thorough the loop as it should but it does not update the database at all.
2. The last condition, where foundparcel and foundowner are false partly happens. It does add some of the records but my add count is like 2500 and the database only shows about 300 more records? Help!

Thanks...

shootsnlad

PS: Yes, edneeis, this will look familiar to you. (It's almost done!)

[Edneeis]

This beast again huh. With complex things like this its good to have some example data. Can you provide a small bit of the text file and maybe the database info in an xml file? Then we could test and find the problem.

[shootsnlad]

Here is the data file...

Thanks...

shootsnlad

[shootsnlad]

I can't attach the database. It's 182k with only 2 tables 3 records each. Max size on here is 100k. Any ideas?
Thanks

shootsnlad

[Edneeis]

Can you zip to get it under the 100k?

[shootsnlad]

Here it is....

[Edneeis]

I haven't forgotten you I've just been really busy and had some technical difficulties, I'll try to help out tonight or tomorrow.

[Edneeis]

Whats the SQL for the data adapters? DaParcel and DaParcelOwner what type of data are they connecting to?

[shootsnlad]

The SQL was generated after I had dragged the Data Adapters to the form. I let VB generate the commands. As for the type of data, it's just the Access Database.

[shootsnlad]

Edneeis,

After doing some more testing it appears as though one table is working and the other isn't. The parcel table in Access is updating with changes, and add's are being taken. The parcelowner table in Access is not getting updated and add's are not being taken. The code for the two looks the same to me. Any ideas?

Thanks!

shootsnlad

[Edneeis]

Try removing the 'dtparcelowner.AcceptChanges()' line right after you add the new row. This method is misleading, it says that the updates have already been done and will then not perform them during the next update. Its sort of like setting the IsDirty flag back to false.

[shootsnlad]

That did it! Works fine now. Thanks! I appreciate the help...

[Edneeis]

No problem. I was a bit slower than usual, but better late than never.