What's wrong with this dataadapter update code?

B

Bmack500

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:[email protected]")
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)
 
B

Bmack500

Okay, I figured it out. I don't need to supply the values for the
paramters at all; then just move the sqlDA.Update right after the loop,
and Voila! It works. However, is there anything else I'm missing that
might make it more efficient? Other thane perhaps batchsize....
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:[email protected]")
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)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax error with Update command - how to use? 5
What is wrong with this SQL Update Code? 3

Top