Re: Checking for duplicate items, Forms4.5

[Quoted] You should be able to check the data in the Database as follows

select MYTABLE.MYTEXT from MYTABLE where MYTABLE.MYTEXT = :MYBLOCK.MYTEXT and :MYTABLE.MYKEY <> :MYBLOCK.MYKEY

This will return any row which exactly matches the current text from the DB, EXCEPT for the current row.

Simon Hedges
Gloucester
UK

Andy Hardy wrote in message ...
>I have a simple table:
>
>key_id, unique_text, description
>
>where the key_id is the primary key and unique_text is unique and not
>null. My block displays 10 rows.
>
>I'm unsure how to handle the validation required so that the unique_text
>is always unique!
>
>For example, if I let Forms build it's own block it will put a when-
>validate on unique_text to prevent the use of nulls. However, it will
>not check for uniqueness and relies on Oracle to reject the record
>during the insert/update.
>
>I would like to be able to do the validation in both update and insert
>modes - insert is simple to program as I can perform a query within the
>when-validate. Update, however is causing me problems - it's possible
>for the user to change the text and then change it back. The when-
>validate fires and my simple query now sees that this value also exists
>in the database, because it is now 'looking' at itself.
>
>I've contemplated using before and after variables to store the current
>and fetched values, but in some of my larger tables this may be time-
>consuming.
>
>I've thought of using the rowid, but I don't think that Oracle can
>insert/update a block which contains these?
>
>Any thoughts?
>
>Andy
>--
>Andy Hardy. PGP key available on request
>===============================================================
Received on Thu May 06 1999 - 23:15:54 CEST