CODEWITHSUNDEEP
c#databasesqlitewindows-store-appswinrt-xaml
Mahab Phoenix Khan
Mahab Phoenix Khan

Reputation: 119

Unable to delete a record in Sqlite db in Windows Store app

Kind of new in Windows 8 app development, I have this issue on this particular part of code. My app uses SQLite database, and I can't delete a record using primary key. I use C# by the way. Here is my snippet where I am unable to delete a record using primary key.

> >

case "Ok":

                 string result = string.Empty;

                int chapterindex=0;
                using (var db = new SQLite.SQLiteConnection(path))
                {


                    var QuestionQ = (db.Table<Chapter>().Where(
                        c => c.ChapterName == chapterselected)).Single();
                    chapterindex = QuestionQ.Index;
                 //the above query executes fine
                  //  result = chapterselected;  chapterselected is a string containing >//chapter name.

                    try
                    {//this one wouldn't
                        db.Delete<Chapter>(chapterindex);

                    }
                    catch(Exception ex)
                    {
                        pageTitle.Text = ex.ToString();
                    }

My MVVM class for chapter is...

[Table("Chapter")]
public class Chapter
{
    public int Index { get; set; }
    public string ChapterName { get; set; }
    public int ChapterNo { get; set; }

}

I did not declare Index as primary key in the above class because I am using autoincrement for that field in my db. here is my table enter image description here

Everything works fine(insert, select, deleteall,drop table etc...except deleting a single row.)

Any help would be appreciated!

Upvotes: 2

Views: 1139

Answers (2)

chue x
chue x

Reputation: 18823

This answer assumes you are using SQLite-Net as your database access layer.

The problem is likely that your class definition of Chapter does not match your table (SQL) definition. You are missing the PrimaryKey and AutoIncrement attributes from your class definition:

[Table("Chapter")]
public class Chapter
{
    [PrimaryKey, AutoIncrement]
    public int Index { get; set; }
    public string ChapterName { get; set; }
    public int ChapterNo { get; set; }
}

SQLite-Net does not currently read the SQL table information, so it does not know which column is the primary key. That is why you are having the problem with deleting.

Unrelated to your specific question, but the AutoIncrement attribute is useful for inserting records, as it will cause SQLite-Net to set your class Index field to be the same as the SQLite generated value for the primary key.

Upvotes: 0

Anthony Griggs
Anthony Griggs

Reputation: 1641

I struggled with this one for a couple hours until I came across Diederik Krols beautiful post at: His Blog. In my case I kept pulling an Exception that it could not delete due to no Primary Key... but I had my Primary Key set so looked for an alternative and came across his post. 

        var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "APE.db");
        using (var db = new SQLite.SQLiteConnection(dbPath))
        {  
                try
                {
                    // Delete Customer Records
                    db.Execute("DELETE FROM EstimateDetails WHERE EstimateDetailID = ?", YourValue);
                }
                catch (Exception ex)
                {
                   // EXCEPTION HANDLING
                }
            }

Upvotes: 1

Related Questions