|
Subject:
|
Cannot insert duplicate key row in object 'dbo.mov
|
|
Posted By:
|
alxGramma
|
Post Date:
|
4/11/2008 8:27:03 AM
|
hallo there
i have been studying on asp using one of your books
i 'm in chapter14 (advanced data handling techniques) of the beginning active server pages 3.0
I use the sql server 2005 express edition and it has been working fine until i get to the point to add data.
i realized when i opened the "movies" table of the movie 2000 database that table is messed up.i sort it out using sql commands but i can not save the out come....
i have also tried using asp code to sort out the table but i cannot use the table afterwards to add new data....
i have managed to print he right order with a simple command object and then a response.write loop until the end of file.
but when i put in the code of addnew.asp (page 595) i get the following error message
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E2F)
Cannot insert duplicate key row in object 'dbo.movies' with unique index 'MoviesMovieId'.
/alx_asp/chapter_14/addasd.asp, line 27
could i please get some help please, because this is an important bit of using ASP
alxG
|
|
Reply By:
|
planoie
|
Reply Date:
|
4/11/2008 10:06:38 AM
|
The error is telling you that you are trying to insert a duplicate value. This is part of database design. There is a key on the table consisting of 1 or more columns. This key requires unique values. You'll need to change the value you are trying to insert to make it work.
-Peter
peterlanoie.blog
|
|
Reply By:
|
robprell
|
Reply Date:
|
4/12/2008 1:41:00 AM
|
Another way of saying the same thing is the database has rules to prevent you from putting in bad data like duplicates. When you tried to put in a duplicate it rejected it as it should.
|
|
Reply By:
|
alxGramma
|
Reply Date:
|
4/14/2008 3:02:13 AM
|
yes I understand this but....
when I load the movie2000 database (downloaded from your site) THE DATA IS MESSED UP.... the id numbers go like 64 , 47, 1, 2, 13, 43 etc
so when i code in objRS.movelast... the last id that i reach is not 330 but a number less than that (aka 47) ...
as I code in intNewId = objRS("movieid") + 1 this number already has an attribute....
this is the code that I use (copied from beginning asp 3.0 )
<%
option explicit
dim strConnect
%>
<!-- #include file="datastore.asp" -->
<!-- METADATA
TYPE="TypeLib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{00000206-0000-0010-8000-00AA006D2EA4}"
VERSION="2.6" -->
<html>
<head>
<title>adding a new record... code sucks</title>
</head>
<body>
<%
dim objRS,intNewRecordID
set objRS = server.createobject ("adodb.recordset")
objRS.open "movies", strConnect, adopenstatic, adlockoptimistic, adcmdtable
objRS.movelast
intNewRecordID = objRS("movieid") + 1
objRS.addnew
objRS("MovieID") = intNewRecordID
objRS("Title") = "psycho"
objRS.update
objRS.close
objRS.open "select from movies where movieid=" & intNewRecordID, _
strConnect, adopenforwardonly, adlockreadonly, adcmdtext
if objRS.eof then
response.write "new record not found - something went wrong"
else
response.write "you've succesfully added a new record : <br>" &_
"movie title : " & objRS("title") & "<br>" &_
"movie ID : " & objRS("movieid")
end if
objRS.close
set objRS = nothing
%>
</body>
</html>
i have managed using order by to sort out the data .. but i can not combine on the code above, and i have also skipped the above error by using a manual movieid (aka 345) .... but this is not the way it should work....
the reason that I posted this topic in SQL forum and not ASP is because the problem is in the database and not in the code.
i would like to sort out the data and save it .... but I can not.
alxG
|
|
Reply By:
|
planoie
|
Reply Date:
|
4/15/2008 1:42:48 PM
|
"Last" is very relative.
In the case of a sql query result, it will be the last record of the ordered set based on the ordering rules.
It appears you are trying to create a new record based on the last ID you encounter. As you are finding the technique you are using is not ideal.
There are usually two techniques used for this:
1) Use a query to find the last ID number, then increment it: SELECT MAX(fieldname) FROM tablename
This is NOT recommended because it is not safe. If you have simultaneous activity on the same operation you can easily get two instance of the same code stepping on each other using the same ID.
2) Use the features of the database to generate the ID. Usually this is accomplished with a field that is configured for identity. In Access this is called "Auto Number". When you create a record, you omit the field name from the insert statement as the database generates the value for you. This is far safer. Then the trick is getting the value out. But that's another thread.
-Peter
peterlanoie.blog
|
