Question Not getting the MyBuilder.getupdateCommand thing

[wjburke2]

I have seen so many posts on this topic and didn't think I would have a problem. Thats when it all fell apart. I am trying to update Inventory records with production counts. It updates the first record but thats it and I am not getting a error. Any advice is welcome.

Sub UpdateCounts()

Dim da As New OleDb.OleDbDataAdapter
Dim ds As New DataSet
Dim dt As New DataTable
Dim dr As DataRow

Dim daInv As New OleDb.OleDbDataAdapter
Dim dsInv As New DataSet("dsInv")
Dim dtInv As DataTable
Dim drInv As DataRow
Dim myBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(daInv)

Dim strSQL As String
Dim intCount As Long
Dim dblCount As Double

Try
strSQL = "SELECT CompName, UpdDate, Sum(UpdCount) AS SumOfCounts " & _
"FROM InventoryUpdLog " & _
"WHERE UpdDate = #" & strRunDate & "# " & _
"GROUP BY CompName, UpdDate;"

da.SelectCommand = New OleDb.OleDbCommand(strSQL, conn)
da.Fill(ds, "CompCount") 'Fill the dataset, ds, with the above SELECT statement
dt = ds.Tables(0)

For Each dr In dt.Rows
CompCount = CompCount + 1

' **************************************************************
' * Update Records into the MS Access Fulfillment Database.
' **************************************************************
strSQL = "Select CompName, OnHand, Boxes, Box_Qty, Upd_Reason, Last_Update, AuditTrail " & _
"FROM Inventory " & _
"WHERE CompName = '" & dr("CompName") & "';"
daInv.SelectCommand = New OleDb.OleDbCommand(strSQL, conn)

daInv.FillSchema(dsInv, SchemaType.Source, "Inventory")
daInv.Fill(dsInv, "Inventory") 'Fill the dataset, dsInv, with the above SELECT statement
dtInv = dsInv.Tables(0)
drInv = dtInv.Rows.Item(0)

myBuilder.GetUpdateCommand()
daInv.UpdateCommand = myBuilder.GetUpdateCommand()

*** Update OnHand ***
CompTotal = CompTotal - dr("SumOfCounts")
intCount = drInv("OnHand") - dr("SumOfCounts")

drInv("AuditTrail") = "Pieces Changed From: " & drInv("OnHand") & ", To: " & intCount & _
vbCrLf & vbLf & drInv("AuditTrail")
drInv("AuditTrail") = "Changes made on " & Now & " by FFBInventroyUpd;" & _
vbCrLf & drInv("AuditTrail")
drInv("OnHand") = intCount

*** Update Boxes ***
If drInv("Box_Qty") > 0 Then
dblCount = drInv("OnHand") / drInv("Box_Qty")
drInv("AuditTrail") = "Boxes Changed From: " & drInv("Boxes") & ", To: " & dblCount & _
vbCrLf & vbLf & drInv("Audit_Trail")
drInv("AuditTrail") = "Changes made on " & Now & " by FFBInventroyUpd;" & _
vbCrLf & drInv("AuditTrail")
drInv("Boxes") = dblCount
End If

*** Update Reason/Date ***
drInv("Upd_Reason") = "Auto Update by FFBInventroyUpd"
drInv("Last_Update") = strRunDate

daInv.Update(dsInv, "Inventory")

Next

Catch ex As Exception
strMsg = "Error - Count Update " & ex.Message
LogWriter.WriteLine(strMsg)
blnStatusGood = False
dbErr = True
End Try

da.Dispose()

End Sub