Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

ADOTable1: Cannot perform this operation on a closed dataset

Avatar of Peter Kiers
Peter KiersFlag for Netherlands asked on
Delphi
3 Comments1 Solution3413 ViewsLast Modified:
Hi,

I have made a little example that loads and save the treeview-nodes along with
its associated text that will be displayed in a dbrichedit to and from a database.

I have put the code in the code section.

But when a database is protected and I put the wrong password in it.
I get the message: ADOTable1: Cannot perform this operation on a closed dataset

Does someone know how to solve this?

Peter
unit Main;

interface

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

const
  Item_Image_Index = 1;
  FolderClose_Image_Index = 2;
  FolderOpen_Image_Index = 3;

type
  TMainForm = class(TForm)
    Splitter1: TSplitter;
    Splitter2: TSplitter;
    StatusBar1: TStatusBar;
    Panel1: TPanel;
    Panel2: TPanel;
    Label1: TLabel;
    ListView1: TListView;
    TreeView1: TTreeView;
    PageControl1: TPageControl;
    MainMenu1: TMainMenu;
    File1: TMenuItem;
    New1: TMenuItem;
    Open1: TMenuItem;
    N1: TMenuItem;
    Export1: TMenuItem;
    Properties1: TMenuItem;
    N2: TMenuItem;
    Print1: TMenuItem;
    N6: TMenuItem;
    Exit1: TMenuItem;
    Insert1: TMenuItem;
    NewTab1: TMenuItem;
    ItemNode1: TMenuItem;
    FolderNode1: TMenuItem;
    ADOConnection1: TADOConnection;
    ADOTable1: TADOTable;
    ADOTabs: TADOTable;
    DataSource2: TDataSource;
    DataSource1: TDataSource;
    OpenDialog1: TOpenDialog;
    procedure TreeView1Editing(Sender: TObject; Node: TTreeNode;
      var AllowEdit: Boolean);
    procedure TreeView1Change(Sender: TObject; Node: TTreeNode);
    procedure FolderNode1Click(Sender: TObject);
    procedure ItemNode1Click(Sender: TObject);
    procedure NewTab1Click(Sender: TObject);
    procedure Open1Click(Sender: TObject);
    procedure New1Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Exit1Click(Sender: TObject);
    procedure Properties1Click(Sender: TObject);
  private
    { Private declarations }
    DBPath: string;
    DBName: string;
    procedure OpenDatabase;
    function FindNode(ni: integer): TTreeNode;

  public
    { Public declarations }
    procedure SetPassword(aPassword: String);
    procedure OpenRecentFile(Sender: TObject);
    procedure NewTab;
    procedure LoadTree;
    procedure EnableAllRichEdits(Enable: Boolean);
    function IsLogin(DatabaseName: string; Password: string = ''): boolean;
  end;

var
  MainForm: TMainForm;

implementation

uses FileProperties;

