DataSet and updating

Ok. I have a table with a primary key being an integer. When using VS.NET 2002, it could not create an update command for my table. Being SQL savvy, I created my own update statement below

Me.SqlCommand4.CommandText = "UPDATE dbo.links_page SET (title=@title, imageurl=@imageurl) WHERE (link_page_id = @link_page_id); SELECT title, link_page_id, imageurl FROM dbo.links_page

It is bound to the SqlDataAdapter I created. When I add a new record, it is getting the proper auto-increment values, and when I execute .Update(myDataSet), the record is added to the database table

But after that record is added, it gives me a constraint error. Apparently, it is having a problem with records that are already in the table. It looks like it is attempting to insert copies of them into the table (which violates my primary key.

The exact error is

Column 'link_page_id' is constrained to be unique. Value '2' is already present

How do I fix this problem

Miha
Tue Apr 27 12:21:44 CDT 2004

Hi,

Why are you doing a select after update in first place?
Also, does DataTable have primary key defined?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in message
news:0FF751E7-5AF3-45B7-95A9-26F8353FCEA4@microsoft.com...
> Ok. I have a table with a primary key being an integer. When using
VS.NET 2002, it could not create an update command for my table. Being SQL
savvy, I created my own update statement below:
>
> Me.SqlCommand4.CommandText = "UPDATE dbo.links_page SET (title=@title,
imageurl=@imageurl) WHERE (link_page_id = @link_page_id); SELECT title,
link_page_id, imageurl FROM dbo.links_page"
>
> It is bound to the SqlDataAdapter I created. When I add a new record, it
is getting the proper auto-increment values, and when I execute
.Update(myDataSet), the record is added to the database table.
>
> But after that record is added, it gives me a constraint error.
Apparently, it is having a problem with records that are already in the
table. It looks like it is attempting to insert copies of them into the
table (which violates my primary key.)
>
> The exact error is:
>
> Column 'link_page_id' is constrained to be unique. Value '2' is already
present.
>
> How do I fix this problem?
>

William
Tue Apr 27 12:39:32 CDT 2004

