Issue with DataSet / DataAdapter and Idendity Column

D

Daniel Jeffrey

Hello,

I am new to ADO.NET (2.0) and I am trying my hardest to figure out the best
ways to do things, but I just keep getting stuck.

I have a DataSet that has a Header and Details configuration with the
relevant joins.

I have 2 DataAdapters, 1 for the Header and 1 For the Detail.

It is all working, unless I add 2 rows to the detail then save.

1 row at a time, and it works fine, the identity column is automatically
updated with the next identity value.

However when I add 2 rows, the first row is posted, and the 2nd row returns
errors about not being Unique.

Can anyone please let me know what I am doing wrong?

Should I be manually seeding these values before post?


DataSet Update Command

TaskAdapter.Update(dsTask, "TASK");
TaskNotesAdapter.Update(dsTask, "TASK_NOTES");

The Insert Statement that was generated is below.

INSERT INTO [TASK_ATTACHMENTS] ([NOTEID], [TASKID], [TITLE],
[ORIGINAL_FILENAME], [ATTACHMENT]) VALUES (@NOTEID, @TASKID, @TITLE, @p1,
@ATTACHMENT);
SELECT RECORDID, NOTEID, TASKID, TITLE, ORIGINAL_FILENAME, ATTACHMENT FROM
TASK_ATTACHMENTS WHERE (RECORDID = SCOPE_IDENTITY())

Table is below

CREATE TABLE [dbo].[TASK_NOTES](
[NOTEID] [int] IDENTITY(1,1) NOT NULL,
[TASKID] [int] NOT NULL,
[NOTEDATE] [datetime] NOT NULL DEFAULT (getdate()),
[ENTEREDBY] [varchar](30) NULL,
[STATUSID] [int] NOT NULL,
[NOTETYPE] [int] NOT NULL,
[NOTES] [varchar](max) NULL,
[SENDEMAIL] [char](1) NOT NULL DEFAULT ('N'),
[IS_QUESTION] [char](1) NOT NULL DEFAULT ('N'),
[QUESTION_USERCODE] [varchar](30) NULL,
[RESPONSE_RECEIVED] [char](1) NOT NULL DEFAULT ('N'),
[RESPONSE_NOTEID] [int] NULL,
PRIMARY KEY CLUSTERED
(
[NOTEID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
D

Daniel Jeffrey

Sorry the Insert statement is

INSERT INTO [TASK_NOTES] ([TASKID], [NOTEDATE], [ENTEREDBY], [STATUSID],
[NOTETYPE], [NOTES], [SENDEMAIL], [IS_QUESTION], [QUESTION_USERCODE],
[RESPONSE_RECEIVED], [RESPONSE_NOTEID]) VALUES (@TASKID, @NOTEDATE,
@ENTEREDBY, @STATUSID, @NOTETYPE, @NOTES, @SENDEMAIL, @IS_QUESTION,
@QUESTION_USERCODE, @RESPONSE_RECEIVED, @RESPONSE_NOTEID);
SELECT NOTEID, TASKID, NOTEDATE, ENTEREDBY, STATUSID, NOTETYPE, NOTES,
SENDEMAIL, IS_QUESTION, QUESTION_USERCODE, RESPONSE_RECEIVED,
RESPONSE_NOTEID FROM TASK_NOTES WHERE (NOTEID = SCOPE_IDENTITY())
 
D

Daniel Jeffrey

Found the solution

the table was Identity(1,1) and the DataSet had it as (0,1)

I find the DataSet generator to be quite troublesome.

Doesn't get defaults, doesn't do identity correctly!

Thanks if anyone read this.

Dan

Daniel Jeffrey said:
Sorry the Insert statement is

INSERT INTO [TASK_NOTES] ([TASKID], [NOTEDATE], [ENTEREDBY], [STATUSID],
[NOTETYPE], [NOTES], [SENDEMAIL], [IS_QUESTION], [QUESTION_USERCODE],
[RESPONSE_RECEIVED], [RESPONSE_NOTEID]) VALUES (@TASKID, @NOTEDATE,
@ENTEREDBY, @STATUSID, @NOTETYPE, @NOTES, @SENDEMAIL, @IS_QUESTION,
@QUESTION_USERCODE, @RESPONSE_RECEIVED, @RESPONSE_NOTEID);
SELECT NOTEID, TASKID, NOTEDATE, ENTEREDBY, STATUSID, NOTETYPE, NOTES,
SENDEMAIL, IS_QUESTION, QUESTION_USERCODE, RESPONSE_RECEIVED,
RESPONSE_NOTEID FROM TASK_NOTES WHERE (NOTEID = SCOPE_IDENTITY())


Daniel Jeffrey said:
Hello,

I am new to ADO.NET (2.0) and I am trying my hardest to figure out the
best ways to do things, but I just keep getting stuck.

I have a DataSet that has a Header and Details configuration with the
relevant joins.

I have 2 DataAdapters, 1 for the Header and 1 For the Detail.

It is all working, unless I add 2 rows to the detail then save.

1 row at a time, and it works fine, the identity column is automatically
updated with the next identity value.

However when I add 2 rows, the first row is posted, and the 2nd row
returns errors about not being Unique.

Can anyone please let me know what I am doing wrong?

Should I be manually seeding these values before post?


DataSet Update Command

TaskAdapter.Update(dsTask, "TASK");
TaskNotesAdapter.Update(dsTask, "TASK_NOTES");

The Insert Statement that was generated is below.

INSERT INTO [TASK_ATTACHMENTS] ([NOTEID], [TASKID], [TITLE],
[ORIGINAL_FILENAME], [ATTACHMENT]) VALUES (@NOTEID, @TASKID, @TITLE, @p1,
@ATTACHMENT);
SELECT RECORDID, NOTEID, TASKID, TITLE, ORIGINAL_FILENAME, ATTACHMENT
FROM TASK_ATTACHMENTS WHERE (RECORDID = SCOPE_IDENTITY())

Table is below

CREATE TABLE [dbo].[TASK_NOTES](
[NOTEID] [int] IDENTITY(1,1) NOT NULL,
[TASKID] [int] NOT NULL,
[NOTEDATE] [datetime] NOT NULL DEFAULT (getdate()),
[ENTEREDBY] [varchar](30) NULL,
[STATUSID] [int] NOT NULL,
[NOTETYPE] [int] NOT NULL,
[NOTES] [varchar](max) NULL,
[SENDEMAIL] [char](1) NOT NULL DEFAULT ('N'),
[IS_QUESTION] [char](1) NOT NULL DEFAULT ('N'),
[QUESTION_USERCODE] [varchar](30) NULL,
[RESPONSE_RECEIVED] [char](1) NOT NULL DEFAULT ('N'),
[RESPONSE_NOTEID] [int] NULL,
PRIMARY KEY CLUSTERED
(
[NOTEID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top