Syntax error near AutoIncrement with SQLite Database creation

Question

It's me again, the guy working with SQLite-net. I had my code working when I did not have AutoIncrement on my Primary Keys of the tables. I wanted to AutoIncrement the keys so I reconstructed the Tables like this:

using SQLite;

namespace VehicleTracks.Models
{
    public class vehicles
    {
        [PrimaryKey, AutoIncrement]
        public int ID { get; set; }
        public string VehID { get; set; }
        public string VehYear { get; set; }
        public string VehMake { get; set; }
        public string VehModel { get; set; }
        public string VehColor { get; set; }
        public string EngineID { get; set; }
        public System.DateTime PurchaseDate { get; set; }
        public string SellerName { get; set; }
        public string SellerStreet { get; set; }
        public string SellerCityStateZip { get; set; }
        public string VehOptions { get; set; }
        public string OdomInitial { get; set; }
        public string VehBodyStyle { get; set; }
        public float PurchaseCost { get; set; }
        public byte[] VehPhoto { get; set; }
        public string VehType { get; set; }
        public string Sold { get; set; }
    }
}

Now when an attempt is made to create the Tables, I get "Syntax Error near AutoIncrement". I tried leaving off AutoIncrement and it does not appear to increment the ID without it.

I'm probably missing something stupid.

Answer 1

Nothing stupid about your code; matches the code samples on https://github.com/praeclarum/sqlite-net alright. But apparently the code samples are wrong, considering this similar problem:

Android table creation Failure (near "autoincrement": syntax error)?

The problem was solved by removing AutoIncrement. Or to quote http://www.sqlite.org/faq.html#q1 :

Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.

(Please double-check whether column ID actually has type INTEGER PRIMARY KEY once the table has been created.)

Longer answer: If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty.

Make sure your INSERT statements do not contain an explicit value (other than NULL) for column ID, otherwise the column will not auto-increment. If that is not possible in SQLite-net (you may need a debugger here), then that may well be a bug. Though it would be surprising that nobody else has ever ran into this.

Maybe you need to make property ID nullable (i.e. use type int?; yes, with the question mark). Mind you, I'm only guessing here; you may need to experiment a bit.