J
Jason Fournier
Hi,
I'm needing assistance with ADO.NET saving a dataset to my database.
I have included code that may show you where I'm going wrong. My goal
is simple enough: I have two SQL Server 2000 databases with only minor
differences. The program I wish to write will automate the process of
copying over certain tables from one database to the other. The
example I have included here is for tables that have the exact same
structure; the only difference between the old and the new is that the
old table from the first database has data, and the new table from the
second database is blank. I want to copy the data from the old and
put it in the new.
My problem is that although I can get the data from the old into a
dataset supposedly linked to the new (via dataadapters), updating the
dataadapter for the new does not submit the data to the actual
database.
Any assistance would be greatly appreciated.
I have commented my sample code to explain what I have done at each
step.
Sincerely,
-- JR Fournier
(e-mail address removed)
-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-
Private Sub CopyFromOldToNew(ByVal strTable As String)
'strTable is the name of the table passed from outside functions.
'I wish to use a subroutine like this to perform the copy because I
'will not know the structure of the tables in advance.
'strConnOld and strConnNew are globally defined connection variables.
'They are defined as follows:
'Const strConnOld As String = _
' "Data Source=localhost;Initial Catalog=utility_be_old;" _
' "Integrated Security=SSPI"
'Const strConnNew As String = _
' "Data Source=localhost;Initial Catalog=utility_be_v2;" _
' "Integrated Security=SSPI"
'I then declare old and new datasets and dataadapters.
Dim dsOld As New DataSet()
Dim dsNew As New DataSet()
Dim daOld As New SqlDataAdapter("Select * from " & strTable, _
strConnOld)
Dim daNew As New SqlDataAdapter("Select * from " & strTable, _
strConnNew)
'While filling my dsOld dataset is obviously necessary, I don't think
'filling my dsNew is. The actual "new" database will always contain
'the structure only, not any data. Keep in mind, the table name that
'has been passed to this subroutine exists in BOTH the old and the
'new database. The structure is the same. I would use an SQL
'command and perform this copy manually, but my goal is to create a
'function that has no advance knowledge of which table is copied.
daOld.Fill(dsOld, strTable)
'daNew.Fill(dsNew, strTable)
'Of all the code involved, the cmdBuilder usage confuses me the most.
Dim cmdBuilder As SqlCommandBuilder = _
New SqlCommandBuilder(daNew)
'The following line of code is one of about 5 ways I have tried.
'This merge statement will successfully take my dsOld data and put
'it into my dsNew dataset. Now, I have also done the row by row
'copy, etc, but the end result of getting the data into the new
'dataset is the same. This is a one line solution that achieves
'the same effect. If there is a way to just use one dataset, since
'the data is the same, I would be happy to use it. But any attempts
'to only use the one dataset resulted in "This table [or this column]
'does not belong to that table", etc.
dsNew.Merge(dsOld, True, MissingSchemaAction.Add)
'I added a datagrid to display the contents of the new dataset,
'proving that dsNew contains the data that should be saved to the
'database.
DataGrid1.SetDataBinding(dsNew, strTable)
'I confess that the cmdBuilder usage confuses me a bit.
'If I am misunderstanding the usage, please assist.
daNew.UpdateCommand = cmdBuilder.GetUpdateCommand()
daNew.InsertCommand = cmdBuilder.GetInsertCommand()
'Is AcceptChanges actually needed on my dsNew dataset?
dsNew.AcceptChanges()
'From what I understand, telling my daNew dataadapter to update
'should commit my added rows to the new database. No error is
'thrown, but no changes are made in the actual database.
daNew.Update(dsNew.Tables(0))
End Sub
-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-
Please help!
I'm needing assistance with ADO.NET saving a dataset to my database.
I have included code that may show you where I'm going wrong. My goal
is simple enough: I have two SQL Server 2000 databases with only minor
differences. The program I wish to write will automate the process of
copying over certain tables from one database to the other. The
example I have included here is for tables that have the exact same
structure; the only difference between the old and the new is that the
old table from the first database has data, and the new table from the
second database is blank. I want to copy the data from the old and
put it in the new.
My problem is that although I can get the data from the old into a
dataset supposedly linked to the new (via dataadapters), updating the
dataadapter for the new does not submit the data to the actual
database.
Any assistance would be greatly appreciated.
I have commented my sample code to explain what I have done at each
step.
Sincerely,
-- JR Fournier
(e-mail address removed)
-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-
Private Sub CopyFromOldToNew(ByVal strTable As String)
'strTable is the name of the table passed from outside functions.
'I wish to use a subroutine like this to perform the copy because I
'will not know the structure of the tables in advance.
'strConnOld and strConnNew are globally defined connection variables.
'They are defined as follows:
'Const strConnOld As String = _
' "Data Source=localhost;Initial Catalog=utility_be_old;" _
' "Integrated Security=SSPI"
'Const strConnNew As String = _
' "Data Source=localhost;Initial Catalog=utility_be_v2;" _
' "Integrated Security=SSPI"
'I then declare old and new datasets and dataadapters.
Dim dsOld As New DataSet()
Dim dsNew As New DataSet()
Dim daOld As New SqlDataAdapter("Select * from " & strTable, _
strConnOld)
Dim daNew As New SqlDataAdapter("Select * from " & strTable, _
strConnNew)
'While filling my dsOld dataset is obviously necessary, I don't think
'filling my dsNew is. The actual "new" database will always contain
'the structure only, not any data. Keep in mind, the table name that
'has been passed to this subroutine exists in BOTH the old and the
'new database. The structure is the same. I would use an SQL
'command and perform this copy manually, but my goal is to create a
'function that has no advance knowledge of which table is copied.
daOld.Fill(dsOld, strTable)
'daNew.Fill(dsNew, strTable)
'Of all the code involved, the cmdBuilder usage confuses me the most.
Dim cmdBuilder As SqlCommandBuilder = _
New SqlCommandBuilder(daNew)
'The following line of code is one of about 5 ways I have tried.
'This merge statement will successfully take my dsOld data and put
'it into my dsNew dataset. Now, I have also done the row by row
'copy, etc, but the end result of getting the data into the new
'dataset is the same. This is a one line solution that achieves
'the same effect. If there is a way to just use one dataset, since
'the data is the same, I would be happy to use it. But any attempts
'to only use the one dataset resulted in "This table [or this column]
'does not belong to that table", etc.
dsNew.Merge(dsOld, True, MissingSchemaAction.Add)
'I added a datagrid to display the contents of the new dataset,
'proving that dsNew contains the data that should be saved to the
'database.
DataGrid1.SetDataBinding(dsNew, strTable)
'I confess that the cmdBuilder usage confuses me a bit.
'If I am misunderstanding the usage, please assist.
daNew.UpdateCommand = cmdBuilder.GetUpdateCommand()
daNew.InsertCommand = cmdBuilder.GetInsertCommand()
'Is AcceptChanges actually needed on my dsNew dataset?
dsNew.AcceptChanges()
'From what I understand, telling my daNew dataadapter to update
'should commit my added rows to the new database. No error is
'thrown, but no changes are made in the actual database.
daNew.Update(dsNew.Tables(0))
End Sub
-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-
Please help!