Problem with clearing ADO Tables

PrimeMinister · January 21, 2021

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

Anders Melander · January 21, 2021

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.

PrimeMinister · January 21, 2021

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...

Anders Melander · January 21, 2021

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.

PrimeMinister · January 21, 2021

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

January 21, 2021

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"

Stano · January 21, 2021

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

January 21, 2021

try my sample: Working as expected

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 January 21, 2021 by Guest

January 21, 2021

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 January 21, 2021 by Guest

Stano · January 21, 2021

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.

Mike Torrettinni · January 21, 2021

See similar question with accepted answer, perhaps it can help you: https://stackoverflow.com/questions/65821505/delphi-clearing-ado-tables

PrimeMinister · January 21, 2021

9 minutes ago, Mike Torrettinni said:

See similar question with accepted answer, perhaps it can help you: https://stackoverflow.com/questions/65821505/delphi-clearing-ado-tables

Thx, I solved my problem now and thx to @emailx45 for trying to help as well

😀

January 21, 2021

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:

.exr -1

Exception Code

Parameter[0] - Read (0), Write (1), Execute (8)

Parameter[1] - Address

.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

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:

Inside - Access Violation C0000005 - Read or Write

Inside - Access Violation C0000005 - Execute

Inside - .exr

Inside - .ecxr

Inside - Windows SDK

Microsoft Docs - Debugging Tools for Windows

Microsoft Docs - How Can I Debug a C++ Access Violation?

Microsoft Docs - Debugging Native Code FAQs

marcas:
Debugging, Security, Troubleshooting

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 January 22, 2021 by Guest

January 21, 2021

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 January 21, 2021 by Guest

Anders Melander · January 22, 2021

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.

January 22, 2021

1 hour ago, Anders Melander said:

Could you give it a rest with the formatting?

for sure!

Jeff Overcash · January 22, 2021

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.

Anders Melander · January 22, 2021

@Jeff Overcash Maybe read the whole thread before you reply. The issue has already been resolved.

Stano · January 22, 2021

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.

Sign In

Problem with clearing ADO Tables

Recommended Posts

PrimeMinister 1

Share this post

Link to post

Anders Melander 1780

Share this post

Link to post

PrimeMinister 1

Share this post

Link to post

Anders Melander 1780

Share this post

Link to post

PrimeMinister 1

Share this post

Link to post

Guest

Share this post

Link to post

Stano 143

Share this post

Link to post

Guest

Share this post

Link to post

Guest

Share this post

Link to post

Stano 143

Share this post

Link to post

Mike Torrettinni 198

Share this post

Link to post

PrimeMinister 1

Share this post

Link to post

Guest

marcas:

Share this post

Link to post

Guest

Share this post

Link to post

Anders Melander 1780

Share this post

Link to post

Guest

Share this post

Link to post

Jeff Overcash 2

Share this post

Link to post

Anders Melander 1780

Share this post

Link to post

Stano 143

Share this post

Link to post

Create an account or sign in to comment

Create an account

Sign in

Browse

Activity