Jump to content
PrimeMinister

Problem with clearing ADO Tables

Recommended Posts

Hey guys, so I'm having a problem with my ADO Tables. I have a button in my program that is meant to clear all tables in the database related to the program when clicked but whenever I click it, it gives errors. (no matter how many times I change my code) Could it be a problem with my Data Module or what? (I'm not sure cause my button which saves stuff to the database, works perfectly without a hitch) How could I alleviate this?

 

Code of my data module:

   const
  scConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%pathtomdb%Racers.mdb;Mode=ReadWrite;Persist Security Info=False;';
var
  dmRacers: TdmRacers;

implementation

{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}

procedure TdmRacers.DataModuleCreate(Sender: TObject);
var
path:string;
begin
  path:=ExtractFilePath(ParamStr(0));
  conToDB.ConnectionString := StringReplace(scConnectionString, '%pathtomdb%', path, []);
  conToDB.Connected:=True;
  tbl1660.Active := True;
  tblXKarts.Active := True;
  tblTwoPointOne.Active := True;
  tblMidB.Active := True;
  tblMidA.Active := True;
  tblLateModel.Active := True;
  tblSprints.Active := True;
  tblV8.Active := True;
  tblHeavyMetals.Active := True;
  tblHotrods.Active := True;
  tblPinkrods.Active := True;
  tblStockrods.Active := True;
  tblMinis.Active := True;
  tblDevelopment.Active := True;
end;

Code from the form:

 public
    { Public declarations }
    MyCmd:TADOCommand;
    procedure EmptyTable(tbl:TADOTable;tablename:string);
    procedure ResetActiveProp(tbl:TADOTable);
  end;

var
  frmEntry: TfrmEntry;

implementation

{$R *.dfm}

procedure TfrmEntry.ResetActiveProp(tbl: TADOTable);
begin
  tbl.Active := True;
end;

procedure TfrmEntry.EmptyTable(tbl: TADOTable; tablename: string);
var
  MyCmd: TADOCommand;
begin
    tbl.Active := true;

    MyCmd.CommandText := 'Delete * from '+tablename;
    MyCmd.Execute;

    tbl.Active := false;
end;

procedure TfrmEntry.bmbClearClick(Sender: TObject);
var
  i:integer;
begin
  cbxGridSelect.ItemIndex:=-1;
  cbxGridSelect.Text:='Select a class';
  edtRacerName.Clear;
  edtCarNumber.Clear;
  edtLicenseNum.Clear;
  i:=MessageDlg('Dialog cleared',mtInformation,[mbOK],0);
end;

procedure TfrmEntry.bmbClearDBClick(Sender: TObject);
var
  i:integer;
begin
  i:=MessageDlg('Are you sure you want to clear the Racers database? (all current data in the database will be lost.)',mtWarning,[mbOK,mbCancel],0);
  if i = mrOk then
  begin
    //clears entire database
    with dmRacers do
    begin

      EmptyTable(tbl1660,'tbl1660');
      EmptyTable(tblXKarts,'tblXKarts');
      EmptyTable(tblTwoPointOne,'tblTwoPointOnes');
      EmptyTable(tblMidB,'tblMidB');
      EmptyTable(tblMidA,'tblMidA');
      EmptyTable(tblLateModel,'tblLateModels');
      EmptyTable(tblSprints,'tblSprints');
      EmptyTable(tblV8,'tblV8');
      EmptyTable(tblHeavyMetals,'tblHeavyMetals');
      EmptyTable(tblHotrods,'tblHotrods');
      EmptyTable(tblPinkrods,'tblPinkrods');
      EmptyTable(tblStockrods,'tblStockrods');
      EmptyTable(tblMinis,'tblMinis');
      EmptyTable(tblDevelopment,'tblDevelopment');

      i:=MessageDlg('Database successfully cleared',mtInformation,[mbOk],0);

      //resets active property of tables
      ResetActiveProp(tbl1660);
      ResetActiveProp(tblXKarts);
      ResetActiveProp(tblTwoPointOne);
      ResetActiveProp(tblMidB);
      ResetActiveProp(tblMidA);
      ResetActiveProp(tblLateModel);
      ResetActiveProp(tblSprints);
      ResetActiveProp(tblV8);
      ResetActiveProp(tblHeavyMetals);
      ResetActiveProp(tblHotrods);
      ResetActiveProp(tblPinkrods);
      ResetActiveProp(tblStockrods);
      ResetActiveProp(tblMinis);
      ResetActiveProp(tblDevelopment);
    end;
  end
  else
  begin
    i:=MessageDlg('Clear aborted',mtInformation,[mbOk],0);
  end;
