• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

Strategy on Inserting/Editing records (with validation)

Hi Experts

I could use some feedback on how you personally do things when developing data-aware applications...

Back when I was a beginner it was great to just insert/edit data right into a DBGrid and let the fields validate the data for you... but then I moved on to having a separate form for Adding/Editing records because of validation and a few other reasons...

In some applications it seems necessary to do all inserting/editing using SQL statements which ensures as little communication with the server as possible, but this really slows down development, so in normal applications I'd rather skip the SQL INSERTs and UPDATEs unless necessary, and instead use data-aware controls. If I come across a field where I need special formatting or validation, then I'll use a non-data-aware control, and then insert the validated data into the field usually before I call Post.

Does that make sense?

So let's say for example I'm inserting a new Staff record.
I call FrmStaffAddOrEdit.showmodal;
On this form are a bunch of DBEdits etc and buttons: btnOkay and a btnCancel.
All the DBEdits use DataSource1.
The Dataset for DataSource1 will change depending on 1) whether I am inserting or editing a record and 2) who the Sender form is (Sometimes two or more forms will need to edit a record, and you just set the Dataset accordingly).
If I am inserting a record, then I will just use a query with SQL SELECT TOP 1 * FROM TblStaff,
which fetches the data structure and just 1 record, and then I set my Dataset to my Query.
If I am editing a record, then I simply connect my Datasource to the Dataset of the record I am editing.

Is this a good way or not? and is there a way you can improve upon this for me?
0
rfwoolf
Asked:
rfwoolf
  • 4
  • 2
  • 2
  • +2
4 Solutions
 
senadCommented:
I check the records on dbedit exit.Something like this :

procedure TForm6.cxTextEdit2Exit(Sender: TObject);
begin
with DataModule3.ADOQuery6  do begin
DataModule3.ADOQuery6.Close;
DataModule3.ADOQuery6.SQL.Clear ;
DataModule3.ADOQuery6.SQL.Text := 'SELECT MYRECORD FROM MYTABLE  WHERE MYRECORD = ' + QuotedStr(cxTextEdit2.Text);
DataModule3.ADOQuery6.Open;
if EOF then begin
Close;
end else begin
ShowMessage('Attention '+cxTextEdit2.Text+' already exists !');
exit;
end;
Close;
end;
end;

0
 
senadCommented:
well,that is hoping I understood you right ....  :-)
0
 
FactorBCommented:
I read great article about making data aware controls in Marco Cantu's book - Mastering Delphi 7, he explained well all classes: TDataLink, TDataSet... Database components text was around 50 pages. Well, enough commercials. :) I personally like to update query directly:

try
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('SELECT Top 1 * FROM Some_Table');
ADOQuery1.Open;
ADOQuery1.Insert;
ADOQuery1.FieldByName('First').AsString:='Mike';
ADOQuery1.FieldByName('Last').AsString:='Green';
ADOQuery1.Post;
except
ShowMessage('There was an error inserting record!');
end;

On other occasions, usually if I have less time, I use the way you described. Both ways are fine.

Regards,
B.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AStaCommented:
Hi.

Good question. :)))

I create and use small and simple forms library for yourself. Main idea - visual forms inheritance.

TfmDockable = class(TForm); // for ManualDock(in to main form)

TfmIBXDBGridEhLive = class(TfmDockable); // for Ins, Edit, Del in grid

TfmDlg = class (TForm);

TfmIBXDlg = class(TfmDlg);

TfmIBXDBGridEh = form(TfmDockable) // for Ins, Edit, Delete in modal form inherited from TfmIBXDlg

Usage:
  1. Create form TfmAccounts = class(TfmIBXDBGridEhLive), as sample
  2. Write SQL in TfmAccounts.IBXDataSet (IBXDataSet placed on TfmIBXDBGridEhLive)
  3. Create fields in TfmAccounts.IBXDataSet
  4. Write code for field check (usaly OnValidate) and specific field editing (as select CurrencyId and CurrencyName from TfmCurrencies)
  5. Add call in menu on Main form

  All work as sorting records, copy all records to clipboard and other - in TfmIBXDBGridEhLive

