Form-Based Filter Problem

[mexashaggy]

I have a form with a table listing all the data from my main database table. I also have 2 list boxes, that retrieve their information from 2 other tables, that are linked to 2 fields in the main table. I use the list boxes to perform a form-based filter. This works the majority of the time, but I get an error when I choose 1 specific option from the list box. The error I receive is this:

Code: Select all   Expand viewCollapse view

The data content could not be updated

Syntax error in SQL expression

It gives me the option for "OK" or "More". If I click "More" I get another window that has Details and an error list, with 4 errors on the list:

1st Error:

Code: Select all   Expand viewCollapse view

The data content could not be updated

2nd Error:

Code: Select all   Expand viewCollapse view

SQL Status: HY000
Error code: 1000

Syntax error in SQL expression

3rd Error:

Code: Select all   Expand viewCollapse view

SQL Status: HY000
Error code: 1000

SELECT * FROM `ip_inventory_v2`.`IP_ADDRESS` WHERE ( )

4th Error:

Code: Select all   Expand viewCollapse view

SQL Status: HY000
Error code: 1000

syntax error, unexpected ')', expecting BETWEEN or IN or SQL_TOKEN_LIKE

I can choose any other option in the list box and it works fine, but this particular one throws up an error, no matter which OS I'm running base from. Oh, I forgot to mention I'm connecting to a mysql server (5.0.51a) running on Ubuntu 8.04.3. Any help would be appreciated.

mex

[RPG]

Hello

What is the query who give the problem?
What is the value what give the problem?

I think the value in the where part give the problem.

Romke

[mexashaggy]

I am not entirely sure how base forms the query from the selections in the list box, but watching the mysql log during a successful filter, it shows this:

Code: Select all   Expand viewCollapse view

SELECT * FROM `ip_inventory_v2`.`IP_ADDRESS` WHERE ( `VLAN` = 'MISC' ) ORDER BY `IP_ADDRESS`.`IP Address`

Here are is the list of values from my VLAN table

Code: Select all   Expand viewCollapse view

+-----------------+-------------+
| VLAN            | DESCRIPTION |
+-----------------+-------------+
| XXXXXXX_VLAN001 | NULL        |
| XXXXXXX_VLAN011 | NULL        |
| XXXXXXX_VLAN020 | NULL        |
| XXXXXXX_VLAN025 | NULL        |
| XXXXXXX_VLAN030 | NULL        |
| XXXXXXX_VLAN060 | NULL        |
| XXXXXXX_VLAN080 | NULL        |
| XXXXXXX_VLAN090 | NULL        |
| XXXXXX_VLAN001  | NULL        |
| XXXXXX_VLAN025  | NULL        |
| XXXXXX_VLAN030  | NULL        |
| XXXXXX_VLAN080  | NULL        |
| XXXXX_VLAN001   | NULL        |
| XXXXX_VLAN025   | NULL        |
| XXXXX_VLAN030   | NULL        |
| XXXXX_VLAN080   | NULL        |
| VPN             | NULL        |
| PUBLIC          | NULL        |
| MISC            | NULL        |
+-----------------+-------------+

The one that is giving me issues, is PUBLIC. I select that one from the list box, and once I hit the "Apply Form-Based Filter" button, it gives the error stated before. After it gives the error, the form becomes unusable, and I have to close it, and reopen it.

[RPG]

Hello

I cannot say any thing about it. I see no errors but maybe more experience people do see more.

But have you tried to change the word PUBLIC.
Also have you tried to delete the part after order?

I'm sorry I cannot do more there I also use only HSQLDB

Romke

[mexashaggy]

I have tried changing the name. Initially, the "MISC" was "OTHER", and it was giving me the error. I thought maybe "OTHER" was a syntax keyword for mysql. So I changed it to "MISC", and the problem seemed to go away. But actually it just moved from "MISC" to "PUBLIC" instead.

I don't know how to modify what Base sends to the mysql server when doing a form-based filter, so I don't know how to remove the part after order.

Thanks for your suggestions.

mex

[mexashaggy]

One other thing that I felt I should mention, if i run the following in query design, it works fine:

Code: Select all   Expand viewCollapse view

SELECT * FROM `ip_inventory_v2`.`IP_ADDRESS` WHERE ( `VLAN` = 'PUBLIC' ) ORDER BY `IP_ADDRESS`.`IP Address`

Because of that, it makes me think that the query created when running the form-based filter is what causes the problem.

[RPG]

Hello

You wrote that you use the listbox for limiting the main table.

This can mean that the error is not in the listbox but in the mainfrom.
You say also that you filter your data how is that filter?

What see you in the filter of that form when you are in edit mode?

also maybe important is do the order statement as late as possible

Romke

[mexashaggy]

The filter is the form-based filter built in to the forms. I do not know how I can look up what is being sent to the MySQL server. I don't believe you can edit the form based filter, it just filters based on your selections.