SQL Update query with Parameters
lazarus / 16-Databases

----------------------------------------------------------------------
fiazhnd · 2017-08-13 16:03

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_na
me],[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 · 2017-08-13 18:28

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 · 2017-08-14 06:36

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i with
adoQueryPT.SQL do begin  Add('Update tproduct Set category_id =
:category_id, product_name = :product_name,');  Add('      product_des
= :product_des, product_cost = :product_cost , default_uom =
:default_uom,');end;