SQLite-net-pcl - Always returning ID as 0 (Xamarin)

Question

I recently moved across from SQLite.NET to SQLite-net-pcl due to the Android 7.0 SQlite issue.

In the process I have updated all my libraries and all is working well with regards to insert/drop etc.

The only issue I have is that every time I retrieve an item from the DB it always has an ID of 0. This is causing problems with updating items. Has anyone had this issue before?

 [SQLite.PrimaryKey, SQLite.AutoIncrement] public int ID { get; set; } public string objectId { get; set; } public DateTime createdAt { get; set; } public DateTime updatedAt { get; set; } public string Title { get; set; }

Thanks in advance.

Make sure that you're not using InsertOrReplace with an AutoIncrement primary key. It will always override item with id 0 and never insert a new one. — redent84
– redent84, Commented Jan 20, 2017 at 11:12
In addition, I think that SQLite-Net namespace is SQLite.Net, in your sample you're using SQLite, maybe your primary key attribute is not being recognized. — redent84
– redent84, Commented Jan 20, 2017 at 11:14
Also make your ID nullable; public int? ID { get; set; } — SushiHangover
– SushiHangover, Commented Jan 20, 2017 at 11:17
Thanks for the answers guys but none of these seem to be working. I seem to have a very strange issue. Definitely Xamarin related. I cant build and run the app in debug and release. All works perfectly. When I package my app I have either 1 of 3 scenarios. 1. App crashes on launch, 2. App works but doesn't pull all data, next load crashes, wipe the data for that app and works perfect. 3. Doesn't install. — Phill Wiggins
– Phill Wiggins, Commented Jan 21, 2017 at 9:57
Probably your table has different column name, not "id" but smth else? If this is the case - you can specify Attribute [Column("<column_name>")] — Leopik
– Leopik, Commented Oct 26, 2019 at 11:19

user8122933 · Accepted Answer · 2017-09-28 16:49:52Z

Please try using this, this worked for me

using SQLite.Net.Attributes;

 [PrimaryKey, AutoIncrement] public int? Id { get; set; }

eris discord · Accepted Answer · 2017-08-09 09:53:32Z

Had almost the same situation. with all rows returning id of 0

class ToDo : Java.Lang.Object { [PrimaryKey, AutoIncrement] public int ID { get; }

except I simply had forgotten to type set; in the property.

one thing that might help someone with this sorta problem is using the GetMapping() method to get some info on the mapping used when CreateTable() is used to create the table. as example:

 Toast.MakeText(this, db.GetMapping<ToDo>().TableName.ToString(), ToastLength.Long).Show(); Toast.MakeText(this, db.GetMapping<ToDo>().HasAutoIncPK.ToString(), ToastLength.Long).Show();

using this I found out that AutoIncrement (HasAutoIncPK = false) wasn't being set on my table.

Denis · Accepted Answer · 2017-07-18 04:33:33Z

See if you created the table with the methods CreateComand(query) and ExecuteNonQuery(). If this is the case, create your table with the CreateTable<Type>() method. The primary key and autoincrement attributes are initialized at the time of creating the table through said method

Bijington · Accepted Answer · 2018-02-07 13:39:18Z

I have been struggling with the same issue here.

I was manually creating the tables to guarantee a smoother update process moving forwards rather than using the CreateTable methods available.

The fix that I eventually stumbled upon was that I was using the wrong data type for my PRIMARY KEY column.

Wrong definition

[RecordIndexId] int PRIMARY KEY NOT NULL

Correct definition

[RecordIndexId] integer PRIMARY KEY NOT NULL

To add a little context there is a big different between the int and integer data types. Explained in this SO answer

@AlexanderGorg I am not sure I understand what you are looking for. The issue I mentioned relates to the database table that you create in SQLite.

Michael Manley · Accepted Answer · 2019-11-18 21:35:34Z

My problem was that I had an internal set for my ID property setter. This had to be public to work correctly.

I'd add this as a comment but alas... I don't have enough rep.

tbear · Accepted Answer · 2024-01-04 12:35:44Z

Make sure that you're not using InsertOrReplace with an AutoIncrement primary key. It will always override item with id 0 and never insert a new one. -redent84

All database entries need to be added using await _database.InsertAsync(myClass);

You know this is not an answer, please don't post comments as answers.

Collectives™ on Stack Overflow

SQLite-net-pcl - Always returning ID as 0 (Xamarin)

6 Answers 6

Comments

Comments

Comments

2 Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

2 Comments

Comments

1 Comment

Linked

Related