B4J Question b4j jsql update record

[tremara1]

I have been working on a DB app and I am progressing but I am a bit stumped with the update record.
I am using jsql and the insert,delete,select are fine. This is this is the query I am using:

sql1.BeginTransaction
   Try
     sql1.ExecNonQuery2("UPDATE myItems SET item=?, WhereP=?, when=?, cost=?, warranty=?, itemPic=?, recPic=? where item=?", Array As Object(strItem,strWhere,lngTicks,strCost,strWarranty,buffer1,buffer2,strItem))
     
   Catch
  Log(LastException.Message)
  End Try

The data table structure is in the attached screen capture.

The error I get is
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "when": syntax error)

I know this is going to be obvious but I have looked at this for hours and tried heaps of suggestions from the forum but to no avail.....

 

[stevel05]

In SQLite WHEN is a Clause keyword, I don't know SQLite well enough to say that's the problem without testing it, but that's where I would start. Change the name, or to test it, just don't update that column and see if it stops complaining, then change the column name if it does.

 

[tremara1]

In SQLite WHEN is a Clause keyword, I don't know SQLite well enough to say that's the problem without testing it, but that's where I would start. Change the name, or to test it, just don't update that column and see if it stops complaining, then change the column name if it does.

Thanks absolutely spot on I did not see that......Trevor

 

[Harris]

Thanks absolutely spot on I did not see that......Trevor

Yes, you have to watch those reserved words... Screwed me up many times.

The smart guys (formally trained) will often prefix all fields with 'x_when' to avoid such issues, regardless of the field name.
One approach, sure there are many others.

 

[tremara1]

Another good suggestion....thanks.

 

[keirS]

Enclosing your when field in quotation marks will resolve this.

sql1.ExecNonQuery2("UPDATE myItems SET item=?, WhereP=?,'when'=?, cost=?, warranty=?, itemPic=?, recPic=? where item=?", ArrayAs Object(strItem,strWhere,lngTicks,strCost,strWarranty,buffer1,buffer2,strItem))