soft delete yielding system error when I update the bit field

crcucb · September 5

I am utilizing the inline example (with a trash can icon on each row) and adding logic to perform a soft delete on click. When I click on delete, I get a: A system error has occurred (More information).

maintable.on('click', 'tbody td.row-remove', function (e) {
  maineditor
        .edit( maintable.rows( {selected: true } ).indexes(), false )
        var row = maintable.rows({ selected: true }).indexes();
        //.val( 'ColoredGoals.Inactive', 0 )
        //.submit();
                 maineditor
          .hide(maineditor.fields())
          .one('close', function () {
              setTimeout(function () {
                  // Wait for animation
                  maineditor.show(maineditor.fields());
              }, 500);
          })
          .edit(row, {
              title: 'Delete',
              message:
                  row.length === 1
                      ? 'Are you sure you wish to delete this row?'
                      : 'Are you sure you wish to delete these ' +
                        row.length +
                        ' rows',
              buttons: 'Delete'
          })
          .val('ColoredGoals.Inactive',1);

});

I see 3 warnings in my server logs:

2025-09-05 20:28:02 PHP Warning: foreach() argument must be of type array|object, null given in C:\ClientSites\da-pool.com\jyorkejudge4.org\php\lib\Editor.php on line 1050 php_error
2025-09-05 20:28:02 PHP Warning: foreach() argument must be of type array|object, null given in C:\ClientSites\da-pool.com\jyorkejudge4.org\php\lib\Editor.php on line 845 php_error
2025-09-05 20:28:02 PHP Warning: foreach() argument must be of type array|object, null given in C:\ClientSites\da-pool.com\jyorkejudge4.org\php\lib\Editor.php on line 1074 php_error

ColoredGoals.Inactive is defined in the editor set to hidden, it's also defined in the datatable with the column set to visible. It's a MS Sql Bit value.

When the field is not hidden, I can edit it and set to a 1;

allan · September 5

Line 1050 of Editor.php isn't a foreach statement. What version of the Editor PHP libraries are you using? I'd suggest updating to the latest version. It might or might not solve the issue, but it is a good starting point!

Allan

allan · September 5

Gah - I was looking at the unreleased version, sorry! It is a foreach in the latest release version.

An error there suggests the data hasn't been submitted. Can you show me the PHP controller you are using? Are you passing in $_POST to the ->process() method?

Allan

crcucb · September 5

Post, is this what you wanted to see?

ajax: {
url: './php/coloredgoals' + sfilename + '.php',
type: 'POST',
data: {
pageaid: pPageAID,
useraid: pUserAID,
}
},

allan · September 6

That's certainly useful, thank you. But could you show me the file PHP file as well? A link to the page would be really useful too, so I can see what is going on at the client-side.

Allan

crcucb · September 6

Thank you,

here is a link (you can click on any of the trash icons to delete)
https://jyorkejudge.org/coloredgoals_test2.php

I believe it has to do with the selector. It seems to work if I select the row prior to clicking on the trash can icon for the row. Do I need to add code so when the trash icon is clicked it selects the current row before proceeding?

here is the server side php:

<?php
session_start();

error_reporting(E_ALL);
ini_set('display_errors', 'On');
include_once 'php_functions.php';
include_once( "lib/config.php" );

$instanceDateTime = new DateTime();
$instanceDateTime->setTimezone(new DateTimeZone('America/New_York'));