end;

Example of errors:

error.JPG.c5cb123cd2765594842517df400a4356.JPG

acccess.JPG.cf33c182ef64bb0f2a9664a1c9c63132.JPG

 

Thanks in advance for the help!

Kind Regards

PrimeMinister

Share this post


Link to post
1 hour ago, PrimeMinister said:

procedure TfrmEntry.EmptyTable(tbl: TADOTable; tablename: string);
var
  MyCmd: TADOCommand;
begin
    tbl.Active := true;

    MyCmd.CommandText := 'Delete * from '+tablename;
    MyCmd.Execute;

    tbl.Active := false;
end;

 

MyCmd isn't initialized so that's probably the cause of the AV. You need to create an instance of TADOCommand or reference an existing instance. Get rid of the local var declaration if you meant to be using the TADOCommand with the same name on the form

 

Apart from that you haven't told us where in your code the problem occurs.

Share this post


Link to post
8 minutes ago, Anders Melander said:

MyCmd isn't initialized so that's probably the cause of the AV. You need to create an instance of TADOCommand or reference an existing instance. Get rid of the local var declaration if you meant to be using the TADOCommand with the same name on the form

 

Apart from that you haven't told us where in your code the problem occurs.

It was on this line

MyCmd.CommandText := 'Delete * from '+tablename;

So I changed my code to this:
   

//clears entire database
    with dmRacers do
    begin

      MyCmd := TADOCommand.Create(Self);
      try
      tbl1660.Active := true;

      MyCmd.CommandText := 'Delete * from tbl1660';
      MyCmd.Execute;

      tbl1660.Active := false;
        {EmptyTable(tbl1660,'tbl1660');
        EmptyTable(tblXKarts,'tblXKarts');
        EmptyTable(tblTwoPointOne,'tblTwoPointOnes');
        EmptyTable(tblMidB,'tblMidB');
        EmptyTable(tblMidA,'tblMidA');
        EmptyTable(tblLateModel,'tblLateModels');
        EmptyTable(tblSprints,'tblSprints');
        EmptyTable(tblV8,'tblV8');
        EmptyTable(tblHeavyMetals,'tblHeavyMetals');
        EmptyTable(tblHotrods,'tblHotrods');
        EmptyTable(tblPinkrods,'tblPinkrods');
        EmptyTable(tblStockrods,'tblStockrods');
        EmptyTable(tblMinis,'tblMinis');
        EmptyTable(tblDevelopment,'tblDevelopment');}
      finally
        MyCmd.Free;
      end;

But now the error is "Missing Connection or ConnectionString".

 

I don't know what to do anymore...

Share this post


Link to post
1 minute ago, PrimeMinister said:

But now the error is "Missing Connection or ConnectionString".

 

I don't know what to do anymore...

Read the documentation or example some existing code that works.

 

The error message is pretty clear about what the problem is. I don't need to explain it to you.

Share this post


Link to post
9 minutes ago, Anders Melander said:

Read the documentation or example some existing code that works.

 

The error message is pretty clear about what the problem is. I don't need to explain it to you.

ok thx

Share this post


Link to post
Guest

my tip about "Connection string"

  • always that possible, test new "string" with value desired in desing-time, then, you'll have more certains about others ways, you see?
  • this works for any other case, include others "target db"

Share this post


Link to post

I am convinced that tbl1660.Active: = true; and company is completely useless.

If you need to update the table, use Refresh. But not where Adocommand is

 

Share this post


Link to post
Guest

try my sample: Working as expected

