Auto increment Error: Creates Duplicate Key (40) 
Author Message
 Auto increment Error: Creates Duplicate Key (40)

HI EVERYBODY,

Found this error Auto increment Error: Creates Duplicate Key (40), and I
can't find any solution to this one. The scenario is this:

----
NT 3.51Server
Win 95 workstation running

---

When one user will open a form to add new record ( and while still in the
Form), then another will execute another record add in another workstation.

The file in question has two auto increment key.

One has a single field and the other has two field key. I did primed the
keys.

Please give me some ideas..

Neil

What was Einstein's greatest mistake?
See http://www.*-*-*.com/ ~acidbent/index.htm



Fri, 01 Oct 1999 03:00:00 GMT  
 Auto increment Error: Creates Duplicate Key (40)


Quote:
>HI EVERYBODY,
>Found this error Auto increment Error: Creates Duplicate Key (40), and I
>can't find any solution to this one. The scenario is this:
>----
>NT 3.51Server
>Win 95 workstation running
>---
>When one user will open a form to add new record ( and while still in the
>Form), then another will execute another record add in another workstation.
>The file in question has two auto increment key.
>One has a single field and the other has two field key. I did primed the
>keys.
>Please give me some ideas..
>Neil
>What was Einstein's greatest mistake?
>See http://home.netvigator.com/~acidbent/index.htm

I would start by using message() to check what records the Primefields
routine is actually retrieving as the previous reord for each of the
two keys.  

Looking at the code it would seem that you could have a problem if the
second workstation started its priming before the ADD on the first
work station.  Maybe you could put a lock on the file in the
Primefields routine?
----------------------
Jon Waterhouse
Andy Rowe Consultants,
St. John's NF



Fri, 01 Oct 1999 03:00:00 GMT  
 Auto increment Error: Creates Duplicate Key (40)


Quote:

> Found this error Auto increment Error: Creates Duplicate Key (40),
and I
> can't find any solution to this one. The scenario is this:

> The file in question has two auto increment key.

Hi Neil

You need to understand how CW handles autonumber.
Briefly:
1. Uses set/previous on the file to get the next sequential number.
2. Adds a record. Only the autonumber field has a value in it.
3. Sets Request to Change mode
4. After other fields have been completed, updates the file with a Put.

When you have file with two unique keys, the above will work fine for
the first user. However, the second user will have problems on step 2
above. There will be two records in the file with a blank record for
the second unique key, which causes the duplicate key error.

Solutions:
1. I once saw a solution here from someone who wrote the date/time into
the second field for the Add in step 2, then cleared it in the buffer
so that it did not display on the screen.

2. Don't use the CW autonumber. Create your own, using a single record
control file. Encrypt it if necessary. (Don't use an INI file, anyone
can use Notepad to hack it.)

I prefer the second method.

Malcolm



Fri, 01 Oct 1999 03:00:00 GMT  
 Auto increment Error: Creates Duplicate Key (40)

Quote:


>> Found this error Auto increment Error: Creates Duplicate Key (40),
> You need to understand how CW handles autonumber.
> Briefly:
> 1. Uses set/previous on the file to get the next sequential number.
> 2. Adds a record. Only the autonumber field has a value in it.
> When you have file with two unique keys, the above will work fine for
> the first user. However, the second user will have problems on step 2
> above. There will be two records in the file with a blank record for
> the second unique key, which causes the duplicate key error.

-> Use 'Exclude Nulls' option for the second key!


Sat, 02 Oct 1999 03:00:00 GMT  
 Auto increment Error: Creates Duplicate Key (40)

Hi All!

some time ago i was dealing with this problem and i get some help
from the list  (i'm not trying to get the credit for this)

you have to prime the autonumber fields on the insert because cw
dosn't reserve the keys beeing added (just in case the user cancelled
the operation)until the add is done so prime the autonumber fields
with the clock function or with any other value  that can't be
duplicate then in the ok control return the value to the natural
file's sequence   HTH

Martin Rodriguez
GCJ Systems
Juarez, Chih.Mexico



