Dataset not in edit or insert mode

Hello,

I have this procedure:

procedure TMainForm.utfSchedMgrPostAppt(Sender: TObject; Appt: TJvTFAppt);
var
  I : Integer;
begin
  With GetApptQuery do
    Begin
      FieldByName('ApptID').AsString := Appt.ID;
      Open;                   <==========================
      If RecordCount > 0 Then
        Edit
      Else
        Begin
          Insert;
          FieldByName('ApptID').AsString := Appt.ID;
        End;
      FieldByName('StartDate').AsDateTime := Appt.StartDate;
      FieldByName('StartTime').AsDateTime := Appt.StartTime;
      FieldByName('EndDate').AsDateTime := Appt.EndDate;
      FieldByName('EndTime').AsDateTime := Appt.EndTime;
      FieldByName('Description').AsString := Appt.Description;
      FieldByName('AlarmEnabled').AsBoolean := Appt.AlarmEnabled;
      FieldByName('AlarmAdvance').AsInteger := Appt.AlarmAdvance;
      Post;
      Close;
    End;
  With DeleteApptLinkQuery do
    Begin
      FieldByName('ApptID').AsString := Appt.ID;
      ExecSQL;
    End;
  With ApptSchedulesQuery do
    Begin
      FieldbyName('ApptID').AsString := Appt.ID;
      Open;
      For I := 0 to Appt.ScheduleCount - 1 do
        Begin
          Insert;
          FieldByName('ApptID').AsString := Appt.ID;
          FieldByName('SchedName').AsString := Appt.Schedules[I];
          Post;
        End;
      Close;
    End;
end;

Open in new window


I get an error: Dataset not in edit or insert mode
at the line where I put the arrow.

It's a procedure that calls for 3 ADOQuery's: GetApptQuery, DeleteApptLinkQuery  and
ApptSchedulesQuery. But at the line where I put the arrow I get the error-message.
Just in case I have put the whole unit in the code-section.

Who knows the answer and is willing to help me?

Greetings,

Peter Kiers
type
  TMainForm = class(TForm)
    ADOConnection: TADOConnection;
    ADOTable1: TADOTable;
    ADOTable2: TADOTable;
    ADOTable3: TADOTable;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    DataSource3: TDataSource;
    NeedApptsQuery: TADOQuery;
    ApptSchedulesQuery: TADOQuery;
    GetApptQuery: TADOQuery;
    DeleteApptLinkQuery: TADOQuery;
    DeleteApptQuery: TADOQuery;
    SchedulesQuery: TADOQuery;
    TFDays: TJvTFDays;
    utfSchedMgr: TJvTFScheduleManager;
    tbEdtAppt: TButton;
    tbNewAppt: TButton;
    Button3: TButton;
    GetApptQueryApptID: TWideStringField;
    procedure tbEdtApptClick(Sender: TObject);
    procedure tbNewApptClick(Sender: TObject);
    procedure TFDaysDblClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure utfSchedMgrRefreshAppt(Sender: TObject; Appt: TJvTFAppt);
    procedure utfSchedMgrPostAppt(Sender: TObject; Appt: TJvTFAppt);
    procedure utfSchedMgrLoadBatch(Sender: TObject; BatchName: string;
      BatchStartDate, BatchEndDate: TDate);
    procedure utfSchedMgrDeleteAppt(Sender: TObject; Appt: TJvTFAppt);
  private
    { Private declarations }
    DBPath: string;
    DBName: string;
  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation

uses ApptEdit;

{$R *.dfm}

procedure TMainForm.FormCreate(Sender: TObject);
var
  conn_str:string;
begin
  DBPath := IncludeTrailingPathDelimiter(ExtractFilePath(Application.Exename));
  DBName := 'DBRoot.mdb';
  conn_str :=
    Format('provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=%s%s;',
    [IncludeTrailingPathDelimiter(DBPath), DBName]);
   ADOConnection.Connected := False;
   ADOConnection.ConnectionString := conn_str;
   ADOConnection.Connected := True;
   ADOTable1.Open;
   ADOTable2.Open;
   ADOTable3.Open;
end;

procedure TMainForm.TFDaysDblClick(Sender: TObject);
begin
  With TFDays do
    If ValidSelection Then
      If Assigned(SelAppt) Then
        tbEdtApptClick(nil)
      Else
        tbNewApptClick(nil);
end;

procedure TMainForm.tbEdtApptClick(Sender: TObject);
begin
  If Assigned(TFDays.SelAppt) Then
    Begin
      frmApptEdit.Appt := TFDays.SelAppt;
      frmApptEdit.ShowModal;
    End
  Else
    MessageDlg('Please select an appointment to edit.', mtInformation,
               [mbOK], 0);
