Ask a Question related to ASP, Design and Development.
-
Roland Hall #1
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
-
default index for primary key of a table
Vinita.Bansal@trilogy.com writes: Postgres does not support UNIQUE constraints (and PRIMARY KEY implies a UNIQUE constraint) without an index.... -
converting unique index into primary key
I need to convert an existing unique index on a very heavily inserted table into a primary key. Alter table works, but locks the table for too... -
default index created for primary key
Hi, I want to turn off the default setting in postgres for index creation on primary key of a table. Is it possible and how? Regards Vinita ... -
Changing Primary Index Question
GlacierYesterday I had to add a column to a table that had a 3-column primary index and this new column (a nullable column) had to become part of... -
is primary key implicitly included in an index?
on the following: CREATE TABLE T (id INTEGER NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL); I want the following query to run very fast:... -
Bob Barrows #2 Re: duplicate values in the index, primary key, or relationship
Roland Hall wrote:
It sounds to me as if you are attempting to update the field to a value that> 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
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
-
Mark Schupp #3 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" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eyZ%23Wu45DHA.3304@tk2msftngp13.phx.gbl...that> 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 valueget> 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> 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
-
Roland Hall #4 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
-
Roland Hall #5 Re: duplicate values in the index, primary key, or relationship
"Roland Hall" <nobody@nowhere> wrote in message
news:uMyxNA55DHA.2416@TK2MSFTNGP10.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
-
Roland Hall #6 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
-
Roland Hall #7 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 Documentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]
Roland Hall Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