Sat, 02 Oct 1999 03:00:00 GMT  
 Auto increment Error: Creates Duplicate Key (40)



Quote:
> HI EVERYBODY,

> Found this error Auto increment Error: Creates Duplicate Key (40), and I
> can't find any solution to this one. The scenario is this:

> ----
> NT 3.51Server
> Win 95 workstation running

> ---

> When one user will open a form to add new record ( and while still in the
> Form), then another will execute another record add in another
workstation.

> The file in question has two auto increment key.

> One has a single field and the other has two field key. I did primed the
> keys.

> Please give me some ideas..

I think the problem is you have 2 auto increment keys. The templates build
code to get the LAST record by the auto increment key, then add one.  I'm
not sure what would happen if there were 2 keys.  Take a close look at the
generated code.

--
Tom Ruby
--------------------------------------------------------------------

http://www.netins.net/showcase/tomruby/
(It must be time to figure out a new tag line)



Sat, 02 Oct 1999 03:00:00 GMT  
 Auto increment Error: Creates Duplicate Key (40)



Quote:
> Hi All!
> you have to prime the autonumber fields on the insert because cw
> dosn't reserve the keys beeing added (just in case the user cancelled
> the operation)until the add is done so prime the autonumber fields
> with the clock function or with any other value  that can't be
> duplicate then in the ok control return the value to the natural
> file's sequence   HTH

Actually, Clarion DOES save the record on insert, reserving the key so it
will know what to fill in on any child records. If the insert is cancelled,
it deletes the record (but forgets to delete any child records ).

--
Tom Ruby
--------------------------------------------------------------------

http://www.netins.net/showcase/tomruby/
(It must be time to figure out a new tag line)



Sat, 02 Oct 1999 03:00:00 GMT  
 Auto increment Error: Creates Duplicate Key (40)

Hi Malcolm,



Quote:
>> Found this error Auto increment Error: Creates Duplicate Key (40),
...
>> The file in question has two auto increment key.
>When you have file with two unique keys, the above will work fine for
>the first user. However, the second user will have problems on step 2
>above. There will be two records in the file with a blank record for
>the second unique key, which causes the duplicate key error.

From my point of view, a file with two autonumber fields is a file
that needs to be normalized.  Of course I don't know the full story
here, and there may indeed me needs for more than one autonumbered
fields in a program, but I would do a lot to avoid that situation to
come up in the first place.

The ideal way is to have one ID field with autonumber in every file
and use it to connect files.  It would (should) always be unique and
there should be no code touching this field ANYWHERE in the whole
program and users should NEVER have access to it.  Trying to relate
files on some "unique" keys, that one day may prove not to be unique
at all isn't all that good.  Adding this to an already defined
dictionary is easy, but it's one hell of a job to go through an
application with 400 procedures and make sure that hand coded updates
and inserts handle a new field correctly.  Believe me, I'm doing it
right now and mind you, not on MY databases<g>

I'd rather give up some speed in the application while it builts an
extra key etc. and a few bytes per record and have a safe database,
than not doing it and head for trouble.

Best regards,

Arnor Baldvinsson
Allerup Edb
Tel: +45 4675 7122
Fax: +45 4675 7144
Denmark


http://www.icetips.com

Opinions are mine, and mine alone!



Sun, 03 Oct 1999 03:00:00 GMT  
 Auto increment Error: Creates Duplicate Key (40)


Quote:
> Hi Malcolm,

> >When you have file with two unique keys, the above will work fine
for
> >the first user. However, the second user will have problems on step
2
> >above. There will be two records in the file with a blank record for
> >the second unique key, which causes the duplicate key error.

> From my point of view, a file with two autonumber fields is a file
> that needs to be normalized.  Of course I don't know the full story
> here, and there may indeed me needs for more than one autonumbered
> fields in a program, but I would do a lot to avoid that situation to
> come up in the first place.

> (snip)

Hi Arnor

I do follow your recommendations 100%. But I don't use the CW
autonumber.

