CODEWITHSUNDEEP
c#sqlitewindows-phone-7windows-phone-8
Prasanna Aarthi
Prasanna Aarthi

Reputation: 3453

unable to update values in SQLite DB after importing- windows phone

I followed this tutorial to import my existing database http://wp.qmatteoq.com/import-an-already-existing-sqlite-database-in-a-windows-8-application/ After copying my DB to project files and setting build action as content,I am copying the database to isolated storage.Now when I try to update a value in the database,and view the change in my binded list box, Im not able to see the changes.When I use isolated storage explorer and view the DB,the values have changed in DB.But querying it doesnt return me the updated values.I am using the same connection string in both cases.

//copy DB from installation folder to isolated storage
private async Task CopyDatabase()
{
    bool isDatabaseExisting = false;

    try
    {
        StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync("sample.db");
        isDatabaseExisting = true;
    }
    catch
    {
        isDatabaseExisting = false;
    }

    if (!isDatabaseExisting)
    {
        StorageFile databaseFile = await Package.Current.InstalledLocation.GetFileAsync("sample.db");
        await databaseFile.CopyAsync(ApplicationData.Current.LocalFolder);
    }
}

//update the value
 dbConn = new SQLiteConnection(DB_PATH);
               dbConn.Query<resources>(" update resources SET isFavorite = 'True' WHERE resourceId =" + rid);
           }
// Query database to populate list box
     string DB_PATH = Path.Combine(ApplicationData.Current.LocalFolder.Path, "sample.db");
     private SQLiteConnection dbConn;
     dbConn = new SQLiteConnection(DB_PATH);
     List<resources> retrievedpdf = dbConn.Query<resources>("select * from resources where categoryId=" + noid + " AND typeId=1" ).ToList<resources>();
      pdflist.ItemsSource = retrievedpdf;

enter image description here

I am able to view changes in exploreer, but querying returns unmodified values.

Upvotes: 0

Views: 500

Answers (2)

Prasanna Aarthi
Prasanna Aarthi

Reputation: 3453

There was a problem with the query , I had been updating string "true" to a bool in a table..This is the correct query int updateCount = dbConn.Execute("update resources SET isFavorite = 1,modifiedTime = DATETIME('now') WHERE resourceId =" + rid);

Upvotes: 0

Jaihind
Jaihind

Reputation: 2778

I would like some changes in query that helps me a lot.

//Copy your database file From Installation folder to IsolatedStorage on App launching.

//Change your update query bu below query
int updateCount = dbConn.Execute("update resources SET isFavorite = 'True' WHERE resourceId =" + rid);

//Change your select Query
 **Edits**

List<resources> retrievedpdf = dbConn.Table<resources>().ToList().Where(x=>x.categoryId.Equals(noid) && x.typeId.Equals(1)).ToList();

//Assign ListBox ItemSource to null before assign actual source

pdflist.ItemsSource = null; 
pdflist.ItemsSource = retrievedpdf;

Upvotes: 1

Related Questions