Forums

This topic is locked

Update - won't, narrowed the problem down

Posted 06 Feb 2002 03:01:08
1
has voted
06 Feb 2002 03:01:08 Stuart Chase posted:
I get this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] 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.
/db/status2.asp, line 89

Background Info: Access2000
tblPilots, form has 2 fields
callsign and pilot_status,
both are drop down menus to select.

Heres the troubleshooting I did.

I tried the submit, having the two fields, callsign and online status,
With ignoring callsign the update would update - but it didn't matter
what callsign I picked it only changed the very first record.

I went back in and made callsign update to field callsign, and it gave me
the error

I have the code here if anybody would like me to email it to them.

And thank you in advance.



Replies

Replied 06 Feb 2002 18:34:37
06 Feb 2002 18:34:37 David Behan replied:
I have seen something like this before when using a stored procedure to extract a number from a table relevant to a record, add one digit to it, and put the new value back in.

However, what was happening was it was changing all the values of all records in that field to the new number. This occurred due to not specifing to update "WHERE id=currentrecord". The same could be occuring with your script.

Alternatively, the other thing that could be occuring (which just occured to me from the symptons you described) is that you are not using an update behaviour but an insert behaviour. To keep referential integrity, ACCESS needs to specify a primary key. If your script is not updating a record, then it may be inserting another record that is the same as one of the ones aready there. Integrity is breeched and ACCESS will not allow it.

Suggestions:

1. Add another field (and do this everytime for each table) called id, set it to autonumber and make it your primary key.

2. Check that your not inserting a record instead of updating.

3. Check you are saying that to update record where callsign is equal to "variable".

Post script if your still having problems.


Regards,


Dave

_________________________
David Behan - www.bmor.com
Replied 07 Feb 2002 02:40:32
07 Feb 2002 02:40:32 b w replied:
Make sure the Primary key isn't exposed to the update process in UD4. If possible use the "ignore" on the primary key. I had to "text" it instead of using a "text field" for the primary key field.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I get this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] 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.
/db/status2.asp, line 89

Background Info: Access2000
tblPilots, form has 2 fields
callsign and pilot_status,
both are drop down menus to select.

Heres the troubleshooting I did.

I tried the submit, having the two fields, callsign and online status,
With ignoring callsign the update would update - but it didn't matter
what callsign I picked it only changed the very first record.

I went back in and made callsign update to field callsign, and it gave me
the error

I have the code here if anybody would like me to email it to them.

And thank you in advance.




<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Replied 07 Feb 2002 14:24:39
07 Feb 2002 14:24:39 Stuart Chase replied:
hi, sorry for the delay in responding.

It is an update command instead of the insert - and I do have Primary key being autonumber. - but I have tried either not bringing it into the form, or displaying as text - not the text field, or the ignore.

I'll give that whereID=current record a shot.

Thank you

Replied 07 Feb 2002 15:52:30
07 Feb 2002 15:52:30 David Behan replied:
Tell me how you got on....

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
hi, sorry for the delay in responding.

It is an update command instead of the insert - and I do have Primary key being autonumber. - but I have tried either not bringing it into the form, or displaying as text - not the text field, or the ignore.

I'll give that whereID=current record a shot.

Thank you


<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

_________________________
David Behan - www.bmor.com
Replied 08 Feb 2002 02:15:31
08 Feb 2002 02:15:31 Stuart Chase replied:
Hi,

It hasnt,

I tried putting the where ID=currentrecord into the recordset - but that was looking for a value that was being passed through.

Replied 08 Feb 2002 13:55:11
08 Feb 2002 13:55:11 David Behan replied:
Throw up the code for me to have a look

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Hi,

It hasnt,

I tried putting the where ID=currentrecord into the recordset - but that was looking for a value that was being passed through.


<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

_________________________
David Behan - www.bmor.com

Edited by - beano on 12 Feb 2002 13:18:49
Replied 08 Feb 2002 14:23:09
08 Feb 2002 14:23:09 Stuart Chase replied:
Theres quite a bit of it. Mind if I email it to you?

