DataSet and updating

[Guest]

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 Markic [MVP C#]]

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

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

 

[William Ryan eMVP]

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

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

 

[William Ryan eMVP]

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

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:

 

[William Ryan eMVP]

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

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:

 

[Guest]

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>

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

 

[William Ryan eMVP]

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.

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:

 

[William Ryan eMVP]

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)

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

 

[Guest]

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 Ryan eMVP]

Glad to hear it worked ;-)

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!