SqlDataAdapter and/or SqlCommandBilder Problem

[Frank Uray]

Hi all

I have some problems with updateing SQL Server Table
from a DataTable.
The SQL Server Table is empty with IDENITY Seed = 0
and it is a tree table (Attribute FK_ID is pointing to ID).

Now I have a DataTable with some (Valid !) data in it,
but I am unable to fill this into the SQL Server Table ...
It seams to be a problem with the ID Column because
it always starts at 1 and is just counting up, not matter what
is in the DataSet.

I have already tried to disable the constraints on the SQL Table,
but nothing is changed ... :-(((

Does anybody knows what I am doing wrong ???

Thanks for any comments and best regards
Frank Uray


Here you see the table definition and some code:

CREATE TABLE [_system].[Process](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FK_ID] [int] NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](max) NULL,
[IsLocked] [smallint] NOT NULL CONSTRAINT [DF_system_Process_IsLocked]
DEFAULT ((0)),
[IsEnabled] [smallint] NOT NULL CONSTRAINT [DF_system_Process_IsEnabled]
DEFAULT ((1)),
[ORDER] [int] NULL CONSTRAINT [DF_system_Process_ORDER] DEFAULT ((0)),
CONSTRAINT [PK_system_Process] PRIMARY KEY CLUSTERED
([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]) ON [PRIMARY]

ALTER TABLE [_system].[Process] WITH NOCHECK ADD CONSTRAINT
[FK_system_Process_Process]
FOREIGN KEY([FK_ID])
REFERENCES [_system].[Process] ([ID])
NOT FOR REPLICATION

ALTER TABLE [_system].[Process] CHECK CONSTRAINT [FK_system_Process_Process]



<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Tree>
<ID>2</ID>
<Name>RootNode1</Name>
<Description>Description RootNode1</Description>
<IsLocked>1</IsLocked>
<IsEnabled>1</IsEnabled>
<ORDER>0</ORDER>
</Tree>
<Tree>
<ID>3</ID>
<Name>RootNode2</Name>
<Description>Description RootNode2</Description>
<IsLocked>0</IsLocked>
<IsEnabled>0</IsEnabled>
<ORDER>0</ORDER>
</Tree>
<Tree>
<ID>4</ID>
<FK_ID>2</FK_ID>
<Name>ChildNode1</Name>
<Description>Description ChildNode1</Description>
<IsLocked>1</IsLocked>
<IsEnabled>1</IsEnabled>
<ORDER>0</ORDER>
</Tree>
<Tree>
<ID>5</ID>
<FK_ID>2</FK_ID>
<Name>ChildNode2</Name>
<Description>Description ChildNode2</Description>
<IsLocked>0</IsLocked>
<IsEnabled>1</IsEnabled>
<ORDER>0</ORDER>
</Tree>
<Tree>
<ID>6</ID>
<FK_ID>2</FK_ID>
<Name>ChildNode3</Name>
<Description>Description ChildNode3</Description>
<IsLocked>0</IsLocked>
<IsEnabled>1</IsEnabled>
<ORDER>0</ORDER>
</Tree>
<Tree>
<ID>7</ID>
<FK_ID>3</FK_ID>
<Name>ChildNode4</Name>
<Description>Description ChildNode4</Description>
<IsLocked>0</IsLocked>
<IsEnabled>1</IsEnabled>
<ORDER>0</ORDER>
</Tree>
<Tree>
<ID>9</ID>
<FK_ID>2</FK_ID>
<Name>ChildNode4</Name>
<Description />
<IsLocked>0</IsLocked>
<IsEnabled>1</IsEnabled>
<ORDER>0</ORDER>
</Tree>
<Tree>
<ID>10</ID>
<Name>TestRoot</Name>
<Description>TestRoot</Description>
<IsLocked>0</IsLocked>
<IsEnabled>1</IsEnabled>
<ORDER>1</ORDER>
</Tree>
<Tree>
<ID>11</ID>
<FK_ID>10</FK_ID>
<Name>Test1111</Name>
<Description>Test1 xxxxxxxxxxxx</Description>
<IsLocked>0</IsLocked>
<IsEnabled>1</IsEnabled>
<ORDER>1</ORDER>
</Tree>
<Tree>
<ID>12</ID>
<FK_ID>10</FK_ID>
<Name>Test2</Name>
<Description>klhjp9uhpihpi</Description>
<IsLocked>0</IsLocked>
<IsEnabled>1</IsEnabled>
<ORDER>2</ORDER>
</Tree>
<Tree>
<ID>14</ID>
<FK_ID>12</FK_ID>
<Name>Test3</Name>
<Description />
<IsLocked>0</IsLocked>
<IsEnabled>1</IsEnabled>
<ORDER>1</ORDER>
</Tree>
<Tree>
<ID>15</ID>
<FK_ID>14</FK_ID>
<Name>Test4</Name>
<Description />
<IsLocked>0</IsLocked>
<IsEnabled>1</IsEnabled>
<ORDER>1</ORDER>
</Tree>
</NewDataSet>


local_Query = "SELECT [ID], [FK_ID], [Name], [Description], [IsLocked],
[IsEnabled], [ORDER] FROM " + local_CurrentDBSchemaTable;

local_DataAdapter = new System.Data.SqlClient.SqlDataAdapter(local_Query,
RstData.return_SQLNATIVEConnection);

local_CommandBuilder = new
System.Data.SqlClient.SqlCommandBuilder(local_DataAdapter);
local_CommandBuilder.GetDeleteCommand();
local_CommandBuilder.GetInsertCommand();
local_CommandBuilder.GetUpdateCommand();
local_DataAdapter.Update(remote_DataSet.Tables[0]);

 

[Alberto Poblacion]

[...]
The SQL Server Table is empty with IDENITY Seed = 0
and it is a tree table (Attribute FK_ID is pointing to ID).

Now I have a DataTable with some (Valid !) data in it,
but I am unable to fill this into the SQL Server Table ...
It seams to be a problem with the ID Column because
it always starts at 1 and is just counting up, not matter what
is in the DataSet.

I have already tried to disable the constraints on the SQL Table,
but nothing is changed ... :-(((

No, disabling the constraints is not going to help. If you have valid
data in the dataset and you want to insert it into the table exactly as is,
you will need to execute a "SET IDENTITY INSERT theTable ON" to enable
inserting into the Identity column. But if you are going to insert data in
this way, you have to ask yourself why the column is set to Identity in the
first place. The idea of identity columns is that they supply a value
automatically, and their content is not delivered from the client code.

 

[Frank Uray]

Hi Alberto

Thanks for your answer.

I have tried the IDENTITY_INSERT already,
it does not work ...

It seams not to be possible ... :-((
So I changed my code and loop the DataSet and
insert the data with SQL Statements (with IDENTITY_INSERT ON).
I was thinking there would be a more efficient way ...

Normally I use the IDENTITY Column, I just wanted to give
the user the possibility to load a tree from a XML File
into the SQL Server, and for this I need to keep the
ID's from the XML, otherways the Tree would not be valid any more.

Thanks and best regards
Frank Uray