[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

TDBGrid Fast Refresh

Posted on 2006-05-03
12
Medium Priority
?
520 Views
Last Modified: 2010-05-18
Hi all

i have aTDbgrid linked on a Query. i want to delete a record from the table where this query reads from , and i want the DBgrid to be refreshed quickly or remove the record i deleted or at least color the record that was deleted in another color


here is the code






VAR
  Qry:TQuery;
  DS:TDataSource ;
 DB1 : TDatabase;
...
...
...
...

  DB1.Connected :=true;
  DS :=TDataSource.Create(self);
  Qry:=TQuery.Create(self);

  Qry.Close;
  Qry.SQL.Clear;
  Qry.DatabaseName :='db1';
  qry.SQL.Add('select * from tmp_table where PR_NO in (select PR_NO from (select PR_NO ,count(*) from tmp_table group by PR_NO having count(*) >1))');
  qry.Open;
  DBGrid1.Columns.Clear;
  for i:=0 to qry.FieldCount -1 do
    begin
      DBGrid1.Columns.add;
      DBGrid1.Columns[i].FieldName :=Qry.Fields[i].FieldName;
    end;
  ds.DataSet :=qry;
  DBGrid1.DataSource :=ds;
...
...
...

//Delete Button Click
    qryDel :=TQuery.Create(self);
    qryDel.DatabaseName :='db1';
    qryDel.Close;
    qryDel.SQL.Clear;
    qryDel.SQL.Add('delete tmp_table  where PR_ID=:PR_ID);
    qryDel.ParamByName('PR_ID').AsFloat :=21312; // the selected row from DBgrid
    qryDel.ExecSQL;

// now the record is deleted and i want to refresh the dbgrid(Fast refresh without refreshing the Query) or mark the Row in the DBGRID in RED or whatever color but i want to be able to know if i click on the marked row that it's deleted
// i don't want to refresh the query since it takes a long time to retrieve the data
// is it possible to delete the record from the DBGrid only?
any ideas?????

Regards,
0
Comment
Question by:Balshe
  • 7
  • 5
12 Comments
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16594695
You could keep the Id's of the deleted records in an array or TStringList or ...
Then in your DBGrid's OnDrawColumnCell you could check if the current ID of the current record can be found in your 'ID-List'.
If so, then color the line red.

I  can make a little bit of code if you want to.
0
 
LVL 1

Author Comment

by:Balshe
ID: 16594775
a little bit of code would help me understand ur idea
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16594826
I just made a small demo-app which makes use of the DBDEMOS-database.

On my Form I have :
    - a TDBGrid (DBGrid1)
          * .DataSource = DataSource1
    - a TSpeedButton (SpeedButton1)
    - a TDataBase (Database1)
          * .AliasName = 'DBDEMOS'
          * . DatabaseName = 'db1'
    - a TQuery (Query1)
          * . DatabaseName = 'db1'
    - a TDataSource (DataSource1)
          * .DataSet = Query1

I also have declared a Form-Private variable : tstrIDList : TStringList;


Then I have the following code :

procedure TForm1.FormCreate(Sender: TObject);
begin
  tstrIDList := TStringList.Create;
  Database1.Open;
  Query1.Open;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  Query1.Close;
  Database1.Close;
  FreeAndNil(tstrIDList);
end;

procedure TForm1.SpeedButton1Click(Sender: TObject);
var
  qryDel : TQuery;
  strID  : String;
begin
  qryDel := TQuery.Create(self);
  qryDel.DatabaseName :='db1';
  qryDel.Close;
  qryDel.SQL.Clear;
  qryDel.SQL.Add('delete from parts where PartNo=:P_ID');
  strID := DBGrid1.DataSource.DataSet.FieldByName('PartNo').AsString;
  qryDel.ParamByName('P_ID').AsInteger := DBGrid1.DataSource.DataSet.FieldByName('PartNo').AsInteger;
  qryDel.ExecSQL;
  if qryDel.RowsAffected > 0 then { DElete was successfull }
    tstrIDList.Add( strID );
  qryDel.Close;
  FreeAndNil( qryDel );
end;

procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
  DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
  if Pos( DataSource1.DataSet.FieldByName('PartNo').AsString, tstrIDList.Text ) > 0 then
  begin
    DBGrid1.Canvas.Brush.Color := $008080FF;  { Light Red }
    DBGrid1.DefaultDrawColumnCell( Rect, DataCol, Column, State );
  end;
end;



Now when I delete a record by clicking the Delete-Button, the application deletes the currently selected record in the DBGrid and if this was successful, then add the PartNo (Unique key) to my StringList.

Then in the OnDrawColumnCell-Event of my DbGrid I check if the current ID  in de the DBGrid is found in my StringList. If so, then I show the complete line in Red.

If you want I can send you the complete project too. Just leave your email-address and I'll send it right away.

Bes tregards,

The Mayor.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16594866
I also post the complete .PAS and .DFM if you want to copy/paste it into a new project :

Form_Main.pas
-------------------


unit Form_Main;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, Grids, DBGrids, ExtCtrls, DBCtrls, Buttons;

type
  TForm1 = class(TForm)
    DBGrid1: TDBGrid;
    Database1: TDatabase;
    Query1: TQuery;
    DataSource1: TDataSource;
    Query1PartNo: TFloatField;
    Query1VendorNo: TFloatField;
    Query1Description: TStringField;
    Query1OnHand: TFloatField;
    Query1OnOrder: TFloatField;
    Query1Cost: TCurrencyField;
    Query1ListPrice: TCurrencyField;
    Panel1: TPanel;
    SpeedButton1: TSpeedButton;
    procedure DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
  private
    { Private declarations }
    tstrIDList : TStringList;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.FormCreate(Sender: TObject);
begin
  tstrIDList := TStringList.Create;
  Database1.Open;
  Query1.Open;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  Query1.Close;
  Database1.Close;
  FreeAndNil(tstrIDList);
end;

procedure TForm1.SpeedButton1Click(Sender: TObject);
var
  qryDel : TQuery;
  strID  : String;
begin
  qryDel := TQuery.Create(self);
  qryDel.DatabaseName :='db1';
  qryDel.Close;
  qryDel.SQL.Clear;
  qryDel.SQL.Add('delete from parts where PartNo=:P_ID');
  strID := DBGrid1.DataSource.DataSet.FieldByName('PartNo').AsString;
  qryDel.ParamByName('P_ID').AsInteger := DBGrid1.DataSource.DataSet.FieldByName('PartNo').AsInteger;
  qryDel.ExecSQL;
  if qryDel.RowsAffected > 0 then { DElete was successfull }
    tstrIDList.Add( strID );
  qryDel.Close;
  FreeAndNil( qryDel );
end;

procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
  DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
  if Pos( DataSource1.DataSet.FieldByName('PartNo').AsString, tstrIDList.Text ) > 0 then
  begin
    DBGrid1.Canvas.Brush.Color := $008080FF;  { Light Red }
    DBGrid1.DefaultDrawColumnCell( Rect, DataCol, Column, State );
  end;
end;

end.



************************************************************
************************************************************



Form_Main.dfm :
-------------------

object Form1: TForm1
  Left = 192
  Top = 219
  Width = 696
  Height = 414
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  OnDestroy = FormDestroy
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 0
    Top = 0
    Width = 688
    Height = 346
    Align = alClient
    DataSource = DataSource1
    Options = [dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect, dgConfirmDelete, dgCancelOnExit]
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
    OnDrawColumnCell = DBGrid1DrawColumnCell
    Columns = <
      item
        Expanded = False
        FieldName = 'PartNo'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'VendorNo'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Description'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'OnHand'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'OnOrder'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Cost'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'ListPrice'
        Visible = True
      end>
  end
  object Panel1: TPanel
    Left = 0
    Top = 346
    Width = 688
    Height = 41
    Align = alBottom
    TabOrder = 1
    object SpeedButton1: TSpeedButton
      Left = 8
      Top = 8
      Width = 97
      Height = 24
      Caption = 'Delete Record'
      OnClick = SpeedButton1Click
    end
  end
  object Database1: TDatabase
    AliasName = 'DBDEMOS'
    DatabaseName = 'db1'
    SessionName = 'Default'
    Left = 104
    Top = 32
  end
  object Query1: TQuery
    DatabaseName = 'db1'
    SQL.Strings = (
      'Select * from Parts')
    Left = 160
    Top = 24
    object Query1PartNo: TFloatField
      FieldName = 'PartNo'
      Origin = '_DB."parts.DB".PartNo'
    end
    object Query1VendorNo: TFloatField
      FieldName = 'VendorNo'
      Origin = '_DB."parts.DB".VendorNo'
    end
    object Query1Description: TStringField
      FieldName = 'Description'
      Origin = '_DB."parts.DB".Description'
      Size = 30
    end
    object Query1OnHand: TFloatField
      FieldName = 'OnHand'
      Origin = '_DB."parts.DB".OnHand'
    end
    object Query1OnOrder: TFloatField
      FieldName = 'OnOrder'
      Origin = '_DB."parts.DB".OnOrder'
    end
    object Query1Cost: TCurrencyField
      FieldName = 'Cost'
      Origin = '_DB."parts.DB".Cost'
    end
    object Query1ListPrice: TCurrencyField
      FieldName = 'ListPrice'
      Origin = '_DB."parts.DB".ListPrice'
    end
  end
  object DataSource1: TDataSource
    DataSet = Query1
    Left = 160
    Top = 56
  end
end
0
 
LVL 1

Author Comment

by:Balshe
ID: 16594962
it works until the row is marked in red, then the application freez since  "DBGrid1DrawColumnCell" Event keeps working not giving the application any time to execute any other instructions !! what do you think about this?
0
 
LVL 1

Author Comment

by:Balshe
ID: 16594980
no , it doesn't freez sorry about this (my mistake)
but it dosen't color the row until i click again on the grid what should i do? i tried dbgrid1.setfocus and it didn't work




0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16595149
Hm, indeed.

When I make use of a normal TButton, then the DBGrid losts focus and the Button gets the focus. Therefor the DBGrid seems not to get the right color.

When I use a TSpeedButton (which cannot get the focus) then everything runs just fine.

Is this an option for you, to make use of a TSpeedButton instead of normal button ?

Best regards,

The Mayor.
0
 
LVL 6

Accepted Solution

by:
wimmeyvaert earned 200 total points
ID: 16595177
I just tested my application with 3 kinds of buttons :
  1) TSpeedButton --> All OK (since DBGrid never looses Focus).
  2) TButton          --> OK if I give the DBGrid focus after deleting the record.
  3) TBitButton      --> OK if I give the DBGrid focus after deleting the record.


Maybe you can leave your email-address so that I can send you my complete demo-application I made.
0
 
LVL 1

Author Comment

by:Balshe
ID: 16595237

this one Works fine thanks
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16595294
OK. Glad to be of some help.
And thanks for the points and the A-Grade !
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16595308
Just a little remark.

Make sure you don't do anything with the 'Marked' records in your db-grid, since these records don't exist anymore in the database.

0
 
LVL 1

Author Comment

by:Balshe
ID: 16595407
yes, thanks for reminding me
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month17 days, 16 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question