> This code never updates the underlying source database. What I'm trying
> to do is iterate through a table, and make changes when I find the key
> in another table (identical schemas). I would like to work with an in -
> memory cache of the data to speed the performance; however, it's never
> getting updated.
> I'm not currently using the last two tables in the code (exclusions &
> currad) so please ignore that. I guess I really don't understand how to
> update the changed information in the dataset to the source database
> after it's all modified.
> Using Visual studion 2005, .net 2.0.
>
> Any help would be greatly appreciated! Thank you in advance.
>
> 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 dsCurrent As DataSet = New DataSet
>         Dim dsChanges As DataSet = New DataSet
>         Dim drIncoming As DataRow
>         Dim drTracked As DataRow
>         Dim dcPK1(0), dcPK2(0), dcPK3(0), dcPK4(0) As DataColumn
>
>         Dim strSQL As String = "SELECT * FROM Incoming WHERE op =
> 'INSERT'; " _
>         & "SELECT * FROM TRACKED; SELECT * FROM EXCLUSIONS; SELECT *
> FROM CURRAD;"
>
>         Dim strSqlUpdate As String = "UPDATE [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 builder As SqlCommandBuilder = New SqlCommandBuilder(sqlDA)
>         builder.QuotePrefix = "["
>         builder.QuoteSuffix = "]"
>
>         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
>
>         'Example find...
>         ' drFIND =
> dtTracked.Rows.Find("SMTP:brett.m***@usaac.army.mil")
>         Dim iIndex As Integer = 1
>
>    For Each drIncoming In dtIncoming.Rows
>
>             'Okay, put the contact info into a structure for
>             'portability to other functions
>             contactInfo = dataRow2Struct(drIncoming)
>
>             'Check & modify info if it's in the tracked database...
>             drTracked =
> dtTracked.Rows.Find(drIncoming.ItemArray(8).ToString)
>             sqlDA.UpdateCommand.Parameters("@cn").Value = "NewCn" &
> iIndex.ToString
>             sqlDA.UpdateCommand.Parameters("@ou").Value = "newOU" &
> iIndex.ToString
>             sqlDA.UpdateCommand.Parameters("@dn").Value = "newDN" &
> iIndex.ToString
>             sqlDA.UpdateCommand.Parameters("@op").Value = "newOP" &
> iIndex.ToString
>             sqlDA.UpdateCommand.Parameters("@adsPath").Value =
> "newADSPath" & iIndex.ToString
>             sqlDA.UpdateCommand.Parameters("@reasonCode").Value =
> "newReasonCode" & iIndex.ToString
>             sqlDA.UpdateCommand.Parameters("@targetAddress").Value =
> drTracked.Item(8).ToString
>
>             drIncoming.Item(1) = drTracked.Item(1)
>             drIncoming.Item(27) = "NONUPDATE"
>             drIncoming.Item(28) = drTracked.Item(28)
>             drIncoming.Item(29) = drTracked.Item(30)
>             drIncoming.Item(13) = drTracked.Item(23)
>             drIncoming.Item(9) = "YOYO"
>             drIncoming.Item(10) = "NOYO"
>
>             dsChanges = dsCurrent.GetChanges
>
>             opensql(1)
>             sqlDA.Update(dsChanges, "Incoming")
>             closesql(1)
>             dsCurrent.Merge(dsChanges, True)
>
>             dsCurrent.AcceptChanges()
>
>             'End If
>             intRec += 1
>         Next
>         sqlCMD.Dispose()
>         sqlDA.Dispose()
>         contactInfo = Nothing
>         lblRejects.Text = "REJECTED " & intRejects & " Records."
>         'sqlReader.Close()
>         closesql(1)