Another adapter.update not working

[LornaDoone]

Everything appears to be working fine, but my table is not being
updated. Can anyone help please?
I am using Vb.net to attempt to copy one Access table to another. Both
tables are identicle in structure, but reside in different databases. I
first delete the records in the second Access table. That works fine.
After I copy table one to table two, I check it in debug mode and I can
see the values have been copied.
ConnOne and ConnTwo were set up in the form. Here's the code for the
copy from one datatable to another and then the update...

Dim ds As DataSet = New DataSet
Dim tblEarmarks As DataTable
Dim tblEarmarksCount As Integer

ConnOne.Open()

Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("Select *
from Earmarks", ConnOne)
da.Fill(ds)
tblEarmarks = ds.Tables(0)

ConnOne.Close()

Dim da2 As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("Select *
from Earmarks", ConnTwo)
Dim ds2 As DataSet = New DataSet
da2.Fill(ds2)
Dim tblEarmarksCopy As DataTable

'create the Insert, Update and Delete commands
'automatically with the command builder

Dim CB As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)

ConnTwo.Open()

'copy one table to another
tblEarmarksCopy = tblEarmarks.Copy()

'these next two lines for debugging only - to check the
'Update Command...
Dim uc2 As OleDb.OleDbCommand = CB.GetUpdateCommand
Dim st2 As String = uc2.CommandText

Try
da2.Update(ds2) 'appears to work, but doesn't
Catch objError As System.Exception
LblDatabaseCopy.Text = "Server error.<br />" & objError.Message &
"<br />" & objError.Source
ConnTwo.Close()
Exit Sub
End Try

ConnTwo.Close()

 

[Miha Markic]

Hi,

Could it be because you are invokoing CommandBuilder on da and for Update
you use da2?

 

[LornaDoone]

Nope - sorry - that was a typo. I am using passing the da2 to the
command builder...still no go...but thanks!

 

[Miha Markic]

Hi again,

I think that I might know what your problem is.
You are using DataTable.Copy method which copies the table as is thus all
copied rows are marked as non-modified and adapter will ignore them.
A better aproach would be to manually copy rows:
for each row as DataRow in tblEarmarks.Rows
Dim newRow as DataRow = tblEarmarksCopy.NewRow()
for each col as DataColumn in row.Columns
newRow(col.ColumnName) = row(col.ColumnName)
end for
tblEarmarksCopy.Rows.Add(newRow)
end for

After this all rows will be marked as new and update should be fine.
Let me know,

 

[Miha Markic]

Ah, and don't forget to copy the datatable structure first.
DataTable tblEarmarksCopy= CType(tblEarmarks.Clone(), DataTable)

 

[LornaDoone]

Hi Miha -
Thanks so much for your help. I added in the row/column update code as
you suggested and also changed the update command to use the new table
(ie da2.Update(tblEarmarksCopy) and it now works. Whew! I appreciate
all your help!
~Laura

 

[Miha Markic]

Hi Miha -
Thanks so much for your help. I added in the row/column update code as
you suggested and also changed the update command to use the new table
(ie da2.Update(tblEarmarksCopy) and it now works. Whew! I appreciate
all your help!
~Laura

Great.