Avoiding duplicate records in unique field
lazarus / 16-Databases

----------------------------------------------------------------------
Ronan · 2014-03-30 16:32

DEar All,

What am I supposed to do, in order to avoid the users from entering
the duplicate data in unique field in database. I'm using the zeos
database components. User has access directly through the DBgrid to
manipulate the data in dataset and in that case what action should I
take?

the glitch here is that , I'm unaware of when user changes the data if
I place it under ZQuery Before/AfterEdit event handler , otherwise I
can use Locate function to test whether the record is already exist or
not.

Regards,

----------------------------------------------------------------------
bambamns · 2014-03-30 20:51

Is it necessary to edit data thru DBGrid ?

There a few disadvantages using DBGrid to Edit data when you wont
total control over entered data.

Try to use separate edit fields and additional buttons for edit,
delete and save data - this will give you more control over entered
data and when you write some triggers and constraints in database you
can avoid duplications or similar kind of wrong data.

----------------------------------------------------------------------
Ronan · 2014-03-31 08:12

Thanks in advance,

QuoteIs it necessary to edit data thru DBGrid ?

There a few disadvantages using DBGrid to Edit data when you wont
total control over entered data.

Try to use separate edit fields and additional buttons for edit,
delete and save data - this will give you more control over entered
data and when you write some triggers and constraints in database you
can avoid duplications or similar kind of wrong data. ?EDiting data
through the DBGrid is not compulsory of course,  but I want it to be
that way because it is very convenient for the end user to immediately
edit the data. I think that workaround can be found to store the
entered data in one way or the other, even though at the cost of that
will be troublesome and more error-prone. The cleanest and latest
option will be the one that you offered processing validation through
the controls.

One more thing, even if try to use the Locate function for probing if
there was any duplicate value, it raises exception due to the
duplicate value I entered into unique field. BUt actually it is not
been posted it just only modified at the DBGrid cell.

My code as below:

Code: [Select]procedure TAxisForm.DBGrid1ColEnter(Sender: TObject);
begin
  X_pos:= SteelDB.X_Query.FieldByName('Axis_Name').AsString;
end;

procedure TAxisForm.DBGrid1EditingDone(Sender: TObject);
var
 exist :String;
begin
  exist :=SteelDB.X_Query.FieldByName('Axis_Name').AsString;
  if SteelDB.X_Query.Locate('Axis_Name',exist, [loCaseInsensitive])
then
  begin
    SteelDB.X_Query.Edit;
    SteelDB.X_Query.FieldByName('Axis_Name').AsString := X_pos;
    SteelDB.X_Query.Post;
  end;
end;

----------------------------------------------------------------------
mangakissa · 2014-03-31 09:07

Quote...but I want it to be that way because it is very convenient for
the end user to immediately edit the data...That's because a lot of
people uses spreadsheets

Isn't OnPost Event or ApplyUpdates event not doing that? If there's a
error, the post should be call abort.

----------------------------------------------------------------------
kpeters58 · 2014-03-31 15:58

I agree with Magakissa - I would let things run their course and
respond to the exception that will inevitably be raised; once the
duplicate key exception is raised, detect it, show a friendly message
to the user and let them correct their entry.

----------------------------------------------------------------------
Ronan · 2014-04-01 09:39

Thanks in advance,
QuoteI agree with Magakissa - I would let things run their course and
respond to the exception that will inevitably be raised; once the
duplicate key exception is raised, detect it, show a friendly message
to the user and let them correct their entry.
Probably I'm misunderstanding or mis-interpreting something, my goal
is same as you described just intercept possible error that may occur
at the database due to duplicate key and preventing it by prompting
from user to change it to another value.

When actually that duplicate key exception will be raised and
reported, in OnPost event? And do we really have an oportunity to
intercept it w/o crashing the whole application? Can you give pseudo
code example for that ?

----------------------------------------------------------------------
ttomas · 2014-04-01 10:16

Field Validation belong to DataSet not to your visual control. You can
connect DBGrid and DBEdit controls to the same Query!

1. If you create your fields in designtime then you can use
Field.OnChange Event
X_QueryAxis_NameOnChange and check with second Query if that value
exists
2. You can also use X_Query.BeforePost event if you need to check
several values

In any case in multiuser environment there is possibility for two
users to Post same Key in same time and if you have check constrain in
your database then you must catch OnPostError event in X_Query and
user friendly Show Message to user.