Databases
StackOverflow2013
Postsdbo
POSQLite exception deleting row using sqlite-net
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLite exception deleting row using sqlite-net
    text
    copied!<p>I got tired to search so here it goes my first SO question hoping someone had the same problem and can help me</p> <h1>Goal</h1> <p>I am trying to store my application data with a SQLite database</p> <h1>Application description</h1> <p>Windows 8 app C# XAML with local SQLite database using <strong>SQLite for Windows Runtime Extension</strong> and <strong><a href="http://www.nuget.org/packages/sqlite-net" rel="nofollow">sqlite-net</a> library</strong></p> <h1>Table definition</h1> <pre><code>public class Product { private int _id; [SQLite.PrimaryKey, SQLite.AutoIncrement] public int ID { get { return _id; } set { _id = value; } } private string _date; public string DATE { get { return _date; } set { _date = value; } } private string _desc; public string DESC { get { return _desc; } set { _desc = value; } } } </code></pre> <h1>Problem1</h1> <p><code>public int Insert (object obj)</code> description says the following:</p> <blockquote> <p>Inserts the given object and retrieves its auto incremented primary key if it has one.</p> </blockquote> <p>However everytime I insert a row it return <strong>1</strong>. I can sucessfully insert with a auto-incremet ID but somehow it does not return me its ID. Why?</p> <h1>Problem 2</h1> <p><strong>I can insert new rows but not delete them</strong></p> <p>Working around problem 1 to get last row generated ID, I try to delete rows but with no success.</p> <p><strong>See this example test that always fails:</strong></p> <pre><code>using (var db = new SQLiteConnection(Path.Combine(_path, _dbname))) { var p1 = new Product() { DESC = "insert1", DATE = DateTime.Now.ToString() }; db.Insert(p1); p1.ID = 1; var p2 = new Product() { DESC = "insert2", DATE = DateTime.Now.ToString() }; // I am sure that this row is getting ID 2, so it will not have a wrong ID p2.ID = 2; db.Insert(p2); var p3 = new Product() { DESC = "insert3", DATE = DateTime.Now.ToString() }; db.Insert(p3); p3.ID = 3; db.Delete&lt;Product&gt;(p2); } </code></pre> <p>As you can see I try to insert 3 rows and delete the second one. The rows are inserted but I get the following <strong>SQLite.SQLiteException</strong> exception:</p> <p><strong>unable to close due to unfinalized statements or unfinished backups</strong></p> <p>Why? I don't open other connections before and after that.</p> <p><br> Thanks in advance</p> <h1>Solved</h1> <h2>Problem 1</h2> <p>+1 and thanks for @Bridgey for pointing out that function does not match it description and for the relevant search</p> <p>The function does not return ID as it says but it defines the object ID. So when I insert a new <strong>Product</strong>, <strong>Product.ID</strong> will have last inserted ID.</p> <h2>Problem 2</h2> <p>I changed <code>db.Delete&lt;Product&gt;(p2);</code> to <code>db.Delete(p2);</code> and now it works. SQLite-net correctly identify the row as <strong>Product</strong>. I still don't know why the <strong>unable to close due to unfinalized statements or unfinished backups</strong> exception was happening. If someone knows why tell me please.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload