SQLite.Net Update generating error "it has no PK"

SteveSykesSteveSykes USMember ✭✭
edited July 2015 in General

Here is my simple class:

    public class settings
    {

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

        public string user_name { get; set; }
        public string password { get; set; }
        public string server { get; set; }

    }

void CreateTables()
{
    database.CreateTable<settings>();
}

It inserts fine, but when I try and update I get "Cannot update settings: it has no PK".
Clearly the class does so what am I doing wrong?

Tagged:

Answers

  • HmatrixHmatrix NGMember

    I had similar issue.
    Change
    using SQLite; to SQLIte.Net.Attribute;

    That solved it for me.`

  • @Hmatrix said:
    I had similar issue.
    Change
    using SQLite; to SQLIte.Net.Attribute;

    That solved it for me.`

    I'm having the same issue, but I do not see a SQLite.Net.Attribute namespace.

  • SteveSykesSteveSykes USMember ✭✭
    edited November 2015

    I had to reference sqlite-net separately . Just use NuGet to get it. (notice the hypen)
    I also added SQLiteNetExtensions to use relationships. (ForeignKey, Many to Many, One to Many etc.)

        [SQLite.PrimaryKey, SQLite.AutoIncrement]
        public int ID
        {
            get
            {
                return id;
            }
            set
            {
                id = value;
            }
        }
    
  • @SteveSykes said:
    I had to reference sqlite-net separately . Just use NuGet to get it. (notice the hypen)
    I also added SQLiteNetExtensions to use relationships. (ForeignKey, Many to Many, One to Many etc.)

    I'll give it a try. I am currently using the SQLite.NET Xamarin component. I'll remove that and add the NuGet package.

  • I removed the SQLite Xamarin Component and added the sqlite-net NuGet library and the namespace SQLite.Net.Attribute still does not exist.

  • SteveSykesSteveSykes USMember ✭✭
    edited November 2015

    You shouldn't need it now. Just a "using SQLite;"
    I guess I should have stated I couldn't get HMatirx's solution to work either.

     [SQLite.Table("wo")]
        public class wo
        {
            int id;
    
            [SQLite.PrimaryKey, SQLite.AutoIncrement]
            public int ID
            {
                get
                {
                    return id;
                }
                set
                {
                    id = value;
                }
            }
        }
    
  • JustinLoveroJustinLovero USMember
    edited November 2015

    Ah, I see. That makes more sense. Unfortunately, I'm still seeing the "it has no PK" error. Here's the class:

    [SQLite.Table("MyCardStorageObject")]
    public class MyCardStorageObject
    {
        [SQLite.PrimaryKey, SQLite.AutoIncrement, SQLite.Column("_id")]
        public int Id { get; set; }
    
        public string Key { get; set; }
    
        public byte[] Data { get; set; }
    
        public DateTime DateSaved { get; set;}
    }
    

    Stepping through the SQLite.Net code when this error occurs, it appears to be due to a Reflection bug. When SQLite.NET tries to create the column mapping, the Id property is excluded because p.CanWrite == false even though Id obviously has a public setter. So, the _id field is never added to the database table. Inserts work because I guess you can insert without a PrimaryKey column. Updates and deletes, however, require a PK. All of the objects returned from the ORM have an Id of 0, the default int value. The rest of the values are returned correctly.

    I already opened an issue for this on the SQLite.NET github repo: https://github.com/praeclarum/sqlite-net/issues/398. Lots more details in there. Note that the example code just uses the attribute names not prefixed with SQLite. It doesn't seem to matter how I use the attributes. It is broken both ways.

    Curious if anyone has any input on this. Also, I inherited this code and I'm uncertain why SQLite.Column("_id") is used and I'm wondering if that may be causing the issue.

  • SteveSykesSteveSykes USMember ✭✭

    That's strange...you're running into the exact problem I had. I removed SQLite.Net and added sqlite-net and added the SQLite to my attributes.
    Here is what I wrote in StackOverflow...maybe I'm missing something to tell you that I did there.
    http://stackoverflow.com/questions/31590119/sqlite-net-update-generating-error-it-has-no-pk

  • FranciscoGGFranciscoGG ESMember ✭✭

    Guys, any solution about this?

  • bobbygbobbyg USMember

    Having trouble with this as well... None of the solutions above seem to work for me.

  • AlexWhiteAlexWhite GBMember ✭✭✭

    I used to get the pk message if my linker settings were wrong, play with those, pretty sure the one that works 100% of the time is don't link and the link SDK only works as well, give it a try.

  • dpedrinhadpedrinha DEMember ✭✭✭
    edited June 2016

    Still no solution on this?
    When I insert to the DB the model PK is actually updated with a non-zero value. But once I try to get any thing from the table, it's always 0.

  • For anyone else having this problem try changing the References. Replace "using SQLite.Net.Attributes;" with "using SQLite;" in the model class when you declare your primary key. Worked for me.

  • FredyWengerFredyWenger CHInsider ✭✭✭✭✭

    Any news...?
    No suggested solution works for me...

  • hvaughanhvaughan USMember ✭✭✭

    @FredyWenger @dpedrinha @bobbyg @FranciscoGG @JustinLovero

    I ran into a similar issue where I would create a new object, it would get a PK. Then when I retrieved the objects from the DB again the PK was 0.

    This happened because I initially did not add the PrimaryKey and AutoIncrement annotations to my model's Id property. Even after I did add the annotations, I still got the same issue.

    That was because the DB and schema had already been created on the device, so in order to update the table schema, you need to either drop the table and recreate it or delete the DB file and start over.

    Deleting the app and reinstalling solves this issue.

  • dpedrinhadpedrinha DEMember ✭✭✭

    @hvaughan I tried that.

    In my case the problem was because I was creating my tables as FTS4. For some reason it doesn't work.

  • Guys,

    I have found solution:

    1. Removed attributes from the Entities (PrimaryKey, AutoIncrement)

    2. Added flags to CreateTable method:
      _dbConnection.CreateTable<OrderEntity>(CreateFlags.ImplicitPK | CreateFlags.AutoIncPK)

  • ruslan_kutowojruslan_kutowoj DEMember ✭✭

    So its 2017 and I have exact the same problem. All my propertys are public, my IDs have SQLite.Primary /.AutoIncrement attributes, OneToMany relationship using SQLite Extensions, also tried Flags like EugeneDrapoguz recommended and set my linking to none in vs. So what is the problem?

    Thats my code:
    `var ParentDB = new ParentDB
    {
    description = "test",

                ChildDB = new List<ChildDB>
                    {
                        new ChildDB {description = "blah" }
                    }
            };`
    

    SQLiteNetExtensions.Extensions.WriteOperations.InsertWithChildren(GetConnection(), ParentDB, recursive: true);

  • hvaughanhvaughan USMember ✭✭✭

    @komarara Make sure you are completely deleting the app from the device each time you make major schema changes, otherwise the old schema will continue to be used when you run the debugger again.

  • ruslan_kutowojruslan_kutowoj DEMember ✭✭
    edited September 2017

    Ok I have added Cascade operation properties and set "preserve application data cache on device between deploys" off. Now im getting a SQLiteNetExtensions.Exceptions.IncorrectRelationship Exception. can you tell my why, because I cant see nothing wrong with my relationship:

    [SQLite.Table("Parent")]
        public class ParentDB
        {
            [SQLite.PrimaryKey, SQLite.AutoIncrement]
            public int? Id { get; set; }
            public string description { get; set; }
    
            [OneToMany(CascadeOperations = CascadeOperation.All)]
            public List<ChildDB> ChildDB { get; set; }
        }
    
     [SQLite.Table("Child")]
        public class ChildDB
        {
            [SQLite.PrimaryKey, SQLite.AutoIncrement]
            public int? Id { get; set; }
            public string description { get; set; }
            [ForeignKey(typeof(ParentDB))]
            public int ParentId { get; set; }
            [ManyToOne(]
            public ParentDB ParentDB { get; set; }
        }
    
  • NamyslawSzymaniukNamyslawSzymaniuk USMember ✭✭✭✭

    Da fuq? You have nullable ID, and then NON-nullable ID for relation. That couldn't work I think.

  • ruslan_kutowojruslan_kutowoj DEMember ✭✭

    Yes because I was frustrated and tried everything I read on stackoverflow, nothing works. Make no difference with or without nullalble int

Sign In or Register to comment.