We help IT Professionals succeed at work.

Check if record exists

Bosanac
Bosanac asked
on
I am entering new record directly into the ADOTable1 (in the grid)
using dbnavigator for inserting/posting/editing etc..
How can I have a message displayed if I enter a record that is allready in the database
as soon as I try move to move to the next field ?
Comment
Watch Question

President
Commented:
 First you have to figure out how you identify a record that matches.  Is it just that the first and last name fields match?  Social Security Number?  Once you know how to identify a macthing record you can try a couple of different ways.

   Because you are entering records directly to a table you can use a unique index.  This way you will get an exception if the user attempts to enter duplicate data and the record will not save.  This is the least work for you.

  Another option I have seen used is to use OnBeforePost combined with a background table scan (the easy way to do this is to open another TTable and scan it).  

  Another option would be to use a separate entry screen (with non DB aware components) to enter the data.  When the user tries to save it, simply compare first.

  I'm sure there are many other ways to accomplish this, let me know if you need more detail on any of these.
Commented:
In that field's OnExit Event add the following:

if MyADOTable.Locate('MyFieldName', TextToFindInMyFieldName, [loCaseInsensitive] then
     begin
       {Do what you need to do...}
     end;

Would look something like:

Options are
loCaseInsensitive
loPartialKey
or none, i.e.; []

John
procedure TForm1.MyADOTable(Sender: TObject);
begin
if MyADOTable.Locate('MyFieldName', TextToFindInMyFieldName, [loCaseInsensitive] then
 begin
 {Do what you need to do...}
 end;
end;

Open in new window

Author

Commented:
it seems i can only trap in on before post event.
how is that supposed to look like
Use within your datasource the event OnUpdateData

  if YourField = '' then
  begin
    MessageDLG('Field value needs some input here....', mtWarning, [mbOK], 0);
    with YourEditField do begin Show; SetFocus; end;
    Abort;
  end;
  etc....

Commented:
Use the OnBeforePost event in your ADOTable, IE.

procedure TForm1.MyADOTable1BeforePost(DataSet: TDataSet);
begin
if MyADOTable.Locate('MyFieldName', TextToFindInMyFieldName, [loCaseInsensitive] then
 begin
 {Do what you need to do...}
 end;

end;

Author

Commented:
whats 'TextToFindInMyFieldName' ?


Commented:
If you are trying to find "Smith" in your field name 'LastName' then "Smith" would be the TextToFindInMyFieldName, which in the example is LastName.

This way you will find a record if it exists whose last name is Smith.

Author

Commented:
I am entering data into the grid directly and I do not know what record to search but to check the one I am entering (so I do not enter a duplicate record by any chance).
Is there a way..or perhaps before post or something ?
Place your validation before post on the OnUpdateData event on your datasource.

if YourCheckHere then
  begin
    MessageDLG('This is not what you want...', mtWarning, [mbOK], 0);
    with YourEditField do begin Show; SetFocus; end;
    Abort;
  end;
  etc, continue your check's here...
Forced accept.

Computer101
EE Admin

Explore More ContentExplore courses, solutions, and other research materials related to this topic.