Getting an error when trying to insert data into SQLite

Question

I am using the following code to update a "Picture" field in my SQLite database:

    public void SetImage(byte[] image)
    {
        var sql = "UPDATE Part SET Picture=@0 WHERE PartId=" + PartId.ToString(CultureInfo.InvariantCulture);
        var con = new SQLiteConnection(GetConnectionString());

        var command = new SQLiteCommand(con)
        {
            CommandText = sql
        };

        command.Parameters.AddWithValue("@0", image);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
        command.Dispose();
        con.Dispose();
    }

All other data access to and from my SQLite database works fine. It just seems that when I try to insert data into this one field, it gives me an error "Database is locked". My application is single threaded and all of my other data access functions properly close and dispose of the proper objects. Anyone have a clue what might be wrong?

@nawfal - No. I closed it and I even closed my virus scanner and anything else that might be opening the file up. — Icemanind
– Icemanind, Commented Nov 5, 2012 at 14:51
@icemsnind. I have had the same issue several times and mostly when I update my ado.net sqlite connector. certain versions work without any fuss while certain other not - not necessarily older version. even newer versions gives me this trouble when older version ran fine. The only solution that worked for me was disposing the SQLite objects using the using block as CL suggests. I would better ask you to incorporate a helper class that manages this so that you dont have the hassle of using using and freeing resources everytime. — nawfal
– nawfal, Commented Nov 6, 2012 at 4:46

CL. · Accepted Answer · 2012-11-05 08:10:05Z

1

The error "Database is locked" is likely to indicates that some other connection still has an open transaction.

Ensure that all commands and connections are properly closed and disposed. To do that even in the case of exceptions, use using.

answered Nov 5, 2012 at 8:10

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Icemanind Over a year ago

I have so thoroughly looked through my code and I simply can not find any place where connections and commands aren't being closed. I will admit that I am calling Dispose() on the objects rather then using using blocks, but that shouldn't matter, right? Also, I used Process Monitor to try to figure out what is going on. I've stepped through my code and locks on the database are always successful up to the command.ExecuteNonQuery(); line. Then it just fails...Its almost like SQLite is exclusively locking itself from the DB

CL. Over a year ago

You can jump out of code with things like break, return, or exceptions. Therefore, you should always use using blocks.

Icemanind Over a year ago

I am at work now, but when I get home, I will change everything to using blocks and I will report my findings.

Icemanind Over a year ago

Still the same issue, but I have noticed something. When I run the program on my computer, it fails, however, when I run it on a friend's computer, it seems to work. The difference between our machines is that my computer is a quad core processor and his is a single core processor (Though he does have hyperthreading). Is it possible that SQLite is doing something to the database in one core while its doing something else in another core? I know my program is not multithreaded. Any clues?

Icemanind Over a year ago

@nawfal - Wow it is working once I rebuilt the entire data layer using 'using' blocks. I was absolutely 100% disposing each object, so its a puzzler to me why 'using' makes a difference, but it seems to work now! Thanks for all the help!

|

Collectives™ on Stack Overflow

Getting an error when trying to insert data into SQLite

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related