strange error on query execution

Discussion in 'microsoft.public.sqlserver.ce' started by Alan Lambert, Dec 22, 2009.

  1. Alan Lambert

    Alan Lambert Guest

    I'm using SQLCE 3.5

    I have a table that is storing an order list with 2 fields ID and
    Description. ID is sequentailly numbered.

    I want to insert 2 extra items in the middle of the list (before item 5,
    let's say) and run the following query:

    UPDATE myTable
    SET Id=Id+2
    WHERE Id>=5

    To move all items greater than 5 'up' 2 places so I can then insert the new
    records. However, I get the following error message:

    Major Error 0x80040E2F, Minor Error 25016
    SET Id=Id+2
    WHERE Id>=5
    A duplicate value cannot be inserted into a unique index. [ Table name =
    myTable,Constraint name = PK_myTable ]

    I understand that it's trying to 'move' the items into already existing
    places but why. When using SQL Server it batch processes and handles this
    fine. Am I doing something wrong here or is there a way around this?

    Many thanks

    Alan
     
    Alan Lambert, Dec 22, 2009
    #1
    1. Advertisements

  2. Hi,
    I would do a cycle in reverse order to update the IDs fro mthe greatest to
    the shorter. Try using an updatable SqlCeResulset to do it.

    Alberto Silva


    __________ Information from ESET NOD32 Antivirus, version of virus signature database 4713 (20091223) __________

    The message was checked by ESET NOD32 Antivirus.

    http://www.eset.com
     
    Alberto Silva, MVP, Dec 28, 2009
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.