[UPDATED] TDBLookupComboBox and SQLQuery

[guest48180]

I have a .fdb with two tables in it: CUSTOMERS & LOCATIONS. In FormCreate I have the TDBLookupComboBox load up the names from the CUSTOMER table. Then I want to use the selected name to bring up info regarding the customer in the LOCATIONS table and show in the DBGrid. The code I have for this is:

SQLQuery1.SQL.Text := 'select * from LOCATIONS where CUSTOMER_NAME = ' + TDBLookupComboBox.Text;

This line is also executed OnFormCreate. The first name in the TDBLookupComboBox is 'BlueStone'. And when the query is executed it comes back with an error telling me that there is no such column - BlueStone. Well, BlueStone isn't a column, it's a customer name, the text in the TDBLookupComboBox. Why am I getting this error?

Here's the code:

Code: [Select]

procedure TForm1.FormCreate(Sender: TObject);
begin
   with IBC1 do
      begin
         DatabaseName:= 'Jericho';
         HostName:= 'localhost';
         Password:= 'masterkey';
         UserName:= 'sysdba';
         Transaction:= SQLT1;
      end;
   with dblcbCustomerName do
      begin
         Datasource:= DS1;
         DataField:= 'NAME';
         KeyField:= 'NAME';
         ListField:= 'NAME';
         ListSource:= DS1;
         Sorted:= True;
      end;

   SQLQ2.SQL.Text:= ('select * from locations where customer_name = ' + dblcbCustomerName.Text);

Landslyde

[joseme]

You should enclose the parameter of the query (in this case, the parameter is dblcbCustomerName.Text) between quotes, to be taken as a string. If you do not enclose it in quotes, it is taken as a field name.

Code: [Select]

SQLQuery1.SQL.Text := 'select * from LOCATIONS where CUSTOMER_NAME = ' + QuotedStr(TDBLookupComboBox.Text);


[BigChimp]

Parameterized queries are even better.

[guest48180]

Thank you joseme for your response.

BigChimp, I don't have internet access...so this response is getting back to you sort of after the fact. After I posted my issue yesterday, I went back home and kept working with it. In the DB Tutorial 2 I recalled the use of Params and ParamByField. And, as it turned out, the first shot at it with my DBLookupComboBox.Text was all I needed. Here's my working result:

Code: [Select]

procedure TForm1.alLoadCustomerExecute(Sender: TObject);
begin
   // This loads the DBLookupComboBox
   with IBC1 do
      begin
         DatabaseName:= 'Jericho';
         HostName:= 'localhost';
         Password:= 'masterkey';
         UserName:= 'sysdba';
         Transaction:= SQLT1;
      end;
   with dblcbCustomerName do
      begin
         Datasource:= DS1;
         DataField:= 'NAME'; // Not sure if all three of these
         KeyField:= 'NAME';  // '.Field' calls are necessary.
         ListField:= 'NAME'; // Best to err on the side of caution.
         ListSource:= DS1;
         Sorted:= True;
      end;

   // Use the Customer Name from CUSTOMER Table, loaded  in dblcbCustomerName.Text,
   // to load, by Customer Name, customer info from LOCATIONS Table.
   SQLQ2.Close;
   SQLQ2.SQL.Text:= 'select * from locations where customer_name = :' + dblcbCustomerName.Text;
   SQLQ2.Params.ParamByName(dblcbCustomerName.Text).AsString:= dblcbCustomerName.Text;
   SQLQ2.Open;

   // Hide the primary key column which is the first column in our queries.
   // We can only do this once the DBGrid has created the columns.
   SQLQ2.FieldByName('LOC_ID').Required:= False;
   DBGrid1.Columns[0].Visible:=false;
end;                                 

This works.

However, it seems I have to use two connections, two transactions, two queries, and two datasources because I use two tables. Is this the only way to do it? Two of everything to access two tables of the same .fdb? Delphi uses a Table component to work with multiple tables, right?

Also, I have a DBNavigator set to the DBLookupComboBox and a DBNavigator set to my DBGrid. Thing is, I can't make changes. I can't add, delete, edit. Nothing. I've gone back and looked at the Tutorial 2 code but can't see anything different from what I'm doing. I connected it and thought that was all I had to do. Is there more? And, as you can see in my code, I have the LOC_ID set to not required when adding a record, but when I do this via the Navigator I get an error saying that it's required but not provided. Please point me in the right direction. Thank you for your help and patience.

Landslyde

[BigChimp]

Note: if you want me to look at the code,  upload the entire project source code + DDL in a new thread (zipped)  - although, as I said, I'm not really a GUI guy. If you want to, you can also send it via mail. I still have the feeling there's something wrong with your database schema.

*If* the only thing you changed is using parameterized queries, then it does not make sense that you now have to use 2 connections and 2 transactions.
The 2 sets code you posted do not give enough info to see why you would need those 2.

See the other thread on why a dblookupcombox may be a bad idea; as well as a link to a thread on what binding properties to set if you do want to use it.
This still has nothing to do with using parameterized queries or not..

Your issues with multiple datasources are probably due to the fact that you are *editing* data with your dblookupcombobox in the CUSTOMER table while you want to use it to only *display* stuff.

I'd suggest comparing the code you have now with the code before you made the change to parameterized queries. It's likely you changed more things.