CODEWITHSUNDEEP
delphidelphi-xe3
ple103
ple103

Reputation: 2110

Is this the correct way to use SQL params?

My intention:

When the user selects an artist, the discography combo box will fill up with the discographies that particular artist has.

The Code:

qDisco (TSQLQuery) is data bound to cbDisco (TComboBox) and contains the following SQL:

SELECT DISTINCT discography
FROM Album
INNER JOIN Artist 
ON Album.artist_id = Artist.id
WHERE Artist.name = :artist;

The :artist param is set by the following code:

procedure Tmain.cbArtistChange(Sender: TObject);
begin
  qDisco.ParamByName('artist').Value := cbArtist.Items[cbArtist.ItemIndex];
end;

I have ensured that the :artist param stores the value of the current item of cbArtist, but when I compile the application, cbDiscography remains unfilled.

What can I do for this to work?

Upvotes: 1

Views: 128

Answers (1)

jachguate
jachguate

Reputation: 17203

The value of any parameter is passed to the database engine when you open the query. Changing the parameter value over a active query object have no effect until you close and re-open the data set.

Mi guess is you're not doing that last step, so change your event handler method to:

procedure Tmain.cbArtistChange(Sender: TObject);
begin
  qDisco.ParamByName('artist').Value := cbArtist.Items[cbArtist.ItemIndex];
  qDisco.Close;
  qDisco.Open;
end;

As you can see, this involves a full database round-trip to retrieve all the discography records associated with the current artist.

Upvotes: 2

Related Questions