end;

procedure TMainForm.tbNewApptClick(Sender: TObject);
begin
  frmApptEdit.ShowModal;
end;

procedure TMainForm.utfSchedMgrDeleteAppt(Sender: TObject; Appt: TJvTFAppt);
begin
  With DeleteApptQuery do
    Begin
      FieldByName('ApptID').AsString := Appt.ID;
      ExecSQL;
    End;
  With DeleteApptLinkQuery do
    Begin
      FieldByName('ApptID').AsString := Appt.ID;
      ExecSQL;
    End;
end;

procedure TMainForm.utfSchedMgrLoadBatch(Sender: TObject; BatchName: string;
  BatchStartDate, BatchEndDate: TDate);
var
  Appt : TJvTFAppt;
  NewAppt : Boolean;
begin
  With NeedApptsQuery do
    Begin
      FieldByName('D1').AsDateTime := BatchStartDate;
      FieldByName('D2').AsDateTime := BatchEndDate;
      FieldByName('SchedName').AsString := BatchName;
      Open;
      First;
      While not EOF do
        Begin
          utfSchedMgr.RequestAppt(FieldByName('ApptID').AsString,
            Appt, NewAppt);
          If NewAppt Then
            Begin
              Appt.SetStartEnd(FieldByName('StartDate').AsDateTime,
                               FieldByName('StartTime').AsDateTime,
                               FieldByName('EndDate').AsDateTime,
                               FieldByName('EndTime').AsDateTime);
              Appt.Description := FieldByName('Description').AsString;
              Appt.AlarmEnabled := FieldByName('AlarmEnabled').AsBoolean;
              Appt.AlarmAdvance := FieldByName('AlarmAdvance').AsInteger;
              With ApptSchedulesQuery do
                Begin
                  FieldByName('ApptID').AsString := Appt.ID;
                  Open;
                  First;
                  While not EOF do
                    Begin
                      Appt.AddSchedule(FieldByName('SchedName').AsString);
                      Next;
                    End;
                  Close;
                End;
            End;
          Next;
        End;
      Close;
    End;
end;

procedure TMainForm.utfSchedMgrPostAppt(Sender: TObject; Appt: TJvTFAppt);
var
  I : Integer;
begin
  With GetApptQuery do
    Begin
      FieldByName('ApptID').AsString := Appt.ID;
      Open;
      If RecordCount > 0 Then
        Edit
      Else
        Begin
          Insert;
          FieldByName('ApptID').AsString := Appt.ID;
        End;
      FieldByName('StartDate').AsDateTime := Appt.StartDate;
      FieldByName('StartTime').AsDateTime := Appt.StartTime;
      FieldByName('EndDate').AsDateTime := Appt.EndDate;
      FieldByName('EndTime').AsDateTime := Appt.EndTime;
      FieldByName('Description').AsString := Appt.Description;
      FieldByName('AlarmEnabled').AsBoolean := Appt.AlarmEnabled;
      FieldByName('AlarmAdvance').AsInteger := Appt.AlarmAdvance;
      Post;
      Close;
    End;
  With DeleteApptLinkQuery do
    Begin
      FieldByName('ApptID').AsString := Appt.ID;
      ExecSQL;
    End;
  With ApptSchedulesQuery do
    Begin
      FieldbyName('ApptID').AsString := Appt.ID;
      Open;
      For I := 0 to Appt.ScheduleCount - 1 do
        Begin
          Insert;
          FieldByName('ApptID').AsString := Appt.ID;
          FieldByName('SchedName').AsString := Appt.Schedules[I];
          Post;
        End;
      Close;
    End;
end;

procedure TMainForm.utfSchedMgrRefreshAppt(Sender: TObject; Appt: TJvTFAppt);
begin
  With GetApptQuery do
    Begin
      FieldByName('ApptID').AsString := Appt.ID;
      Open;
      If RecordCount = 1 Then
        Begin
          Appt.SetStartEnd(FieldByName('StartDate').AsDateTime,
                           FieldByName('StartTime').AsDateTime,
                           FieldByName('EndDate').AsDateTime,
                           FieldByName('EndTime').AsDateTime);
          Appt.Description := FieldByName('Description').AsString;
          Appt.AlarmEnabled := FieldByName('AlarmEnabled').AsBoolean;
          Appt.AlarmAdvance := FieldByName('AlarmAdvance').AsInteger;
        End;
      Close;
    End;
  Appt.ClearSchedules;
  With ApptSchedulesQuery do
    Begin
      FieldByName('ApptID').AsString := Appt.ID;
      Open;
      First;
      While not EOF do
        Begin
          Appt.AddSchedule(FieldByName('SchedName').AsString);
          Next;
        End;
      Close;
    End;
