Announcement

Collapse
No announcement yet.

Form based on muliple tables

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Form based on muliple tables

    Hi,

    In mysql I did create a view based on 3 tables. In SC I designed a form based on that view. When I do open the form and try to edit the data I do receive the error message: "Can not modify more than one base table through a join view "

    Do you know how to solve this?

    Thanks,

    Marco

  • #2
    Originally posted by Marcot View Post
    Hi,

    In mysql I did create a view based on 3 tables. In SC I designed a form based on that view. When I do open the form and try to edit the data I do receive the error message: "Can not modify more than one base table through a join view "

    Do you know how to solve this?

    Thanks,

    Marco
    This is actually not a SC issue, this message is caused by MySQL. The only way to solve it (afaik) is to breakup the insert on the view into 3 different inserts. The onbeforeinsert is not a valid location afaik you cannot prevent the insert on the view to be executed. So I guess you need to use the onvalidate event where you can 'replace into' instead of 'insert into' so that you are able to insert/modify data and then do an sc_redir to prevent the other events being triggered. Bit fishy though.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      @Marcot: a view is always readonly and can't update.
      Best regards: - Reinhard -

      I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

      Comment


      • #4
        @Marcot, aducom, RHS, I have the same issue. I am able to update the view while in MySql but when i attach to a form in SC it is giving that error message, please help if you get a solution., Jephta

        Comment


        • #5
          @RHS: http://dev.mysql.com/doc/refman/5.0/...atability.html
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment


          • #6
            @aducom, my view only has two tables and i am able to edit/update it in MySql. But in SC i get the message "Error
            Error updating database - Can not modify more than one base table through a join view", please help

            Comment


            • #7
              @aducom : yes ..., but not in real live (see the error message in post #1).
              Best regards: - Reinhard -

              I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

              Comment


              • #8
                It looks like SC use all the fields of the table/view in a form when applying SQL INSERT/UPDATE. When updating a view, only one underlying table is allowed. I think that why you can update in MySQL but not SC.

                User Giu taught me to avoid the SQL Insert/Update by :
                Edit Fields -> DB value (Insert) / (Update) -> Calculated by the database.

                See if it can solve your problem.

                Although it work, it's far from perfect. Like I use SQL trigger update some fields, I always need to do the above setup even the field/s not in the form.

                What is the pros to use all the fields in SQL INSERT/UPDATE ?

                Regards,

                Almond Wong

                Comment

                Working...
                X