image.thumb.png.eb0aa275dfb00cc393ead3c3d11e76fe.png    image.thumb.png.d1799e862f96fb7b5e6fb288952f5750.png

 

unit uFormMain;

interface

uses
  Winapi.Windows,
  Winapi.Messages,
  System.SysUtils,
  System.Variants,
  System.Classes,
  Vcl.Graphics,
  Vcl.Controls,
  Vcl.Forms,
  Vcl.Dialogs,
  Vcl.StdCtrls,
  Data.DB,
  Data.Win.ADODB,
  Vcl.Grids,
  Vcl.DBGrids;

type
  TForm1 = class(TForm)
    Memo1: TMemo;
    btnOpenConnection: TButton;
    ADOConnection1: TADOConnection;
    btnChangeDBPath: TButton;
    ADOCommand1: TADOCommand;
    btnGetAllTablesOnDB: TButton;
    ListBox1: TListBox;
    btnPlease_EmptyThisTable_Selected: TButton;
    DBGrid1: TDBGrid;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    btnOpenTableSelected: TButton;
    procedure btnOpenConnectionClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnChangeDBPathClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btnGetAllTablesOnDBClick(Sender: TObject);
    procedure btnPlease_EmptyThisTable_SelectedClick(Sender: TObject);
    procedure btnOpenTableSelectedClick(Sender: TObject);
  private
    procedure prcDBinfo;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

var
  lADOConnText: string =                                                                     { }
    'Provider=Microsoft.Jet.OLEDB.4.0;' +                                                    { }
    'Data Source=D:\RADRIOTests\VCL_MDB_Database_Access_Test_Connection\Data\dbdemos.mdb;' + { }
    'Mode=ReadWrite|Share Deny None;' +                                                      { }
    'Persist Security Info=False;';
  //
  lMyNewPathMDBfile: string = 'D:\RADRIOTests\VCL_MDB_Database_Access_Test_Connection\Data\dbdemos.mdb';

function fncMyIIF(lCondition: boolean): string;
begin
  result := 'false';
  //
  if lCondition then
    result := 'true';
end;

procedure TForm1.btnChangeDBPathClick(Sender: TObject);
var
  i: integer;
begin
  if (ADOConnection1.Connected) then
    ADOConnection1.Close;
  //
  for i := 0 to (ADOConnection1.Properties.Count - 1) do
  begin
    if ADOConnection1.Properties.Item[i].Name = 'Data Source' then
      ADOConnection1.Properties.Item[i].Value := lMyNewPathMDBfile; // <--- changing DB path!!!
    //
    Memo1.Lines.Add(                              { }
      Format('%s=%s', [                           { }
          ADOConnection1.Properties.Item[i].Name, { }
          ADOConnection1.Properties.Item[i].Value { }
        ])                                        { }
      );
  end;
  //
  try
    ADOConnection1.Open();
    //
    prcDBinfo;
  except
    on E: Exception do
      ShowMessage('my error:' + sLineBreak + E.Message);
  end;
end;

procedure TForm1.btnGetAllTablesOnDBClick(Sender: TObject);
begin
  if ADOConnection1.Connected then
  begin
    ADOConnection1.GetTableNames(ListBox1.Items, false);
  end
  else
    ShowMessage('DB not connected!');
end;

procedure TForm1.btnOpenConnectionClick(Sender: TObject);
begin
  Memo1.Lines.Add(lADOConnText);
  //
  try
    if not(ADOConnection1.Connected) then
      ADOConnection1.Open();
    //
    prcDBinfo;
  except
    on E: Exception do
      ShowMessage('my error:' + sLineBreak + E.Message);
  end;
end;

procedure TForm1.btnOpenTableSelectedClick(Sender: TObject);
begin
  try
    if not ADOConnection1.Connected then
      ADOConnection1.Open();
  except
    on E: Exception do
    begin
      ShowMessage('my error:' + sLineBreak + E.Message);
      exit;
    end;
  end;
  //
  if (ListBox1.ItemIndex > -1) then
  begin
    ADOQuery1.SQL.Text := Format('select * from %s', [ListBox1.Items[ListBox1.ItemIndex]]);
    ADOQuery1.Open;
  end;
