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
Delphi

Avatar of undefined
Last Comment
TG-Steve

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
jimyX

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
systan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
TG-Steve

ASKER
Thank you guys
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes