select returns id column = 0

[rafgr]

I am using sqlite.net (1.0.11) in vs2015 xamarin for android and xamarin studio community on a mack book. The android app is working fine when I select a record. It returns the ID column that was setup as primary key integer field.

The IOS app which uses the same c# code from android and used to work fine in IOS 8. It was originally developed in IOS 8 but in IOS 9 and 10 the select query returns a 0 for the id column.

I can select .... where id = x and it returns the correct row. The ids in the table are correct just returns a zero.

I DON'T HAVE A CLUE WHAT BE CAUSING THIS!

[praeclarum]

Yeah I'm not sure what's going on either.

Is the PK auto incrementing? Would you post your query code?

Unfortunately you are on a very old version. Would you try upgrading to sqlite-net-pcl on nuget?

[rafgr]

Frank; Thanks for the quick response. The primary key is auto incrementing. I have attached the database.cs file which is the same file on android and ios. The calls that use the id are moveset and moveinterval. I have attached the demo.db3 file that is installed with each application and the swimclock.db3 from the phone. I removed the sqlite component and added the swlite-net-pcl package and have attached the package config file. I did notice that under components in in the actual project folder the original files are still there. Regards; Dick Farnsworth Phone: 800-835-2611 Cell: 616-450-7293 From: Frank A. Krueger [mailto:notifications@github.com] Sent: Friday, August 11, 2017 3:05 PM To: praeclarum/sqlite-net <sqlite-net@noreply.github.com> Cc: Dick Farnsworth <dick@istime.com>; Author <author@noreply.github.com> Subject: Re: [praeclarum/sqlite-net] select returns id column = 0 (#606) Yeah I'm not sure what's going on either. Is the PK auto incrementing? Would you post your query code? Unfortunately you are on a very old version. Would you try upgrading to sqlite-net-pcl on nuget? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#606 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AJzDDoyS8phVDn62mmbyAESR1uclSbgqks5sXKXMgaJpZM4O080U>. using Foundation; using System; using System.CodeDom.Compiler; using UIKit; using CoreGraphics; using SQLite; using System.IO; using System.Text; using System.Linq; using System.Collections.Generic; using System.Data; // ios namespace SwimClock { public partial class dataBase { public string selectedCategory{ get; set; } public string selectedWorkout{ get; set; } public string selectedSet{ get; set; } public int selectedSetNumber{ get; set; } public int intervalCheckedPos{ get; set; } public int setCheckedPos{ get; set; } public string countDir{ get; set; } public string intervalTime{get; set;} public int setRep{ get; set; } public int intervalRep{ get; set; } public int noIntervalsInSet{ get; set; } public int selectedInterval{ get; set; } public int numberOfSetsInWo{ get; set; } private string dbPath; // database path public string dbAction; public dataBase () { var documents = Environment.GetFolderPath (Environment.SpecialFolder.Personal); dbPath = Path.Combine (documents, "SwimClock.db3"); } // classes and functions // // class to define workout table public class Workout { //[SQLite.PrimaryKey, SQLite.AutoIncrement , Column("ID")] public int ID{get; set;} [MaxLength(30)] public string workoutName{get; set;} public int setNumber{get; set;} [MaxLength(30)] public string Category{get; set;} [MaxLength(30)] public string setName{get; set;} } public class Sets { // structure for 'sets' table [PrimaryKey, AutoIncrement, Column("Id")] public int Id{ get; set; } public string Category{ get; set; } public string SetName{ get; set; } public int intervalNumber{get; set;} public string CountDirection{ get; set; } public int SetRep{ get; set; } public int Distance{ get; set; } [MaxLengthAttribute(6)] public string Stroke{ get; set; } public int time { get; set; } public int intervalRep { get; set; } public int setDistance{ get; set; } public int setLength{ get; set; } [MaxLength(8)] public string displayTime { get; set; } [MaxLength(8)] public string displaySetLenght{ get; set; } } public class Categories { [PrimaryKey, AutoIncrement, Column("ID")] public int ID{get; set;} [MaxLength(15)] public string Category{get; set;} } public List<string> getWorkoutList() { string getname=""; string lastWorkout=""; var dbCat = new SQLiteConnection(dbPath); var workList = new List<string>(); var test =dbCat.Query<Workout>("Select oid as rowID, * from Workout where id =?", "5"); var workName = dbCat.Table<Workout>().ToList().OrderBy( y=> y.workoutName); foreach (var row in workName) { getname = row.workoutName.ToString(); // workList.Add(getname); if (getname != lastWorkout) { lastWorkout = getname; workList.Add (getname); } } dbCat.Close(); return workList; } public void getWorkout () { var db = new SQLiteConnection (dbPath); var sets = db.Query<Workout>("Select ID,Category,setNumber,setName from Workout where workoutName=? and setNumber=? ",selectedWorkout, setCheckedPos); foreach (var s in sets) { selectedCategory = s.Category; selectedSet = s.setName; selectedSetNumber = s.setNumber; } db.Close (); return; } public List<string> categoryList() { List<string> tableItems = new List<string> (); if (File.Exists (dbPath)) { using (var conn = new SQLite.SQLiteConnection (dbPath)) { string getName = ""; var x = conn.Query<Categories> ("Select Category, id from Categories order by upper(Category)"); foreach (var row in x) { getName = row.Category.ToString (); tableItems.Add (getName); } conn.Close (); } } else { } return tableItems; } // // Get the list of unique set names and position of a set name or "" = first setname // public List<string> getSetListbyCategory() { var db = new SQLiteConnection (dbPath); string lastSet = ""; var list = new List<string> (); var fname1 = db.Table<Sets> ().Where (c => c.Category.Equals (selectedCategory)).ToList ().OrderBy (y => y.SetName); foreach (var s in fname1) { string x = (s.SetName); int y = (s.Id); if (x != lastSet) { list.Add (x); lastSet = x; } } db.Close (); return list; } public List<string> getSetListbyWorkout() { int listCount = 0; var db = new SQLiteConnection (dbPath); string lastSet = ""; var list = new List<string> (); var fname1 = db.Table<Workout> ().Where (c => c.workoutName.Equals (selectedWorkout)).ToList ().OrderBy (y => y.setNumber); foreach (var s in fname1) { string x = (s.setName); int y = (s.ID); // if (x != lastSet) // { list.Add (x); // lastSet = x; listCount++; // } } numberOfSetsInWo = listCount; db.Close (); return list; } public List<string> getSets() { // setup cursor from sets var list2 = new List<string>(); var db = new SQLiteConnection (dbPath); //var sets = db.Query<Workout>("Select Category, setNumber, setName from Workout where workoutName=? ",selectedWorkout); var sets =db.Table<Workout>().Where(c=> c.workoutName.Equals(selectedWorkout)).ToList().OrderBy( y=> y.setNumber); foreach(var s in sets) { if(s.setNumber!=0) { string ino = s.setNumber.ToString(); string iname= (s.setName.ToString()); list2.Add(ino+". "+iname); } } db.Close(); return list2; } public void updateDataBase(string Name) { var db = new SQLiteConnection (dbPath); // if (dbAction == "Add Set") // { // var newSet = new Sets{ Category = selectedCategory, SetName = Name, intervalNumber = 1, CountDirection = "U" }; // selectedSet = Name; // db.Insert (newSet); //// btnset.SetTitle (Name, UIControlState.Normal); // } if (dbAction == "Add Category") { var newCat = new Categories{Category = Name}; selectedCategory = Name; db.Insert(newCat); // btnCategory.SetTitle (Name, UIControlState.Normal); } // delete a set in sets need the category if(dbAction == "Del Set") { var setID = db.Query<Sets>("select Id From Sets where SetName=? and Category=? ",selectedSet,selectedCategory); foreach(var s in setID) { db.Delete<Sets>(s.Id); } } // selete a set in workout by name if(dbAction == "del workset") { var wname =db.Table<Workout>().Where(c=>c.workoutName.Equals(selectedWorkout)).ToList().OrderBy( y=> y.setNumber); foreach(var s in wname) { if(s.setNumber == setCheckedPos ) { db.Delete<Workout>(s.ID); } else { } } } if(dbAction == "Del Category") { var catID = db.Query<Categories>("select Id From Categories where Category=? ",Name); foreach(var s in catID) { db.Delete<Categories>(s.ID); } } if(dbAction == "Move Interval") { int moveTo =Convert.ToInt16( Name); moveTo = (moveTo == 0 )? 1 : moveTo ; // check for numeric moveInterval(moveTo); // renumberIntervals(); // getIntervals(selectedSet); } if (dbAction == "Move Set") { int moveTo = Convert.ToInt16 (Name); moveSet (moveTo); } if(dbAction=="Add Workout") { var newWorkout = new Workout{workoutName = Name, setName = " "}; db.Insert (newWorkout); selectedWorkout = Name; } if(dbAction == "Delete workout") { var setID = db.Query<Workout>("select ID, workoutName From Workout where workoutName=? ",selectedWorkout); foreach(var s in setID) { db.Delete<Workout>(s.ID); } } if (dbAction == "Add Set") { int nextSet=getNextSetNumber(selectedWorkout); if(nextSet==1) { // update the first set with number & name db.ExecuteScalar<Workout> ("UPDATE Workout Set setNumber=?, setName=?, Category=? WHERE workoutName=? ",nextSet,selectedSet, selectedCategory, selectedWorkout); } else { // insert new set var newWork = new Workout{workoutName= selectedWorkout, Category = selectedCategory, setName = selectedSet, setNumber = nextSet }; db.Insert (newWork); } } dbAction = ""; db.Close (); return ; } public void moveInterval(int insertPos) { var db = new SQLiteConnection (dbPath); var wlist =db.Table<Sets>().Where(c=> c.Category.Equals(selectedCategory) && c.SetName.Equals(selectedSet)).ToList().OrderBy( y=> y.intervalNumber); int noSets = wlist.Count(); bool increment = true; if(insertPos > intervalCheckedPos) { increment = false; } // find ID for the selected set to move if(insertPos > noSets) { // error message } else { foreach(var row in wlist) { int curNumber= row.intervalNumber; if(curNumber == intervalCheckedPos) { int newPos = insertPos-1; if(increment) { newPos = insertPos; } db.ExecuteScalar<Sets> ("UPDATE Sets Set intervalNumber=? WHERE Id=? ",newPos,row.Id); } else { if (!increment && curNumber >intervalCheckedPos && curNumber < insertPos) { curNumber --; } if(increment && curNumber >= insertPos && curNumber < intervalCheckedPos) { curNumber ++; } db.ExecuteScalar<Sets>("UPDATE sets Set intervalNumber=? where id=? ",curNumber,row.Id); } } } db.Close(); } public bool checkDuplicateWorkout(string wkname) { bool duplicate = false; var db = new SQLiteConnection (dbPath); var qresult = db.Query<Workout>("select workoutName from Workout where workoutName=?", wkname); if(qresult.Count()>0) { duplicate = true; } db.Close(); return duplicate; } public int getNextSetNumber(string workName) { int next=0; var db = new SQLiteConnection (dbPath); // var wname =db.Query<Workout>("select workoutName, setNumber from Workout where workoutName=? orderby=?",workName,"setNumber"); var wname =db.Table<Workout>().Where(c=>c.workoutName.Equals(workName)).ToList().OrderBy( y=> y.setNumber); foreach (var s in wname) { next= s.setNumber+1; } db.Close(); return next ; } public void renumberSets() { var db = new SQLiteConnection (dbPath); var wname =db.Table<Workout>().Where(c=> c.workoutName.Equals(selectedWorkout)).ToList().OrderBy( y=> y.setNumber); int setNum=1; foreach(var s in wname) { db.ExecuteScalar<Workout> ("UPDATE Workout Set setNumber=? WHERE Id=? ",setNum,s.ID); setNum ++; } db.Close(); } /// <summary> /// Moves the set and renumbers them. /// </summary> public void moveSet(int insertPos) { var db = new SQLiteConnection (dbPath); var wlist =db.Table<Workout>().Where(c=> c.workoutName.Equals(selectedWorkout)).ToList().OrderBy( y=> y.setNumber); int noSets = wlist.Count(); bool increment = true; if(insertPos > setCheckedPos) { increment = false; } // find ID for the selected set to move if(insertPos > noSets) { // error message } else { foreach(var row in wlist) { int curNumber= row.setNumber; if(curNumber == setCheckedPos) { int newPos = insertPos-1; if(increment) { newPos = insertPos; } db.ExecuteScalar<Workout> ("UPDATE Workout Set setNumber=? WHERE Id=? ",newPos,row.ID); } else { if (!increment && curNumber >setCheckedPos && curNumber < insertPos) { curNumber --; } if(increment && curNumber >= insertPos && curNumber < setCheckedPos) { curNumber ++; } db.ExecuteScalar<Workout>("UPDATE Workout Set setNumber=? where id=? ",curNumber,row.ID); } } } db.Close(); } public List<string> getIntervals() { // setup cursor from sets var list2 = new List<string>(); var db = new SQLiteConnection (dbPath); // var setIntervals = db.Query<Sets>("Select Id,intervalRep, displayTime, intervalNumber from Sets where setname=? and category=?",selectedset,selectedCategory); var setIntervals=db.Table<Sets>().Where(c=> c.SetName.Equals(selectedSet) && c.Category.Equals(selectedCategory)).ToList().OrderBy( y=> y.intervalNumber); int i = 1; foreach(var s in setIntervals) { string ino = s.intervalNumber.ToString()+". "; string irep = (s.intervalRep.ToString()); string time = (s.displayTime); // setRep = s.SetRep; list2.Add(ino+irep+" @ "+time); i ++; } noIntervalsInSet = i-1; db.Close(); return list2; } public void getInterval(int intervalNo, string setSelected) { var db = new SQLiteConnection (dbPath); var intervals = db.Query<Sets>("Select * from Sets where setname=? and Category=? and intervalNumber=?",setSelected,selectedCategory,intervalNo); foreach(var s in intervals) { if (s.displayTime == null) { intervalTime = "00:00"; } else { intervalTime = s.displayTime; } setRep = s.SetRep; intervalRep = s.intervalRep; countDir = s.CountDirection; } db.Close(); return; } public int checkForUsed(string table, string name, string category, ref int numberSetsUsed) { int used = 0; var db = new SQLiteConnection (dbPath); if (table == "Sets") { var setUsed = db.Query<Workout> ("Select * from Workout where setname=? and Category=?", name, category); used = setUsed.Count (); } else { var categoryUsedWo = db.Query<Workout>("Select * from Workout where Category = ?", category); var categoryUsedSet = db.Query<Sets>("Select * from Sets where Category = ?", category); used = categoryUsedWo.Count () ; numberSetsUsed = categoryUsedSet.Count(); } db.Close (); return used; } public string getFirstSet() { var db = new SQLiteConnection (dbPath); var setList = db.Query<Sets> ("SELECT Category FROM SETS ORDER BY Category"); string result =setList [0].Category; db.Close (); return result; } public int numberOfSetsInWorkout(string sWorkout) { int count = 0; var db = new SQLiteConnection(dbPath); var setList = db.Query<Workout>("select * from workout where workoutName = ? and setnumber > 0", sWorkout); count = setList.Count; db.Close(); return count; } // check for duplicate names and don't allow public bool checkDuplicateName(string name, string table) { bool duplicate = false; var db = new SQLiteConnection (dbPath); if (table == "Category") { var exists = db.Query<Categories> ("select * from Categories where Category = ?", name); duplicate = exists.Count () > 0 ? true : false; } else { var exists = db.Query<Sets> ("select * from Sets where Category = ? and setName = ?", selectedCategory, name); duplicate = exists.Count () > 0 ? true : false; } db.Close (); return duplicate; } // check if this is the last workout or set public bool lastItem (string table) { bool onlyRecord = false; var db = new SQLiteConnection (dbPath); var list = new List<string> (); if (table == "Workouts") { list = getWorkoutList(); } else { list = getSetListbyCategory (); } db.Close (); onlyRecord = list.Count == 1 ? true : false; return onlyRecord; } // check for duplicate names and don't allow public bool checkDuplicateName(string category, string name, int interval, string table) { bool duplicate = false; var db = new SQLiteConnection(dbPath); if (table == "Category") { //var exists = db.Query<Categories>("select * from Categories"); var exists = db.Query<Categories>("select * from Categories where trim(Category) like ?", category); duplicate = exists.Count() > 0 ? true : false; } if (table == "Set") { var exists = db.Query<Sets>("select * from Sets where trim(Category) = ? and trim(setName) = ? and intervalNumber = ?", category, name, interval); duplicate = exists.Count() > 0 ? true : false; } if (table == "Workout") { var exists = db.Query<Workout>("select * from Workout where trim(WorkoutName) like ?", name.Trim()); duplicate = exists.Count() > 0 ? true : false; } db.Close(); return duplicate; } /// <summary> /// Add new category from import file to the local database /// </summary> /// <param name="catName"></param> public void importCategory(string catName) { var db = new SQLiteConnection(dbPath); var newCategory = new Categories { Category = catName }; db.Insert(newCategory); db.Close(); } /// <summary> /// add new workout from the import file /// </summary> /// <param name="woName"></param> /// <param name="setnumber"></param> /// <param name="category"></param> /// <param name="setname"></param> public void importWorkout(string woName, int setnumber, string category, string setname) { var db = new SQLiteConnection(dbPath); var newWorkout = new Workout { workoutName = woName, setNumber = setnumber, Category = category, setName = setname }; db.Insert(newWorkout); db.Close(); } /// <summary> /// Add new set from the import file into local database /// </summary> /// <param name="category"></param> /// <param name="setname"></param> /// <param name="intNumber"></param> /// <param name="direction"></param> /// <param name="setrep"></param> /// <param name="distance"></param> /// <param name="stroke"></param> /// <param name="time"></param> /// <param name="intervalrep"></param> /// <param name="setdistance"></param> /// <param name="setlength"></param> /// <param name="displaytime"></param> public void importSets(string category, string setname, int intNumber, string direction, int setrep, int distance, string stroke, int time, int intervalrep, int setdistance, int setlength, string displaytime) { var db = new SQLiteConnection(dbPath); var newSet = new Sets { Category = category, SetName = setname, intervalNumber = intNumber, CountDirection = direction, SetRep = setrep, Distance = distance, Stroke = stroke, time = time, intervalRep = intervalrep, setDistance = setdistance, setLength = setlength, displayTime = displaytime }; db.Insert(newSet); db.Close(); } } }

[praeclarum]

Please also delete the source code files SQLite.cs and SQLiteAsync.cs from the old package.

This query seems to be wrong:

var test =dbCat.Query<Workout>("Select oid as rowID, * from Workout where id =?", "5");

I think it should be

var test =dbCat.Query<Workout>("Select oid as rowID, * from Workout where id =?", 5);

Since the ID is an integer, not a string.

[rafgr]

Frank; The only place I found those 2 files was in the down load folder under mobile-samples- master. I don’t see them under the project, just sqlite-net file in components and packages. The test query was to check to see if it would select by the ID and it does for string or integer. Regards; Dick Farnsworth Phone: 800-835-2611 Cell: 616-450-7293 From: Frank A. Krueger [mailto:notifications@github.com] Sent: Monday, August 14, 2017 3:42 PM To: praeclarum/sqlite-net <sqlite-net@noreply.github.com> Cc: Dick Farnsworth <dick@istime.com>; Author <author@noreply.github.com> Subject: Re: [praeclarum/sqlite-net] select returns id column = 0 (#606) Please also delete the source code files SQLite.cs and SQLiteAsync.cs from the old package. This query seems to be wrong: var test =dbCat.Query<Workout>("Select oid as rowID, * from Workout where id =?", "5"); I think it should be var test =dbCat.Query<Workout>("Select oid as rowID, * from Workout where id =?", 5); Since the ID is an integer, not a string. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#606 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AJzDDsEUD-3nz5hdsiOm65a6yX3JpICMks5sYKL7gaJpZM4O080U>.

[rafgr]

I just noticed that there is a using SQlite; I tried sqlitepcl; was a drop down option and the SQLite stayed bold; Removed it and got namespace errors Am I using the IOS sqlite and not yours? Regards; Dick Farnsworth Phone: 800-835-2611 Cell: 616-450-7293 From: Frank A. Krueger [mailto:notifications@github.com] Sent: Monday, August 14, 2017 3:42 PM To: praeclarum/sqlite-net <sqlite-net@noreply.github.com> Cc: Dick Farnsworth <dick@istime.com>; Author <author@noreply.github.com> Subject: Re: [praeclarum/sqlite-net] select returns id column = 0 (#606) Please also delete the source code files SQLite.cs and SQLiteAsync.cs from the old package. This query seems to be wrong: var test =dbCat.Query<Workout>("Select oid as rowID, * from Workout where id =?", "5"); I think it should be var test =dbCat.Query<Workout>("Select oid as rowID, * from Workout where id =?", 5); Since the ID is an integer, not a string. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#606 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AJzDDsEUD-3nz5hdsiOm65a6yX3JpICMks5sYKL7gaJpZM4O080U>.

[praeclarum]

If you're using sqlite-net-pcl then you're using my library that uses the iOS native library. The correct namespace is SQLite

[rafgr]

That’s what I am using. Does that mean the id problem was caused by a change in the ios native library? Regards; Dick Farnsworth Phone: 800-835-2611 Cell: 616-450-7293 From: Frank A. Krueger [mailto:notifications@github.com] Sent: Tuesday, August 15, 2017 3:23 PM To: praeclarum/sqlite-net <sqlite-net@noreply.github.com> Cc: Dick Farnsworth <dick@istime.com>; Author <author@noreply.github.com> Subject: Re: [praeclarum/sqlite-net] select returns id column = 0 (#606) If you're using sqlite-net-pcl then you're using my library that uses the iOS native library. The correct namespace is SQLite — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#606 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AJzDDtnAtQ3GkuhhA7c6kMUhkdy4GHNnks5sYfAQgaJpZM4O080U>.

[praeclarum]

No I have no idea what's causing you problems. Would you try upgrading to the 1.5-beta prerelease version?

[rafgr]

I think I figured it out. I am using an iPhone 5s for development and last week it updated automatically. It was a few days after that when I was testing a new release that the problem showed up. I also had done an update to your package so wasn’t sure what caused it . When you said you were using the IOS layer I got to wondering if apple had changed something and discovered 5s support is gone which I don’t understand why 10.3.3 was put in it last week when that is supposedly for a 64 bit processor and the 5 is a 32 bit. I started redoing my code to not use the id field and will have to get out for beta today because the current version is expiring today and I still have 5 users on 5s phones. Your Thoughts? Regards; Dick Farnsworth Phone: 800-835-2611 Cell: 616-450-7293 From: Frank A. Krueger [mailto:notifications@github.com] Sent: Tuesday, August 15, 2017 11:47 PM To: praeclarum/sqlite-net <sqlite-net@noreply.github.com> Cc: Dick Farnsworth <dick@istime.com>; Author <author@noreply.github.com> Subject: Re: [praeclarum/sqlite-net] select returns id column = 0 (#606) No I have no idea what's causing you problems. Would you try upgrading to the 1.5-beta prerelease version? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#606 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AJzDDlgUii5cfB17LHiMchA2tHXyK3Glks5sYmZBgaJpZM4O080U>.

[praeclarum]

My best guess is that you still have a mismatch between sting IDs and int IDs. This library does not parse strings to be ints. If you open your database in sqlite3, I think you'll see that the ID column is text which is why the library can't load it.

[rafgr]

Then why does it work on phones that havent updated to the lattest ios version? The test query is just that a test. All the other code that uses s.id was working until the last week or so. Sent via the Samsung Galaxy S7, an AT&T 4G LTE smartphone

…

-------- Original message -------- From: "Frank A. Krueger" <notifications@github.com> Date: 8/16/17 6:02 PM (GMT-05:00) To: praeclarum/sqlite-net <sqlite-net@noreply.github.com> Cc: Dick Farnsworth <dick@istime.com>, Author <author@noreply.github.com> Subject: Re: [praeclarum/sqlite-net] select returns id column = 0 (#606) My best guess is that you still have a mismatch between sting IDs and int IDs. This library does not parse strings to be ints. If you open your database in sqlite3, I think you'll see that the ID column is text which is why the library can't load it. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#606 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AJzDDpJfeHYaezI0RtAdl_eb4971Rfmmks5sY2bUgaJpZM4O080U>.

[rafgr]

I opened it in firefox sqlite tool and this what it shows. Regards; Dick Farnsworth Phone: 800-835-2611 Cell: 616-450-7293 From: Frank A. Krueger [mailto:notifications@github.com] Sent: Wednesday, August 16, 2017 6:02 PM To: praeclarum/sqlite-net <sqlite-net@noreply.github.com> Cc: Dick Farnsworth <dick@istime.com>; Author <author@noreply.github.com> Subject: Re: [praeclarum/sqlite-net] select returns id column = 0 (#606) My best guess is that you still have a mismatch between sting IDs and int IDs. This library does not parse strings to be ints. If you open your database in sqlite3, I think you'll see that the ID column is text which is why the library can't load it. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#606 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AJzDDpJfeHYaezI0RtAdl_eb4971Rfmmks5sY2bUgaJpZM4O080U>.

[bijington]

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

There is actually a difference between int and integer that I wasn't initially aware of:
(https://stackoverflow.com/a/20289487/32348)

[fastlater]

@bijington I cannot open your link. I am trying to set the primary key autoincrement of the class public int id variable but result is: id field is null for each row. Any extra advice?

public class table1
    {
        [PrimaryKey,AutoIncrement, NotNull] 
        public int id { set; get; }
        ...
     }