如何在Delphi中使用SQL获取值并将其设置为变量? [英] How to get a value using SQL in Delphi and setting the value to a variable?

问题描述

我正试图从桌子上获得药品价格，但我只得到:

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;

推荐答案

您没有正确使用查询. qryPrices.SQL是SQL语句本身.这只是文字.您需要执行一些操作才能实际运行该语句. (请参见下文.)

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.)

您还已将该变量嵌入引号内，这意味着未对其进行求值，并且对(错拼)QuotedStr的函数调用也未得到评估.没有功能quaotedStr().如果您坚持使用级联SQL的糟糕想法，则需要正确执行.如果要清除然后添加，则可以只分配给SQL.Text而不是一步来完成:

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);

此外，查询将不会执行任何操作，直到您实际执行它为止.您需要使用qryPrices.Open来运行SELECT语句，或者使用qryPrices.ExecSQL来运行INSERT，UPDATE或DELETE语句.

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.

您应该摆脱立即连接SQL的想法(在习惯之前)，并学习使用参数化查询.它使数据库驱动程序可以为您处理格式，转换和引用，还可以防止SQL注入(其他人可以访问您的数据).这是应该帮助您入门的更正版本.

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;

这篇关于如何在Delphi中使用SQL获取值并将其设置为变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！