I don't use reserved VFP keywords as field name even reserved mySQL keywords
It works if I split into a few statement with the same field names



Could the problem be in the names of the fields (some of the field names you're using are reserved VFP keywords)?

Can you try creating a stored procedure and passing parameters to it to do complex update/insert?

Also I remember there was a thread in tek-tips forum few years ago with the same problem.

>Hi
>Is SQL insert or update command cannot save many fields in one statement with VFP9 ?
>
>example...
>
> SQLExec(lnHandle, "insert into member (ac,cardtype,name,add1,add2,add3,add4,ic,tel,joined,point,;
> expiry,suspend,canc) ;
> value (?m.ac,?m.cardtype,?m.name,?m.add1,?m.add2,?m.add3,?m.add4,?m.ic,?m.tel,?m.joined,?m.point,;
> ?m.expiry,?m.suspend,?m.canc)")
>this OK when saving the form
>
> SQLExec(lnHandle, "insert into member (ac,cardtype,name,add1,add2,add3,add4,ic,tel,joined,point,;
> expiry,suspend,canc,master_ac,dt,sync) ;
> value (?m.ac,?m.cardtype,?m.name,?m.add1,?m.add2,?m.add3,?m.add4,?m.ic,?m.tel,?m.joined,?m.point,;
> ?m.expiry,?m.suspend,?m.canc,?m.master_ac,?m.dt,?m.sync)")
>this got error with "Command contains unrecognized phrase/keyword (Cancel/Ignore/Ignore all)"
>
>
>Sometime I got error during runtime when create table or insert table with many fields in one statement
>In order to create or save the record have to split the statement.....
>
>* create table
>SQLExec(lnHandle, ;
> "create table if not exists user"+;
> "(user char(20) not null, password char(20), supervisor char(1), outlet char(5),;
> canc char(1), duty char(1), m1 char(1), m2 char(1), m3 char(1), m4 char(1), m5 char(1),;
> m6 char(1), m7 char(1), m8 char(1), m9 char(1), m10 char(1), m11 char(1))")
>SQLExec(lnHandle,"alter table user add column m12 char(1), add column m13 char(1),;
> add column m14 char(1), add column m15 char(1), add column m16 char(1),;
> add column m17 char(1), add column m18 char(1), add column m19 char(1), add column m20 char(1)")
>SQLExec(lnHandle,"alter table user add column m21 char(1), add column m22 char(1), add column m23 char(1),;
> add column m24 char(1), add column m25 char(1), add column m26 char(1)")
>SQLExec(lnHandle,"alter table user add column m27 char(1), add column m28 char(1),;
> add column m29 char(1), add column m30 char(1)")
>SQLExec(lnHandle,"alter table user add column m31 char(1), add column m32 char(1), add column m33 char(1),;
> add column m34 char(1), add column m35 char(1), add column m36 char(1)")
>SQLExec(lnHandle,"alter table user add column m37 char(1), add column m38 char(1),;
> add column m39 char(1), add column m40 char(1)")
>
>*insert record
>SQLExec(lnHandle, "insert into user (user, password, supervisor, duty,;
>M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12,M13,M14,M15,M16,M17,M18,M19,M20,M21,M22,M23,M24,M25,M26) values ;
>('TEST','123456','Y','Y','','','','','','','','','','','','','','','','','','','','','','','','','','')")
>SQLExec(lnHandle, "UPDATE user set M27='',M28='',M29='',M30='',M31='',M32='',M33='',M34='',M35='',M36='',M37='',M38='',M39='',M40='' where user='TEST'")
>
>why ???????