{$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;
(*---------------------------------------------------*)
procedure TMainForm.EnableAllRichEdits(Enable: Boolean);
var i, ii : Integer;
     page : TTabSheet;
begin
     for i := 0 to PageControl1.PageCount - 1 do
          begin
          page := PageControl1.Pages[i];
          for ii := 0 to page.ControlCount - 1 do
               begin
               if page.Controls[ii] is TDBRichEdit then
                    page.Controls[ii].Enabled := Enable;
               end;
          end;
end;
(*---------------------------------------------------*)
procedure TMainForm.Exit1Click(Sender: TObject);
begin
 Close;
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.FolderNode1Click(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
  ADOTable1.First;
  ADOTabs.First;
end;
(*---------------------------------------------------*)
function TMainForm.IsLogin(DatabaseName, Password: string): boolean;
begin
   Result := False;
   ADOConnection1.Close;
   ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data ' +
     'Source='+DatabaseName+';Mode=ReadWrite;Persist ' ;
   if Password <> '' then ADOConnection1.ConnectionString := ADOConnection1.ConnectionString +
    'Security Info=False;Jet OLEDB:Database Password='+Password;
   try
     ADOConnection1.Open;
     if ADOConnection1.Connected then Result := True;
   except
     Result := False;
   end;
end;
(*---------------------------------------------------*)
procedure TMainForm.ItemNode1Click(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;
(*---------------------------------------------------*)
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;
 //   SortTabs;
 //   SaveTabOrder;
    PageControl1.TabIndex := 0;
    EnableAllRichEdits(False);
end;
(*---------------------------------------------------*)
procedure TMainForm.New1Click(Sender: TObject);
begin
  Caption := 'Untitled - Personal Knowledgebase';
  DBPath := IncludeTrailingPathDelimiter(ExtractFilePath(Application.Exename));
  DBName := 'dbtree.pkr';
  OpenDatabase;
end;
(*---------------------------------------------------*)
procedure TMainForm.NewTab;
var
  ColumnIndex:Integer;
  ColumnName:String;
  procedure FindOrCreateColumn;
  var
   q: TAdoQuery;
   Node: TTreeNode;
   V: Variant;
  begin
   if Not Assigned(ADOTable1.FindField(ColumnName)) Then
    begin
     With ADOTabs do
      begin
       Append;
       FieldValues['DATA_COLUMN']:=ColumnName ;
       FieldValues['TAB_CAPTION']:=ColumnName ;
       FieldValues['TAB_ORDER']:=ColumnIndex ;
       Post;
      end; 
     Node := TreeView1.Selected;
     if Assigned(Node) Then V := integer(Node.Data);
     q := TAdoQuery.Create(nil);
     try 
      ADOTable1.Close; 
      q.Connection := ADOConnection1;
      q.SQL.Add('Alter table Tree Add Column ' + ColumnName + ' OLEOBJECT');
      try 
       q.ExecSQL;
      except 
       on E: Exception do showmessage('Error: ' + E.Message); 
      end;
     finally 
       q.Free; 
     end;
     ADOTable1.Open; 
     if Assigned(Node) Then
      begin
       TreeView1.Select(Node); 
       ADOTable1.Locate('ID', V, []);
      end;
    end;
  end; 
var
  aRE: TDBRichEdit; 
  tabSheet: TTabSheet; 
begin
  tabSheet := TTabSheet.Create(PageControl1); 
  tabSheet.PageControl := PageControl1;
  ColumnIndex:=tabSheet.tabindex + 1;
  ColumnName:='Data' + IntToStr(ColumnIndex); 
  FindOrCreateColumn;
  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);
  if ADOTabs.Locate('DATA_COLUMN', ColumnName, [loCaseInsensitive]) 
   Then begin
         tabSheet.caption := ADOTabs.FieldValues['TAB_CAPTION'];
         tabSheet.Tag:= ADOTabs.FieldValues['TAB_ORDER'];
         if tabSheet.Tag=0 Then tabSheet.Tag:=ColumnIndex;
        end 
   Else begin
         tabSheet.caption := Format('Page(%d)',[ColumnIndex]);
         tabSheet.Tag:=ColumnIndex;
         With ADOTabs do 
          begin
           Append; 
           FieldValues['DATA_COLUMN']:=aRE.DataField;
           FieldValues['TAB_CAPTION']:=tabSheet.caption;
           FieldValues['TAB_ORDER']:=tabSheet.Tag;
           Post;
          end;
        end;
  PageControl1.ActivePage := tabSheet;
  TreeView1Change(TreeView1 , TreeView1.Selected );
end;
(*---------------------------------------------------*)
procedure TMainForm.NewTab1Click(Sender: TObject);
begin
 NewTab;
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;
    MainForm.Caption := ExtractFileName(OpenDialog1.FileName) + ' - ' + 'Personal Knowledgebase';
  //  updateRecent(sFileName);
    OpenDatabase;
  end;
end;
(*---------------------------------------------------*)
procedure TMainForm.OpenDatabase;
var
 i: integer;
begin
  if not IsLogin(DBPath+'\'+DBName) then
  if not IsLogin(DBPath+'\'+DBName, InputBox('Password', 'Please enter password:', '')) then
    begin
    showmessage('Invalid Password');
    Exit;
    end;
  with PageControl1 do for i := PageCount - 1 downto 0 do Pages[i].Free;
  ADOTable1.Open;
  ADOTabs.Open;
  LoadTree;
end;
(*---------------------------------------------------*)
procedure TMainForm.OpenRecentFile(Sender: TObject);
var
  sFileName: string;
  i: Integer;
begin
  //sFileName := FRecentFiles[TMenuItem(Sender).Tag];
  //i := FRecentFiles.IndexOf(sFileName);
  //FRecentFiles.Delete(i);
  //ReloadFileList(sFileName);
  MainForm.Caption := ExtractFileName(SFilename) + ' - ' + 'Personal Knowledgebase';
  DBName := ExtractFileName(sFileName);
  DBPath := ExtractFilePath(sFileName);
  OpenDatabase;
end;
(*---------------------------------------------------*)
procedure TMainForm.Properties1Click(Sender: TObject);
begin
 FilePropForm.Show;
end;
(*---------------------------------------------------*)
procedure TMainForm.SetPassword(aPassword: String);
var  ovConn:        OleVariant;
const
  adModeShareExclusive =  12;
begin
  ADOConnection1.Close;
  ovConn:=CreateOleObject('ADODB.Connection');
  try
     ovConn.Mode:=adModeShareExclusive;
        ovConn.Open(Format('Provider=Microsoft.Jet.OLEDB.4.0;Data Source="%s"', [DBPath+'\'+DBName]));
        ovConn.Execute(Format('ALTER DATABASE PASSWORD `%s` NULL', [aPassword]));
        ovConn.Close;
  finally
     ovConn:=Unassigned;
  end;
end;
(*---------------------------------------------------*)
procedure TMainForm.TreeView1Change(Sender: TObject; Node: TTreeNode);
var
  V: Variant;
begin
  if Assigned(TreeView1.Selected) then
  begin
    V := Integer(TreeView1.Selected.Data);
    if V > 0 then ADOTable1.Locate('ID', V, []);
    EnableAllRichEdits(TreeView1.Selected.ImageIndex = Item_Image_Index);
  end
  else
   EnableAllRichEdits(False);
end;
(*---------------------------------------------------*)
procedure TMainForm.TreeView1Editing(Sender: TObject; Node: TTreeNode;
  var AllowEdit: Boolean);
begin
  AllowEdit := Node.Parent <> nil;
end;
(*---------------------------------------------------*)
end.
ASKER CERTIFIED SOLUTION
Avatar of Emmanuel PASQUIER
Emmanuel PASQUIERFlag of France imageFreelance Project Manager
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answers