Ocean Knight Ocean Knight - 2 months ago 7
SQL Question

How to insert real values into a table using SQL?

I'm trying to insert values into the MedicationPrices table with the following code.

procedure TForm1.btnAddMedicineClick(Sender: TObject);
var
sMedication, sQuantity : string;
rPrice : real;
begin
sMedication := InputBox('Add Medication','Please enter the medications name','');
sQuantity := InputBox('Add Medication','Please enter the the quantity','');
rPrice := StrToFloat(InputBox('Add Medication','Please enter the the price',''));

with dmHospital do
begin
qryPrices.SQL.Clear;
qryPrices.SQL.Add('INSERT INTO MedicationPrices (Medication, Quantity)');
qryPrices.SQL.Add('VALUES(' + QuotedStr(sMedication) +',' + QuotedStr(sQuantity) + ' )');
qryPrices.Parameters.ParamByName('Price').Value := rPrice;
qryPrices.ExecSQL;
qryPrices.SQL.Clear;
qryPrices.SQL.Text := 'SELECT * MedicationPrices ';
qryPrices.Open;
end;
end;


However it and a few different variations just don't work. I get:
Error message

I don't understand why it doesn't see 'Price' as it is clearly in the table.
Design view of table

Answer

You should add the parameters in your query (line with VALUES).

Then when you use the ParamByName function, it will basically replace the parameter (:Price) from the query by the value that you set (rPrice).

Example of correction:

with dmHospital do
begin
  qryPrices.SQL.Clear;
  qryPrices.SQL.Add('INSERT INTO MedicationPrices (Medication, Quantity, Price)');
  qryPrices.SQL.Add('VALUES(:Medication, :Quantity, :Price)');
  qryPrices.Parameters.ParamByName('Medication').Value := sMedication;
  qryPrices.Parameters.ParamByName('Quantity').Value := sQuantity;
  qryPrices.Parameters.ParamByName('Price').Value := rPrice;
  qryPrices.ExecSQL;
  qryPrices.SQL.Clear;
  qryPrices.SQL.Text := 'SELECT * FROM MedicationPrices ';
  qryPrices.Open;
end;

See also this Q&A about parameters in Delphi in INSERT.

Comments