none
Error mesage when I run a SQLite command - Database is locked. RRS feed

  • Question

  • Hi all,

    I have some code, but it shows me an error when I run it.

    Werror msg: The database file is locked database is locked

    My code:

    try
    {
        string query = "UPDATE Table1 SET column1 = @col1 WHERE columnID = @colID AND column2 = @col2;";
        using (SQLiteConnection con = new SQLiteConnection(conString))
        {
            con.open();
            using (SQLiteCommand cmd = new SQLiteCommand(query, con))
            {
                foreach (Data d in list)
                {
                    cmd.Parameters.AddWithValue("@col1", d._date);
                    cmd.Parameters.AddWithValue("@col2", String.Empty);
                    cmd.Parameters.AddWithValue("@colID", d._clientID);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
    catch(Exception ex)
    {
        MessageBox.Show("Error : " + ex.Message);
    }

    Can you see the problem? Whats wrong?

    Thursday, January 17, 2019 1:56 PM

All replies

  • There is no problem with the code shown. Any issues are external e.g. another process has the database open exclusively or someplace else in your project you have opened the database and not closed the connection. So to repeat myself, the code shown is fine.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 17, 2019 2:35 PM
    Moderator
  • I dont think so.

    I was looking for opened connection and closed everything I found. Nothing.

    Then I tried to change little part of code inside using SQLiteCommand ...

    of course I also change from SQLite update to select ...

    From

                foreach (Data d in list)
                {
                    cmd.Parameters.AddWithValue("@col1", d._date);
                    cmd.Parameters.AddWithValue("@col2", String.Empty);
                    cmd.Parameters.AddWithValue("@colID", d._clientID);
                    cmd.ExecuteNonQuery();
                }

    To

    SQLiteDataReader dr = cmd.ExecuteReader();
    if (dr.HasRows)
    {
        while (dr.Read())
        {
            if (Convert.ToInt32(dr[0].ToString()) < 135)
            {
                                           
                MessageBox.Show(dr[0].ToString());
            }
        }
    }
    And this works. So no idea what problem is.




    Thursday, January 17, 2019 3:14 PM
  • Maybe you should execute ‘cmd.Parameters.Clear()’ before adding new parameters.

    Thursday, January 17, 2019 5:11 PM
  • Hi TakeshiKitano,

    Thank you for posting here.

    For your question, please refer to the link below. It gives a good explanation.

    https://stackoverflow.com/questions/17592671/sqlite-database-locked-exception

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 18, 2019 7:25 AM
    Moderator
  • Thanks,

    I will try it. But I did not mention that I do this update inside select.

    So my whole code looks like this.

    using(SQLiteConnection con = new SQLiteConnection(conString))
    {
        con.Open();
        using ( ... SQLiteCommand cmd ... select from )
        {
            using(... datareader ... dr)
            {
                if(dr[column].ToString() == "asd")
                {
                    using(update table2 set column = )
                    {
                        cmd2.ExecuteNonQuery() ... here is that problem
                    }
                }
            }
        }
    }
        

    I am sorry for that. I didn't think it can be important.

    Friday, January 18, 2019 12:46 PM
  • Try not using a using statement for the reader (dr) then inside the if(dr[column].ToString() == "asd") close the reader then execute ExecuteNonQuery.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, January 18, 2019 3:58 PM
    Moderator