Possible corruption to SQLite table after strange "no such table" error

Question

First some basic information:

• I have an SQLite database in the "Program files" folder on my root C: drive in Win10 x64.
• I am accessing the database simultaneously from 3 programs.

The issue:

I created a new table table2 using DB Browser for SQLite. Then, I tried to insert a row using a simple app in debug mode in VS2013. The code is very simple :

using (SQLiteConnection database = new SQLiteConnection("Data Source=C:/Program Files/myapp/testdb.db;Version=3;"))
{
    database.Open();

    using (SQLiteCommand cmd = database.CreateCommand())
    {
        cmd.CommandText = "INSERT INTO trades VALUES (@id, @pass);";
        cmd.Prepare();

        cmd.Parameters.AddWithValue("@id", 36365);
        cmd.Parameters.AddWithValue("@pass", "verysecurepassword123");

        cmd.ExecuteNonQuery();
    }
}

On running, it gave me a SQL logic error or missing database error with the message no such table. I tried multiple times, but got the same result. All this time, DB Browser told me that the table exists, even after I tried restarting it; meaning that the table did, in fact, exist. [One point worth noting may be that VS was running in running in non-admin mode, which would have been an issue since it needed admin permissions to write to the db.]

Then I tried changing the CommandText to SELECT COUNT(*) FROM sqlite_master; and executing scalar. The result was 1, meaning it was not able to see the new table.

EDIT : I am almost completely sure that this was a cache issue of some sort as I tried listing the tables again on a copy of the database (different path), and it read 2 tables correctly.

I then tried restarting VS, after which the error promptly changed to cannot write to readonly table. I tried restarting again, this time with admin privileges, and it recognized the table and inserted just fine.

My questions :

1. What caused the first exception to give a wrong reason?
2. How was it magically fixed on restarting VS?
3. The third app running on the same db is on a seperate admin instance of VS, but the debugger is detached. This app is a tiny scale server and I always run it from VS so I can debug easily. It uses the first table (not the new one). Is it possible that whatever caused the (cache?) issue above could also cause data corruption on the first table used in this app?

Answer 1

The Program Files directory is write protected for non-administrators. Sometimes, Windows tries to redirect writes to some other directory for backwards compatibility, but as you've seen, this does not always work reliably.

Put files that need to be changed into a directory that you have write access to (i.e., your application data directory).