* * *

Author Topic: SQL Update query with Parameters  (Read 315 times)

fiazhnd

  • New member
  • *
  • Posts: 27
SQL Update query with Parameters
« on: August 13, 2017, 06:03:55 pm »
I have no idea where i am wrong can anyone help me to correct it.

I am using MS access, tproduct table has two 2FK and 1PK. i try to take PK from BDEdit1 to pass to adoquery but face following error;

Syntax error in UPDATE statement.



Procedure TFUnitMaster.OnClickUpdate(Sender: TObject);
begin
if (Edit1.Text = '') or (Edit2.Text='') or (Edit3.Text='')  then
    begin
     ShowMessage('Please enter the missing data!');
    end
    else begin
    adoQueryPT.Close();
    adoQueryPT.SQL.Clear;
    adoQueryPT.SQL.Add('Update tproduct Set ([category_id],[product_name],[product_des],[product_cost],[default_uom])');
    adoQueryPT.SQL.Add('Values(:category_id,:product_name,:product_des,:product_cost,:default_uom)');
    adoQueryPT.Sql.Add('where ID=' + DBEdit1.Text);
    //adoQueryPT.Sql.Add('where ID=' + DBAdvGrid1.DataSource.DataSet.FieldByName('ID').AsString);
    adoQueryPT.Parameters.ParamByName('category_id').Value:= Integer(ComboBox1.Items.Objects[ComboBox1.ItemIndex]);
    adoQueryPT.Parameters.ParamByName('product_name').Value := Edit1.Text;
    adoQueryPT.Parameters.ParamByName('product_des').Value:= Edit3.Text;
    adoQueryPT.Parameters.ParamByName('product_cost').Value:= Edit2.Text;
    adoQueryPT.Parameters.ParamByName('default_uom').Value:= Integer(ComboBox2.Items.Objects[ComboBox2.ItemIndex]);
    adoQueryPT.ExecSQL;
    ShowMessage('Product Save Successfully');
 end;
end;

Leledumbo

  • Hero Member
  • *****
  • Posts: 7652
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: SQL Update query with Parameters
« Reply #1 on: August 13, 2017, 08:28:24 pm »
Wht if you display adoQueryPT.SQL.Text? It certainly is much easier to read (and find your syntax error) than a bunch of strings as .Add parameter.

mangakissa

  • Hero Member
  • *****
  • Posts: 710
Re: SQL Update query with Parameters
« Reply #2 on: August 14, 2017, 08:36:44 am »
If this is the way to handle updates with adoquery, it's very smart.
What you're doing is using an insert query like update. That doesn't work.
Code: Pascal  [Select]
  1. with adoQueryPT.SQL do
  2. begin
  3.   Add('Update tproduct Set category_id = :category_id, product_name = :product_name,');
  4.   Add('      product_des = :product_des, product_cost = :product_cost , default_uom = :default_uom,');
  5. end;    
  6.  
« Last Edit: August 14, 2017, 08:39:09 am by mangakissa »
Lazarus 1.64 (32b) / FPC 3.0
Windows 10

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus