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

x
?
Solved

Loop in a DBGrid

Posted on 2007-10-10
20
Medium Priority
?
1,551 Views
Last Modified: 2010-04-05
Hi all,

I've got a bit of a problem.

I'm using a DBGrid and one of the Columns in called 'AccountID'.

This column contains around 60000 records and I need to automate a procedure otherwise it's going to take me forever.

I need a loop that Gets every item under 'AccountID' and does something.

For example:

AccountID
------------
ADER1332
ADER1334
ADER1445
etc...

THE LOOP I NEED:

1. Get the 1st AccounID Field
2. Do something
3 Get the Next AccountID Field
4 Do something
etc...
Till if ends.

The Do Something...I'll fill it up. I just need to know how the Loop reading a specific Column(item) on a DBGrid.

Hope you can help

Thanks

ST3VO
0
Comment
Question by:ST3VO
  • 9
  • 8
  • 3
20 Comments
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20052689
There are about 3 ways to do this.
1. Change the values in the dataset/table itself. Once the operation has been performed on this data it will always stay like that until you perform a new operation to change it to something else
2. Change the way that the data from a field is read by data-aware components. This doesn't change the data permanently, and only ensures that your DBGrids etc view the data differently, but meanwhile back in the actual database table (dataset) the data is how it was originally. Of course if your user goes and edits this data in a data-aware control, it will then store it the way it was viewed - so you do another thing that ensures that what the user is 'posting' to the database gets manipulated before its posted.
3. You over-ride the DBGRID to tell it to get it to draw its data differently.

I will try cover each method...

--METHOD 1 --
If your DBGrid's DataSource is DataSource 1 and DataSource1's Dataset is Table1, then do the following.
begin
Table1.first;
While Table1.eof = false do
begin
  Table1.FieldByName('AccountID').text := Table1.FieldByName('AccountID').text + ' Different';
  Table1.Next;
end;

Next... Method 2
0
 

Author Comment

by:ST3VO
ID: 20052747
I'm using ADOConnection and ADOQuery.
0
 

Author Comment

by:ST3VO
ID: 20052769
Ahh before I forget...

I need to loop via the DBGrid contents and not thru all the actual records on the Table.

This is because I first run a Query, then I need to loop thru the results of that query which isn't all the records on the table.

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 13

Expert Comment

by:rfwoolf
ID: 20052801
=METHOD 2 =

From Delphi's help menu on TField.OnGetText:
Occurs when the DisplayText or Text properties of the field are referenced.

Write an OnGetText handler to perform custom processing for the DisplayText and Text properties. Use OnGetText to format the Value of the field differently when it must be edited from the format used when simply displaying the value, or use OnGetText to display something other than the field's value when it is displayed.
 
If there is no OnGetText handler, the value of the DisplayText and Text properties is simply the value of the AsString property.
---

For this you will need a dataset, e.g. TTAble or TQuery etc

In design-time, Activate/Enable your dataset so that it connects to the database (just for now, you will disable/de-activate it later).
Double-click on TTable. Its Field editor will appear.
Right-click in the field editor and click "Add all fields"
This creates Persistent Fields for all the fields in your dataset. This means that even if you had to now disable/de-activate the dataset, your application still "remembers" and even expects those same fields to come back again when you use your dataset during runtime.
Still in the Fields Editor, rightclick on the AccountID field. In the Object Inspector, under Events, double-click to create an event handler for the OnGetText event

procedure MyTableAccountIDGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
begin
//Set this field to display the 8 first characters of the field's data and add the word 'Special'
  Text := Copy(MyTableAccountID.AsString, 1, 8) + ' Special';
//Or you can override the text completely:
  Text := 'Something';
end;

===

Now, what happens when your users now see this new value in the DBGrid and edit it?

From the delphi helpfile for TField.OnSetText Event:
Occurs when the Text property is assigned a value.
Write an OnSetText event handler to specify processing that occurs whenever Text receives a new value. If there is an OnGetText event handler that formats the Value of the field in some special way for editing purposes, OnSetText should reverse the process when parsing the edited string.
 
If there is no OnSetText handler, the Text property is set by setting the AsString property.
===

So here is how to manipulate the data that gets received from the user
(Set an OnSetText event handler for the Field AccountID)

procedure MyTableAccountIDSetText(Sender: TField; const Text: string);
begin
  text := 'NEW VALUE';
end;

==WARNING==
Your dataset (or database table) will obviously expect the data formatted in the way it expects.
If the field is an Integer field, you can't set text to "Fourteen" or "awefawef" - you will need to use values like "14" etc
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20052821
===RESPONSE===
"This is because I first run a Query, then I need to loop thru the results of that query which isn't all the records on the table."

It's the same for a Query

ADOQuery.First;
While ADOQuery.eof = false do
begin
  ADOQuery.FieldByName('AccountID').value := 'Something else';
  ADOQuery.next;
end;
==WARNING==
When changing the value of a field you must provide it with the datatype it expects, e.g.
if it's an Integer field
  ADOQuery.FieldByName('AccountID').value := 14;
or if it's a date:
  ADOQuery.FieldByName('AccountID').value := now;

0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20052879
== METHOD 3 == <-- what you're looking for

Select your DBGrid
In the Object Inspector under the 'Events' tab, create an event handler for
OnDrawColumnCell

procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
  DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
if column.Title.Caption = 'AccountID' then
begin
  DBGrid1.canvas.TextRect(rect,rect.Left,rect.top,inttostr('Whatever'))
 end
else
  DBGrid1.canvas.TextRect(rect,rect.Left,rect.top,table1.fields[DataCol].AsString);
0
 

Author Comment

by:ST3VO
ID: 20052925
Hmm...I still need to be able to GET the text contents of each field under the column and I'm going to use this data to perform other queries.

Let suppose we have this.

var TheActiveField : String;
begin
TheActiveField:=(The Active Field in the loop text);

You know what I mean?

0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20052948
==EXTENSION TO METHOD 3=

The following will set the values in the Account ID to "Account ID# 1' depending on the recordno in the ADOQuery...

procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
  DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
if column.Title.Caption = 'AccountD' then
begin
DBGrid1.canvas.TextRect(rect,rect.Left,rect.top,inttostr('Account ID# ' + InttoStr(ADOQuery1.RecNo)));
end
else
DBGrid1.canvas.TextRect(rect,rect.Left,rect.top,table1.fields[DataCol].AsString);
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20052992
RE: ST3VO:
"Hmm...I still need to be able to GET the text contents of each field under the column and I'm going to use this data to perform other queries.

Let suppose we have this.
  var TheActiveField : String;  
  begin
  TheActiveField:=(The Active Field in the loop text);
You know what I mean?

===

You say "I still need to be able to GET the text contents of each field under the column " -- if I understand you correctly, do you mean this:

procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
  DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
if column.Title.Caption = 'AccountID' then
begin
DBGrid1.canvas.TextRect(rect,rect.Left,rect.top,('Client Name: ' + ADOQuery1.FieldByName('ClientName').AsString
+ ' Date of Birth: ' + ADOQuery1.FieldByName('DateOfBirth').AsString))
end
else
DBGrid1.canvas.TextRect(rect,rect.Left,rect.top,table1.fields[DataCol].AsString);
end;

(In the above example your ADOQuery1 has fields called "DateOfBirth" and "ClientName" which it accesses. Note that it will only be able to refer to the field values of the record it is on. So if the DBGrid is painting the first row, it will refer to the ADOQuery1's current record -- you won't be able to say, access the next record -- let me know if you need to do that).
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20052999
Oh in the above example, the result will be something like this
(Under Account ID column) :
Client Name: John Smith Date of Birth: 01/03/1980

(of course that column would be quite wide)

Is this what you were looking for?
0
 
LVL 6

Assisted Solution

by:bokist
bokist earned 800 total points
ID: 20054798
Perhaps something like this :

      with DBGrid.DataSource.DataSet do
         begin
         First;
         DisableControls;
         try
            while not EOF do
               begin
               if DBGid.DataSource.DataSet.FieldByName('AccountId).asinteger = some_nr  then do_something
               Next;
            end;
         finally
            EnableControls;
         end;
      end;
0
 

Author Comment

by:ST3VO
ID: 20054856
Hmmm...that's more like what I'm looking for...

But got a comment:

with DBGrid.DataSource.DataSet do
         begin
         First;
         DisableControls;
         try
            while not EOF do
               begin
               if DBGid.DataSource.DataSet.FieldByName('AccountId).asinteger = some_nr  //<-- (I need to get the number not insert it)  


then do_something // For example: Field Text goes to Edit1
               Next;
            end;
         finally
            EnableControls;
         end;
      end;

Hope this helps

0
 

Author Comment

by:ST3VO
ID: 20054864
One more thing....I need to work with the actual contents of the DBGrid and not the (All) DB Records.
0
 
LVL 6

Expert Comment

by:bokist
ID: 20054891
In suggested solution you are working with data from DBGrid.
For further solutions, please give me more detailed explanation about the  task.
0
 

Author Comment

by:ST3VO
ID: 20054898
Just one thing I need. I need to GET the field text that is currently active or current.
0
 
LVL 6

Expert Comment

by:bokist
ID: 20054922
 while not EOF do
         begin
          if DBGrid.SelectedRows.CurrentRowSelected = True  then
             if DBGid.DataSource.DataSet.FieldByName('AccountId).asinteger = some_nr  then do_something
          Next;
     end;
 
0
 
LVL 13

Accepted Solution

by:
rfwoolf earned 1200 total points
ID: 20054928
we have explained how to do that.
Here's a walkthrough:
begin
ADOQuery.first; //Set the dataset to the FIRST RECORD
while ADOQuery.eof = false do //While the dataset isn't at its end (past its last record do...
begin
 //Next line: Shows a message displaying the value of the "AccountID" field in the current record.
//You will see that as the while loop progresses  it will show you the value for the AccountID
//for record 1, then 2, then 3 ... til end
 Showmessage(ADOQuery.FieldByName('AccountID').AsString);
 ADOQuery.next; //Iterate to the next record in the dataset;
end;

==
Instead of that "Showmesage" line you could do something like this
ADOQuery1.Edit;
ADOQuery1.FieldByName('AccountID').Value :=  ADOQuery1.FieldByName('AccountID').value + 1;
ADOQuery1.Post;
...OR SOMETHINg like that
0
 

Author Comment

by:ST3VO
ID: 20055082
Thanks a million!!!

0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20055109
I notist that in a few earlier examples I didn't call Edit and post in the loop. This is important, you would get errors saying "Dataset is not in edit or insert mode" = which happens when you try change the value of a field without it being in edit or insert mode.

Also don't forget one little mistake that a lot of people make:
when saying
ADOQuery1.FieldByName('AccountID').value := '123'
--you can never say something else like
ADOQuery1.FieldByName('AccountID').asString:= '123'
or
ADOQuery1.FieldByName('AccountID').asInteger := '123'
--because those are readonly properties

only things like .value or .text etc will work.

Just thought I'd clear that up.

===

Another comment. If your database has 60 000 records like you say it does, it might make sense to run the data manipulations on the SERVER itself using a stored procedure, or doing your manipulations via SQL.
It's not serious, but doing 60 000 operations make hog network traffic or just take longer on your application unless its directly on the server itself.
Doing a stored procedure or doing what you want using SQL can be quite complicated - best you open a new question for that if you're keen - it's not very easy.


 
0
 

Author Comment

by:ST3VO
ID: 20055177
Thanks rfwoolf,

I've got the SQL sorted out.

What I need to do with the up is to create different small files from a large one and then pack and upload the created files to a server.
It will only be doing this once a day and on a designated server so it won't be a problem as far as network traffic is concerned.

Thanks again :o)

ST3VO
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Loops Section Overview
Suggested Courses
Course of the Month18 days, 7 hours left to enroll

825 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