ADOTable1: Cannot perform this operation on a control dataset.

Dear Experts,

I have a little example that I have put in the code-section.
When I perform these steps everything works fine:
1. Execute progamm
2. Load database (with opendialog)
3. Create folders and files in the Treeview with the folder/file buttons
4. Protect my database

But when I perform these step in this order: 1 +2 + 4 + 3.
I get error message: ADOTable1: Cannot perform this operation on a closed dataset.

Who can tel me what this means and how to solve it.

Greetings,

Peter Kiers
unit Main;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, OleServer, StdActns, ExtActns, ActnList, DB, ADODB, DBCtrls,
  AppEvnts, ImgList, Menus, ComCtrls, StdCtrls, ExtCtrls;

const
  Item_Image_Index = 1;
  FolderClose_Image_Index = 2;
  FolderOpen_Image_Index = 3;
  InputBoxMessage = WM_User + 201; 

type
  TMainform = class(TForm)
    Splitter1: TSplitter;
    Splitter2: TSplitter;
    Panel1: TPanel;
    Panel2: TPanel;
    Label1: TLabel;
    ListView1: TListView;
    TreeView1: TTreeView;
    PageControl1: TPageControl;
    StatusBar1: TStatusBar;
    MainMenu1: TMainMenu;
    File1: TMenuItem;
    Properties1: TMenuItem;
    N2: TMenuItem;
    ImageList2: TImageList;
    ADOConnection1: TADOConnection;
    ADOTable1: TADOTable;
    ADOTabs: TADOTable;
    DataSource1: TDataSource;
    OpenDialog1: TOpenDialog;
    DataSource2: TDataSource;
    ImageList1: TImageList;
    Exit1: TMenuItem;
    Open1: TMenuItem;
    test1: TMenuItem;
    folder1: TMenuItem;
    file2: TMenuItem;
    procedure file2Click(Sender: TObject);
    procedure folder1Click(Sender: TObject);
    procedure Open1Click(Sender: TObject);
    procedure TreeView1Change(Sender: TObject; Node: TTreeNode);
    procedure FormDestroy(Sender: TObject);
    procedure Exit1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Properties1Click(Sender: TObject);
  private
    { Private declarations }
    DBPath: string;
    DBName: string;
    _Password:String; 
    procedure OpenDatabase;
    procedure InputBoxSetPasswordChar(var Msg: TMessage); message InputBoxMessage;
    function FindNode(ni: integer): TTreeNode;
    function GetInput(aCaption, aPrompt: String): String;
  public
    { Public declarations }
    function ConnectString(Password:String=''):String;
    function SetPassword(aPassword: String):Boolean;
    function IsLogin(Password: string): boolean;
    property Password:String read _Password;
    procedure LoadTree;
    procedure NewTab;
  end;

var
  Mainform: TMainform;

implementation

uses Properties;

{$R *.dfm}

function CustomSortProc(Node1, Node2: TTreeNode; Data: Integer): Integer; stdcall;
begin
  if Node1.ImageIndex <> Node2.ImageIndex then
  begin
    if Node1.ImageIndex = Item_Image_Index then Result := -1 else Result := 1;
  end else
    Result := AnsiCompareText(Node1.Text, Node2.Text);
