Professional Web Applications Themes

duplicate values in the index, primary key, or relationship - ASP

I have a MSA2K db. Table: customers Columns: fileNumber: (primary) Indexed(No Duplicates) fileName Client ClientType fileYear boxNumber: Indexed(Duplicates OK) When running a SQL UPDATE (listed below) I get an error: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. I read all records based on a query and populate a form. I allow for some/all records to be modified. I ...

  1. #1

    Default duplicate values in the index, primary key, or relationship

    I have a MSA2K db.
    Table: customers
    Columns:
    fileNumber: (primary) Indexed(No Duplicates)
    fileName
    Client
    ClientType
    fileYear
    boxNumber: Indexed(Duplicates OK)

    When running a SQL UPDATE (listed below) I get an error:
    The changes you requested to the table were not successful because they
    would create duplicate values in the index, primary key, or relationship.
    Change the data in the field or fields that contain duplicate data, remove
    the index, or redefine the index to permit duplicate entries and try again.

    I read all records based on a query and populate a form. I allow for
    some/all records to be modified. I post the form to a new page, build my
    SQL string and execute. I'm almost sure the error is with the primary field
    but I'm updating, not adding.

    strSQL = "UPDATE Customers SET Customers.fileNumber = '" &
    replace(Request.Form("fileNumber" & x),"'","''") & "', Customers.fileName =
    '" & replace(Request.Form("fileName" & x),"'","''") & "', Customers.Client =
    '" & replace(Request.Form("Client" & x),"'","''") & "', Customers.ClientType
    = '" & replace(Request.Form("ClientType"),"'","''") & "', Customers.fileYear
    = '" & replace(Request.Form("fileYear"),"'","''") & "', Customers.boxNumber
    = '" & replace(Request.Form("boxNum" & x),"'","''") & "' WHERE
    Customers.ClientType= '" & replace(Request.Form("ClientType"),"'","''") & "'
    AND Customers.fileYear = '" & replace(Request.Form("fileYear"),"'","''") &
    "'"

    x is a record index added to the field based on the number of records read
    from the database.

    TIA...

    Roland


    Roland Hall Guest

  2. #2

    Default Re: duplicate values in the index, primary key, or relationship

    Roland Hall wrote:
    > I have a MSA2K db.
    > Table: customers
    > Columns:
    > fileNumber: (primary) Indexed(No Duplicates)
    > fileName
    > Client
    > ClientType
    > fileYear
    > boxNumber: Indexed(Duplicates OK)
    >
    > When running a SQL UPDATE (listed below) I get an error:
    > The changes you requested to the table were not successful because
    > they would create duplicate values in the index, primary key, or
    > relationship. Change the data in the field or fields that contain
    > duplicate data, remove the index, or redefine the index to permit
    > duplicate entries and try again.
    >
    > I read all records based on a query and populate a form. I allow for
    > some/all records to be modified. I post the form to a new page,
    > build my SQL string and execute. I'm almost sure the error is with
    > the primary field but I'm updating, not adding.
    >
    > strSQL = "UPDATE Customers SET Customers.fileNumber = '" &
    > replace(Request.Form("fileNumber" & x),"'","''") & "',
    > Customers.fileName = '" & replace(Request.Form("fileName" &
    > x),"'","''") & "', Customers.Client = '" &
    > replace(Request.Form("Client" & x),"'","''") & "',
    > Customers.ClientType = '" &
    > replace(Request.Form("ClientType"),"'","''") & "', Customers.fileYear
    > = '" & replace(Request.Form("fileYear"),"'","''") & "',
    > Customers.boxNumber = '" & replace(Request.Form("boxNum" &
    > x),"'","''") & "' WHERE Customers.ClientType= '" &
    > replace(Request.Form("ClientType"),"'","''") & "' AND
    > Customers.fileYear = '" & replace(Request.Form("fileYear"),"'","''")
    > & "'"
    >
    > x is a record index added to the field based on the number of records
    > read from the database.
    >
    > TIA...
    >
    > Roland
    It sounds to me as if you are attempting to update the field to a value that
    already exists in the database ... but you already knew that, right?

    If you response.write the sql statement and execute it in access, do you get
    the same error?

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows Guest

  3. #3

    Default Re: duplicate values in the index, primary key, or relationship

    Are you trying to change the value of the primary key with the update
    statement?

    If so, check the database to make sure that another record does not already
    use the new value.
    Also make sure that the where clause specifies at most a single record.

    If not change the where clause to use the primary key instead of the other
    values.

    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    [url]www.ielearning.com[/url]


    "Bob Barrows" <reb01501NOyahoo.SPAMcom> wrote in message
    news:eyZ%23Wu45DHA.3304tk2msftngp13.phx.gbl...
    > Roland Hall wrote:
    > > I have a MSA2K db.
    > > Table: customers
    > > Columns:
    > > fileNumber: (primary) Indexed(No Duplicates)
    > > fileName
    > > Client
    > > ClientType
    > > fileYear
    > > boxNumber: Indexed(Duplicates OK)
    > >
    > > When running a SQL UPDATE (listed below) I get an error:
    > > The changes you requested to the table were not successful because
    > > they would create duplicate values in the index, primary key, or
    > > relationship. Change the data in the field or fields that contain
    > > duplicate data, remove the index, or redefine the index to permit
    > > duplicate entries and try again.
    > >
    > > I read all records based on a query and populate a form. I allow for
    > > some/all records to be modified. I post the form to a new page,
    > > build my SQL string and execute. I'm almost sure the error is with
    > > the primary field but I'm updating, not adding.
    > >
    > > strSQL = "UPDATE Customers SET Customers.fileNumber = '" &
    > > replace(Request.Form("fileNumber" & x),"'","''") & "',
    > > Customers.fileName = '" & replace(Request.Form("fileName" &
    > > x),"'","''") & "', Customers.Client = '" &
    > > replace(Request.Form("Client" & x),"'","''") & "',
    > > Customers.ClientType = '" &
    > > replace(Request.Form("ClientType"),"'","''") & "', Customers.fileYear
    > > = '" & replace(Request.Form("fileYear"),"'","''") & "',
    > > Customers.boxNumber = '" & replace(Request.Form("boxNum" &
    > > x),"'","''") & "' WHERE Customers.ClientType= '" &
    > > replace(Request.Form("ClientType"),"'","''") & "' AND
    > > Customers.fileYear = '" & replace(Request.Form("fileYear"),"'","''")
    > > & "'"
    > >
    > > x is a record index added to the field based on the number of records
    > > read from the database.
    > >
    > > TIA...
    > >
    > > Roland
    >
    > It sounds to me as if you are attempting to update the field to a value
    that
    > already exists in the database ... but you already knew that, right?
    >
    > If you response.write the sql statement and execute it in access, do you
    get
    > the same error?
    >
    > Bob Barrows
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >

    Mark Schupp Guest

  4. #4

    Default Re: duplicate values in the index, primary key, or relationship

    "Bob Barrows" wrote:
    : It sounds to me as if you are attempting to update the field to a value
    that
    : already exists in the database ... but you already knew that, right?

    Yes. It appears it is not possible, AFAIK, to update with the same value if
    the field is set to No Duplicates.

    : If you response.write the sql statement and execute it in access, do you
    get
    : the same error?

    Since I'm Access challenged, I'm not sure where to do the above.

    These are the next two lines so I have one record to test with.

    Response.Write(strSQL) & "<br />" & vbCrLf
    conn.execute(strSQL)

    Roland


    Roland Hall Guest

  5. #5

    Default Re: duplicate values in the index, primary key, or relationship

    "Roland Hall" <nobodynowhere> wrote in message
    news:uMyxNA55DHA.2416TK2MSFTNGP10.phx.gbl...
    : "Bob Barrows" wrote:
    : : It sounds to me as if you are attempting to update the field to a value
    : that
    : : already exists in the database ... but you already knew that, right?
    :
    : Yes. It appears it is not possible, AFAIK, to update with the same value
    if
    : the field is set to No Duplicates.
    :
    : : If you response.write the sql statement and execute it in access, do you
    : get
    : : the same error?
    :
    : Since I'm Access challenged, I'm not sure where to do the above.

    Ok, FYI... I'm using Outlook 2K3, but it probably doesn't matter.
    I created a new query in design mode and selected the table. I then
    selected SQL View and put in the update I got from the response.write. It
    says it wants to update 11 rows when IMO it should only be 1 so perhaps my
    update is not correct and it is possibly with the where clause? It's
    looking for clientType and fileYear and should be looking at fileNumber
    where the fileNumber matches, I assume. Perhaps that is why it says it is a
    duplicate since it's trying to update all rows where clientType and fileYear
    meet the test?

    Roland


    Roland Hall Guest

  6. #6

    Default Re: duplicate values in the index, primary key, or relationship

    "Mark Schupp" wrote:
    : Are you trying to change the value of the primary key with the update
    : statement?
    :
    : If so, check the database to make sure that another record does not
    already
    : use the new value.
    : Also make sure that the where clause specifies at most a single record.
    :
    : If not change the where clause to use the primary key instead of the other
    : values.

    Yes. It appears to be the where clause. Working on that now.
    Thanks for responding.

    Roland


    Roland Hall Guest

  7. #7

    Default Re: duplicate values in the index, primary key, or relationship

    "Roland Hall" wrote:
    : "Mark Schupp" wrote:
    : : Are you trying to change the value of the primary key with the update
    : : statement?
    : :
    : : If so, check the database to make sure that another record does not
    : already
    : : use the new value.
    : : Also make sure that the where clause specifies at most a single record.
    : :
    : : If not change the where clause to use the primary key instead of the
    other
    : : values.
    :
    : Yes. It appears to be the where clause. Working on that now.
    : Thanks for responding.

    Ok, I got it working by changing the where clause so it now reads:

    strSQL = "UPDATE Customers SET Customers.fileNumber = '" &
    replace(Request.Form("fileNumber" & x),"'","''") & "', Customers.fileName =
    '" & replace(Request.Form("fileName" & x),"'","''") & "', Customers.Client =
    '" & replace(Request.Form("Client" & x),"'","''") & "', Customers.ClientType
    = '" & replace(Request.Form("ClientType"),"'","''") & "', Customers.fileYear
    = '" & replace(Request.Form("fileYear"),"'","''") & "', Customers.boxNumber
    = '" & replace(Request.Form("boxNum" & x),"'","''") & "' WHERE
    Customers.fileNumber= '" & replace(Request.Form("fileNumber" & x),"'","''")
    & "'"

    Thank you Bob and Mark.

    --
    Roland Hall
    /* This information is distributed in the hope that it will be useful, but
    without any warranty; without even the implied warranty of merchantability
    or fitness for a particular purpose. */
    Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
    WSH 5.6 Doentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
    MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]


    Roland Hall Guest

Similar Threads

  1. Question Remove Duplicate Rows in MySQL Table Having No Primary Key or Unique Index
    By deltaforce in forum Brainstorming Area
    Replies: 0
    Last Post: July 25th, 10:14 AM
  2. converting unique index into primary key
    By Ed L. in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 23rd, 03:42 AM
  3. default index created for primary key
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 7
    Last Post: December 22nd, 07:30 PM
  4. Changing Primary Index Question
    By Bill Hamilton in forum Informix
    Replies: 8
    Last Post: September 4th, 05:29 PM
  5. is primary key implicitly included in an index?
    By Zig Mandel in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 09:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139