Preventing Key Violation for duplicate keys 
Author Message
 Preventing Key Violation for duplicate keys

Using Delphi 2.0 C/S with W95
I'm using a form to add a record to a TTable using the Insert method. The
table is indexed on an Alpha 8 field called ANType and the corresponding
TDBEdit is called deANType
I'd like to check that the field contains a unique value before moving to
the next field, and have tried the following in the OnExit event ...
if dmData.taANType.FindKey([deATType.Text]) then begin
   showmessage('Analysis Type already exists');
   deATType.SetFocus;
end;

BUT this gives me a key violation (the very error I'm trying to avoid). I
think that FindKey is changing the table state, and moving me away from
the new record.

Any advice on how to code this would be very welcome.
Replies by e-mail appreciated. TIA   :-)



Wed, 18 Jun 1902 08:00:00 GMT
 Preventing Key Violation for duplicate keys

Quote:

> Using Delphi 2.0 C/S with W95
> I'm using a form to add a record to a TTable using the Insert method. The
> table is indexed on an Alpha 8 field called ANType and the corresponding
> TDBEdit is called deANType
> I'd like to check that the field contains a unique value before moving to
> the next field, and have tried the following in the OnExit event ...
> if dmData.taANType.FindKey([deATType.Text]) then begin
>    showmessage('Analysis Type already exists');
>    deATType.SetFocus;
> end;
> BUT this gives me a key violation (the very error I'm trying to avoid). I
> think that FindKey is changing the table state, and moving me away from
> the new record.

The Findkey - when successful - probably moves off your current record,
causing it to be posted, only to be rejected because of the key
violation.
Maybe you could use a separate query (TQuery component).  If no records
were found, then you have a unique value.

Aage J.
10



Wed, 18 Jun 1902 08:00:00 GMT
 Preventing Key Violation for duplicate keys

Quote:

>Using Delphi 2.0 C/S with W95
>I'm using a form to add a record to a TTable using the Insert method. The
>table is indexed on an Alpha 8 field called ANType and the corresponding
>TDBEdit is called deANType
>I'd like to check that the field contains a unique value before moving to
>the next field, and have tried the following in the OnExit event ...
>if dmData.taANType.FindKey([deATType.Text]) then begin
>   showmessage('Analysis Type already exists');
>   deATType.SetFocus;
>end;

>BUT this gives me a key violation (the very error I'm trying to avoid). I
>think that FindKey is changing the table state, and moving me away from
>the new record.

>Any advice on how to code this would be very welcome.
>Replies by e-mail appreciated. TIA   :-)

You could try using lookup(). According to the manual it does *not*
move the table cursor to the matching row, only returns values from
it. Mind you the online help says it does move the cursor.

Ian Turner, Aurora Computer Solutions, Lancashire, UK



Wed, 18 Jun 1902 08:00:00 GMT
 Preventing Key Violation for duplicate keys

I have had this problem too.

My solution was to use an TEdit box rather than a TDBEdit.
Then in the various events like OnChange of the TEdit search for the record
in question
and if found say give the user a messageBox saying this is a duplicate.

--
Eamonn Wallace

Quote:
> I'm using a form to add a record to a TTable using the Insert method. The
> table is indexed on an Alpha 8 field called ANType and the corresponding
> TDBEdit is called deANType
> I'd like to check that the field contains a unique value before moving to
> the next field, and have tried the following in the OnExit event ...
> if dmData.taANType.FindKey([deATType.Text]) then begin
>    showmessage('Analysis Type already exists');
>    deATType.SetFocus;
> end;



Wed, 18 Jun 1902 08:00:00 GMT
 Preventing Key Violation for duplicate keys

Alternatively, you could try trapping the exception on the FindKey and if
the exception is a key violation, display your own message.
--
Mark Bratcher


Quote:

> > Using Delphi 2.0 C/S with W95
> > I'm using a form to add a record to a TTable using the Insert method.
The
> > table is indexed on an Alpha 8 field called ANType and the
corresponding
> > TDBEdit is called deANType
> > I'd like to check that the field contains a unique value before moving
to
> > the next field, and have tried the following in the OnExit event ...
> > if dmData.taANType.FindKey([deATType.Text]) then begin
> >    showmessage('Analysis Type already exists');
> >    deATType.SetFocus;
> > end;
> > BUT this gives me a key violation (the very error I'm trying to avoid).
I
> > think that FindKey is changing the table state, and moving me away from
> > the new record.

> The Findkey - when successful - probably moves off your current record,
> causing it to be posted, only to be rejected because of the key
> violation.
> Maybe you could use a separate query (TQuery component).  If no records
> were found, then you have a unique value.

> Aage J.
> 10



Wed, 18 Jun 1902 08:00:00 GMT
 Preventing Key Violation for duplicate keys

Alternatively, you could try trapping the exception on the FindKey and if
the exception is a key violation, display your own message.
--
Mark Bratcher


Quote:

> > Using Delphi 2.0 C/S with W95
> > I'm using a form to add a record to a TTable using the Insert method.
The
> > table is indexed on an Alpha 8 field called ANType and the
corresponding
> > TDBEdit is called deANType
> > I'd like to check that the field contains a unique value before moving
to
> > the next field, and have tried the following in the OnExit event ...
> > if dmData.taANType.FindKey([deATType.Text]) then begin
> >    showmessage('Analysis Type already exists');
> >    deATType.SetFocus;
> > end;
> > BUT this gives me a key violation (the very error I'm trying to avoid).
I
> > think that FindKey is changing the table state, and moving me away from
> > the new record.

> The Findkey - when successful - probably moves off your current record,
> causing it to be posted, only to be rejected because of the key
> violation.
> Maybe you could use a separate query (TQuery component).  If no records
> were found, then you have a unique value.

> Aage J.
> 10



Wed, 18 Jun 1902 08:00:00 GMT
 Preventing Key Violation for duplicate keys

Quote:

> Alternatively, you could try trapping the exception on the FindKey and if
> the exception is a key violation, display your own message.
> > BUT this gives me a key violation (the very error I'm trying to avoid).
> I
> > > think that FindKey is changing the table state, and moving me away from
> > > the new record.

> > The Findkey - when successful - probably moves off your current record,
> > causing it to be posted, only to be rejected because of the key
> > violation.
> > Maybe you could use a separate query (TQuery component).  If no records
> > were found, then you have a unique value.

> > Aage J.
> > 10

Exactly how would you do this ie. trap & determine what type of violation occured??

Thanks
Derek.



Wed, 18 Jun 1902 08:00:00 GMT
 Preventing Key Violation for duplicate keys

Quote:

> I have had this problem too.

> My solution was to use an TEdit box rather than a TDBEdit.
> Then in the various events like OnChange of the TEdit search for the
> record
> in question
> and if found say give the user a messageBox saying this is a
> duplicate.

> --
> Eamonn Wallace

Eamonn,

That's why I devellopped DBVldEdit a DBEdit With OnValidate event. So
Data can be validated before leaving the field thus preventing a post.
The component is also capable of  sidestepping, defaults on new
record,mask editing
it can be found at Torries Delphi pages
:http://carbohyd.siobc.ras.ru/torry/

Regards,Sjef van der Velde



Wed, 18 Jun 1902 08:00:00 GMT
 Preventing Key Violation for duplicate keys

I have read another solution earlier on this list - though I haven't
tried it myself.

Add another data source to the form pointing to the same database as
the one you are using. In the beforepost processing, try looking up the
value the user is inputing in the second data source. If it finds one,
then the key is a duplicate, so cancel the posing and warn the user.

Hope this helps



Wed, 18 Jun 1902 08:00:00 GMT
 Preventing Key Violation for duplicate keys

Quote:

> Using Delphi 2.0 C/S with W95
> I'm using a form to add a record to a TTable using the Insert method. The
> table is indexed on an Alpha 8 field called ANType and the corresponding
> TDBEdit is called deANType
> I'd like to check that the field contains a unique value before moving to
> the next field, and have tried the following in the OnExit event ...
> if dmData.taANType.FindKey([deATType.Text]) then begin
>    showmessage('Analysis Type already exists');
>    deATType.SetFocus;
> end;

> BUT this gives me a key violation (the very error I'm trying to avoid). I
> think that FindKey is changing the table state, and moving me away from
> the new record.

> Any advice on how to code this would be very welcome.
> Replies by e-mail appreciated. TIA   :-)

Findkey moves you off the record which causes a post. Try using another
Table component tied to the same table to do the lookup.

Bill Taylor



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. Violation of PRIMARY KEY constraint Cannot insert duplicate key in object

2. prevent duplicate primary key

3. D2.01/Sybase: App hangs on duplicate key violation

4. Cannot Insert duplicate row in Primary Key violation

5. Reordering of key fields leads to key violations (Pdox 5.0)

6. Reordering of key fields leads to key violations (Pdox 5.0)

7. INSERT INTO TABLE KEYED ONLY ON IDENTITY COLUMN RESULTS IN KEY VIOLATION (6.5)

8. Paradox Key Violation - changing a keyed field

9. Server: Msg 2627, Level 14, State 1 Violation of PRIMARY KEY constraint 'PK_FACULTY'. Cannot insert duplicate key

10. Violation on Primary Key, duplicate key in object 'sysrestorehistory'

11. Violation of PRIMARY KEY, duplicate key in object 'sysrestorehistory'


 
Powered by phpBB® Forum Software