Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 11th, 2008, 08:27 AM
Registered User
 
Join Date: Apr 2008
Location: Athens, , Greece.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to alxGramma
Default Cannot insert duplicate key row in object 'dbo.mov

 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
  #2 (permalink)  
Old April 11th, 2008, 10:06 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
  #3 (permalink)  
Old April 12th, 2008, 01:41 AM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #4 (permalink)  
Old April 14th, 2008, 03:02 AM
Registered User
 
Join Date: Apr 2008
Location: Athens, , Greece.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to alxGramma
Default

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

<!-- 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
  #5 (permalink)  
Old April 15th, 2008, 01:42 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

"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


Similar Threads
Thread Thread Starter Forum Replies Last Post
cannot insert duplicate key row in object 'Table' skyline666 SQL Server 2005 0 April 15th, 2008 05:03 AM
insert a duplicate row with one changed field only swdgaurav SQL Language 1 December 31st, 2007 04:54 AM
invalid object dbo.split error............ hepsy.i ASP.NET 1.0 and 1.1 Professional 1 August 8th, 2007 06:31 PM
Problem with insert new row in *.dbo table dimeanel Pro VB.NET 2002/2003 1 January 23rd, 2006 12:05 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.