belgie
1/31/2008 2:26:00 PM
I see.
I tried creating a second dataset pulled from the second data adapter and
merging it with the first dataset, but it still doesn't appear to consider
the merged rows as 'New' rows, because they don't get inserted.
The only solution I have found is to loop through the original dataset and
Add the rows to the second dataset one by one - there doesn't appear to be
any other way to flag them as Inserted.
I'll take your suggestion and use the SQLBulkCopy class.
Thanks
Bill
"Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
news:B6C2890C-660E-4859-94B2-1A2C1D7EDBF0@microsoft.com...
> Bill,
>
> One problem is that there are not any new, changed or deleleted rows in
> the
> datatable to update when you call the adapter's Update method.
>
> Kerry Moorman
>
>
> "BillE" wrote:
>
>> Thanks for the suggestion, I'll check it out.
>>
>> Is there a problem using the method I described?
>>
>> Thanks
>> Bill
>>
>>
>> "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
>> news:B7FF1CEC-FE68-40AC-A45E-86B57023B478@microsoft.com...
>> > BillE,
>> >
>> > You might consider using the SqlBulkCopy class for this.
>> >
>> > Kerry Moorman
>> >
>> >
>> > "BillE" wrote:
>> >
>> >> Hi!
>> >> I'm transferring the records in a table from one database to another.
>> >>
>> >> I create a data adapter using a connection from the first database to
>> >> fill a
>> >> dataset.
>> >>
>> >> I add an InsertCommand with a different connection to the second
>> >> database,
>> >> and add the necessary parameters.
>> >>
>> >> I hoped that when I called the update method, the records in the
>> >> dataset
>> >> added from the first database would be inserted into the identical
>> >> table
>> >> in
>> >> the second database.
>> >>
>> >> This didn't work, but no exception was thrown. Just no records were
>> >> added.
>> >>
>> >> My code is like this modified code from msdn - should this work?
>> >>
>> >> Private Sub AdapterUpdate(ByVal connectionStringA As String, ByVal
>> >> connectionStringB As String)
>> >>
>> >> dim connectionA As SqlConnection = New SqlConnection( _
>> >> connectionStringA)
>> >>
>> >> dim connectionB As SqlConnection = New SqlConnection( _
>> >> connectionStringB)
>> >>
>> >> Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
>> >> "SELECT CategoryID, CategoryName FROM dbo.Categories", _
>> >> connectionA)
>> >>
>> >> adapter.InsertCommand = New SqlCommand( _
>> >> "INSERT Categories (CategoryName) values (@CategoryName),
>> >> connectionB)
>> >>
>> >> adapter.UpdateCommand.Parameters.Add( _
>> >> "@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")
>> >>
>> >> Dim categoryTable As New DataTable
>> >> adapter.Fill(categoryTable)
>> >>
>> >> adapter.Update(categoryTable)
>> >>
>> >> End Sub
>> >>
>> >>
>> >>
>>
>>
>>