🚀 go-pugleaf

OK, here is a test function.    Failure occurs at the first
"ADORecordSet.Exec(Function("Open"));" statement.  It actually worked only
once out of a dozen times.  When it fails, it complains about not being able
to find the JUNK table.  However, when I shut the program down and open the
new file that had been created, the table IS there and it has been populated
with the test data shown in the test SQL string SQLstatement1A.  Of course,
when I get this test working, I will refactor to the actual data model I'll
be using and into several functions.  You can see what I intend to do: get
the NDX corresponding to a given value in the lookup table, insert the value
if it isn't already there and then get the corresponding NDX, and then
construct the SQL for the insert into the second table (but I haven't
constructed the code for this last step yet).

Any ideas as to what I've done wrong?

void __fastcall TCookbookMainForm::New1Click(TObject *Sender)
{
  if(OpenDialog->Execute()) {
    if(FileExists(OpenDialog->FileName)) {
      ShowMessage("File already exists and will be recreated");
      DeleteFile(OpenDialog->FileName);
    }
    AnsiString ConnectionString("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=");
    ConnectionString += OpenDialog->FileName;
    RecipeCatalog=Variant::CreateObject("ADOX.Catalog");
    RecipeCatalog.Exec(Function("Create") << ConnectionString);
    //AnsiString SQLstatement("CREATE TABLE JUNK( NDX INTEGER NOT NULL,
NAMEVALUE VARCHAR(60) NOT NULL )");
    Connection=Variant::CreateObject("ADODB.Connection");
    Connection.Exec(Function("Open") << ConnectionString);
    AnsiString SQLstatement1("CREATE TABLE JUNK (NDX COUNTER(1, 1) NOT NULL
CONSTRAINT PRIMKEY PRIMARY KEY, NAMEVALUE VARCHAR(60) WITH COMPRESSION NOT
NULL UNIQUE)");
    AnsiString SQLstatement1A("CREATE UNIQUE INDEX FOODNAMENDX ON JUNK
(NDX)");
    AnsiString SQLstatement2("CREATE TABLE TABLE2 (NDX INTEGER NOT NULL,
COL2 VARCHAR(20) WITH COMPRESSION, CONSTRAINT FORKEY FOREIGN KEY (NDX)
REFERENCES JUNK(NDX) ON UPDATE CASCADE ON DELETE SET NULL)");
    Connection.Exec(Function("Execute") << SQLstatement1);
    Connection.Exec(Function("Execute") << SQLstatement1A);
    Connection.Exec(Function("Execute") << SQLstatement2);
    AnsiString SQLstatement3("INSERT INTO JUNK (NAMEVALUE) VALUES
('TESTNAMEDATA')");
    Connection.Exec(Function("Execute") << SQLstatement3);
    AnsiString SQLstatement4("SELECT NDX FROM JUNK WHERE
NAMEVALUE='TESTNAMEDATA'");
    //Variant RecordSet=Connection.Exec(Function("Execute") <<
SQLstatement4);
    Variant ADORecordSet;
    try {
      ADORecordSet=Variant::CreateObject("ADODB.Recordset");
    } catch (EOleSysError &err) {
      Application->MessageBox("Could not create Recordset
object",err.Message.c_str(),MB_OK);
      Application->Terminate();
      return;
    }

    if(ADORecordSet.IsNull()) {
      ShowMessage("Could not connect to ADO");
      Application->Terminate();
      return;
    }
    ADORecordSet.Exec(PropertySet("CacheSize") << 50);
    ADORecordSet.Exec(PropertySet("Source") << SQLstatement4);
    ADORecordSet.Exec(PropertySet("ActiveConnection") <<
RecipeCatalog.Exec(PropertyGet("ActiveConnection")));
    ADORecordSet.Exec(PropertySet("CursorType") << adOpenKeyset);
    ADORecordSet.Exec(PropertySet("LockType") << adLockOptimistic);
    try {
      ADORecordSet.Exec(Function("Open"));
    } catch(EOleSysError &err) {
      Application->MessageBox("Could not open -
",err.Message.c_str(),MB_OK);
      return;
    }
    Variant RC=ADORecordSet.Exec(Function("RecordCount"));
    unsigned int RecordCount=RC;
    if (RC != 1) {
      Connection.Exec(Function("Execute") << SQLstatement3);
      try {
        ADORecordSet.Exec(Function("Open"));
      } catch(EOleSysError &err) {
        Application->MessageBox("Could not open -
",err.Message.c_str(),MB_OK);
        return;
      }
    };
    Variant Fields=ADORecordSet.Exec(PropertyGet("Fields"));
    Variant NDX=Fields.Exec(PropertyGet("NDX"));
    int itmp = NDX;
    AnsiString tmp = NDX;
    ShowMessage(tmp);
    //AnsiString SQLstatement5
  }
}


Thanks,

Ted