Link to home
Start Free TrialLog in
Avatar of TG-Steve
TG-Steve

asked on

Delphi MySql insert query from mdb read results

Hi guys

might be a little long winded this so appologies in advance. i currently have a form with adoquery and adoconnection which query a local access mdb with the following
procedure TForm1.SearchButtonClick(Sender: TObject);
var
  Q: TADOQuery;
begin
  Memo1.Lines.Clear;
  FCurrentRef := GetUniqueRefFromPLU(StrToInt(Edit1.Text));
  if FCurrentRef > 0 then
  begin
    Memo1.Enabled := True;
    Q := TADOQuery.Create(nil);
    try
      Q.Connection := ADOConnection1
      Q.SQL.Text := Format('Select Comments, Description, Stylename, Cost, Sell from Style where UniqueRef = %d', [FCurrentRef]);
      Q.Active := True;
      Memo1.Lines.Text := Q.FieldByName('Comments').AsString;
      Edit2.Text := Q.FieldByName('Description').AsString;
      Edit3.Text := Q.FieldByName('Stylename').AsString;
      Edit4.Text := Q.FieldByName('Cost').AsString;
      Edit5.Text := Q.FieldByName('Sell').AsString;
      Q.Close; 
    finally
      FreeAndNil(Q);
    end;
  end;
end;

Open in new window


ok when i edit the memo1 on the form with data i would usually hit another button which carrys out the below
var
  Q: TADOQuery;
begin
  if Memo1.Lines.Count > 0 then
  begin
    Q := TADOQuery.Create(nil);
    try
      Q.Connection := ADOConnection1;
      Q.SQL.Add('Update Style');
      Q.SQL.Add(Format('SET Comments = %s', [QuotedStr(Memo1.Lines.Text)]));
      Q.SQL.Add(Format('WHERE UniqueRef = %d', [FCurrentRef]));
      Q.ExecSQL;
      Memo1.Enabled := False;
      Q.Close;
    finally
      FreeAndNil(Q);
    end;
  end;
end;

Open in new window


ok what i am trying to achieve is to write back to the mdb (same tables and fields as above) but just with a single digit being "1" instead of memo1 contents and the contents of memo1 i want to write to a remote mysql database so i will drop an adoconnection2,adoquery2,datasource on the form pointing at the remote mysql of which i want to insert the contents of edit1 into column 2 named "rootplu" and the contents of memo1 into 3rd column named "description" column 1 is named "id" and i want to use something like the below in order to carry this out so i can maintain a rolling id column without getting duplicates (the below is snippets from another project but would assume i can use similer idea)
ADOQuery2.SQL.Text := 'SELECT MAX(id) AS NEXTID FROM Data_Description;
  ADOQuery2.Active := True;
  if not ADOQuery2.IsEmpty then
    RollingID := ADOQuery2.FieldByname('NEXTID').AsInteger + 1
  else
    RollingID := 1;

ADOQuery2.SQL.text := ('INSERT INTO Data_Description VALUES(:id, :rootplu, :description);

while not ADOQuery1.Eof do
  begin
    ADOQuery2.Parameters.ParamByName('id').Value := RollingID;
    Inc(RollingID);
    ADOQuery2.Parameters.ParamByName('rootplu').Value := ?dont know what to example;
    ADOQuery2.Parameters.ParamByName('description').Value := ?dont know what to example;
    ADOQuery2.ExecSQL;

Open in new window


Experts on here have helped me compile the above snippets over the past and would prefer if someone could write the replacement code instead of suggesting ideas please because i think it may take me days for me to work it out and i am limited to 24hours

thank you very much in advance
ASKER CERTIFIED SOLUTION
Avatar of jimyX
jimyX

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TG-Steve
TG-Steve

ASKER

Hi JimyX

thats exaxtly what i am after. the first 2 snippets are good working parts of my application but the 3rd is bits i have took from another to try and explain what i am trying to get to. the snippet you have suggested is great but for me to create another procedure would only be partial of what is required so i need to know what goes above and below the code in your snippet. in your snippet there is a suggestion of "while not ADOQuery1.Eof do" would this be needed?
 
and i know this goes above your snippet but what goes between to make this a complete working procedure
 
ADOQuery2.SQL.Text := 'SELECT MAX(id) AS NEXTID FROM Data_Description;
  ADOQuery2.Active := True;
  if not ADOQuery2.IsEmpty then
    RollingID := ADOQuery2.FieldByname('NEXTID').AsInteger + 1
  else
    RollingID := 1;

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you guys