Variables in a SELECT..FROM...WHERE statement

[BillKilgore]

I'm trying to call up a vendor number into a DBLookupComboBox to go with the name selected in an adjacent DBLookupComboBox. That box is associated with an
ADOTable.

The vendor number DBLookupComboBox and its TDataSource are both associated with an ADOQuery.

The code I'm using is as follows;

Procedure TfmLogInventoryInput.DBCBTryVenNumClick(Sender: TObject);
Var loc_vendor_number,loc_vendor_name : string;
Begin
loc_vendor_number := EdVenNum.Text;
loc_vendor_name := DBCBVendor.Text;
with ADOQuery1 do
begin
close;
SQL.clear;
SQL.Add('SELECT VendorNumber FROM Vendor WHERE LastName = loc_vendor_name');
Active := True;
open;
loc_vendor_number :=FieldByName('VendorNumber').AsString;
EdVenNum.Text := loc_vendor_number;
SQL.Clear;
close;
end;
End;

Trying to run this results in an error saying 'loc_vendor_name has no default value.'

However, if I relace WHERE LastName = loc_vendor_name with WHERE LastName = "Jones" it works fine, though it selects the wrong VendorNumber possibly due to cursor movement between 'Select' and 'FieldByName' execution.

I've gone through the archives and found an example that led me to try, WHERE LastName = '+loc_vendor_name+' It also encountered the above error.

Is Delphi OK with a variable in a where statement?

 

[towerbase]

You need the SQL statement to look like:

SQL.Add('SELECT VendorNumber FROM Vendor WHERE LastName = ' + QuotedStr(loc_vendor_name) );

QuotedStr is a good function to use as it copes with situations where your variable might itself contain a quote such as Sean O'Callaghan

Alternatively, you could use the Params property of TADOQuery.

Andrew
Hampshire, UK

 

[BillKilgore]

Thank you Andrew. I've just used it with no problems. It also selected the correct number. I still can't figure that one.

Anyway, thanks again, Bill Kilgore

 

[whosrdaddy]

the problem is that you are using the variables inside the string so delphi isn't seeing the loc_vendor_name part as a variable but as an ordinairy string




--------------------------------------
What You See Is What You Get