Database Driven Auto Complete Text Box

[Karthik]

I have a select box to pick my state. I have a Database Driven Auto Complete Text Box that only needs to display cities in the state selected. I bound a field to the Choices Selection criteria on the text box. I am returning control to the program when the value in the select box changes. But the auto complete text box doesnt display any records when I do this. Can you please help.

Thanks,
Karthik.

[David]

Can you let me know an (exact) example of the text you are populating into the field bound to the "choices selection criteria" for the auto complete box? The data should be formatted as an SQL WHERE clause, minus the word 'WHERE'.

If the syntax is not correct, or there is some other mistake, the SQL query can be invalidated, causing the problem.

Let me know and we can investigate further.

[David]

Thanks for sending the field information offline -- I see the trouble.

You are putting a field name (defined in the display file DDS) into the SQL query, where it has no meaning. This invalidates the SQL query. What you want to do instead is append the value of your display file field into the "selection criteria" field.

Keep in mind that what you are doing with the "selection criteria" is building an SQL WHERE clause.

So, if you have something like this:

RPG CODE

CRITERIA = 'MYDBFIELD = MYDSPFFIELD';

You are creating an invalid SQL query since your display file field has no meaning in the SQL query.

Instead, you want to append the DSPF field value into the selection criteria field like this:

RPG CODE

CRITERIA = 'MYDBFIELD = ''' + %TrimR(MYDSPFFIELD) + '''';

This gives you a valid WHERE clause for the SQL query.