Syntax error. in query expression -Delphi

Question

I have following query and face error and i am Using XE8 with MS Access

Syntax error. in query expression 'select CCarID from tblcar where Car = (LX008)'

Procedure TFNewCarAct.BtnSaveClick(Sender: TObject);
begin
adoQueryCCA.Close();
adoQueryCCA.SQL.Clear;
adoQueryCCA.SQL.Add('INSERT INTO tblcaractivity ([CCarID],[Date],[Millage],[SerRcd],[EOType],[EOQunt],[AirFil],[GOil])');
adoQueryCCA.SQL.Add('values (select CCarID from tblcar where Car = ('+ComboBox2.Text+'))');
adoQueryCCA.SQL.Add('VALUES(:Date,:Millage,:SerRcd,:EOType,:EOQunt,:AirFil,:GOil)');
adoQueryCCA.Parameters.ParamByName('Date').Value:= Edit6.Text;
adoQueryCCA.Parameters.ParamByName('Millage').Value:= Edit1.Text;
adoQueryCCA.Parameters.ParamByName('SerRcd').Value:= memo1.Text;
adoQueryCCA.Parameters.ParamByName('EOType').Value:= Edit2.Text;
adoQueryCCA.Parameters.ParamByName('EOQunt').Value:= Edit3.Text;
adoQueryCCA.Parameters.ParamByName('AirFil').Value:= Edit4.Text;
adoQueryCCA.Parameters.ParamByName('GOil').Value:= Edit5.Text;
adoQueryCCA.ExecSQL;
ShowMessage('Done');
end;

Update:

procedure TFNewCarAct.FromShow(Sender: TObject);
begin
   ADOQueryCT.Open;
   while Not ADOQueryCT.Eof do
   begin
      ComboBox1.Items.Add(ADOQueryCT.FieldByName('Name').AsString);
      ADOQueryCT.Next;
   end;
   ADOQueryCT.Close;
   if ComboBox1.Items.Count > 0 then ComboBox1.ItemIndex := 0;
   end;

procedure TFNewCarAct.OnComboBox1Change(Sender: TObject);
begin
 ComboBox2.Items.BeginUpdate;
   try
      ComboBox2.Clear;
      ADOQueryCC.Parameters.ParamByName('Name').Value := ComboBox1.Text;
      ADOQueryCC.Open;
      while Not ADOQueryCC.Eof do
      begin
         ComboBox2.Items.AddObject(ADOQueryCC.FieldByName('Car').AsString, '');
         ADOQueryCC.Next;
      end;
      ADOQueryCC.Close;
      if ComboBox2.Items.Count > 0 then ComboBox2.ItemIndex := 0;
   finally
      ComboBox2.Items.EndUpdate;
   end;
end;

The Car in the comboBox2 acquire from the tblecar and want to save the FK in the tblcaractivity table.

The suggestion provides by the Victoria now cause "Unspecified error".

Can you help how i modify my code to save FK in tblcaractivity table.

remove `values`. correct syntax is `insert into ... select ...`. BTW: Add the db tag which you are using. — Jens, Aug 07 '17 at 08:55
You must escape the string value. You can rephrase the query and use parameter. Or, even better, store the car ID into the combo box list data object and simply use it as an integer parameter. — Victoria, Aug 07 '17 at 09:04
is that will be correct statement adoQueryCCA.SQL.Add('INSERT INTO tblcaractivity ([CCarID])(select CCarID from tblcar where Car = ('+ComboBox2.Text+'))'); — Munir, Aug 07 '17 at 09:07
`'VALUES ((SELECT CCarID FROM tblcar WHERE Car = ' + QuotedStr(ComboBox2.Text) + '), :Date...)'` might work. But I would personally store the IDs in the combo box (if ever used visual control as a storage). — Victoria, Aug 07 '17 at 09:09
@Victoria "Unspecified error". thank you for the help but it didn't work. can you guide me how to store ID in combobox because i can with DBlookupcomboBox but have no idea about normal combobox. — Munir, Aug 07 '17 at 09:29
"can you guide me ..." That is not the q you originally asked and is not suitable for a reply in a comment. Please post a new q if you want an answer. — MartynA, Aug 07 '17 at 09:59
your query is wrong. Either you have `insert into (fields) select ...` or you have `insert into (fields) values (:parametes...)` but not both — GuidoG, Aug 07 '17 at 10:45
@GuidoG then how should i get FK to store into tblecaractivity table — Munir, Aug 07 '17 at 10:56
I dont know your table structure and this is not your question. But invalid sql will not solve anything — GuidoG, Aug 07 '17 at 14:43

score 0 · Accepted Answer · answered Aug 07 '17 at 15:44

Give a try;

Procedure TFNewCarAct.BtnSaveClick(Sender: TObject);
begin
adoQueryCCA.Close();
adoQueryCCA.SQL.Clear;
adoQueryCCA.SQL.Add('INSERT INTO tblcaractivity ([CCarID],[Date],[Millage],[SerRcd],[EOType],[EOQunt],[AirFil],[GOil])');
adoQueryCCA.SQL.Add('VALUES(:CCarID,:Date,:Millage,:SerRcd,:EOType,:EOQunt,:AirFil,:GOil)');
adoQueryCCA.Parameters.ParamByName('CCarID').Value:= Integer(ComboBox2.Items.Objects[ComboBox2.ItemIndex]);
adoQueryCCA.Parameters.ParamByName('Date').Value:= Edit6.Text;
adoQueryCCA.Parameters.ParamByName('Millage').Value:= Edit1.Text;
adoQueryCCA.Parameters.ParamByName('SerRcd').Value:= memo1.Text;
adoQueryCCA.Parameters.ParamByName('EOType').Value:= Edit2.Text;
adoQueryCCA.Parameters.ParamByName('EOQunt').Value:= Edit3.Text;
adoQueryCCA.Parameters.ParamByName('AirFil').Value:= Edit4.Text;
adoQueryCCA.Parameters.ParamByName('GOil').Value:= Edit5.Text;
adoQueryCCA.ExecSQL;
ShowMessage('Done');
end;

procedure TFNewCarAct.FromShow(Sender: TObject);
begin
   ADOQueryCT.Open;
   while Not ADOQueryCT.Eof do
   begin
      ComboBox1.Items.Add(ADOQueryCT.FieldByName('Name').AsString);
      ADOQueryCT.Next;
   end;
   ADOQueryCT.Close;
   if ComboBox1.Items.Count > 0 then ComboBox1.ItemIndex := 0;
   end;

procedure TFNewCarAct.OnComboBox1Change(Sender: TObject);
begin
 ComboBox2.Items.BeginUpdate;
   try
      ComboBox2.Clear;
      ADOQueryCC.Parameters.ParamByName('Name').Value := ComboBox1.Text;
      ADOQueryCC.Open;
      while Not ADOQueryCC.Eof do
      begin
         ComboBox2.Items.AddObject(ADOQueryCC.FieldByName('Car').AsString, TObject(ADOQueryCC.FieldByName('CCarID').AsInteger));
         ADOQueryCC.Next;
      end;
      ADOQueryCC.Close;
      if ComboBox2.Items.Count > 0 then ComboBox2.ItemIndex := 0;
   finally
      ComboBox2.Items.EndUpdate;
   end;
end;

Syntax error. in query expression -Delphi

1 Answers1