Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

Help with Databases and multiple tables

I haven't done much development with databases so I need some help.

I have two Access databases.   I need to look in the registry for a certain value
sLocale := jciRegsUtil.GetRegStringValue(TOOL, 'AimVer', 'LocaleName', '');
and search my Logistics table(Warehouse database) for records with sLocale in the Area field and add these records to my Contact table (Address database).
                   
I was going to try using the BatchMove component with the batAppend, but I believe that would append the entire second table.  Is there a way to use a select statement in a query and then the BatchMove to add the records from the select statement?

 Any help/code  would be appreciated :)

0
Tammi
Asked:
Tammi
  • 2
  • 2
1 Solution
 
trex_fireCommented:
No but you can cycle through your query and insert your rows in your other table by putting another query with parameters.  Like:

MyQuery.Open;
While not MyQuery.EOf do begin
  InsertQuery.ParamByName('Param1').AsSomething := ...;
  InsertQuery.ParamByName('Param2').AsSomething := ...;
  ...
  InsertQuery.ExecSQL;
  MyQuery.Next;
end;

In InsertQuery, your SQL will look like this:

Insert into TableName
  (Field1,Field2,...)
  values (:Param1,:param2,...)

Hope it helps  ;)

T-Rex
0
 
TammiAuthor Commented:
I actually got the BatchMove function to work by specifying my query as the source.  I'm still having problems getting the variable (Param) to work.  When I run the code below, I get a error "Parameter Param1 not found".  Any ideas??

begin
      qryArea := TQuery.Create(nil);
      qryArea.DatabaseName := 'dbnWarehouse';
      Param1 := TParam.Create(nil);
      qryArea.Params.AddParam(Param1);
      qryArea.ParamByName('Param1').Value := sLocale;
      qryArea.SQL.Text := 'SELECT * FROM Logistics WHERE Area = :Param1';
      qryArea.ExecSQL;
      BtchMvBradford.Source := qryArea;
      BtchMvBradford.Execute;
      qryArea.Free;
    end;

Thanks!!

Tammi
0
 
trex_fireCommented:
Ok would be easier to put your SQL in your TQuery and then look in Params property and adjust 'Param1' properties and Open / Close your query instead of ExecSQL... or just put your SQL before accessing your Params like:

begin
     qryArea := TQuery.Create(nil);
     qryArea.DatabaseName := 'dbnWarehouse';
     Param1 := TParam.Create(nil);
     qryArea.SQL.Text := 'SELECT * FROM Logistics WHERE Area = :Param1';
     qryArea.Params.AddParam(Param1);
     qryArea.ParamByName('Param1').Value := sLocale;
     qryArea.ExecSQL;
     BtchMvBradford.Source := qryArea;
     BtchMvBradford.Execute;
     qryArea.Free;
   end;


trex_fire@hotmail.com

T-Rex

 
0
 
TammiAuthor Commented:
I ended up using the TQuery component and putting the SQL in the properties.  I had tried this previously but kept getting errors.  Discovered that I should have defined the DataSource, just the Database.  I used the following code:

begin
      qryArea.ParamByName('Param1').Value := sLocale;
      qryArea.Open;
      BtchMvBradford.Source := qryArea;
      BtchMvBradford.Execute;
      qryArea.Close;
      BtchMvBradford.Free;
    end;

Thanks for your help!!

Tammi
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now