B4J Question msql and primary ID auto increment

[raphipps2002]

I have been using 'mysql' without a primary key and inserting records without a problem. I realised I needed primary key and created a table as follows;

with the ID field there are now 21 columns

Public BankTableStr As String  = "CREATE TABLE bank (ID int NOT NULL AUTO_INCREMENT, IorE Text, trnDate text, catagory Text, description Text, bankOut double, bankIn double, Net double, Vat double, vatRec int, bankID text, catag text, subCatag text, vatQTR text, vatdateReconcilled text, col15 text,col16 text, col17 text, col18 text, col19 text,col20 text, PRIMARY KEY (ID))"

However, I am not quiet sure now how to insert a record as I get an error saying error count (see below) Examples indicate I do not need any reference to the primary ID as it should be auto increment.

There are 20 x '?' and 20 x Array in Object

sql.ExecNonQuery2("INSERT INTO bank values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",Array As Object(Arow(0),dDate,Arow(2),Arow(3),Arow(4),Arow(5),Net,VAT,Arow(8),BID(1),Cat,Scat,"","","","","","","",""))

The log is as follows

Error occurred on line: 900 (Main)
java.sql.SQLException: Column count doesn't match value count at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1193)
at anywheresoftware.b4j.objects.SQL.ExecNonQuery2(SQL.java:193)
at b4j.cashbook.main._btnaddincome_mouseclicked(main.java:880)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:593)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:228)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:158)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:90)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at anywheresoftware.b4j.objects.NodeWrapper$1.handle(NodeWrapper.java:85)
at anywheresoftware.b4j.objects.NodeWrapper$1.handle(NodeWrapper.java:1)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)

 

[Roycefer]

In SQLite, you use null in the primary key slot to have the primary key auto-increment. It's probably the same for MySQL. You should have 21 items in VALUES(), the first one being null.

 

[raphipps2002]

Oh i see...i will try. I thought something like that but the syntax of the auto increment is NOT Null so it seemed odd to use Null...

 

[raphipps2002]

Worked as treat! Thanks