Replied 08 Feb 2002 14:24:39
08 Feb 2002 14:24:39 David Behan replied:
go

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Theres quite a bit of it. Mind if I email it to you?


<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

_________________________
David Behan - www.bmor.com
Replied 11 Feb 2002 14:22:06
11 Feb 2002 14:22:06 Stuart Chase replied:
Did you get the file ok Beano?

Thank you
Stuart


Replied 12 Feb 2002 21:43:14
12 Feb 2002 21:43:14 Stuart Chase replied:
Ran into some errors.

I actually just forgot to delete tblStatus - I realized it would end up making me do double entries than just tblPilots.

I changed the recordset name and string to reflect the connection and table name.

But always got this error - on the Pilot_Status dropdown box

ADODB.Field error '800a0bcd' Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record /db/status2.asp line 158

Line 158 is (I took out all &lt; to let it display)

select name="pilot_status"&gt;
option value="Online" %If (rsStatus.Fields.Item("pilot_status".Value = "Online" Then Response.Write("SELECTED" : Response.Write(""%&gt;&gt;Online /option&gt;
option value="Offline" &lt;%If (rsStatus.Fields.Item("pilot_status".Value = "Offline" Then Response.Write("SELECTED" : Response.Write(""%&gt;&gt;Offline /option&gt;
/select&gt;

did it do this to you also?

Replied 12 Feb 2002 21:48:16
12 Feb 2002 21:48:16 David Behan replied:
I was getting that as well because the status page was looking for a current record. There was none because you had to select the record from a previous page. In the files I sent you, you'll see that I added a master page linked to the details page. The only one that will work is the ROADRUNNER record as his name is the only one that I added in the database that you sent me.

Please explain what you are using this section for...



<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Ran into some errors.

I actually just forgot to delete tblStatus - I realized it would end up making me do double entries than just tblPilots.

I changed the recordset name and string to reflect the connection and table name.

But always got this error - on the Pilot_Status dropdown box

ADODB.Field error '800a0bcd' Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record /db/status2.asp line 158

Line 158 is (I took out all &lt; to let it display)

select name="pilot_status"&gt;
option value="Online" %If (rsStatus.Fields.Item("pilot_status".Value = "Online" Then Response.Write("SELECTED" : Response.Write(""%&gt;&gt;Online /option&gt;
option value="Offline" &lt;%If (rsStatus.Fields.Item("pilot_status".Value = "Offline" Then Response.Write("SELECTED" : Response.Write(""%&gt;&gt;Offline /option&gt;
/select&gt;

did it do this to you also?


<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

_________________________
David Behan - www.bmor.com
Replied 12 Feb 2002 22:21:27
12 Feb 2002 22:21:27 Stuart Chase replied:
I didnt realize it was selecting a previous record.

hmm

ok, well what I do is Moderators, and you can see the basic in action on www.detour.stormhosts.com
A mod clicks a change status link - it pops up the page to select callsign and status dropdown. They hit submit and it will change the display as you can see on the homepage for the above url.
(I plan to later have the page autorefresh every x minutes.)

Replied 12 Feb 2002 22:26:17
12 Feb 2002 22:26:17 David Behan replied:
If they log in on the home page then you should be using cookies. So why not set the id of the page of their status equal to their cookie[callsign] and then the person who is logged in would be the only one to be able to change their own callsign status...



<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I didnt realize it was selecting a previous record.

hmm

ok, well what I do is Moderators, and you can see the basic in action on www.detour.stormhosts.com
A mod clicks a change status link - it pops up the page to select callsign and status dropdown. They hit submit and it will change the display as you can see on the homepage for the above url.
(I plan to later have the page autorefresh every x minutes.)


<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

_________________________
David Behan - www.bmor.com
Replied 13 Feb 2002 03:37:56
13 Feb 2002 03:37:56 Stuart Chase replied:
I thought of that, but it would be nice
to be able to change another MOD's status if for instance they left and forgot to change it.

But if this would be easier, I don't mind making the master pilot update page include the status.

Strange, I have cookies activated on this (IE 6.0) but I can never get it to recall my logging in.

Reply to this topic