ADOQuery to save new and update record

Question

I created one TForm to create new user into Access Database.

I created DBedit1 which updates the Edit1 with change event. If enter totally new data into my TEdit files and press save with the insert into query it saves the data without any problem.

procedure TFNewUser.BtnSaveClick(Sender: TObject);
begin
    if (Edit1.Text = '') or (Edit2.Text='') or (Edit3.Text='') or (Edit4.Text='') then begin
        ShowMessage('Please enter the missing data!')
    end else if (Edit3.Text) <> (Edit4.Text) then begin
        ShowMessage('Password not match!')
    end else begin
        adoQuery1.Close();
        adoQuery1.SQL.Clear;
        adoQuery1.SQL.Add('INSERT INTO taccount (UserN,FName,Pword,ID)VALUES');
        adoQuery1.SQL.Add('(:UserN,:FName,:Pword,:ID)');
        adoQuery1.Parameters.ParamByName('UserN').Value:= Edit2.Text;
        adoQuery1.Parameters.ParamByName('FName').Value:= Edit1.Text;
        adoQuery1.Parameters.ParamByName('Pword').Value:= Edit3.Text;
        adoQuery1.Parameters.ParamByName('ID').Value:= Edit5.Text;
        Adoquery1.ExecSQL;
        ShowMessage('New user created successfully');
    end;
    ADOQuery1.close;
    ADOQuery1.SQL.text:='select * from taccount';
    ADOQuery1.Open;
end;

1. When a user clicks on BtnNew, enter a new record into TEdit fields when press saves it save new record - is that possible to assign an auto number to record without user input.

2. But if data is loaded into Tedit from existing record how to update by pressing BtnSave.

Answer 1

Kobik has given you a good answer to the question you actually asked. I thought I would post this to show you that you don't need TEdits and you can leave all the work to DB-aware controls like TDBEdit and TDBNavigator.

If you compile and run the project as written, you'll see that when you click the + button in the DBNavigator, the AdoQuery goes into Insert mode and the mouse cursor is placed in the UserN DBEdit so that you can begin filling in the new user record.

There is a btnSave which is the way you are supposed to save the record. I've provided this button solely because you have one. Notice that if you click the Save button on the DBNavigator, you get a message saying that the Save button hasn't been clicked, and the insert operation is cancelled and the new user data is discarded.

Most of the code in the project is only necessary because it is mimicking your use of a separate Save button. To illustrate this, change the project as follows

• set btnSave's Visible property to false

• add an Exit as the first line of CheckSaveButtonClicked, before the if ...

& compile/run the project again.

You'll find that the Save button on the DBNavigator now works and the project behaves, from the point of view of the behaviour of the DBNavigator, exactly as the user would intuitively expect it to.

So in fact, if you use the DBNavigator as it is intended to be used, none of the code in the project is necessary AT ALL, except DBEdit1.SetFocus which places the mouse cursor in this control at the start of an Insert operation, and AdoQuery1.open of course. That's why I said in a comment that you can leave the work to DB-aware controls if you use them properly.

Update

you suggest me how to restrict user enter duplicates username with "custom >message" i think should need to add in before post

Personally, I think the best way to avoid duplicates and do other validation before the new record is inserted in the database, is to initially add it to a separate local table (e.g. a TClientDataSet or a FireDAC TFDMemTable). Then, once the user has inputted enough information to check for duplicates and do whatever other validation you want, you can alert the user to any problems and get them to correct them. Once the new record is "clean", you copy it from the local table to your main table.

Doing it that way allows you to use db-aware controls for the user to input the new-record data. Also, personally, for real-world applications I have always required the user to use a special a "new record wizard" as a separate form containing the db-aware controls for the local input table; usually this is a multi-tabbed form, except in very simple cases. This is more effort than some other methods, like using the db-aware controls provided for editing existing records , but works much better and allows you to trap certain kinds of error that would be difficult or impossible to do if the user inputs the new record directly to the main table.

Code

type
  TForm1 = class(TForm)
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    DataSource1: TDataSource;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    ADOQuery1ID: TAutoIncField;
    ADOQuery1UserN: TStringField;
    ADOQuery1FName: TStringField;
    ADOQuery1Pwd: TStringField;
    DBEdit1: TDBEdit;
    DBEdit2: TDBEdit;
    DBEdit3: TDBEdit;
    btnSave: TButton;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    procedure FormCreate(Sender: TObject);
    procedure ADOQuery1BeforeEdit(DataSet: TDataSet);
    procedure ADOQuery1BeforeInsert(DataSet: TDataSet);
    procedure ADOQuery1BeforePost(DataSet: TDataSet);
    procedure btnSaveClick(Sender: TObject);
  private
    procedure CheckSaveButtonClicked;
    function GetSaveEnabled: Boolean;
    procedure SetSaveEnabled(const Value: Boolean);
  protected
  public
    SaveClicked : Boolean;
    property SaveEnabled : Boolean read GetSaveEnabled write SetSaveEnabled;
  end;
