How to add field to ADO Recordset on runtime in Delphi 6?

Question

I have a little simple question about ADO recordset. I have two _RecordSet going in function and i'm comparing the difference of them.

Code is below.

    function TPT101_ANAF.DataSetKarsilastir(Eski: _Recordset; Yeni: _Recordset) :_Recordset;
    var
    varBookMark:OleVariant;
    begin
        Eski.MoveFirst;
        Yeni.MoveFirst;
        while not Yeni.EOF do
        begin
                Eski.Find('SİCİL = '''+Yeni.Fields['SİCİL'].Value+'''',0, adSearchForward, adBookmarkFirst);
                If (Eski.BOF = True) OR (Eski.EOF = True) Then
                begin
                PT101FDM.EXCELSET.Append;
                **/* i want to add differences to EXCELSET (From Yeni RecordSet)
                /* but the disconnected ADODataset error raising.**  
                PT101FDM.EXCELSET.Insert;
            end;
            Yeni.MoveNext;
    end;
    Result := PT101FDM.EXCELSET.Recordset;

end;

What should i do for create a new RecordSet and write the data in it.

Note: EXCELSET.Recordset is going an another function that makes an excel sheet so it has no such thing with database.

Answer 1

I think it's easier to do this if you use a pre-existing spreadsheet that doesn't contain the worksheet you want to use to save your data. Then, you can construct a Sql statement to create a table with the structure you want and set the CommandText of a TAdoCommand to that, and just call TAdoCommand.Execute on it, to add the worksheet to your spreadsheet. (Obviously, it would be easier just to add the worksheet manually in Excel, but I'm assuming you don't want to do that, for whatever reason, and anyway it's fun/useful to work out how to do things like this "the hard way").

Below is a minimal project to create a new table in an existing workbook, which I'm hoping will be enough to get you going.

I found that trickiest bit to get it working was to get the TAdoConnectionString right. In particular, in the AdoConnectionString builder in the IDE, you need to go to the All tab and enter the appropriate value in the Extended Properties entry, in my case 'Excel 8.0'.

Beware that (in D7 at any rate):

a) the Extended Properties entry tends to get blanked if you make subsequent changes in the IDE to datasets which are connected to it and

b) in the IDE, the TAdoCommand somehow gets a Parameter object created which needs to be deleted before compiling and running the program.

Update I'm assuming that once you've created the new table/worksheet, you'll be able to populate it using a standard Delphi Ado component of your choice, but for completeness I've updated the code below to show a way of doing it.

Code

  TForm1 = class(TForm)
    [...]
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    ListBox1: TListBox;
    ADOCommand1: TADOCommand;
    Button1: TButton;
    [...]
const
  scSheetName = 'test';
  scCreateSheet = 'create table %s (id  integer, name char(80))';
  scSelect = 'select * from [%s]';

procedure TForm1.FormDestroy(Sender: TObject);
begin
  AdoQuery1.Close;
  AdoQuery2.Close;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  if ListBox1.Items.IndexOf(scSheetName)  < 0 then begin
    AdoCommand1.CommandText := Format(scCreateSheet, [scSheetName]);
    AdoCommand1.Execute;
  end;
  AdoQuery2.SQL.Text := Format(scSelect, [scSheetName]);
  if AdoQuery2.Active then
    AdoQuery2.Close;
  AdoQuery2.Open;
  if AdoQuery2.RecordCount = 0 then begin
    AdoQuery2.InsertRecord([1, 'Name1']);
    AdoQuery2.InsertRecord([2, 'Name2']);
  end;
  GetTables;
end;

procedure TForm1.GetTables;
begin
  AdoConnection1.GetTableNames(ListBox1.Items,True);
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  AdoConnection1.Connected := True;
  AdoQuery1.Open; // this just selects whatever is on the first worksheet of the spreadsheet
  GetTables;
end;

Dfm

object ADOConnection1: TADOConnection
  ConnectionString =
    'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\aa' +
    'ad7\Ado\Excel\Table1.xls;Mode=Share Deny None;Extended Propertie' +
    's=Excel 8.0;Persist Security Info=False;Jet OLEDB:System databas' +
    'e="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";J' +
    'et OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OL' +
    'EDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions' +
    '=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Da' +
    'tabase=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don''t Co' +
    'py Locale on Compact=False;Jet OLEDB:Compact Without Replica Rep' +
    'air=False;Jet OLEDB:SFP=False'
  LoginPrompt = False
  Provider = 'Microsoft.Jet.OLEDB.4.0'
  Left = 16
  Top = 8
end
object ADOQuery1: TADOQuery
  Connection = ADOConnection1
  Parameters = <>
  SQL.Strings = (
    'select * from [sheet1$]')
  Left = 48
  Top = 16
end
object ADOCommand1: TADOCommand
  CommandText = 'create table test (id  integer,'#13#10'name char(80)'#13#10')'
  Connection = ADOConnection1
  Parameters = <>
  Left = 152
  Top = 16
end
object DataSource2: TDataSource
  Left = 184
  Top = 16
end
object ADOQuery2: TADOQuery
  Connection = ADOConnection1
  Parameters = <>
  SQL.Strings = (
    'select * from [test]')
  Left = 40
  Top = 288
end