end;

end.

Open in new window

LVL 1
Peter KiersOperatorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aflarinCommented:
Hi Peter,

Maybe it's a typo? ParamByName instead of FieldByName?

procedure TMainForm.utfSchedMgrPostAppt(Sender: TObject; Appt: TJvTFAppt);
var
    I : Integer;
begin
   With GetApptQuery do
     Begin
        ParamByName('ApptID').AsString := Appt.ID;
        Open;                   <==========================
    ...


0
aflarinCommented:
The same:

With DeleteApptLinkQuery do
Begin
       ParamByName('ApptID').AsString := Appt.ID;
       ExecSQL;
    End;
  With ApptSchedulesQuery do
    Begin
       ParamByName('ApptID').AsString := Appt.ID;
       Open;



0
Peter KiersOperatorAuthor Commented:
The code came from a TQuery that I have changed to TADOQuery
The only thing I had to change is the ParamByName to FieldByName
cause ParamByName is not recognized by TADOQuery.

Peter
0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

aflarinCommented:
Use Parameters.ParamByName:

procedure TMainForm.utfSchedMgrPostAppt(Sender: TObject; Appt: TJvTFAppt);
var
  I : Integer;
begin
  With GetApptQuery do
    Begin
      Parameters.ParamByName('ApptID').AsString := Appt.ID;
      Open;                   <==========================
      If RecordCount > 0 Then
        Edit
      Else
        Begin
          Insert;
          FieldByName('ApptID').AsString := Appt.ID;
        End;
      FieldByName('StartDate').AsDateTime := Appt.StartDate;
      FieldByName('StartTime').AsDateTime := Appt.StartTime;
      FieldByName('EndDate').AsDateTime := Appt.EndDate;
      FieldByName('EndTime').AsDateTime := Appt.EndTime;
      FieldByName('Description').AsString := Appt.Description;
      FieldByName('AlarmEnabled').AsBoolean := Appt.AlarmEnabled;
      FieldByName('AlarmAdvance').AsInteger := Appt.AlarmAdvance;
      Post;
      Close;
    End;
  With DeleteApptLinkQuery do
    Begin
      Parameters.ParamByName('ApptID').AsString := Appt.ID;
      ExecSQL;
    End;
  With ApptSchedulesQuery do
    Begin
      Parameters.ParamByName('ApptID').AsString := Appt.ID;
      Open;
      For I := 0 to Appt.ScheduleCount - 1 do
        Begin
          Insert;
          FieldByName('ApptID').AsString := Appt.ID;
          FieldByName('SchedName').AsString := Appt.Schedules[I];
          Post;
        End;
      Close;
    End;
end;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aflarinCommented:
and Value instead of AsString:

procedure TMainForm.utfSchedMgrPostAppt(Sender: TObject; Appt: TJvTFAppt);
var
  I : Integer;
begin
  With GetApptQuery do
    Begin
      Parameters.ParamByName('ApptID').Value := Appt.ID;
      Open;                  
      If RecordCount > 0 Then
        Edit
      Else
        Begin
          Insert;
          FieldByName('ApptID').AsString := Appt.ID;
        End;
      FieldByName('StartDate').AsDateTime := Appt.StartDate;
      FieldByName('StartTime').AsDateTime := Appt.StartTime;
      FieldByName('EndDate').AsDateTime := Appt.EndDate;
      FieldByName('EndTime').AsDateTime := Appt.EndTime;
      FieldByName('Description').AsString := Appt.Description;
      FieldByName('AlarmEnabled').AsBoolean := Appt.AlarmEnabled;
      FieldByName('AlarmAdvance').AsInteger := Appt.AlarmAdvance;
      Post;
      Close;
    End;
  With DeleteApptLinkQuery do
    Begin
      Parameters.ParamByName('ApptID').Value := Appt.ID;
      ExecSQL;
    End;
  With ApptSchedulesQuery do
    Begin
      Parameters.ParamByName('ApptID').Value := Appt.ID;
      Open;
      For I := 0 to Appt.ScheduleCount - 1 do
        Begin
          Insert;
          FieldByName('ApptID').AsString := Appt.ID;
          FieldByName('SchedName').AsString := Appt.Schedules[I];
          Post;
        End;
      Close;
    End;
end;
0
Peter KiersOperatorAuthor Commented:
Oke, it continues but stops at:
FieldByName('StartDate').AsDateTime := Appt.StartDate;

I quess Fieldbyname has to be Parameters.ParamByName but
wath about .asdatetime ???

P.
0
Peter KiersOperatorAuthor Commented:
I got it. Thanks for the info.
500 points are comming to you...

PK
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.