[...]
procedure TForm1.FormCreate(Sender: TObject);
begin
  SaveEnabled := False;
  AdoQuery1.Open;
end;

procedure TForm1.ADOQuery1BeforeEdit(DataSet: TDataSet);
begin
  SaveEnabled := True;
end;

procedure TForm1.ADOQuery1BeforeInsert(DataSet: TDataSet);
begin
  SaveEnabled := True;
  DBEdit1.SetFocus;
end;

procedure TForm1.CheckSaveButtonClicked;
begin
  if not SaveClicked then begin
    AdoQuery1.Cancel;
    ShowMessage('Save button not clicked');
    Abort;   //  In case the user clicked the DBNavigator Save button
  end;
end;

procedure TForm1.ADOQuery1BeforePost(DataSet: TDataSet);
begin
  CheckSaveButtonClicked;
end;

procedure TForm1.btnSaveClick(Sender: TObject);
begin
  SaveClicked := True;
  AdoQuery1.Post;
  SaveEnabled := False;
end;

function TForm1.GetSaveEnabled: Boolean;
begin
  Result := btnSave.Enabled;
end;

procedure TForm1.SetSaveEnabled(const Value: Boolean);
begin
  btnSave.Enabled := Value;
  SaveClicked := False;
end;

DFM Contents

object Form1: TForm1
  Left = 259
  Top = 103
  AutoScroll = False
  Caption = 'MADefaultForm'
  ClientHeight = 314
  ClientWidth = 444
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  Position = poScreenCenter
  Scaled = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 48
    Top = 201
    Width = 30
    Height = 13
    Caption = 'UserN'
  end
  object Label2: TLabel
    Left = 48
    Top = 225
    Width = 34
    Height = 13
    Caption = 'FName'
  end
  object Label3: TLabel
    Left = 48
    Top = 249
    Width = 21
    Height = 13
    Caption = 'Pwd'
  end
  object DBGrid1: TDBGrid
    Left = 40
    Top = 8
    Width = 320
    Height = 153
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object DBNavigator1: TDBNavigator
    Left = 48
    Top = 168
    Width = 240
    Height = 25
    DataSource = DataSource1
    TabOrder = 1
  end
  object DBEdit1: TDBEdit
    Left = 85
    Top = 198
    Width = 121
    Height = 21
    DataField = 'UserN'
    DataSource = DataSource1
    TabOrder = 2
  end
  object DBEdit2: TDBEdit
    Left = 85
    Top = 222
    Width = 121
    Height = 21
    DataField = 'FName'
    DataSource = DataSource1
    TabOrder = 3
  end
  object DBEdit3: TDBEdit
    Left = 85
    Top = 246
    Width = 121
    Height = 21
    DataField = 'Pwd'
    DataSource = DataSource1
    TabOrder = 4
  end
  object btnSave: TButton
    Left = 288
    Top = 240
    Width = 75
    Height = 25
    Caption = 'Save'
    TabOrder = 5
    OnClick = btnSaveClick
  end
  object DataSource1: TDataSource
    DataSet = ADOQuery1
    Left = 112
    Top = 8
  end
  object ADOConnection1: TADOConnection
    Connected = True
    ConnectionString =
      'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security In' +
      'fo=False;Initial Catalog=MATest;Data Source=MAT410\ss2014'
    LoginPrompt = False
    Provider = 'SQLOLEDB.1'
    Left = 32
    Top = 8
  end
  object ADOQuery1: TADOQuery
    Connection = ADOConnection1
    BeforeInsert = ADOQuery1BeforeInsert
    BeforeEdit = ADOQuery1BeforeEdit
    BeforePost = ADOQuery1BeforePost
    Parameters = <>
    SQL.Strings = (
      'select * from taccount')
    Left = 72
    Top = 8
    object ADOQuery1ID: TAutoIncField
      FieldName = 'ID'
      ReadOnly = True
    end
    object ADOQuery1UserN: TStringField
      FieldName = 'UserN'
      Size = 50
    end
    object ADOQuery1FName: TStringField
      FieldName = 'FName'
      Size = 50
    end
    object ADOQuery1Pwd: TStringField
      FieldName = 'Pwd'
      Size = 50
    end
  end
end