Problem adding records with SQLDataadapter to SQL Server database

4 Jan 2006 1:29 PM

Rob Smeets

Hi,

I'm building an application, to edit data on a SQL Server database.
Database has a table Jobs with primary key (JobID) with identity seed = 1
and identity increment = 1

I'm using SQLConnection and SqlDataAdapters, DataSet, datagrid and DataViews
Dataset represents the SQL database.

When i add a record to the DataSet(Jobs), there is a Primary key generated.
So for example the datagrid on my form shows the following:

JobID    JobName            JobDescription
1    test1            This is the first job record
2    test2            This is the second job record
3    test3            This is the third job record

When i click add i add a new record to the dataset.
The primairy key is generated (not by my code) as JobID = 4.
But my JobID on SQL server (because i've added several records and deleted
them) is 15. When i click save my SQLDataAdapter is updating the SQL server
and adds the record (through stored procedures) without supplying a JOBID.
When the save is done i have 2 extra records. One is a record wich only
exists in the dataset with JobID = 4 and one new record from the SQL server
with JobID = 15

This is not expected behaivior... Anyone have some ideas?

Help would be greatly appriciated, thanks in advance

Rob Smeets

4 Jan 2006 2:10 PM

Miha Markic [MVP C#]

Hi Rob,

First thing to do is to use negative values for both seed and increment.
So the new records won't interfere with old ones.
As per duplicates, this is (annoyingly) correc: see if this helps you
http://support.microsoft.com/kb/313540/en-us

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"Rob Smeets" <RobSme***@discussions.microsoft.com> wrote in message
news:7826CD22-63BC-4BDD-A864-C5D1C22217CC@microsoft.com...
> Hi,
>
> I'm building an application, to edit data on a SQL Server database.
> Database has a table Jobs with primary key (JobID) with identity seed = 1
> and identity increment = 1
>
> I'm using SQLConnection and SqlDataAdapters, DataSet, datagrid and
> DataViews
> Dataset represents the SQL database.
>
> When i add a record to the DataSet(Jobs), there is a Primary key
> generated.
> So for example the datagrid on my form shows the following:
>
>
> JobID JobName JobDescription
> 1 test1 This is the first job record
> 2 test2 This is the second job record
> 3 test3 This is the third job record
>
>
> When i click add i add a new record to the dataset.
> The primairy key is generated (not by my code) as JobID = 4.
> But my JobID on SQL server (because i've added several records and deleted
> them) is 15. When i click save my SQLDataAdapter is updating the SQL
> server
> and adds the record (through stored procedures) without supplying a
> JOBID.
> When the save is done i have 2 extra records. One is a record wich only
> exists in the dataset with JobID = 4 and one new record from the SQL
> server
> with JobID = 15
>
> This is not expected behaivior... Anyone have some ideas?
>
> Help would be greatly appriciated, thanks in advance
>
> Rob Smeets