// DataTables PHP library and database connection
include( "lib/DataTables.php" );
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;
    
    
    
  $pageAID = 0;

  
    if ( isset($_POST['pageaid']) ) {
        $pageAID =  $_POST['pageaid'];
         //log_message("found _POST['pageaid']=" . $pageAID , '../logs/debug.log', 'DEBUG', $_SERVER['PHP_SELF'], True);

    
    };
    if ( isset($_POST['useraid']) ) {
        $userAID =  $_POST['useraid'];
        // log_message("found _POST['useraid']=" . $userAID , '../logs/debug.log', 'DEBUG', $_SERVER['PHP_SELF'], True);

    
    };
    
    if ($pageAID ==0 ) {
          echo json_encode( [ "data" => [] ] );
}
else {
    
   
    
/*
 * Example PHP implementation used for the join.html example
 */
 //log_message('AddressAID found in POST =' . $_POST['AddressAID'], '../logs/debug.log', 'DEBUG', $_SERVER['PHP_SELF'], false);
 Editor::inst( $db, 'ColoredGoals' , 'ColoredGoalAID')
    ->field( 
         Field::inst( 'ColoredGoals.ColoredGoalAID' ),
      Field::inst( 'ColoredGoals.UserAID' )
        ->options(
                Options::inst()
                    ->table('view_jyUsers')
                    ->value('UserAID')
                    ->label('UserFullName')
            )
        ->validator(
                        Validate::dbValues(),
                        ValidateOptions::inst()->allowEmpty(true)
                    ),     
      Field::inst( 'view_jyUsers.UserFullName' ),
      Field::inst( 'ColoredGoals.PageAID' )
              ->options(
                Options::inst()
                    ->table('Pages')
                    ->value('PageAID')
                    ->label('Page')
                    ->where( function ( $q ) {
                            $q->where( 'UsedColoredGoals', 1); 
                            $q->where( 'PageInactive', 0); 
                                } )

            )
        ->validator(
                        Validate::dbValues(),
                        ValidateOptions::inst()->allowEmpty(true)
                    ),     
      Field::inst( 'Pages.Page' ),
      
      Field::inst( 'ColoredGoals.ColorAID' )
        ->setFormatter( Format::ifEmpty( null ) )
       ->options(
                Options::inst()
                    ->table('Colors')
                    ->value('ColorAID')
                    ->label('ColorName')
                    ->where( function ( $q ) {
                            $q->where( 'Inactive', 0); 
                                } )
            )
        ->validator(
                        Validate::dbValues(),
                        ValidateOptions::inst()->allowEmpty(true)
                    ),
      Field::inst( 'Colors.ColorName' ),
      
      Field::inst( 'ColoredGoals.ColoredGoalTypeAID' )
            ->setFormatter( Format::ifEmpty( null ) )
            ->options(
                Options::inst()
                    ->table('ColoredGoalTypes')
                    ->value('ColoredGoalTypeAID')
                    ->label('ColoredGoalType')
                    ->where( function ( $q ) {
                            $q->where( 'Inactive', 0); 
                                } )
            )
            ->validator(
                        Validate::dbValues(),
                        ValidateOptions::inst()->allowEmpty(true)
                    ),
        Field::inst('ColoredGoalTypes.ColoredGoalType'),
      
      
      
      Field::inst( 'ColoredGoals.ActAID' )
      ->setFormatter( Format::ifEmpty( null ) )
      ->options(
                Options::inst()
                    ->table('Act')
                    ->value('ActAID')
                    ->label('ActName')
                    ->where( function ( $q ) {
                            $q->where( 'ActInactive', 0); 
                            $q->where( 'UsedForColoredGoals', 1); 
                                } )
            )
          //  ->validator(Validate::required())
        ->validator(
                        Validate::dbValues(),
                        ValidateOptions::inst()->allowEmpty(true)
                    ),
       // >validator(Validate::dbValues()),
        Field::inst('Act.ActName'),

      
      
      Field::inst( 'ColoredGoals.OperandAID' )
      ->setFormatter( Format::ifEmpty( null ) )
        ->options(
                Options::inst()
                    ->table('Operands')
                    ->value('OperandAID')
                    ->label('Operand')
            )
          //  ->validator(Validate::required())
                    ->validator(
                        Validate::dbValues(),
                        ValidateOptions::inst()->allowEmpty(true)
                    ),
        Field::inst('Operands.Operand'),
      
      
   

      Field::inst( 'ColoredGoals.Inactive' )
      ->setFormatter( Format::ifEmpty( 0) ),
      Field::inst( 'ColoredGoals.MinValue' )
         ->setFormatter( Format::ifEmpty( null ) ),
      Field::inst( 'ColoredGoals.TempInactive' )  
      ->setFormatter( Format::ifEmpty( null ) )  
      
      
      
      
      
      
         ->options( Options::inst()
             ->order( 'ColoredGoals.ColorAID, ColoredGoals.ColoredGoalTypeAID' )  )
     
          


    )   
     ->where( 'ColoredGoals.UserAID', $_SESSION['jyorkejudge']['useraid'] , '=')
     ->where( 'ColoredGoals.Inactive', 0 , '=')
     ->where( 'ColoredGoals.PageAID', $pageAID , '=')
   ->leftJoin('(SELECT [ColoredGoalTypeAID] ,[ColoredGoalType] FROM [ColoredGoalTypes] where [Inactive]=0) as ColoredGoalTypes', 'ColoredGoalTypes.ColoredGoalTypeAID', '=', 'ColoredGoals.ColoredGoalTypeAID')

    
        ->leftJoin('view_jyUsers', 'view_jyUsers.UserAID', '=', 'ColoredGoals.UserAID')
        ->leftJoin('(SELECT [PageAID] ,[Page] FROM [Pages] where [UsedColoredGoals]=1 and PageInactive=0) as Pages', 'Pages.PageAID', '=', 'ColoredGoals.PageAID')
    
        
      ->leftJoin('(SELECT [ColorAID],[ColorName] FROM [Colors] where Inactive=0) as Colors', 'Colors.ColorAID', '=', 'ColoredGoals.ColorAID')
        ->leftJoin('(SELECT * FROM [Act] where [ActInactive]=0 and [UsedForColoredGoals]=1) as Act', 'Act.ActAID', '=', 'ColoredGoals.ActAID')  
        

        ->leftJoin('Operands', 'Operands.OperandAID', '=', 'ColoredGoals.OperandAID') 
        
     ->on( 'preRemove', function ($editor, $id, &$values ) {
        // Disallow all delete actions since data cannot be removed completely
        global $instanceDateTime;

        return false;
     } )   
      ->on( 'postCreate', function ( $editor, $id, &$values, &$row ) {
        global $instanceDateTime;
        logChange( $editor->db(), 'create', $id, $instanceDateTime, $values, $_SERVER['PHP_SELF']  );
    } )
    ->on( 'postEdit', function ( $editor, $id, &$values, &$row ) {
        global $instanceDateTime;
        logChange( $editor->db(), 'edit', $id, $instanceDateTime, $values, $_SERVER['PHP_SELF']  );
    } )
    ->on( 'postRemove', function ( $editor, &$id, &$values ) {
        global $instanceDateTime;
        logChange( $editor->db(), 'delete', $id, $instanceDateTime, $values, $_SERVER['PHP_SELF']   );
    } )
     
     ->debug(true) // Add this line
    ->process($_POST)
    ->json();

}

allan · September 7

Many thanks. I don't immediately see what is going wrong from the code, so I thought I'd take a look at the page, but on load I get:

DataTables warning: table id=ColoredGoals - An SQL error occurred: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The SELECT permission was denied on the object 'Operands', database 'crcole_dapool2', schema 'crcole_crc'.

Allan

crcucb · September 8

It may have been when I was making some updates. I just checked, and the page is working. The issue appears to be related to clicking the delete icon for a row without first selecting the row.

soft delete yielding system error when I update the bit field

soft delete yielding system error when I update the bit field

Answers

Howdy, Stranger!

Categories

DataTables

soft delete yielding system error when I update the bit field

soft delete yielding system error when I update the bit field

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables