how to maintain database output in combo box or dbgrid after closing TAdoconnection

Question

Hello, In this question my objective is to retrieve a database table content. populate dbGrid, close connection. If I use the following code, dbgrid or combobox are going to loose the information.

adoQry := TADOQuery.Create(self);
adoQry.Connection := adoConn;
adoQry.SQL.Add(SqlStr);
adoQry.Prepared := true;

try
  adoQry.Active := True;
except
  on e: EADOError do
 begin
   MessageDlg('Error while doing query', mtError,
              [mbOK], 0);

   Exit;
 end;
end;
for i := 0 to adoQry.RecordCount - 1 do
begin
  cmbCnty.Items.Add(adoQry.Fields[1].AsString);
   adoQry.Next
end;

FreeAndNil(adoConn);
FreeAndNil(adoQry);

In case dbGrid, I use StringGrid and it works for me. However, sometimes I would like to use dbGrid, but not sure how to keep a content with the close connection to the database (after retrieving the content, of course) Any suggestions, examples would appreciated.
Chris

Answer 1

You can populate a TClientDataSet with your Query ResultSet, and then link the TClientDataSet to the TDBGrid.

Answer 2

If I remember correctly (it a long time agoo I used it, and i can't try it from here), you can set the connection on til adoQuery to nil, and then it behaves like a disconnected dataset (like t-client dataset). But the data goes away if you close the dataset (adqQry).

In the comment it was stated that the above statement is incorrect (and I still haven't tested it). But in this Microsoft Knowlegde Base article "How To Create ADO Disconnected Recordsets" http://support.microsoft.com/kb/184397, it shows the same technique.

The same technique is also described in the Delphi about article "TOP ADO programming TIPS" http://delphi.about.com/od/beginners/l/aa021202a.htm

It it correct that setteing connection to nil in most dataset, also closes the dataset.

Answer 3

Benny is correct. You may have to use an TAdoDataSet instead of a TAdoQuery. TAdoDatasets and TClientDatasets have similar functionality. From my understanding, the TAdoQuery components were designed to help migrate a BDE app to dbGo.