If it is interesting - i try write more and with sources.


App-samle.JPG
0
 
senadCommented:
not on dbedit but edit's ....a typo....
However i prefer to tell the user what the error was rather him
cursing me :-)
0
 
AStaCommented:
>not on dbedit but edit's ....a typo....
>However i prefer to tell the user what the error was rather him
>cursing me :-)

I don't understand?

Check user data:

1. Edit.OnExit , OnKeyDown, OnKeyPress
2. Field.OnValidate
3. Field.OnChange
3. xxxDataSet.OnBeforePost
4. Process database error and show message.
  ModalResult := mrNone;
  try
    xxxDataSet.Post.
    ModalResult := mrOk;
  except
    on E: Exception do
     ShowMessage(Format('We have problem: Class: %s, Message: %s. Fix it immediately!', [E.ClassName, E.Message])); :)))
  end;

How to show error?
ShowMessage, Application.MessageBox, add additional panel in dialog with error message
and disable/enable btnOkay

>If I am inserting a record, then I will just use a query with SQL SELECT TOP 1 * FROM TblStaff,
>which fetches the data structure and just 1 record, and then I set my Dataset to my Query.
>If I am editing a record, then I simply connect my Datasource to the Dataset of the record I am editing.

Hard way.

I have:
  TfmIBXDlg = class(TForm)
    ...
    function Insert: Boolean; virtual;
    function Edit(Id: Integer): Boolean; virtual;
    function Delete (Id: Integer): Boolean; virtual;
    ...
  end;

  TfmIBXGridEh = class(TfmDockable)
  ...
  procedure Refresh(Id: Integer); virtual;
  procedure Insert: virtual;
  ...
  end;

I create:
  TfmxxxDlg = class(TfmIBXDlg)

  // SELECT T.Id, T.Name, T.Comment_ FROM Table1 T WHERE T.Id = :P_Id
  // INSERT INTO Table1 ...
  // UPDATE Table1 ...
  // DELETE FROM Table1 ...

  // Edits, DBEdits etc...

  // Error check
  end;

  TfmxxxGrd = class(TfmIBXGridEh)
  ...
    procedure Insert; override;
  ...
  end;

procedure TfmxxxGrd.Insert;
var
  fmxxxDlg: TfmxxxDlg;
begin
  fmxxxDlg := TfmxxxDlg.Create(Self);
  try
  // all error processing in function fmxxxDlg.Insert: Boolean;
  if fmxxxDlg.Insert then
    Refresh(fmxxxDlg.IBDataSetID.AsInteger);
  finally
    fmxxxDlg.Free;
  end;
end;

Edit and delete - simular.

0
 
AshokCommented:
senad,

You got the close in wrong place otherwise it is OK.

procedure TForm6.cxTextEdit2Exit(Sender: TObject);
begin
  with DataModule3.ADOQuery6  do
  begin
    DataModule3.ADOQuery6.Close;
    DataModule3.ADOQuery6.SQL.Clear ;
    DataModule3.ADOQuery6.SQL.Text := 'SELECT MYRECORD FROM MYTABLE  WHERE MYRECORD = ' + QuotedStr(cxTextEdit2.Text);
    DataModule3.ADOQuery6.Open;
    if not EOF then
    begin
      Close;
      ShowMessage('Attention '+cxTextEdit2.Text+' already exists !');
      exit;
    end;
    Close;
  end;
end;

HTH
Ashok
0
 
AshokCommented:
rfwoolf,

Your approach is good.  You just need to make sure you add some validation when Insert or Edit could fail.
If Edit allows to edit a Primary key value by user entry, it could generate duplicate.

Ashok
0
 
senadCommented:
ashok111 - thank you ....this was C&P from somewhere.... :-)
0
 
rfwoolfAuthor Commented:
Thanks guys for the answers.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now