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.

Jacob

Not sure what happened to my CR's in the original post. I'll try once more: – Jacob

Too long to add through comment. I'll have to post it as another question. That's probably taboo but I don't think you can easily read it as shown. – Jacob

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.

Barbey

Thanks for the info Ruud. I have done a good bit of experimenting (until I'm blue in the face) :) but I believe that my issue just might have to do with the fact that I did a manual edit to the SQLite.cs file trying to get it to accept two Primary Keys. This was prior to my adding the AutoIncrement. I suppose that this manual mod I did (not my creation, got it from elsewhere on the Great Internet), interfered with proper operation of the incrementing of my int type Primary Key. I'm hoping (not a method, I know) that it is solved now. – Jacob

Answer 2

sqlite> CREATE TABLE app (a INTEGER PRIMARY KEY NOT NULL, B VARCHAR);
sqlite> insert into app (b) values ('');
sqlite> insert into app (b) values ('a');
sqlite> 
sqlite> insert into app (b) values ('b');
sqlite> insert into app (b) values ('c');
sqlite> insert into app (b) values ('d');
sqlite> select * from app;
1|
2|a
3|b
4|c
5|d
sqlite> exit;

NOTE: IN SQLite AUTOINCREMENT keyword is recommended not to be used. You need to use INTEGER PRIMARY KEY NOT NULL. It will automatically insert the incremented value for this attribute.

Alver

"IN SQLite there in no AUTOINCREMENT keyword." The SQLite documentation disagrees with you. It doesn't recommend using AUTOINCREMENT, but has supported it since at least 2005. – Honan

Answer 3

I´m new to sqlite-net ( https://github.com/praeclarum/sqlite-net ) and was having issues with an autoincrement PK.

Although I´ve created the table with INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT I got an exception on inserts because the PK was set in 0 in the first row and then I got exception because it tried to insert the same value, so the autoincrement was not working. What I have done is edit the DataAccess.cs and in the table class I´ve replaced the [PrimaryKey] public Int64 id { get; set; } for [PrimaryKey] [AutoIncrement] public Int32 id { get; set; }. I don´t know why it uses bigint (Int64) instead of Int32 as I´ve specified INTEGER in table creation, but now it´s working OK. I can add a new item through LINQ and the autoincrement id (the primary key of the table) increments automatically in each row.

Aubervilliers