troubleshooting Question

Delphi MySql insert query from mdb read results

Avatar of TG-Steve
TG-Steve asked on
Delphi
4 Comments2 Solutions1213 ViewsLast Modified:
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;

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;

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;

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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros