Cannot insert duplicate key row in object 'dbo.mov

alxGramma · April 11th, 2008, 08:27 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

planoie · April 11th, 2008, 10:06 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

robprell · April 12th, 2008, 01:41 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.

alxGramma · April 14th, 2008, 03:02 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
%>



<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

planoie · April 15th, 2008, 01:42 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