That Select statement is the problem, the update fires row by row , but your
select is firing and returning the whole dataset not just the row.. so you
need to add a
WHERE ((link_page_id = @link_page_id) for instance (NOt sure if that's the
value of your key, but you want to pull back only one record that matches
what you updated

"ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in message
news:0FF751E7-5AF3-45B7-95A9-26F8353FCEA4@microsoft.com...
> Ok. I have a table with a primary key being an integer. When using
VS.NET 2002, it could not create an update command for my table. Being SQL
savvy, I created my own update statement below:
>
> Me.SqlCommand4.CommandText = "UPDATE dbo.links_page SET (title=@title,
imageurl=@imageurl) WHERE (link_page_id = @link_page_id); SELECT title,
link_page_id, imageurl FROM dbo.links_page"
>
> It is bound to the SqlDataAdapter I created. When I add a new record, it
is getting the proper auto-increment values, and when I execute
.Update(myDataSet), the record is added to the database table.
>
> But after that record is added, it gives me a constraint error.
Apparently, it is having a problem with records that are already in the
table. It looks like it is attempting to insert copies of them into the
table (which violates my primary key.)
>
> The exact error is:
>
> Column 'link_page_id' is constrained to be unique. Value '2' is already
present.
>
> How do I fix this problem?
>

William
Tue Apr 27 13:21:38 CDT 2004

The refresh dataset option on the configuration wizard does this for you..
nothing wrong with using a Select, it's just what you are selecting.

If you are getting it without the update, it's b/c you are trying to insert
a value into the db that isn't valid. Have you stepped through and seen what
the param values are ? If not, walk through your datatable with a
Debug.Writeline and write them out.. then verify they don't exist in the db.


Also, have you set the autoincrement property of the DataColumn to true? It
not, make sure you do it. Set the AutoINcrement Seed to 0 and the
AutoINcrementValue to -1. You may just be trying to add a value that's
already in there
"ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in message
news:1DB0FF7C-7504-4046-803B-952A1C95077B@microsoft.com...
> The reason why I'm using a select statement is because I thought that I
needed it. Looking at the insert statement that was automatically generated
by VS.NET, this is what it contained:
>
> NSERT INTO dbo.links_page(title, imageurl) VALUES (@title, @imageurl);
SELECT title, link_page_id, imageurl FROM dbo.links_page
>
> Also, removing the select statement from the update statement ***does not
work***. I still get the same error. The updated statement is below:
>
> UPDATE dbo.links_page SET (title=@title, imageurl=@imageurl) WHERE
(link_page_id = @link_page_id)
>
> link_page_id is my primary key.
>
> Any suggestions would be greatly appreciated.
>
>
> ----- William Ryan eMVP wrote: -----
>
> That Select statement is the problem, the update fires row by row ,
but your
> select is firing and returning the whole dataset not just the row..
so you
> need to add a
> WHERE ((link_page_id = @link_page_id) for instance (NOt sure if
that's the
> value of your key, but you want to pull back only one record that
matches
> what you updated
>
> "ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in
message
> news:0FF751E7-5AF3-45B7-95A9-26F8353FCEA4@microsoft.com...
> > Ok. I have a table with a primary key being an integer. When
using
> VS.NET 2002, it could not create an update command for my table.
Being SQL
> savvy, I created my own update statement below:
> >> Me.SqlCommand4.CommandText = "UPDATE dbo.links_page SET
(title=@title,
> imageurl=@imageurl) WHERE (link_page_id = @link_page_id); SELECT
title,
> link_page_id, imageurl FROM dbo.links_page"
> >> It is bound to the SqlDataAdapter I created. When I add a new
record, it
> is getting the proper auto-increment values, and when I execute
> ..Update(myDataSet), the record is added to the database table.
> >> But after that record is added, it gives me a constraint error.
> Apparently, it is having a problem with records that are already in
the
> table. It looks like it is attempting to insert copies of them into
the
> table (which violates my primary key.)
> >> The exact error is:
> >> Column 'link_page_id' is constrained to be unique. Value '2' is
already
> present.
> >> How do I fix this problem?
> >

William
Tue Apr 27 13:23:15 CDT 2004

I posted this in another NG a few minute ago, but this is probably the
problem (I thought it was you but I realize it wasn't)l

<<Set the Autoincrement propety of the datacolumn to true. Then set the
Autoincrement seed as 0 and the Autoincrement value to -1. This way,
when -1 hits the db, SQL Server will asssign the next legit value and
guarantee that you and some other user don't step on each other.

http://www.knowdotnet.com/articles/adopartiii.html>>
"ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in message
news:1DB0FF7C-7504-4046-803B-952A1C95077B@microsoft.com...
> The reason why I'm using a select statement is because I thought that I
needed it. Looking at the insert statement that was automatically generated
by VS.NET, this is what it contained:
>
> NSERT INTO dbo.links_page(title, imageurl) VALUES (@title, @imageurl);
SELECT title, link_page_id, imageurl FROM dbo.links_page
>
> Also, removing the select statement from the update statement ***does not
work***. I still get the same error. The updated statement is below:
>
> UPDATE dbo.links_page SET (title=@title, imageurl=@imageurl) WHERE
(link_page_id = @link_page_id)
>
> link_page_id is my primary key.
>
> Any suggestions would be greatly appreciated.
>
>
> ----- William Ryan eMVP wrote: -----
>
> That Select statement is the problem, the update fires row by row ,
but your
> select is firing and returning the whole dataset not just the row..
so you
> need to add a
> WHERE ((link_page_id = @link_page_id) for instance (NOt sure if
that's the
> value of your key, but you want to pull back only one record that
matches
> what you updated
>
> "ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in
message
> news:0FF751E7-5AF3-45B7-95A9-26F8353FCEA4@microsoft.com...
> > Ok. I have a table with a primary key being an integer. When
using
> VS.NET 2002, it could not create an update command for my table.
Being SQL
> savvy, I created my own update statement below:
> >> Me.SqlCommand4.CommandText = "UPDATE dbo.links_page SET
(title=@title,
> imageurl=@imageurl) WHERE (link_page_id = @link_page_id); SELECT
title,
> link_page_id, imageurl FROM dbo.links_page"
> >> It is bound to the SqlDataAdapter I created. When I add a new
record, it
> is getting the proper auto-increment values, and when I execute
> ..Update(myDataSet), the record is added to the database table.
> >> But after that record is added, it gives me a constraint error.
> Apparently, it is having a problem with records that are already in
the
> table. It looks like it is attempting to insert copies of them into
the
> table (which violates my primary key.)
> >> The exact error is:
> >> Column 'link_page_id' is constrained to be unique. Value '2' is
already
> present.
> >> How do I fix this problem?
> >

William
Tue Apr 27 14:14:43 CDT 2004

How many rows are considered changed (Modified/Added/Deleted)? If the
rowstate for the 1st 3 is unchanged, nothing in Update should be firing for
it, so check the rowstate for each one.
"ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in message
news:4B919A02-B72E-4326-97E2-67ACA6328990@microsoft.com...
> Already did that. It adds the records to the table correctly, but once
the DataAdapter.Update command is executed, it also trys to add the records
that are already in the table.
>
> For instance, lets say I have the following table:
>
> ID title URL
> ----------------------------------------------------------------------
> 1 Microsoft http://www.microsoft.com
> 2 Google http://www.google.com
> 3 Experts Exchange http://www.experts-exchange.com
>
> I use a DataAdapter.Fill to fill my dataset object.
>
> I add the following record:
>
> ID title URL
> ----------------------------------------------------------------------
> 4 Yahoo! http://www.yahoo.com
>
> Then, I use DataAdapter.Update to send the updates to the MS SQL 7 server.
>
> If this table I just described had the exact same problem as the one I'm
developing, DataAdapter.Update will add record 4 correctly. But...once
DataAdapter.Update sees records 1, 2, and 3, it thinks they are new records,
and attempts to add them. This would bring me to the following error:
>
> Column 'id' is constrained to be unique. Value '1' is already present.
>
> So apparently, DataAdapter.Update is not using my Update statement at all.
>
> If you have any suggestions, they would be greatly appreciated.
>
> ----- William Ryan eMVP wrote: -----
>
> I posted this in another NG a few minute ago, but this is probably
the
> problem (I thought it was you but I realize it wasn't)l
>
> <<Set the Autoincrement propety of the datacolumn to true. Then set
the
> Autoincrement seed as 0 and the Autoincrement value to -1. This way,
> when -1 hits the db, SQL Server will asssign the next legit value and
> guarantee that you and some other user don't step on each other.
>
> http://www.knowdotnet.com/articles/adopartiii.html>>
> "ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in
message
> news:1DB0FF7C-7504-4046-803B-952A1C95077B@microsoft.com...
> > The reason why I'm using a select statement is because I thought
that I
> needed it. Looking at the insert statement that was automatically
generated
> by VS.NET, this is what it contained:
> >> NSERT INTO dbo.links_page(title, imageurl) VALUES (@title,
@imageurl);
> SELECT title, link_page_id, imageurl FROM dbo.links_page
> >> Also, removing the select statement from the update statement
***does not
> work***. I still get the same error. The updated statement is
below:
> >> UPDATE dbo.links_page SET (title=@title, imageurl=@imageurl) WHERE
> (link_page_id = @link_page_id)
> >> link_page_id is my primary key.
> >> Any suggestions would be greatly appreciated.
> >>> ----- William Ryan eMVP wrote: -----
> >> That Select statement is the problem, the update fires row by
row ,
> but your
> > select is firing and returning the whole dataset not just the
row..
> so you
> > need to add a
> > WHERE ((link_page_id = @link_page_id) for instance (NOt sure
if
> that's the
> > value of your key, but you want to pull back only one record
that
> matches
> > what you updated
> >> "ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com>
wrote in
> message
> > news:0FF751E7-5AF3-45B7-95A9-26F8353FCEA4@microsoft.com...
> >> Ok. I have a table with a primary key being an integer. When
> using
> > VS.NET 2002, it could not create an update command for my
table.
> Being SQL
> > savvy, I created my own update statement below:
> >>> Me.SqlCommand4.CommandText = "UPDATE dbo.links_page SET
> (title=@title,
> > imageurl=@imageurl) WHERE (link_page_id = @link_page_id);
SELECT
> title,
> > link_page_id, imageurl FROM dbo.links_page"
> >>> It is bound to the SqlDataAdapter I created. When I add a new
> record, it
> > is getting the proper auto-increment values, and when I
execute
> > ..Update(myDataSet), the record is added to the database
table.
> >>> But after that record is added, it gives me a constraint error.
> > Apparently, it is having a problem with records that are
already in
> the
> > table. It looks like it is attempting to insert copies of
them into
> the
> > table (which violates my primary key.)
> >>> The exact error is:
> >>> Column 'link_page_id' is constrained to be unique. Value '2' is
> already
> > present.
> >>> How do I fix this problem?
> >>

anonymous
Tue Apr 27 14:11:04 CDT 2004

Well, I looked through my code, and the autoincrement, autoincrementseed, and autoincrementstep were commented out. I uncommented them, and now it doesn't do the insert command on DataAdapter.Update!!

The error is

Column 'link_page_id' is constrained to be unique. Value '8' is already present.

Now, if the autoincrement is supposed to let SQL decide a key number, then how come SQL can't see it already has an entry with '8'? There are 8 records in the table, numbered 2 through 9 (1 was deleted.


----- William Ryan eMVP wrote: ----

I posted this in another NG a few minute ago, but this is probably th
problem (I thought it was you but I realize it wasn't)

<<Set the Autoincrement propety of the datacolumn to true. Then set th
Autoincrement seed as 0 and the Autoincrement value to -1. This way
when -1 hits the db, SQL Server will asssign the next legit value an
guarantee that you and some other user don't step on each other

http://www.knowdotnet.com/articles/adopartiii.html>
"ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in messag
news:1DB0FF7C-7504-4046-803B-952A1C95077B@microsoft.com..
> The reason why I'm using a select statement is because I thought that
needed it. Looking at the insert statement that was automatically generate
by VS.NET, this is what it contained
>> NSERT INTO dbo.links_page(title, imageurl) VALUES (@title, @imageurl)
SELECT title, link_page_id, imageurl FROM dbo.links_pag
>> Also, removing the select statement from the update statement ***does no
work***. I still get the same error. The updated statement is below
>> UPDATE dbo.links_page SET (title=@title, imageurl=@imageurl) WHER
(link_page_id = @link_page_id
>> link_page_id is my primary key
>> Any suggestions would be greatly appreciated
>>> ----- William Ryan eMVP wrote: ----
>> That Select statement is the problem, the update fires row by row
but you
> select is firing and returning the whole dataset not just the row.
so yo
> need to add
> WHERE ((link_page_id = @link_page_id) for instance (NOt sure i
that's th
> value of your key, but you want to pull back only one record tha
matche
> what you update
>> "ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote i
messag
> news:0FF751E7-5AF3-45B7-95A9-26F8353FCEA4@microsoft.com..
>> Ok. I have a table with a primary key being an integer. Whe
usin
> VS.NET 2002, it could not create an update command for my table
Being SQ
> savvy, I created my own update statement below
>>> Me.SqlCommand4.CommandText = "UPDATE dbo.links_page SE
(title=@title
> imageurl=@imageurl) WHERE (link_page_id = @link_page_id); SELEC
title
> link_page_id, imageurl FROM dbo.links_page
>>> It is bound to the SqlDataAdapter I created. When I add a ne
record, i
> is getting the proper auto-increment values, and when I execut
> ..Update(myDataSet), the record is added to the database table
>>> But after that record is added, it gives me a constraint error
> Apparently, it is having a problem with records that are already i
th
> table. It looks like it is attempting to insert copies of them int
th
> table (which violates my primary key.
>>> The exact error is
>>> Column 'link_page_id' is constrained to be unique. Value '2' i
alread
> present
>>> How do I fix this problem
>>

William
Tue Apr 27 14:17:45 CDT 2004

That's only if you set it to a Negative Number. What are the values of
those three fields? they should be true, 0 and -1 (Autoincrement,
AutoIncrementSeed, AutoIncrementValue)
"ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in message
news:C1D38740-AD32-40D3-A972-9D3A539D0FB7@microsoft.com...
> Well, I looked through my code, and the autoincrement, autoincrementseed,
and autoincrementstep were commented out. I uncommented them, and now it
doesn't do the insert command on DataAdapter.Update!!!
>
> The error is:
>
> Column 'link_page_id' is constrained to be unique. Value '8' is already
present.
>
> Now, if the autoincrement is supposed to let SQL decide a key number, then
how come SQL can't see it already has an entry with '8'? There are 8
records in the table, numbered 2 through 9 (1 was deleted.)
>
>
> ----- William Ryan eMVP wrote: -----
>
> I posted this in another NG a few minute ago, but this is probably
the
> problem (I thought it was you but I realize it wasn't)l
>
> <<Set the Autoincrement propety of the datacolumn to true. Then set
the
> Autoincrement seed as 0 and the Autoincrement value to -1. This way,
> when -1 hits the db, SQL Server will asssign the next legit value and
> guarantee that you and some other user don't step on each other.
>
> http://www.knowdotnet.com/articles/adopartiii.html>>
> "ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in
message
> news:1DB0FF7C-7504-4046-803B-952A1C95077B@microsoft.com...
> > The reason why I'm using a select statement is because I thought
that I
> needed it. Looking at the insert statement that was automatically
generated
> by VS.NET, this is what it contained:
> >> NSERT INTO dbo.links_page(title, imageurl) VALUES (@title,
@imageurl);
> SELECT title, link_page_id, imageurl FROM dbo.links_page
> >> Also, removing the select statement from the update statement
***does not
> work***. I still get the same error. The updated statement is
below:
> >> UPDATE dbo.links_page SET (title=@title, imageurl=@imageurl) WHERE
> (link_page_id = @link_page_id)
> >> link_page_id is my primary key.
> >> Any suggestions would be greatly appreciated.
> >>> ----- William Ryan eMVP wrote: -----
> >> That Select statement is the problem, the update fires row by
row ,
> but your
> > select is firing and returning the whole dataset not just the
row..
> so you
> > need to add a
> > WHERE ((link_page_id = @link_page_id) for instance (NOt sure
if
> that's the
> > value of your key, but you want to pull back only one record
that
> matches
> > what you updated
> >> "ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com>
wrote in
> message
> > news:0FF751E7-5AF3-45B7-95A9-26F8353FCEA4@microsoft.com...
> >> Ok. I have a table with a primary key being an integer. When
> using
> > VS.NET 2002, it could not create an update command for my
table.
> Being SQL
> > savvy, I created my own update statement below:
> >>> Me.SqlCommand4.CommandText = "UPDATE dbo.links_page SET
> (title=@title,
> > imageurl=@imageurl) WHERE (link_page_id = @link_page_id);
SELECT
> title,
> > link_page_id, imageurl FROM dbo.links_page"
> >>> It is bound to the SqlDataAdapter I created. When I add a new
> record, it
> > is getting the proper auto-increment values, and when I
execute
> > ..Update(myDataSet), the record is added to the database
table.
> >>> But after that record is added, it gives me a constraint error.
> > Apparently, it is having a problem with records that are
already in
> the
> > table. It looks like it is attempting to insert copies of
them into
> the
> > table (which violates my primary key.)
> >>> The exact error is:
> >>> Column 'link_page_id' is constrained to be unique. Value '2' is
> already
> > present.
> >>> How do I fix this problem?
> >>

anonymous
Tue Apr 27 14:26:04 CDT 2004

Well, I feel stupid now..

I removed the autoincrement stuff, and took out the SELECT statement at the end of my INSERT command, and it started working

It's times like this that make Borland more and more appealing :)

I'm guessing that the select statement after the insert was throwing off the insert portion of DataAdapter.Update, and making it think that there was more than 1 row to insert into the table

I thank you for all of your help! If it had not of been for your pointing out of the SELECT statement, I would be rather frustrated right now :

I hope that VS.NET Whidbey has some of these issues resolved (or at least mentioned in MSDN.) Again, thanks for all of your help!

William
Tue Apr 27 14:54:16 CDT 2004

Glad to hear it worked ;-)
"ADO.NET.Needs.Work" <anonymous@discussions.microsoft.com> wrote in message
news:C4331688-43AC-4CB5-9924-567AD8BC9167@microsoft.com...
> Well, I feel stupid now...
>
> I removed the autoincrement stuff, and took out the SELECT statement at
the end of my INSERT command, and it started working!
>
> It's times like this that make Borland more and more appealing :).
>
> I'm guessing that the select statement after the insert was throwing off
the insert portion of DataAdapter.Update, and making it think that there was
more than 1 row to insert into the table.
>
> I thank you for all of your help! If it had not of been for your pointing
out of the SELECT statement, I would be rather frustrated right now :)
>
> I hope that VS.NET Whidbey has some of these issues resolved (or at least
mentioned in MSDN.) Again, thanks for all of your help!