How to insert real values into a table using SQL?

Question

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:

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

I think you were missing the difference between a column (aka field) name and a query parameter. They are not the same thing. — MartynA
– MartynA, Commented Sep 26, 2016 at 10:15
@JerryDodge Thanks for your vote of confidence. Luckily it's not for a real hospital. — Ocean Knight
– Ocean Knight, Commented Sep 27, 2016 at 7:02
@AlbertoMiola Why? It makes coding shorter and therefore quicker. — Ocean Knight
– Ocean Knight, Commented Sep 27, 2016 at 7:03

Community · Accepted Answer · 2017-05-23 12:33:14Z

9

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.

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Sep 26, 2016 at 10:09

brclz

8269 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MartynA Over a year ago

Looks like there is a FROM missing from your SELECT statement.

brclz Over a year ago

Good catch @MartynA, I did not check the rest of the code. I just edited my answer ;-)

Ocean Knight Over a year ago

@brclz Thanks for your answer but i still get the same error message but with ':Medication' instead.

brclz Over a year ago

@OceanKnight: True! The syntax was a bit wrong, its ParamByName('PARAMETER_NAME').Value without the two dots. I updated it.

Collectives™ on Stack Overflow

How to insert real values into a table using SQL?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related