Error when editing field in table, error talks about view in different column, weird

javismiles

Hi, I have a table with 10 fields, one of them gets the data from a view in the DB,
and when I now try to edit any other field of that table, I get this error:
An SQL error occurred: SQLSTATE[HY000]: General error: 1471 The target table avgsol of the INSERT is not insertable-into

which is absurd because I am not trying to edit the field of the view avgsol, but now I cannot edit any other field!!!

so when I get the data for a field from a view, I cannot edit anymore any field in the table?

help please

javismiles

very annoying,
after getting one of the columns through a view, i simply cannot select any column as I always get:
An SQL error occurred: SQLSTATE[HY000]: General error: 1471 The target table avgsol of the INSERT is not insertable-into

even though I am not selecting the column of the view!

javismiles

I should specify that I have this code to allow for inline editing, do I need to change anything here to allow for columns with views not interfering?

    $('#'+obj.table).on( 'click', 'tbody td:not(:first-child)', function (e) {
    eArray[index].inline( thetable.cell(this).index(), {onBlur: 'submit'})})

javismiles

// $('#'+obj.table).on( 'click', 'tbody td:not(:first-child)', function (e) {
// eArray[index].inline( thetable.cell(this).index(), {onBlur: 'submit'})})

javismiles

so maybe I need to exclude somehow the column that takes info from a mysql view from the editing inserts, how do i do that? i definitely need to fix this as that column prevents people from inline editing any other field of that table

javismiles

• So In my table I have used this to hide some hidden cells that are used only for calculations:
• { targets: [1,2,3,4,5,6,8], visible: true},
  { targets: '_all', visible: false }

problem is that now when using the EDIT button, it includes those hidden fields, i need for those fields to not be included in the edit modal

also I cannot successfully edit any row because I always get this error connected to the fact that one of my cells gets the info from a mysql VIEW, and for some reason that makes any editing fail,

An SQL error occurred: SQLSTATE[HY000]: General error: 1471 The target table avgsol of the INSERT is not insertable-into

javismiles

I dont get it, i have tried every single thing to try to mask, hide, disable editor fields:

eArray[index].field( 'avgsol.p' ).hide();

fieldobj[index] = { 'label':obj.d+":", 'name':obj.i, 'type':obj.t }; (setting that one to hidden)

etc, etc,
and nothing, impossible, I keep getting this error with any editing of any cell:

An SQL error occurred: SQLSTATE[HY000]: General error: 1471 The target table avgsol of the INSERT is not insertable-into

this only happens in the table that is using that avgsol cell that gets the data correctly from a MYSQL VIEW, it does not happen in other tables that dont use views

allan

Rather than inserting or updating the view, I'd suggest directly modifying the original table. Use the VIEW to get the data sure, but write into the original table. That's going to mean using a different Editor instance though, which will need to be targeted from the ajax option.

That said, you've got a number of threads on this topic, let's try to keep them in one place if that's okay, as what you are trying to do is strung out over various places and its not very easy to follow.

Were you using a GROUP BY in your SELECT that used an AVG? I didn't see one, but I would have expected one.

Allan

javismiles

I have also disabled those fields

    eArray[index].field( 'avgsol.p' ).disable();    
    eArray[index].field( 'avgsol.c' ).disable();    

but nothing, it keeps trying to insert that avgsol table field that is connected to a mysql view...

javismiles

hey Allan, sorry apologies you are right, I will write each thing in only 1 place
I used groupby yes, in the final working version I use groupby and the view works wonderfully

Im not sure what you mean about using a different editor instance... mmm... and also "Rather than inserting or updating the view, I'd suggest directly modifying the original table. "

the thing Allan is that I do not need to update neither insert that field of the view! only read it!

so all i need Allan is to be able to have that field as readonly, and not included in the update or insert statements, that should not be difficult, right? and yet i have done everything, set it readonly, disable it, hide it, etc, and it keeps being included in the insert and update statements, how can i prevent this? thanks a lot

javismiles

and my view was this:
SELECT AVG(a.points) as p, COUNT(a.points) as c, a.idvoted FROM easyvotes a where a.type>=2 group by a.idvoted

using group by of course, and it works all great,

again I dont need to do any update or insert on that cell, only read,
thats my only issue, how do i take it out from any update/insert operation

javismiles

when doing inline editing of a single cell no probs (as long as I dont set that field to hidden for some reason),
is when using the EDIT button to edit the row that the problem happens because it tries to insert also that cell-view, so how can I take that cell out of the actions of the EDIT/DELETE buttons?

javismiles

I fixed this issue by using this, however a new error appeared

$('#'+obj.table).on( 'click', 'tbody td:not(.child), tbody span.dtr-data', function (e) {
// Ignore the Responsive control and checkbox columns
if ( $(this).hasClass( 'control' ) || $(this).hasClass('select-checkbox') ) {
return;
}
whereAmI=thetable.cell(this).index().column;
if (whereAmI==13 || whereAmI==14){
// eArray[index].inline( thetable.cell(this).index(), {onBlur: 'submit'});
eArray[index].inline( this , {onBlur: 'submit'});

        }
    } );

Now the editing doesnt give error anymore on the extra cells when in compact responsive mode, However!!! now in normal horizontal expanded mode, after editing a cell, the editing succeeds but this error is triggered:

datatables.min.js:281 Uncaught TypeError: Cannot read property 'contents' of undefined
at f.inline (datatables.min.js:281)
at HTMLTableElement.<anonymous> (datatables.min.js:281)
at HTMLTableElement.i (datatables.min.js:14)
at HTMLTableElement.dispatch (datatables.min.js:14)
at HTMLTableElement.y.handle (datatables.min.js:14)
at Object.trigger (datatables.min.js:14)
at HTMLTableElement.<anonymous> (datatables.min.js:14)
at Function.each (datatables.min.js:14)
at w.fn.init.each (datatables.min.js:14)
at w.fn.init.trigger (datatables.min.js:14)
f.inline @ datatables.min.js:281
(anonymous) @ datatables.min.js:281
i @ datatables.min.js:14
dispatch @ datatables.min.js:14
y.handle @ datatables.min.js:14
trigger @ datatables.min.js:14
(anonymous) @ datatables.min.js:14
each @ datatables.min.js:14
each @ datatables.min.js:14
trigger @ datatables.min.js:14
r @ datatables.min.js:127
P @ datatables.min.js:82
vb @ datatables.min.js:91
(anonymous) @ datatables.min.js:88
i @ datatables.min.js:86
success @ datatables.min.js:87
u @ datatables.min.js:14
fireWith @ datatables.min.js:14
k @ datatables.min.js:14
(anonymous) @ datatables.min.js:14
load (async)
send @ datatables.min.js:14
ajax @ datatables.min.js:14
sa @ datatables.min.js:88
lb @ datatables.min.js:88
P @ datatables.min.js:80
T @ datatables.min.js:82
(anonymous) @ datatables.min.js:157
iterator @ datatables.min.js:151
(anonymous) @ datatables.min.js:157
(anonymous) @ datatables.min.js:154
commit @ datatables.min.js:334
f._dataSource @ datatables.min.js:304
f._submitSuccess @ datatables.min.js:321
(anonymous) @ datatables.min.js:318
g.complete @ datatables.min.js:300
u @ datatables.min.js:14
fireWith @ datatables.min.js:14
k @ datatables.min.js:14
(anonymous) @ datatables.min.js:14
load (async)
send @ datatables.min.js:14
ajax @ datatables.min.js:14
f._ajax @ datatables.min.js:302
f._submit @ datatables.min.js:318
o @ datatables.min.js:288
f.submit @ datatables.min.js:288
f._blur @ datatables.min.js:303
f.blur @ datatables.min.js:267
f._tidy @ datatables.min.js:324
f.inline @ datatables.min.js:281
(anonymous) @ easypanel.js:258
dispatch @ datatables.min.js:14
y.handle @ datatables.min.js:14

let me know if I can pass you via private message a link for you to see a direct test case and then we can find out for good what the issue is, thank u

javismiles

so just found out that this error is triggered when the onBlur happens on another editable cell, doesnt happen when the onBlur is clicking on a non editable cell, happens when where u click to do the onblur is another editable cell

javismiles

mamma mia, that error is fixed if i use

eArray[index].inline( thetable.cell(this).index(), {onBlur: 'submit'});
instead of
eArray[index].inline( this , {onBlur: 'submit'});

but then the inline editing is not triggered on the compact cells in responsive mode!!!

so I fix one and the other issue appears and viceversa, help please

javismiles

so this seems to fix it all,
i just dont know if this is the right way as your examples dont show the need to do this, but so far its the only thing that makes it work:

    $('#'+obj.table).on( 'click', 'tbody td:not(.child), tbody span.dtr-data', function (e) {
    if ( $(this).hasClass( 'control' ) || $(this).hasClass('select-checkbox') ) {return;}

        whereAmI=thetable.cell(this).index().column;
        if (whereAmI==13 || whereAmI==14){

            if( $(this).hasClass( "dtr-data" )){
                eArray[index].inline( this , {onBlur: 'submit'});
            }
            else {
                eArray[index].inline( thetable.cell(this).index(), {onBlur: 'submit'});
            }


        }
    } );

javismiles

basically this seems to be the key,
without this one or the other fails

  if( $(this).hasClass( "dtr-data" )){
        eArray[index].inline( this , {onBlur: 'submit'});
    }
    else {
        eArray[index].inline( thetable.cell(this).index(), {onBlur: 'submit'});
    }

allan

No, that doesn't seem right. If you are using server-side processing then yes, use cell().index(), for client-side processing this should be fine.

Beyond that, can you give me a link to the page showing the issue so I can help debug it.

Allan

javismiles

Im using serverside Allan, you should know anyway that using serverside you can still use -this-, on responsive mode, but not in normal mode, cell()index works on normal mode yes,
in any case we go back here to the issues with the responsive mode, again like with the classes, the behaviour is different between normal and responsive, right now inline editing is working for me all well but I had to make two different paths one for responsive and one for non responsive, it was the only way, anyway now I took out responsive mode in any case, thank u

allan

Im using serverside Allan, you should know anyway that using serverside you can still use -this-

You can initially. But things go wrong if you then attempt to click another cell, submitting the current cell and redrawing the table. Using cell().index() is what I would recommend.

Allan

javismiles

yes, I was using cell index always, I was only using this for the responsive compact mode because guess what cell index did not work for me in the responsive compact mode but in the normal mode I always used cell index.

However in any case, I have now decided to switch all to client mode, because I thought that the 10K, 100K thing was for the total size of table, I get now that it refers to the size of the data being trasferred by ajax, in that case I will use client mode because what people get from my tables, even if the table is huge, should not be more than a 1000 rows max I predict, so I switched already to client mode,

so in client mode you say that this should be fine, in any case I have it now with cell index so I guess I Can keep it the same for now

I gotta also try now again the memcached stuff with client mode,

thank u