Why won't my dataadapter update update?

  • Thread starter Thread starter DavidA
  • Start date Start date
D

DavidA

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")
 
Hi David,

First of all, you should use

da1.Fill(ds1);
da1.Update(ds1);

To target WOSAdmin for inserting.

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.)

In order to set DataRowState to Added (for SqlCommandBuilder to create
insert command), one way is to use DataTable.Rows.Add(DataRow) method.
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 said:
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")
Click to expand...
 
Thanks Elton, it worked a treat.

Hi David,

First of all, you should use

da1.Fill(ds1);
da1.Update(ds1);

To target WOSAdmin for inserting.
Click to expand...

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.)
Click to expand...

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.
Click to expand...

Yes, that works perfectly, although I did have to dim the
MembershipCategoryTable - are you a C# programmer normally?
 
I’ m glad that helped to you.

Yes, you note that I mixed C# in demonstration code. Currently C# is my
major working language, thought I used VB.NET for couple of years.

Elton
 
Back
Top