end;
(*---------------------------------------------------*)
function TMainform.ConnectString(Password: String): String;
begin
 Result:=Format('Provider=Microsoft.Jet.OLEDB.4.0;Data Source="%s";Persist Security Info=False;', [DBPath+'\'+DBName]);
 if Password<>'' Then Result:=Result+Format('Jet OLEDB:Database Password=%s;',[AnsiQuotedStr(Password,'"')]);
end;
(*---------------------------------------------------*)
procedure TMainform.Exit1Click(Sender: TObject);
begin
 Close;
end;
(*---------------------------------------------------*)
procedure TMainform.file2Click(Sender: TObject);
var
  n, n2: TTreeNode;
  NewText: string;
begin
  if (TreeView1.Selected = nil) or (TreeView1.Selected.ImageIndex <> FolderClose_Image_Index) then Exit;
  NewText := InputBox('New Item', 'Item Name', '');
  if NewText = '' then Exit;
  n2 := TreeView1.Selected;
  n := TreeView1.Items.AddChild(n2, NewText);
  n.ImageIndex := Item_Image_Index;
  n.SelectedIndex := Item_Image_Index;
  TreeView1.Selected := nil;
  ADOTable1.Insert;
  ADOTable1.FieldByName('Status').AsInteger := 0;
  ADOTable1.FieldByName('Name').AsString := n.Text;
  ADOTable1.FieldByName('Image_Index').AsInteger := Item_Image_Index;
  if Assigned(n2)
    then ADOTable1.FieldByName('Parent').AsInteger := Integer(n2.Data)
  else ADOTable1.FieldByName('Parent').AsInteger := 0;
  ADoTable1.Post;
  n.Data := Pointer(ADOTable1.FieldByName('ID').AsInteger);
  TreeView1.Selected := n;
  if n.Parent = nil
    then TreeView1.CustomSort(@CustomSortProc, 0, False)
  else n.Parent.CustomSort(@CustomSortProc, 0, False);
end;
(*---------------------------------------------------*)
function TMainform.FindNode(ni: integer): TTreeNode;
var
  i: integer;
begin
  with treeview1 do for i := 0 to Items.Count - 1 do if ni = Integer(Items[i].Data) then
      begin
        Result := Items[i];
        Exit;
      end;
  Result := nil;
end;
(*---------------------------------------------------*)
procedure TMainform.folder1Click(Sender: TObject);
var
  n, n2: TTreeNode;
  NewText: string;
begin
  if (TreeView1.Selected <> nil) and (TreeView1.Selected.ImageIndex = Item_Image_Index) then Exit;
  NewText := InputBox('New Folder', 'Folder Name', '');
  if NewText = '' then Exit;
  n2 := TreeView1.Selected;
  n := TreeView1.Items.AddChild(n2, NewText);
  n.ImageIndex := FolderClose_Image_Index;
  n.SelectedIndex := FolderOpen_Image_Index;
  TreeView1.Selected := nil;
  ADOTable1.Insert;
  ADOTable1.FieldByName('Status').AsInteger := 0;
  ADOTable1.FieldByName('Name').AsString := n.Text;
  ADOTable1.FieldByName('Image_Index').AsInteger := FolderClose_Image_Index;
  if Assigned(n2)
    then ADOTable1.FieldByName('Parent').AsInteger := Integer(n2.Data)
  else ADOTable1.FieldByName('Parent').AsInteger := 0;
  ADoTable1.Post;
  n.Data := Pointer(ADOTable1.FieldByName('ID').AsInteger);
  TreeView1.Selected := n;
  if n.Parent = nil
    then TreeView1.CustomSort(@CustomSortProc, 0, False)
  else n.Parent.CustomSort(@CustomSortProc, 0, False);
end;
(*---------------------------------------------------*)
procedure TMainform.FormCreate(Sender: TObject);
begin
  Caption := 'Untitled - Personal Knowledgebase';
  DBPath := IncludeTrailingPathDelimiter(ExtractFilePath(Application.Exename));
  DBName := 'dbtree.pkr';
  OpenDatabase;
end;
(*---------------------------------------------------*)
procedure TMainform.FormDestroy(Sender: TObject);
begin
 If ADOConnection1.Connected then 
  begin
   ADOTable1.First;
   ADOTabs.First;
  end;
end;
(*---------------------------------------------------*)
function TMainform.GetInput(aCaption, aPrompt: String): String;
var
  ok: Boolean;
begin
 Result := '';
 repeat
 PostMessage(Handle, InputBoxMessage, 0, 0);
  ok := InputQuery(aCaption, aPrompt, Result);
  if ok and (Result = '') THEN
   showmessage ('Please enter a password.');
 until(not ok or (Result <> ''));
end;
(*---------------------------------------------------*)
procedure TMainform.InputBoxSetPasswordChar(var Msg: TMessage);
var
  hInputForm, hEdit: HWND;
begin
  hInputForm := Screen.Forms[0].Handle;
  if (hInputForm <> 0) then
  begin
    hEdit := FindWindowEx(hInputForm, 0, 'TEdit', nil);
    SendMessage(hEdit, EM_SETPASSWORDCHAR, ord('*'), 0);
  end;
end;
(*---------------------------------------------------*)
function TMainform.IsLogin(Password: string): boolean;
begin
 With ADOConnection1 do
  try
   if Connected Then Close;
   ConnectionString:=ConnectString(Password);
   Mode:=cmReadWrite;
   Open;  
   Result := ADOConnection1.Connected;
   if Result Then _Password:=Password;
   if Pos('dbtree.pkr', DBName) = 0 then
   Caption := ExtractFileName(DBName) + ' - ' + 'Personal Knowledgebase';
  except
   Result := False;
  end;
end;
(*---------------------------------------------------*)
procedure TMainform.LoadTree;
var
  i, p: integer;
  n, n2: TTreeNode;
begin
  ADOTable1.First;
  with TreeView1 do
  begin
    Items.BeginUpdate;
    Items.Clear;
    while not ADOTable1.Eof do
    begin
      n2 := FindNode(ADOTable1.FieldByName('Parent').Value);
      n := Items.AddChild(n2, ADOTable1.FieldByName('Name').Value);
      n.ImageIndex := ADOTable1.FieldByName('Image_Index').Value;
      if n.ImageIndex = FolderClose_Image_Index
        then n.SelectedIndex := FolderOpen_Image_Index
      else n.SelectedIndex := n.ImageIndex;
      p := ADOTable1.FieldByName('ID').Value;
      n.Data := Pointer(p);
      ADOTable1.Next;
    end;
    for i := 0 to Items.Count - 1 do Items[i].Expand(True);
    CustomSort(@CustomSortProc, 0);
    Items.EndUpdate;
    if items.Count > 0 then
    begin
      selected := items[0];
      if assigned(selected) then
      begin
        selected.Collapse(true);
        selected.Expand(False);
      end;
    end;
  end;
  with TreeView1 do if Items.Count > 0 then Selected := Items[0];
  for i := 1 to Adotable1.FieldCount do
    if (Adotable1.FindField('Data' + inttostr(i)) <> nil) then
   NewTab;
  PageControl1.TabIndex := 0;
end;
(*---------------------------------------------------*)
procedure TMainform.NewTab;
var
  ColumnIndex:Integer;
  ColumnName:String;
  aRE: TDBRichEdit;
  tabSheet: TTabSheet; 
begin 
  tabSheet := TTabSheet.Create(PageControl1); 
  tabSheet.PageControl := PageControl1;
  ColumnIndex:=tabSheet.tabindex + 1;
  ColumnName:='Data' + IntToStr(ColumnIndex);
  aRE := TDBRichEdit.Create(tabSheet); 
  aRE.Parent := tabSheet; 
  aRE.Name := 'DBRichedit' + IntToStr(ColumnIndex); 
  aRE.Align := alClient; 
  aRE.ParentFont := False; 
  aRE.HideSelection := False; 
  aRE.ScrollBars := ssVertical; 
  aRE.Visible := True; 
  aRE.DataSource := DataSource1; 
  aRE.DataField := ColumnName; 
  tabSheet.Name := 'TabSheet' + IntToStr(ColumnIndex);
  PageControl1.ActivePage := tabSheet;
  TreeView1Change(TreeView1 , TreeView1.Selected ); 
end;
(*---------------------------------------------------*)
procedure TMainform.Open1Click(Sender: TObject);
var
  sFileName: string;
begin
  OpenDialog1.InitialDir := DBPath;
  if OpenDialog1.Execute then
  begin
    DBName := ExtractFileName(OpenDialog1.FileName);
    DBPath := ExtractFilePath(OpenDialog1.FileName);
    sFileName := OpenDialog1.FileName;
    OpenDatabase;
  end;
end;
(*---------------------------------------------------*)
procedure TMainform.OpenDatabase;
var
 i: integer;
begin
 if not IsLogin('') then
  begin
    if not IsLogin(GetInput('Password', 'Please enter password:')) then
    begin
     showmessage('Invalid Password');
     Exit;
    end;
  end;
 with PageControl1 do for i:=Pagecount -1 downto 0 do pages[i].Free;  
 ADOTable1.Open;
 ADOTabs.Open;
  LoadTree;
end;
(*---------------------------------------------------*)
procedure TMainform.Properties1Click(Sender: TObject);
begin
  PropertiesDlg.ShowModal;
end;
(*---------------------------------------------------*)
function TMainform.SetPassword(aPassword: String): Boolean;
Var
 OldPass,NewPass:String;
begin
 if _Password<>'' Then OldPass:='`'+_Password+'`' Else OldPass:='NULL';
 if aPassword<>'' Then NewPass:='`'+aPassword+'`' Else NewPass:='NULL';
 With ADOConnection1 do 
  try
   if Connected Then Close;
   ConnectionString:=ConnectString(_Password);
   Mode:=cmShareExclusive;
   Open;
   Execute('ALTER DATABASE PASSWORD '+NewPass+' '+OldPass);
   Result:=True;
  except
   Result:=False;
  end;
 if Result Then _Password:=aPassword;
 IsLogin(_Password);
end;
(*---------------------------------------------------*)
procedure TMainform.TreeView1Change(Sender: TObject; Node: TTreeNode);
var
  V: Variant;
begin
  if Assigned(TreeView1.Selected) then
  begin
    V := Integer(TreeView1.Selected.Data);
  end;
end;
(*---------------------------------------------------*)
end.

unit Properties;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls, Buttons;

const
  InputBoxMessage = WM_User + 200;

type
  TPropertiesDlg = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    PswChkBx: TCheckBox;
    Button1: TButton;
    Button2: TButton;
    procedure FormShow(Sender: TObject);
    procedure PswChkBxClick(Sender: TObject);
  private
    { Private declarations }
    procedure InputBoxSetPasswordChar(var Msg: TMessage); message InputBoxMessage;
    function GetInput(aCaption, aPrompt: String): String;
  public
    { Public declarations }
  end;

var
  PropertiesDlg: TPropertiesDlg;

implementation

uses Main;

{$R *.dfm}


procedure TPropertiesDlg.FormShow(Sender: TObject);
begin
   PswChkBx.Checked:= MainForm.Password<>'';
end;
(*---------------------------------------------------*)
function TPropertiesDlg.GetInput(aCaption, aPrompt: String): String;
var
  ok: Boolean;
begin
 Result := '';
 repeat
 PostMessage(Handle, InputBoxMessage, 0, 0);
  ok := InputQuery(aCaption, aPrompt, Result);
  if ok and (Result = '') THEN
   showmessage ('Please enter a password.');
 until(not ok or (Result <> ''));
end;
(*---------------------------------------------------*)
procedure TPropertiesDlg.InputBoxSetPasswordChar(var Msg: TMessage);
var
  hInputForm, hEdit: HWND;
begin
  hInputForm := Screen.Forms[0].Handle;
  if (hInputForm <> 0) then
  begin
    hEdit := FindWindowEx(hInputForm, 0, 'TEdit', nil);
    SendMessage(hEdit, EM_SETPASSWORDCHAR, ord('*'), 0);
  end;
end;
(*---------------------------------------------------*)
procedure TPropertiesDlg.PswChkBxClick(Sender: TObject);
var
 InputStr1 : string; 
 InputStr2 : string; 
begin
 if PswChkBx.Checked then
  begin
  InputStr1 := GetInput('Password', 'Please enter new password:');
   if InputStr1 <> '' Then
   begin
   InputStr2 := GetInput('Password', 'Please confirm new password:');
   if InputStr2 <> '' then
   begin
       if InputStr1 <> InputStr2
        then showmessage('The confirmation password was not correct. Password is unchanged.')
        else
         begin
          if MainForm.SetPassword(InputStr2)
           Then showmessage('Password successfully changed.')
           Else ShowMessage('Password could not be changed!');
          Exit;
         end;
      end;
    end;
  end;
 MainForm.SetPassword('');
 PswChkBx.Checked:=False;
end;
(*---------------------------------------------------*)
end.

Open in new window

LVL 1
peterkiersAsked:
Who is Participating?
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.

Emmanuel PASQUIERFreelance Project ManagerCommented:
your ADOConnection is still in read-only mode, because when protecting the mode is changed to exclusive, and Login is called but WITH THE CONNECTION STILL OPENED.

And login first check that, and if connected do nothing and exit.

2 solutions : force reconnection in IsLogin, or close ADOConnection in DB protection. That last is the best solution because it actually fix the problem.
function TMainform.SetPassword(aPassword: String): Boolean;
Var
 OldPass,NewPass:String;
begin
 if _Password<>'' Then OldPass:='`'+_Password+'`' Else OldPass:='NULL';
 if aPassword<>'' Then NewPass:='`'+aPassword+'`' Else NewPass:='NULL';
 With ADOConnection1 do 
  try
   if Connected Then Close;
   ConnectionString:=ConnectString(_Password);
   Mode:=cmShareExclusive;
   Open;
   Execute('ALTER DATABASE PASSWORD '+NewPass+' '+OldPass);
   Result:=True;
  except
   Result:=False;
  end;
 if Result Then _Password:=aPassword;
 ADOConnection1.Close; // <= Add this line
 IsLogin(_Password);
end;

Open in new window

0
peterkiersAuthor Commented:
Still receive: ADOTable1: Cannot perform this operation on a control dataset.
0
Geert GOracle dbaCommented:
on a control dataset
or
on a closed dataset ?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

peterkiersAuthor Commented:
Still receive: ADOTable1: Cannot perform this operation on a closed dataset.

Greetings, Peter Kiers
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
I misread IsLogin, the line I made you add is not necessary (IsLogin is not exiting if already connected by closing the connection, which is correct)

still, that should be working. Can you :
- test that once you set the password, the IsLogin called there is executed, correctly
- make sure that whatever the problem is, it's only just when you set the password, but if you close/reopen the application you can login with password and run your app as you wish
0
peterkiersAuthor Commented:
Yes, my problem is only when I set a password.
I don't know if its relevant but I have put in the
code-section where I get the break.

Peter
procedure TMainform.Folder1Click(Sender: TObject);
var
  n, n2: TTreeNode;
  NewText: string;
begin
  if (TreeView1.Selected <> nil) and (TreeView1.Selected.ImageIndex = Item_Image_Index) then Exit;
  NewText := InputBox('New Folder', 'Folder Name', '');
  if NewText = '' then Exit;
  n2 := TreeView1.Selected;
  n := TreeView1.Items.AddChild(n2, NewText);
  n.ImageIndex := FolderClose_Image_Index;
  n.SelectedIndex := FolderOpen_Image_Index;
  TreeView1.Selected := nil;
  ADOTable1.Insert;
  ADOTable1.FieldByName('Status').AsInteger := 0; <================I get the break here
  ADOTable1.FieldByName('Name').AsString := n.Text;
  ADOTable1.FieldByName('Image_Index').AsInteger := FolderClose_Image_Index;
  if Assigned(n2)
    then ADOTable1.FieldByName('Parent').AsInteger := Integer(n2.Data)
  else ADOTable1.FieldByName('Parent').AsInteger := 0;
  ADoTable1.Post;
  n.Data := Pointer(ADOTable1.FieldByName('ID').AsInteger);
  TreeView1.Selected := n;
  if n.Parent = nil
    then TreeView1.CustomSort(@CustomSortProc, 0, False)
  else n.Parent.CustomSort(@CustomSortProc, 0, False);
end;

Open in new window

0
Emmanuel PASQUIERFreelance Project ManagerCommented:
not much relevant, because the problem is elsewhere.

Trace the SetPassword, and specially the IsLogin called within to check what is wrong

Replace with this IsLogin , and tell us if you have a message
function TMainform.IsLogin(Password: string): boolean;
begin
 With ADOConnection1 do
  try
   if Connected Then Close;
   ConnectionString:=ConnectString(Password);
   Mode:=cmReadWrite;
   Open;  
   Result := ADOConnection1.Connected;
   if Result Then _Password:=Password;
   if Pos('dbtree.pkr', DBName) = 0 then
   Caption := ExtractFileName(DBName) + ' - ' + 'Personal Knowledgebase';
  except
   on E:Exception do
    begin
     Result := False;
     ShowMessage(E.ClassName+':'+E.Message);
    end;
  end;
end;

Open in new window

0
peterkiersAuthor Commented:
I have replaced it and I still have the same message.

step 1+2+3+4 works fine
step 1+2+4+3 not

Peter
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
we have forgotten to simply reopen the tables (that are closed when the connection is closed). They don't auto-reopen, I should have thought about that...

I change also SetPassword to have a message error in case IsLogin do not reopen the DB correctly (should not happen)
function TMainform.IsLogin(Password: string): boolean;
begin
 With ADOConnection1 do
  try
   if Connected Then Close;
   ConnectionString:=ConnectString(Password);
   Mode:=cmReadWrite;
   Open;  
   Result := ADOConnection1.Connected;
   if Result Then _Password:=Password;
   if Pos('dbtree.pkr', DBName) = 0 then
   Caption := ExtractFileName(DBName) + ' - ' + 'Personal Knowledgebase';
  except
   on E:Exception do
    begin
     Result := False;
     ShowMessage(E.ClassName+':'+E.Message);
    end;
  end;
 if Result Then
  begin
   ADOTable1.Open;
   ADOTabs.Open;
  end;
end;


function TMainform.SetPassword(aPassword: String): Boolean;
Var
 OldPass,NewPass:String;
begin
 if _Password<>'' Then OldPass:='`'+_Password+'`' Else OldPass:='NULL';
 if aPassword<>'' Then NewPass:='`'+aPassword+'`' Else NewPass:='NULL';
 With ADOConnection1 do 
  try
   if Connected Then Close;
   ConnectionString:=ConnectString(_Password);
   Mode:=cmShareExclusive;
   Open;
   Execute('ALTER DATABASE PASSWORD '+NewPass+' '+OldPass);
   Result:=True;
  except
   Result:=False;
  end;
 if Result Then _Password:=aPassword;
 if Not IsLogin(_Password) 
  Then ShowMessage('DB Error : could not reopen database after password changed');
end;

Open in new window

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
Emmanuel PASQUIERFreelance Project ManagerCommented:
and also change OpenDatabase. All tables are open by IsLogin
procedure TMainform.OpenDatabase;
var
 i: integer;
begin
 if not IsLogin('') then
  begin
    if not IsLogin(GetInput('Password', 'Please enter password:')) then
    begin
     showmessage('Invalid Password');
     Exit;
    end;
  end;
 with PageControl1 do for i:=Pagecount -1 downto 0 do pages[i].Free;  
 LoadTree;
end;

Open in new window

0
peterkiersAuthor Commented:
I have tested it and it has solved my problem
Only another little problem has occured.

When I have protect my db with password and I load that db
first a message appears: EOleExecption: geen geldig wachtwoorden (means no valid password)
and then the Inputquert appears where the user can enter his password!!!

peter

0
Emmanuel PASQUIERFreelance Project ManagerCommented:
delete (or comment) these lines :

   on E:Exception do
    begin
     Result := False;
     ShowMessage(E.ClassName+':'+E.Message);
    end;

they are no longer needed, that was just to understand if some problem is hapenning
0
peterkiersAuthor Commented:
Thanx, Now it works, 500 points are comming to you...

Peter Kiers

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.