How to get a value using SQL in Delphi and setting the value to a variable?

Question

I'm trying to get the price of medication from the table but i just get:

procedure TForm1.BuyButtonClick(Sender: TObject);
var
  iAmount : integer;
  rRate : real;
  sMedication : string;
  sRate : string;
begin
  iAmount := 0;
  sMedication := BuyCombobox.Items[BuyCombobox.ItemIndex];
  dmHospital.qryPrices.SQL.Clear;
  dmHospital.qryPrices.SQL.Add('SELECT Price(R) FROM MedicationPrices WHERE Medication = quaotedstr(sMedication)');
  sRate := dmHospital.qryPrices.SQL;
  ShowMessage(sRate);
end;

dmHospital.qryPrices.SQL is your sql text. Use dmHospital.qryPrices.Open instead. — Val Marinov
– Val Marinov, Commented Jul 1, 2016 at 12:58
Also dmHospital.qryPrices.SQL.Add('SELECT Price(R) FROM MedicationPrices WHERE Medication = quaotedstr(sMedication)'); is incorrect. Use 'SELECT Price(R) FROM MedicationPrices WHERE Medication= ' + QuotedStr(sMedication) instead. — Val Marinov
– Val Marinov, Commented Jul 1, 2016 at 13:07

Ken White · Accepted Answer · 2016-07-02 03:01:30Z

6

You're not using the query properly. qryPrices.SQL is the SQL statement itself. It's just text. You need to do something to actually run the statement. (See below.)

You've also embedded the variable inside the quotes, which means it's not being evaluated, and neither is the function call to the (misspelled) QuotedStr. There is no function quaotedStr(). If you insist on the poor idea of concatenating SQL, you need to do it properly. If you're going to clear and then add, you can just assign to SQL.Text instead to do it in one step:

dmHospital.qryPrices.SQL.Text := 'SELECT Price(R) FROM MedicationPrices WHERE Medication = ' + Quotedstr(sMedication);

Also, the query won't do anything until you actually execute it. You need to use qryPrices.Open to run a SELECT statement, or qryPrices.ExecSQL to run an INSERT, UPDATE or DELETE statement.

You should get out of the thought of concatenating SQL immediately (before you get the habit) and learn to use parameterized queries. It allows the database driver to handle the formatting and conversion and quoting for you, and it also prevents SQL injection that can give others access to your data. Here's a corrected version that should get you started.

procedure TForm1.BuyButtonClick(Sender: TObject);
var
  sMedication : string;
  sRate : string;
begin
  iAmount := 0;
  sMedication := BuyCombobox.Items[BuyCombobox.ItemIndex];
  dmHospital.qryPrices.SQL.Text := 'SELECT Price(R) FROM MedicationPrices WHERE Medication = :Medication';
  dmHospital.qryPrices.Parameters.ParamByName('Medication').Value := sMedication;
  dmHospital.qryPrices.Open;
  sRate := dmHospital.qryPrices.FieldByName('Price(R)').AsString;
  dmHospital.qryPrices.Close;
  ShowMessage(sRate);
end;

edited Jul 2, 2016 at 3:01

answered Jul 1, 2016 at 13:42

Ken White

126k15 gold badges237 silver badges476 bronze badges

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

4 Comments

GuidoG Over a year ago

should you not check if any record is returned in the dataset ?

Ken White Over a year ago

@Guido: No. If no record is returned, then reading the field will return an empty string. Not sure why you downvoted my answer, since it is correct, does things properly, and contains a full explanation of what was wrong and how to fix it.

Val Marinov Over a year ago

@GuidoG In most cases, this is not necessary. If necessary, it may be checked the result.

GuidoG Over a year ago

@KenWhite i did not downvote you im not so triggerhappy as some people we are all here to learn

Vancalar · Accepted Answer · 2016-07-01 13:39:47Z

5

You should modify Your code to actually work: My advise is to use parameters instead of QuotedStr:

dmHospital.qryPrices.SQL.Clear;
dmHospital.qryPrices.SQL.Add('SELECT Price(R) AS Rate FROM MedicationPrices WHERE   Medication = :pMedication');
dmHospital.qryPrices.Params.ParamByName('pMedication').AsString=sMedication;

(Note that in ADOQuery You'd use .Parameters instead of .Params)

dmHospital.qryPrices.Open;
sRate=dmHospital.qryPrices.FieldByName('Rate').AsString;
ShowMessage(sRate);

Regards

answered Jul 1, 2016 at 13:39

Vancalar

9731 gold badge7 silver badges16 bronze badges

Comments

GuidoG · Accepted Answer · 2016-07-01 13:29:12Z

0

Not tested it (dont have Delphi at hand here) but it should be something like this :

iAmount := 0;  
sMedication := BuyCombobox.Items[BuyCombobox.ItemIndex];  
dmHospital.qryPrices.SQL.Clear;  
dmHospital.qryPrices.SQL.Add('SELECT Price(R) as price FROM MedicationPrices WHERE Medication = ' + QuotedStr(sMedication));  

dmHospital.qryPrices.Open;
if (dmHospital.qryPrices.RecordCount = 1)
  sRate := dmHospital.qryPrices.FieldByName('price').AsString;  

ShowMessage(sRate);

answered Jul 1, 2016 at 13:29

GuidoG

12.3k6 gold badges59 silver badges102 bronze badges

3 Comments

Ken White Over a year ago

Boo for the string concatenation and use of RecordCount on a SQL dataset. (Use parameters and IsEmpty instead.) Don't teach new users bad technique; instead, teach them correctly from the start.

Cobus Kruger Over a year ago

@GuidoG RecordCount is often implemented by fetching all the records from the server. That's okay if you're planning to iterate all the records anyway, but is best avoided if you're just checking if the result set is empty (use IsEmpty) or if you're displaying the data in a grid, which would fetch it as needed. This is not a problem on small result sets, but could be when there are millions of matching records. So it's best to form the habit to use IsEmpty for cases like this.

GuidoG Over a year ago

Thanks you just thought me something new.

Collectives™ on Stack Overflow

How to get a value using SQL in Delphi and setting the value to a variable?

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related