Thanks in advance!
I'm using Visual studio.net 2005
I can't get the underlying table (incoming) to update. I modify the
datarows, checking the database before and after running (the code gets
several hits while iterating the records), and it doesn't update it.
What am I doing wrong?
Sub scrubTrackedExclusions()
'Dim contactInfo As New contactStruct
Dim sqlFindTracked As New SqlCommand
'Dim sqlUPDATE As New SqlCommand
Dim intRec As Integer = 1
Dim dtIncoming As New DataTable
Dim dtTracked As New DataTable
Dim dtExclusions As New DataTable
Dim dtCurrad As New DataTable
Dim drvCurrad As System.Data.DataRowView()
Dim drvRow As System.Data.DataRow
Dim dsCurrent As DataSet = New DataSet
Dim dsChanges As DataSet = New DataSet
Dim drIncoming As DataRow
Dim drTracked As DataRow
Dim drExclusions As DataRow
Dim dcPK1(0), dcPK2(0), dcPK3(0) As DataColumn
Dim i As Integer = 0
Dim strSQL As String = "SELECT * FROM Tradoc.dbo.Incoming WHERE
op = 'INSERT' or op = 'UPDATE'; " _
& "SELECT * FROM TRACKED Where Status = 'ACTIVE'; SELECT * FROM
EXCLUSIONS; SELECT * FROM CURRAD;"
Dim strSqlUpdate As String = "UPDATE [Tradoc].[dbo].[Incoming]
SET [cn] = @cn, " _
& "[ou] = @ou, [dn] = @dn, [op] = @op, [adsPath] = @adsPath,
[reasonCode] = @reasoncode " _
& "WHERE [targetAddress] = @targetAddress"
opensql(1)
Dim sqlCMD As SqlCommand = New SqlCommand(strSQL,
sqlConnection1)
sqlCMD.CommandTimeout = 30
Dim sqlDA As SqlDataAdapter = New SqlDataAdapter
sqlDA.SelectCommand = sqlCMD
Dim sqlUpdate As SqlCommand = New SqlCommand(strSqlUpdate,
sqlConnection1)
sqlUpdate.CommandType = CommandType.Text
sqlDA.UpdateCommand = sqlUpdate
sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@cn",
SqlDbType.VarChar, 100, "CN"))
sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@ou",
SqlDbType.VarChar, 400, "ou"))
sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@dn",
SqlDbType.VarChar, 400, "dn"))
sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@op",
SqlDbType.VarChar, 10, "op"))
sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@adsPath",
SqlDbType.VarChar, 400, "adsPath"))
sqlDA.UpdateCommand.Parameters.Add(New
SqlParameter("@reasonCode", SqlDbType.VarChar, 50, "reasonCode"))
sqlDA.UpdateCommand.Parameters.Add(New
SqlParameter("@targetAddress", SqlDbType.VarChar, 400,
"targetAddress"))
sqlDA.Fill(dsCurrent, "Table")
dsCurrent.Tables(0).TableName = "Incoming"
dsCurrent.Tables(1).TableName = "Tracked"
dsCurrent.Tables(2).TableName = "Exclusions"
dsCurrent.Tables(3).TableName = "CurrAD"
dtIncoming = dsCurrent.Tables("Incoming")
dtTracked = dsCurrent.Tables("Tracked")
dtExclusions = dsCurrent.Tables("Exclusions")
dtCurrad = dsCurrent.Tables("currAD")
dcPK1(0) =
dsCurrent.Tables("Incoming").Columns("targetAddress")
dcPK2(0) = dsCurrent.Tables("Tracked").Columns("targetAddress")
dcPK3(0) =
dsCurrent.Tables("Exclusions").Columns("targetAddress")
'dcPK4(0) = dsCurrent.Tables("Currad").Columns("targetAddress")
dtIncoming.PrimaryKey = New DataColumn()
{dtIncoming.Columns("TargetAddress")}
dsCurrent.Tables("Incoming").PrimaryKey = dcPK1
dtTracked.PrimaryKey = New DataColumn()
{dtTracked.Columns("TargetAddress")}
dsCurrent.Tables("Tracked").PrimaryKey = dcPK2
dtExclusions.PrimaryKey = New DataColumn()
{dtExclusions.Columns("TargetAddress")}
dsCurrent.Tables("Exclusions").PrimaryKey = dcPK3
'dtCurrad.PrimaryKey = New DataColumn()
{dtCurrad.Columns("TargetAddress")}
'dsCurrent.Tables("Currad").PrimaryKey = dcPK4
Dim dvCurrad As DataView = New
DataView(dsCurrent.Tables("Currad"), "", "targetAddress",
DataViewRowState.CurrentRows)
'Example find...
' drFIND =
dtTracked.Rows.Find("SMTP:brett.mack@usaac.army.mil")
Dim iIndex As Integer = 1
For Each drIncoming In dtIncoming.Rows
lblGeneral.Text = "Scrubbing record #: " & intRec & " for
Tracking & Exclusions."
'Check & modify info if it's in the tracked database...
drTracked =
dtTracked.Rows.Find(drIncoming.ItemArray(8).ToString)
'Dim intIndex As Integer = dvAllad.Find(strcn)
If Not drTracked Is Nothing Then
'If we get a hit, then check the current contacts db
'for a target address match...
drvCurrad =
dvCurrad.FindRows(drIncoming.ItemArray(8).ToString)
'Okay, if we get a hit on the targetAddress on the
Current
'contacts, check to see if the objectGuid matches. If
it matches,
'Go ahead and modify the data and set the op to
Update...
'Check if it's empty...
If drvCurrad.Length >= 1 Then
For i = 0 To drvCurrad.Length
'If not, check for a Guid match....
If drTracked.Item(6).ToString =
drvCurrad(i).Row(6).ToString Then
'if it matches, reset the appropriate
incoming columns
drIncoming.Item(1) = drTracked.Item(1)
drIncoming.Item(25) = drTracked.Item(25)
drIncoming.Item(26) = drTracked.Item(26)
drIncoming.Item(27) = "UPDATE"
drIncoming.Item(28) = drTracked.Item(28)
drIncoming.Item(29) = "TRACKED"
Else
'Not yet implemented
End If
Next
End If
drExclusions =
dtExclusions.Rows.Find(drIncoming.ItemArray(8).ToString)
'Check & see if the contact is in the Excluded
database. If so, then
'go ahead and mark it as excluded; this way it won't be
touched.
If Not drExclusions Is Nothing Then
'drIncoming.Delete()
drIncoming.Item(27) = "EXCLUDED"
drIncoming.Item(29) = "EXCLUDED"
drIncoming.Item(33) = "EXCLUDED"
End If
iIndex += 1
intRec += 1
End If
Next
'This is where I'm pretty sure it should update everything!
dsChanges = dsCurrent.GetChanges
If Not dsChanges Is Nothing Then
sqlDA.Update(dsChanges, "Incoming")
dsCurrent.Merge(dsChanges, True)
End If
sqlCMD.Dispose()
sqlDA.Dispose()
'contactInfo = Nothing
lblRejects.Text = "REJECTED " & intRejects & " Records."
'sqlReader.Close()
closesql(1)
End Sub