SQL Error with Master/Detail form

Applications Forms
, ,
#1

Hi All,

I am getting an error when running a master/detail form stating ‘Error while accessing the database… error in SQL syntax…’ The form has a master table and links to a related table. The linkage was done in by choosing the related form using the master/detail relationship standard master/detail procedure on the menu. I think I’ve narrowed the issue to the fact that the first record in the master table has a leading apostrophe. So I’m thinking that an escape character or something is somehow needed in the sql that is generated so that the apostrophe can be handled. If I do a WHERE clause on the Master such that this record is skipped, the master/detail form works okay.

Any thoughts on how I might a) edit the relationship in SQL, and b) what might be the correct syntax. I don’t know where to find the generated SQL and how to modify the code generation.

The database is MySql.

Thanks for any thoughts!!

Lyle

#2

Dear lyleholc,
Can you paste the whole error message in post with SQL query? Also put the database description (DESC table) for master and related tables.

#3

Also: set debug mode on to see the generated sql statements. Useually this gives you a good clue to see what’s wrong within the definition. (application -> debug : show sql statement)

#4

Dear Mlind and Albert,

Thank you for your help.

Here is what displays on the problem record.

Error

Error while accessing the database:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘hood’’ at line 1

View SQL

Here is the SQL that displayed also:

(mysqlt): SELECT ID, UID, Type, Word, link, Soundx from ipu order by ID LIMIT 0,1

Oddly enough, when it’s not used with Master/Detail, it fetches the row okay.

Is it possible to modify the select someplace before generating?

Thanks so much. Just getting started with ScriptCase.

Lyle

#5

Lyle,

There is problem with your detail form linking when you pass parameters. Make sure that your detail form has a where condition (you can use global variable also as [variable] so that parameter can be pass.) and there is any common field which has relationship between master and details form. E.g. Class(id, name, description) and Student(id, class_id, name, address) are two tables and you have form of class as master then its id should be same as class_id in students form so that whenever you select class you will have list of all students studying in that class.

#6

Hi Milind,

That’s strange because I built the master detail using the menu functions setting the relation and all. And the master and detail link fine as long as the specific record with the apostrophe is excepted such as with where ID>1.

Thanks for helping!!

Lyle

#7

Hi Guys,

I changed the master/detail sql and now it is working.

Thank you!!