Dataset Update problem

I'm at my wits end on this. Hopefully it's just something stupid I did.

I have a dataset of a that I'm populating by looping through some records in
a Btrieve database (Peachtree) and then writing to a SQL table. New records
load fine and dandy but if the dataset contains any modified records,
updates hang (60 second timeout).
The dataset was created with the wizard in VS05. I've tried putting the
update outside the loop after the end while with the same results.
I've included the code for the sub below

Many thanks in advance

Kevin Vogler
Private Sub getSalesOrderJournal()

Dim ps As New PeachtreeStuff

Dim CurrentPeriod = ps.mwCurrentPeriod()

Dim oSOJournal As PAW.SalesOrderJournal = New PAW.SalesOrderJournal

Me.SalesOrderHeadersTableAdapter.Fill(Me.SalesOrders.SalesOrderHeaders)

Dim Status As Integer

Dim MatchRow As Integer = -1

ps.PeachCon() ' connection to Peachtree Btrieve

Status = oSOJournal.OpenFile()

Dim RangeP As Short = CurrentPeriod

Dim CustomerStatus As Integer = 0

Dim ItemStatus As Integer = 0

Dim row As SalesOrders.SalesOrderHeadersRow

Status = oSOJournal.ReadFirst(RangeP)

While Status = 0

Me.SalesOrders.SalesOrderHeaders.DefaultView.Sort = "NRecord"

MatchRow =
Me.SalesOrders.SalesOrderHeaders.DefaultView.Find(oSOJournal.NRecord)

CustID_SONum = oSOJournal.CustomerIndex.ToString + "_" +
oSOJournal.InvoiceNumber.ToString

If MatchRow = -1 Then

row = Me.SalesOrders.SalesOrderHeaders.NewSalesOrderHeadersRow()

Else

row = Me.SalesOrders.SalesOrderHeaders.Rows(MatchRow)

End If

row("NRecord") = oSOJournal.NRecord

row("SalesOrderNumber") = oSOJournal.InvoiceNumber.ToString

row("CustomerID") = oSOJournal.CustomerIndex.ToString

row("CustID_SONum") = CustID_SONum

row("OrderClosed") = oSOJournal.CloseSalesOrder.ToString

If MatchRow = -1 Then

Me.SalesOrders.SalesOrderHeaders.Rows.Add(row)

End If

Try

Me.SalesOrderHeadersTableAdapter.Update(Me.SalesOrders.SalesOrderHeaders)

Catch ex As Exception

End Try

Status = oSOJournal.ReadNext

row = Nothing

End While


Status = oSOJournal.CloseFile

If Not IsNothing(oSOJournal) Then
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oSOJournal)

oSOJournal = Nothing

ps.PeachDisCon()

ps = Nothing

End Sub

Kevin,

I think that this should be the last time that you create this kind of code.

Try
Me.SalesOrderHeadersTableAdapter.Update(Me.SalesOrders.SalesOrderHeaders)
Catch ex As Exception
End Try

It is in my idea it is not possible to create more dangerous code.

At least there has to be something after the catch exception as
MessageBox.Show(ex.ToString) and than your problem will probably be showed.

Cor