I have many files with two unique keys (but only one is autonumber).
The autonumber field is used for relations. The other unique key looks
like a primary key to the user, but it is not connected to anything.
The user can change it as much as he likes.

Some time ago there was a thread on autonumbering. I found one of the
messages, but cannot find the ones with the methods of handling the
different situations. Below is a message from Alexey.

Malcolm

----


Quote:
>Hello!

>Date:  15 Jul 1996 03:00:34 -0300
>> (snip)
>Look the algorithm of the ADDing new record if there is auto-increment
field.
>The form inserts a pseudo-record with blank fields and

auto-incrementing field

- Show quoted text -

Quote:
>setting up to the next value and HOLDs it. When user have pressed Ok
button
>form performs validation checks of REQuired fields and PUTs record to
a file.
>If user have pressed Cancel button, a pseudo-record is deleting.
>Lacks are follows:
>1) A pseudo-record is inserting out of transaction frame. So, changes
in a file
>   could not be rolled back.
>2) If there is unique key different from the auto-incrementing one, it
causes
>   problems with adding records to a file by application instances
running
>   concurrently in the network.
>3) In many cases holes in the auto-incrementing field's sequence,
changes of
>   field's value and changes of the records' relative order are
prohibited.
>   The standard algorithm can not be used in these situations.
>4) Because link field's value is available, inserted child records
become
>   accessible for concurrently running applications or threads (say,
in browse
>   based on the physical records' order) before parent record would be
really
>   saved. This violation of transaction (parent+child) entity causes
once more
>   problem: child record could be changed before parent record would
be saved.
>   It is a result of the standard Form template handles passive
concurrency
>   check limitation for parent record only and for UPDATE/DELETE only.
>The strong implementation of the Form should to be based on the rule
that ALL
>changes in both parent and child files are performing as WHOLE and
UNDIVIDED
>transaction. Changes in the child files must collecting in the memory
as it
>implements in the CDD/CFD's Child template. An auto-incrementing field
value
>must calculating only when user have pressed Ok after LOGOUT would be
executed
>and only after that link fields of the child records would be set.
>=======================================================================

========


Mon, 04 Oct 1999 03:00:00 GMT  
 Auto increment Error: Creates Duplicate Key (40)

Quote:

> From my point of view, a file with two autonumber fields is a file
> that needs to be normalized.  Of course I don't know the full story
> here, and there may indeed me needs for more than one autonumbered
> fields in a program, but I would do a lot to avoid that situation to
> come up in the first place.

Hi, Arnor,

I agree.  I also prefer to leave the assignment of the autonumber till
just before the ADD() on the primary file, inside of a Transaction.
That way, we don't wind up with unused values in the key if a cancel is
done after the value has been assigned and someone else has created a
new record.

  LOGOUT

  Determine next sequential autonumber
  ADD primary record
  If Error ROLLBACK

  GET primary record ADDed (to get sequential autonumber just assigned)

  Prime child records
  ADD child records
  IF error ROLLBACK

  COMMIT

I use Btrieve quite a bit.  Btrieve has an AutoIncrement key type.
Btrieve manages that key type.  I do not check the AutoNumber flag in
Clarion on my Btrieve files.  If the Clarion program assigns a value of
zero to the AutoIncrement key before ADDing, Btrieve will automatically
assign the value of that key.  The GET on the primary record will obtain
the value that Btrieve just assigned. I can then use the value just
obtained to prime the child records.  It works great in interactive or
batch processing mode.

Best regards,
Tim Farrar



Thu, 14 Oct 1999 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. Error 40 Create Duplicate Key

2. Error #40 - Duplicate Key-entry

3. "creates duplicate key" error

4. Auto Increment Key Problem

5. CPD2110/LPM25 Auto Incremented key - John Farmer

6. CPD2110/LPM2.5 Auto Increment Key

7. Clarion 4 w/ ABC and Auto-Increment Keys

8. How to Auto Increment a Key without ABC

9. Auto-Incrementing Key

10. Auto-increment keys

11. Auto Increment Error

12. Auto Increment Error

 

 
Powered by phpBB® Forum Software