We help IT Professionals succeed at work.

Delphi MySql insert query from mdb read results

TG-Steve
TG-Steve used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011
Commented:
If I get you clearly, you need to pass Edit1 and Memo1 to the parameters:
while not ADOQuery1.Eof do
  begin
    ADOQuery2.Parameters.ParamByName('id').Value := RollingID;
    Inc(RollingID);
    ADOQuery2.Parameters.ParamByName('rootplu').Value := edit1.text;
    ADOQuery2.Parameters.ParamByName('description').Value := memo1.lines.text;
    ADOQuery2.ExecSQL;
    ADOQuery1.next;
  end;

Open in new window


If I am not stressing the point you are after, please clarify more.

Author

Commented:
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

Commented:
hi
>>"while not ADOQuery1.Eof do" would this be needed?
it's not needed,

and should be this,
////ADOQuery2.SQL.Text := 'SELECT id as NEXTID from Data_Description ORDER BY id DESC LIMIT 1';
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 is gone
  else
    RollingID := 0; //// 1 is replaced

Inc(RollingID); //// increment here
ADOQuery2.SQL.text := ('INSERT INTO Data_Description VALUES(:id, :rootplu, :description);
ADOQuery2.Parameters.ParamByName('id').Value := RollingID;
ADOQuery2.Parameters.ParamByName('rootplu').Value := ?dont know what to example;
ADOQuery2.Parameters.ParamByName('description').Value := ?dont know what to example;
ADOQuery2.ExecSQL;

btw, you can change the data type of id TO Autoinc,
so when you insert it, it looks like this,
//// SELECT MAX(id) is gone ...
ADOQuery2.SQL.text := ('INSERT INTO Data_Description VALUES(:rootplu, :description);
ADOQuery2.Parameters.ParamByName('rootplu').Value := Edit1.Text;
ADOQuery2.Parameters.ParamByName('description').Value := Memo1.Text;
ADOQuery2.ExecSQL;

Author

Commented:
Thank you guys