end;

procedure TForm1.btnPlease_EmptyThisTable_SelectedClick(Sender: TObject);
var
  i: integer;
begin
  { BeginTrans returns a value of type integer, indicating the nesting level of the new transaction.
    A successful execution of BeginTrans triggers an OnBeginTransComplete event and sets the InTransaction property to true
    The ADO connection object must have an active connection before BeginTrans can be used
  }
  try
    if not ADOConnection1.Connected then
      ADOConnection1.Open();
  except
    on E: Exception do
    begin
      ShowMessage('my error:' + sLineBreak + E.Message);
      exit;
    end;
  end;
  //
  try
    if (ListBox1.ItemIndex > -1) then
    begin
      try
        ADOCommand1.CommandText := Format('delete * from %s', [ListBox1.Items[ListBox1.ItemIndex]]);
        //
        if not ADOConnection1.InTransaction then
          ADOConnection1.BeginTrans; // see return value
        //
        ADOCommand1.Execute; // see return value
        //
        ADOConnection1.CommitTrans;
        //
        btnOpenTableSelected.Click; // refreshing DBGrid...
      except
        on E: Exception do
          ShowMessage('my error:' + sLineBreak + E.Message);
      end;
    end
    else
      ShowMessage('Stop: Table not selected...');
  finally
    if ADOConnection1.InTransaction then
      ADOConnection1.RollbackTrans; // AV is db not conneted
  end;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  ADOConnection1.Close;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  if (ADOConnection1.Connected) then
    ADOConnection1.Close;
  //
  ADOConnection1.ConnectionString := lADOConnText;
end;

procedure TForm1.prcDBinfo;
var
  i: integer;
begin
  Memo1.Lines.Add('DB default: ' + ADOConnection1.DefaultDatabase);
  Memo1.Lines.Add('Version: ' + ADOConnection1.Version);
  Memo1.Lines.Add('Provider: ' + ADOConnection1.Provider);
  Memo1.Lines.Add('Dataset couting: ' + ADOConnection1.DataSetCount.ToString);
  Memo1.Lines.Add('In Transaction: ' + fncMyIIF(ADOConnection1.InTransaction));
  //
  Memo1.Lines.Add('----');
  //
  for i := 0 to (ADOConnection1.Properties.Count - 1) do
  begin
    Memo1.Lines.Add(                              { }
      Format('%s=%s', [                           { }
          ADOConnection1.Properties.Item[i].Name, { }
          ADOConnection1.Properties.Item[i].Value { }
        ])                                        { }
      );
  end;
end;

end.

 

hug  (prime minister's Boss )  :)))))

Edited by Guest

Share this post


Link to post
Guest
1 hour ago, Stano said:

I am convinced that tbl1660.Active: = true; and company is completely useless.

If you need to update the table, use Refresh. But not where Adocommand is

 

the table can be (yes) "opened"!!! if exist some dependeces, the "AV" will be showed...

Edited by Guest

Share this post


Link to post

I assume that reference integrity is defined in the DB. I think AV is the wrong term used. Each correct DB raises an exception, not an AV, in the event of an integrity violation, which must be handled.
I know two basic ways to deal with emptying tables.
   1. Empty tables from the lowest level to the next. In terms of referential integrity
   2. Use DeleteCascade when defining reference integrity.

Share this post


Link to post
Guest
7 hours ago, Stano said:

I assume that reference integrity is defined in the DB. I think AV is the wrong term used. Each correct DB raises an exception, not an AV, in the event of an integrity violation, which must be handled.
I know two basic ways to deal with emptying tables.
   1. Empty tables from the lowest level to the next. In terms of referential integrity
   2. Use DeleteCascade when defining reference integrity.

Ring's Lord from the "differences":

Quote

An Access Violation is a type of Exception caused when an application Reads, Writes or Executes an invalid Memory Address.

The Exception Code is 0xC0000005

The first Exception Parameter (0) is the type of the violation; Read (0), Write (1) or Execute (8)

The second Exception Parameter (1) is the address of the violation.

Follow these steps when debugging these issues with the Debugging Tools for Windows:

  1. .exr -1
    • Exception Code
    • Parameter[0] - Read (0), Write (1), Execute (8)
    • Parameter[1] - Address
  2. .ecxr
    • Register values at the Access Violation
    • Assembler instruction
      • Read - look at the expression on the right of the comma
      • Write - look at the expression on the left of the comma
      • Execute - look at the address of expression
  3. k
    • View the Call Stack at the Access Violation

The memory address may be invalid because of one of these common scenarios:

  • NULL Pointer - addresses between 0x0 and 0x10000 (64K) - e.g. a function that usually returns a pointer returned NULL (0x0), and the pointer was accessed without verification
  • Memory Corruption - the address was mistakenly or maliciously overwritten - commonly via a buffer overrun (or underrun)
  • Use-After-Free - the address was valid, but is now being accessed after it is freed (data) or unloaded (code)
  • Bit-Flip - RAM (hardware) issue where one or more bits have flipped (rare)

For Read or Write issues, refer to the Read or Write episode.
For Execute issues, refer to the Execute episode.

Note that CLR applications with throw a System.NullReferenceException exception instead of an Access Violation exception when the address is between 0x0 and 0x10000 (64K).

Additional Resources:

 

  • In Database system, whatever the type, it will translate what, in fact, will generate an "Access Violation" (since the "data" (not the records of tables in the Database), but what is in current memory, does not correspond to a valid or existing "data" !
  • The "Reference Integrity" will cause an "Access Violation" referring to the "data" that points to the data in the file. So, if the reference no longer points to a valid value, obviously, that we are "violating" the rules, therefore, it will not be possible to have "access" to the given "data", whatever it may be, a variable, a record in a data table, etc ...
  • Anyway, it is an "Access Violation" in its fundamental point of being.
  • Now the technical name for a given area can be any other that is relevant and pertinent to the area in question.

 

And so, the Elder said: forgive me Lord, he doesn't know the quiz!

Edited by Guest

Share this post


Link to post
Guest

image.thumb.png.d3cac46f20381d7c62003af17a914910.png

 

As this is a Database being accessed via "ODBC", we will have the lowest class "EOleException", thanks Microsoft for your OLE objects and properties (Variants by the way)!

 

hug

Edited by Guest

Share this post


Link to post
2 hours ago, emailx45 said:

Ring's Lord from the "differences":

Could you give it a rest with the formatting? Your posts are hard enough to tolerate without it.

  • Like 2

Share this post


Link to post
Guest
1 hour ago, Anders Melander said:

Could you give it a rest with the formatting?

for sure!  :classic_love:

Share this post


Link to post

your problem is in your empty Table

procedure TfrmEntry.EmptyTable(tbl: TADOTable; tablename: string);
var
  MyCmd: TADOCommand;
begin
    tbl.Active := true;

    MyCmd.CommandText := 'Delete * from '+tablename;
    MyCmd.Execute;

    tbl.Active := false;
end;

 

You have a local variable named MyCmd, but you never initialize it.  Nor do you hook it up to the connection.  try this instead

 

procedure TfrmEntry.EmptyTable(tbl: TADOTable);
var
  MyCmd: TADOCommand;
begin
  MyCmd := TADOCommand.Create(nil);
  try
    MyCmd.Connection := tbl.Connection;
    MyCmd.CommandText := 'Delete * from ' + tbl.TableName;
    MyCmd.Execute;
  finally
    MyCmd.Free;
  end;
end;

Note you have 2 MyCmd variables.  One is local to your EmptyTable procedure and one public to your form, but neither is shown to ever be created.  You need to both instantiate a TADOCommand object (the .Create ) and you need to tell it what connection to use (setting the connection property).  Don't forget to clean up the variable when done.

  • Like 1

Share this post


Link to post
9 hours ago, Anders Melander said:
12 hours ago, emailx45 said:

Ring's Lord from the "differences":

Could you give it a rest with the formatting? Your posts are hard enough to tolerate without it.

Highlighting important parts is fine. Even pictures! But using a large font for almost the whole topic is no longer okay.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×