"DavidA" wrote:

>
> I am trying to copy data from a table in one database to an identical
> table in another database.
>
> The first table (WOS) contains 11 records, the second (WOSAdmin) is
> empty.
>
> The problem is that the data is never inserted into the second table.
> Can anyone explain what I am doing wrong?
>
> Definitions of the tables and the code I am using below:
>
> USE [WOS]
> GO
> /****** Object:  Table [dbo].[MembershipCategory]    Script Date:
> 01/21/2006 21:48:19 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[MembershipCategory](
>     [TypeOfMember] [smallint] NOT NULL,
>     [MembershipType] [nvarchar](50) COLLATE Latin1_General_CI_AS
> NULL,
>     [AnnualFee] [money] NULL,
>     [JoiningFee] [money] NULL,
>  CONSTRAINT [PK_MembershipCategory] PRIMARY KEY CLUSTERED
> (
>     [TypeOfMember] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
>
>
> USE [WOSAdmin]
> GO
> /****** Object:  Table [dbo].[MembershipCategory]    Script Date:
> 01/21/2006 21:47:28 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[MembershipCategory](
>     [TypeOfMember] [smallint] NOT NULL,
>     [MembershipType] [nvarchar](50) COLLATE Latin1_General_CI_AS
> NULL,
>     [AnnualFee] [money] NULL,
>     [JoiningFee] [money] NULL,
>  CONSTRAINT [PK_MembershipCategory] PRIMARY KEY CLUSTERED
> (
>     [TypeOfMember] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> 
>
>        Dim conn As New SqlConnection
>         Dim da As New SqlDataAdapter
>         Dim connString As String = "data
> source=RUATHA\SQLEXPRESS;Initial Catalog=WOS;Integrated Security=True"
>         conn.ConnectionString = connString
>         conn.Open()
>         Dim cmdString As String = "select TypeOfMember,
> MembershipType, AnnualFee, JoiningFee from MembershipCategory"
>         Dim cmd As New SqlCommand(cmdString, conn)
>         da.SelectCommand = cmd
>         Dim ds As New DataSet
>         da.Fill(ds, "MembershipCategory")
>         conn.Close()
>
>         connString = "data source=RUATHA\SQLEXPRESS;Initial
> Catalog=WOSAdmin;Integrated Security=True"
>         conn.ConnectionString = connString
>         conn.Open()
>         Dim cmd1 As New SqlCommand(cmdString, conn)
>         Dim da1 As New SqlDataAdapter(cmd1)
>         Dim builder As New SqlCommandBuilder(da1)
>         da1.InsertCommand = builder.GetInsertCommand
>         da1.DeleteCommand = builder.GetDeleteCommand
>         da1.UpdateCommand = builder.GetUpdateCommand
>
>         Dim ds1 As New DataSet
>         da.Fill(ds1, "MembershipCategory")
>         ds1.Merge(ds)   ' I have used Copy, but makes no difference
>             ' In both cases ds1 contains the data from ds
>
>         da.Update(ds1, "MembershipCategory")
>

>Following code snippet shows whole process:
>
>        ' build source
>        Dim conn As New SqlConnection
>        Dim da As New SqlDataAdapter
>        Dim connString As String = "data
>source=RUATHA\SQLEXPRESS;Initial Catalog=WOS;Integrated Security=True"
>        conn.ConnectionString = connString
>        conn.Open()
>        Dim cmdString As String = "select TypeOfMember,
>MembershipType, AnnualFee, JoiningFee from MembershipCategory"
>        Dim cmd As New SqlCommand(cmdString, conn)
>        da.SelectCommand = cmd
>        Dim ds As New DataSet
>        da.Fill(ds, "MembershipCategory")
>        conn.Close()
>
>        ' build target         
>        connString = "data source=RUATHA\SQLEXPRESS;Initial
>Catalog=WOSAdmin;Integrated Security=True"
>        conn.ConnectionString = connString
>        conn.Open()
>        Dim cmd1 As New SqlCommand(cmdString, conn)
>        Dim da1 As New SqlDataAdapter(cmd1)
>        Dim builder As New SqlCommandBuilder(da1)
>        Dim ds1 As New DataSet
>        da1.Fill(ds1, "MembershipCategory")
>       
>        ' transfer data
>        Dim newRow As DataRow
>        DataTable MembershipCategoryTable = ds1.Tables("MembershipCategory") 

>        Foreach row As DataRow In ds.Tables("MembershipCategory").Rows
>            newRow = MembershipCategoryTable.NewRow()
>            newRow.ItemArray = row.ItemArray
>            MembershipCategoryTable.Rows.Add(newRow)                   
>        Next
>
>        ' Update target database
>        da1.Update(ds1, "MembershipCategory")
>
>HTH
>
>Elton Wang
>

"DavidA" wrote:

> Thanks Elton, it worked a treat.
>
> On Sat, 21 Jan 2006 17:23:02 -0800, "Elton W"
> <Elt***@discussions.microsoft.com> wrote:
>
> >Hi David,
> >
> >First of all, you should use
> >
> >da1.Fill(ds1);
> >da1.Update(ds1);
> >
> >To target WOSAdmin for inserting.
> >
>
> Copying error - was correct in the original code!
>
> >Secondly, if you use either Merge or Copy method to transfer data from ds to
> >ds1, it also copy DataRowState (Unchanged) value to according datarow. Hence
> >when run da1.Update method,  SqlCommandBuilder will find no new row for
> >inserting (it builds insert/update/delete commands depending on
> >DataRowState.)
> >
>
> That was the problem. I thought that copy copied the data in the same
> way as the row copy below, and that as the data wasn't in the 2nd
> table it would be flagged as changed.
>
> >In order to set DataRowState to Added (for SqlCommandBuilder to create
> >insert command), one way is to use DataTable.Rows.Add(DataRow) method.
>
> Yes, that works perfectly, although I did have to dim the
> MembershipCategoryTable - are you a C# programmer normally?
>
> >Following code snippet shows whole process:
> >
> >        ' build source
> >        Dim conn As New SqlConnection
> >        Dim da As New SqlDataAdapter
> >        Dim connString As String = "data
> >source=RUATHA\SQLEXPRESS;Initial Catalog=WOS;Integrated Security=True"
> >        conn.ConnectionString = connString
> >        conn.Open()
> >        Dim cmdString As String = "select TypeOfMember,
> >MembershipType, AnnualFee, JoiningFee from MembershipCategory"
> >        Dim cmd As New SqlCommand(cmdString, conn)
> >        da.SelectCommand = cmd
> >        Dim ds As New DataSet
> >        da.Fill(ds, "MembershipCategory")
> >        conn.Close()
> >
> >        ' build target         
> >        connString = "data source=RUATHA\SQLEXPRESS;Initial
> >Catalog=WOSAdmin;Integrated Security=True"
> >        conn.ConnectionString = connString
> >        conn.Open()
> >        Dim cmd1 As New SqlCommand(cmdString, conn)
> >        Dim da1 As New SqlDataAdapter(cmd1)
> >        Dim builder As New SqlCommandBuilder(da1)
> >        Dim ds1 As New DataSet
> >        da1.Fill(ds1, "MembershipCategory")
> >       
> >        ' transfer data
> >        Dim newRow As DataRow
> >        DataTable MembershipCategoryTable = ds1.Tables("MembershipCategory") 
>          ^^^^^^^^^^^^^^^^^^^^^^^^
> >        Foreach row As DataRow In ds.Tables("MembershipCategory").Rows
> >            newRow = MembershipCategoryTable.NewRow()
> >            newRow.ItemArray = row.ItemArray
> >            MembershipCategoryTable.Rows.Add(newRow)                   
> >        Next
> >
> >        ' Update target database
> >        da1.Update(ds1, "MembershipCategory")
> >
> >HTH
> >
> >Elton Wang
> >
>