"Kevin" <kvogler@swbell.net.(donotspam)> schreef in bericht
news:%23B8HP931GHA.1268@TK2MSFTNGP02.phx.gbl...
> I'm at my wits end on this. Hopefully it's just something stupid I did.
>
> I have a dataset of a that I'm populating by looping through some records
> in a Btrieve database (Peachtree) and then writing to a SQL table. New
> records load fine and dandy but if the dataset contains any modified
> records, updates hang (60 second timeout).
> The dataset was created with the wizard in VS05. I've tried putting the
> update outside the loop after the end while with the same results.
> I've included the code for the sub below
>
> Many thanks in advance
>
> Kevin Vogler
> Private Sub getSalesOrderJournal()
>
> Dim ps As New PeachtreeStuff
>
> Dim CurrentPeriod = ps.mwCurrentPeriod()
>
> Dim oSOJournal As PAW.SalesOrderJournal = New PAW.SalesOrderJournal
>
> Me.SalesOrderHeadersTableAdapter.Fill(Me.SalesOrders.SalesOrderHeaders)
>
> Dim Status As Integer
>
> Dim MatchRow As Integer = -1
>
> ps.PeachCon() ' connection to Peachtree Btrieve
>
> Status = oSOJournal.OpenFile()
>
> Dim RangeP As Short = CurrentPeriod
>
> Dim CustomerStatus As Integer = 0
>
> Dim ItemStatus As Integer = 0
>
> Dim row As SalesOrders.SalesOrderHeadersRow
>
> Status = oSOJournal.ReadFirst(RangeP)
>
> While Status = 0
>
> Me.SalesOrders.SalesOrderHeaders.DefaultView.Sort = "NRecord"
>
> MatchRow =
> Me.SalesOrders.SalesOrderHeaders.DefaultView.Find(oSOJournal.NRecord)
>
> CustID_SONum = oSOJournal.CustomerIndex.ToString + "_" +
> oSOJournal.InvoiceNumber.ToString
>
> If MatchRow = -1 Then
>
> row = Me.SalesOrders.SalesOrderHeaders.NewSalesOrderHeadersRow()
>
> Else
>
> row = Me.SalesOrders.SalesOrderHeaders.Rows(MatchRow)
>
> End If
>
> row("NRecord") = oSOJournal.NRecord
>
> row("SalesOrderNumber") = oSOJournal.InvoiceNumber.ToString
>
> row("CustomerID") = oSOJournal.CustomerIndex.ToString
>
> row("CustID_SONum") = CustID_SONum
>
> row("OrderClosed") = oSOJournal.CloseSalesOrder.ToString
>
> If MatchRow = -1 Then
>
> Me.SalesOrders.SalesOrderHeaders.Rows.Add(row)
>
> End If
>
> Try
>
> Me.SalesOrderHeadersTableAdapter.Update(Me.SalesOrders.SalesOrderHeaders)
>
> Catch ex As Exception
>
> End Try
>
> Status = oSOJournal.ReadNext
>
> row = Nothing
>
> End While
>
>
> Status = oSOJournal.CloseFile
>
> If Not IsNothing(oSOJournal) Then
> System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oSOJournal)
>
> oSOJournal = Nothing
>
> ps.PeachDisCon()
>
> ps = Nothing
>
> End Sub
>
>

I understand what your comment. However, this is a stripped down version I
created for this posting of the actual sub, not the production one (which
has all the error info being written out to file), . I created this for
testing and have breakpoints set at the catch. I should have mentioned that
when I run this code in the debugger it doesn't reach the catch, it just
stops at the update and freezes.

Here's a further reduction without the COM reference and with your message
box. If I break and check the dataset before the update command it has the
appropriate changes for the records specified. There are only 160 rows in
the dataset and 160 rows in the SQL table. When it hits the update it just
freezes until the debugger times out.

Me.SalesOrderHeadersTableAdapter.Fill(Me.SalesOrders.SalesOrderHeaders)

Dim MatchRow As Integer = -1

Dim row As SalesOrders.SalesOrderHeadersRow

Dim i As Integer

For i = 167769 To 168826

Me.SalesOrders.SalesOrderHeaders.DefaultView.Sort = "NRecord"

MatchRow = Me.SalesOrders.SalesOrderHeaders.DefaultView.Find(i)

If MatchRow > -1 Then

row = Me.SalesOrders.SalesOrderHeaders.Rows(MatchRow)

row("OrderClosed") = "True"

Me.SalesOrders.SalesOrderHeaders.Rows(MatchRow).EndEdit()

End If

Next i

row = Nothing

Try

Me.SalesOrderHeadersTableAdapter.Update(Me.SalesOrders.SalesOrderHeaders)

Catch ex As Exception

MsgBox(ex.ToString)

End Try

It turns out that the SQL table has a trigger on it. One update not a
problem, many updates at once is a problem. I'll have to find a work-around
for the trigger or the update.

Thanks for your time

Kevin Vogler