We help IT Professionals succeed at work.

Loop in a DBGrid

ST3VO
ST3VO asked
on
2,293 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
Comment
Watch Question

Commented:
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

Author

Commented:
I'm using ADOConnection and ADOQuery.

Author

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

Commented:
=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

Commented:
===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;

Commented:
== 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);

Author

Commented:
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?

Commented:
==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);

Commented:
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).

Commented:
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?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
One more thing....I need to work with the actual contents of the DBGrid and not the (All) DB Records.

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

Author

Commented:
Just one thing I need. I need to GET the field text that is currently active or current.

Commented:
 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;
 
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks a million!!!

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


 

Author

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
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
Empower Your Career
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

Ask ANY Question

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

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.