4 Replies Latest reply on Jun 8, 2009 7:18 AM by Gary Clutterbuck

    Help required with Custom Transaction

    Gary Clutterbuck


      I'm not sure if this is possible or not, but I'd be very grateful for any assistance anyone can offer.


      I wish to have a couple of drop down form fields that read from a table After selecting a choice in the drop down fields, it uses the data selected in the form fields to run a query that will empty the existing table, and insert new records.  Although I intend to do this on multiple fields, I'm just trying it on a single fields at the moment.


      I am using a Cusatom Transaction, as I believe this will allow me to enter SQL that will perform more than one task.  Is my understanding of this correct?


      The following code attempts to delete any existing records in the table, then insert the new records.  However, an error appear when I clcik the button to insert the record.  It seems as though the SQL doesn't like deleting and inserting consecutively in the same custom transaction.


      Is this possible, and if so, can anyone advise me how to achieve this.  My current SQL code in the Transaction is:-


      DELETE TempAspirationalRole.* FROM TempAspirationalRole ;

      INSERT   INTO  TempAspirationalRole
      SELECT TblRoles.RoleProfile_ID, TblRoles.RoleName, TblCourses.CourseName, TblRoleCourses.Requirement
      FROM (TblRoles INNER JOIN TblRoleCourses ON TblRoles.RoleProfile_ID = TblRoleCourses.RoleProfile_ID) INNER JOIN TblCourses ON TblRoleCourses.CourseRef = TblCourses.CourseRef
      WHERE (((TblRoles.RoleProfile_ID)={AspirationalRoleID})) ;


      The above will work correctly in MySQL if I substitute the {AspirationalRoleID} for an actual record no.


      Thanks for your time,


        • 1. Re: Help required with Custom Transaction
          Purple Edge

          Your codes not in mysql, it's in a web page, so you need to do a few extra things.


          Go to the php website and look up how to execute a sql statement, or look at the code that DW uses to execute a mysql statement when getting a recordset.

          • 2. Re: Help required with Custom Transaction
            Gary Clutterbuck Level 1


            Thanks for your help - but I'm still having problems.


            Ultimately, I would like my page to:-

            Delete all records in 2 separate tables (Table A and Table B)

            Display a form with two drop down fields in it.  The fields would allow the user to select information from the same recordset (i.e. 2 records from the same recordset)

            Record 1 would be entered into Table A

            Record 2 would be entered into Table B

            A further query would compare Table A and Table B and display the differences (unmatched) in other (previously undisplayed fields) from the Recordset.


            Is this possible from a single page?


            Any step by step guides would be extremely helpful.  I'm really struggling with this and have an urgent deadline to meet.


            Thank you,


            • 3. Re: Help required with Custom Transaction
              Purple Edge Level 1

              Ultimately, but start with something simple...





              //start My_ Delete
              function My_Delete(&$tNG) {


              //Get the current values from the form
              $threadid = $tNG->getColumnValue('NbForumMessageID');

              $query = "DELETE FROM messagesubscribe WHERE threadID = '" . $threadid ."'" ;

              $result = mysql_query($query);
              if (!$result) {
                   $message  = 'Invalid query: ' . mysql_error() . "\n\n";
                   $message .=  'Query String: ' . $query;
                $message = nl2br($message);



              // repeat as necessary for other mysql queries


              //end My_trigger

              • 4. Re: Help required with Custom Transaction
                Gary Clutterbuck Level 1

                Thank you for your help with this, and sorry I have not been able to reply sooner.


                Although I found an alternative solution to meet the deadline, the solution provided above is much better.  Thanks again,