C# SQLite database is locked

Question

0.00/5 (No votes)

See more:

I have a simple update code but I don't know from some reasons is not working. I receive: database is locked. Can anybody help me? This is the code:

C#

try
                       {
                           SQLiteCommand sel1 = new SQLiteCommand("select totalsim from accounts where username='DPaul'",Conexiune.getConnection());
                           SQLiteDataReader readt = sel1.ExecuteReader();
                           while (readt.Read())
                           {
                               int totalsimm = Convert.ToInt32(readt["totalsim"]);
                               totalsimm++;
                               using (SQLiteCommand update = new SQLiteCommand("UPDATE accounts SET totalsim=@totalsimm where username = 'DPaul'", Conexiune.getConnection()))
                               {
                                   update.Parameters.Add(new SQLiteParameter("@totalsimm", totalsimm));
                                   Int32 rows = update.ExecuteNonQuery();
                               }
                           }
                       }
                       catch (Exception ex)
                       {
                           MessageBox.Show(ex.Message);
                       }

I have one more select but is in a separate function.

Posted 27-Mar-15 9:40am

DPaul1994

Updated 22-Dec-20 22:33pm

Add a Solution

2 solutions

Solution 5

string google = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\History";
SQLiteConnection connection = new SQLiteConnection("Data Source=" + google + ";Version=3;New=False;Compress=True;");

using (SQLiteConnection conn = new SQLiteConnection(connection))
{
connection.Open();

SQLiteDataAdapter sd = new SQLiteDataAdapter("select url,title,visit_count,last_visit_time from urls order by last_visit_time", connection);
DataSet ds = new DataSet();
sd.Fill(ds, "urls");
dataGridView1.DataSource = ds.Tables[0];
connection.Close();
}
Hi This is my Code I got a error database
locked how to resolve it please give some idea

Posted 22-Dec-20 22:33pm

Member 15028582

Comments

CHill60 23-Dec-20 5:52am

If you have a question then use the red "Ask a Question" link at the top of this page. Do not post Questions (or Comments) as a solution to another member's post

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-03-27T09:54:00

Solution 1

You can't do that.
You have the connection open to handle the reader in the outer while loop, and you are trying to UPDATE the same table from the same connection within that loop - these operations are incompatible, so you get a "Database is locked" error.

The best solution would be to dump the Reader, and load a DataTable via a DataAdapter instead, and then use the DataTable data to loop and do the UPDATE statements.

"Well, is not about I give up, I'm sick of this update :)) I tried to change the code in so many ways, but without success. This is my last try. I get the same error. I tried to close and reopen connection, than close is again, but with no succees. I tried with DataAdapter, with DataReader but it didn't work. I don't know to else to try. And I have to change only a value from a single row, don't understand why to use foreach loop.."

Why a foreach loop? Because a query of any form returns a number of rows - which may be zero (if there is no match), one (if there is only one), or more (if there is more than one value) - and it's alwasy good practice to not assume that it will return one and only one, even if your program logic says it will. Data errors happen! :laugh:

So start by using a DataAdapter instead of the DataReader:

C#

string collect = "SELECT option1,option2,option3 FROM questions WHERE question=@QU";
SQLiteCommand comp = new SQLiteCommand(collect, Conexiune.getConnection());
comp.CommandType = CommandType.Text;
comp.Parameters.AddWithValue("@QU", SimulatorManager.Intrebare);
SQLiteDataAdapter da = new SQLiteDataAdapter(comp);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow row in dt.Rows)
    {
    ...
    }

(I've made some changes to the way you did it: SQL verbs are normally in UPPERCASE to make it easier to pick them out of an SQL statement. And never, ever concatenate strings to form an SQL statement - that way leads to SQL injection attacks which can damage or destroy your database, just by typing in text boxes. Always use parameterized queries!)

Then in the loop you just use the row info to decide if an update is needed:

C#

int op1 = (int)row["option1"];
int op2 = (int)row["option2"];
int op3 = (int)row["option3"];
...

Make sense so far?

Posted 27-Mar-15 9:54am

OriginalGriff

Updated 29-Mar-15 4:55am

v2

Comments

DPaul1994 28-Mar-15 8:01am

You mean like that?
using (SQLiteDataAdapter update = new SQLiteDataAdapter("update totalsim from accounts set totalsim=totalsim+1 where username = 'DPaul'", Conexiune.getConnection()))
{
DataTable updt = new DataTable();
update.Fill(updt);
}

Cause I tried this and I receive:
SQL logic error or missing database near 'from': syntax error

OriginalGriff 28-Mar-15 8:07am

Did you check your SQL syntax? :laugh:
UPDATE MyTable SET ...
Not
UPDATE MyColumn FROM MyTable SET ...

DPaul1994 28-Mar-15 8:26am

OMG! =)))) Yeah, sorry for that. But database is still locked.

OriginalGriff 28-Mar-15 8:28am

This may be a silly question, but have you still got the DataReader in there? Show the exact code please!

DPaul1994 28-Mar-15 8:35am

Well this may be a silly answer, but that is the entire code :))) I don't really know how to use datatable..yeah :))

OriginalGriff 28-Mar-15 8:50am

No, it's inside a while loop using a DataReader still, I suspect!

DPaul1994 28-Mar-15 9:15am

So, using SQLiteCommand, inside this - using SQLiteDataReader - using SQLiteDataAdapter and here is the table?

OriginalGriff 28-Mar-15 9:21am

No.
Use an adapter to fill the table from the SELECT command instead of a DataReader, then use a foreach loop to access the rows of the DataTable. Then use a SqLiteCommand inside the loop to do the UPDATE.
That way there isn't a reader open when you try to do the UPDATE as the adapter reads and returns all the rows immediately - the reader doesn't which is why SqLite is complaining.

DPaul1994 28-Mar-15 10:09am

Ok, I'll try this :)

DPaul1994 29-Mar-15 9:48am

I didn't succeed to fix it. If you can write the code so I can see exactly what are you talking about, it's ok, else that's it. Thank you anyway for your time :)

OriginalGriff 29-Mar-15 9:55am

You won't get anywhere if you keep giving up all the time! :laugh:
Let's do this in easy stages.
What code did you end up with for your SELECT, using a DataAdapter?

DPaul1994 29-Mar-15 10:33am

Well, is not about I give up, I'm sick of this update :)) I tried to change the code in so many ways, but without success. This is my last try. I get the same error. I tried to close and reopen connection, than close is again, but with no succees. I tried with DataAdapter, with DataReader but it didn't work. I don't know to else to try. And I have to change only a value from a single row, don't understand why to use foreach loop..
<pre lang="c#">
private void UpdateTotalSim()
{
using (SQLiteCommand cmd = new SQLiteCommand(@"update accounts set totalsim=totalsim+1 where username='" + SimulatorManager.Utilizator + "'", Conexiune.getConnection()))
{
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}

private void button4_Click(object sender, EventArgs e)
{
if (check1.Checked == true) result1 = 1;
else result1 = 0;
if (check2.Checked == true) result2 = 1;
else result2 = 0;
if (check3.Checked == true) result3 = 1;
else result3 = 0;
string collect = "Select option1,option2,option3 from questions where `question`='" + SimulatorManager.Intrebare + "'";
SQLiteCommand comp = new SQLiteCommand(collect, Conexiune.getConnection());
comp.CommandType = CommandType.Text;
SQLiteDataReader read = comp.ExecuteReader(CommandBehavior.CloseConnection);
try
{
while (read.Read())
{
int op1 = (int)read["option1"];
int op2 = (int)read["option2"];
int op3 = (int)read["option3"];
if (check1.Checked == false && check2.Checked == false && check3.Checked == false)
MessageBox.Show("Selectati minim o optiune");
else
{
if ((result1 == op1) && (result2 == op2) && (result3 == op3))
{
corect++;
label4.Text = corect.ToString();
check1.Checked = false;
check2.Checked = false;
check3.Checked = false;
select();
}
else
{
incorect++;
label6.Text = incorect.ToString();
check1.Checked = false;
check2.Checked = false;
check3.Checked = false;
select();
}
if (corect + incorect == 1)
{
MessageBox.Show("Test terminat!");
if (corect == 1)
{
UpdateTotalSim(); // HERE I CALL THE UPDATE FUNCTION
MessageBox.Show("Bravo!");
}
else
{
MessageBox.Show("Bleah");
}
this.Close();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
</pre>

DPaul1994 29-Mar-15 10:40am

I don't know if it matters, but I have another function which select some data and is called on load form. (using SQLiteDataReader)

OriginalGriff 29-Mar-15 10:56am

Answer updated.
And it may - do you close the reader, command, and connection when you are finished with them?

DPaul1994 29-Mar-15 11:15am

What you did there is just an example of how I could do it, right? Cause is not what I have to do. First I have to collect and compare, then I have to update, you get that, right? No, I don't close them..connection is always opened. I try that now

OriginalGriff 29-Mar-15 11:25am

"I have to collect and compare, then I have to update"
That's what that does: the data adapter reads all the info from the DB and then returns it as a DataTable - a DateReader doesn't: it reads a line at a time each time you call the Read method, and the database is "busy" until the reader and it's associated command is closed. That's *why* you get an error when trying to UPDATE in your loop - the table is locked by the loop code.

Using a data adapter lets you read all the data, and then process it later after the command is complete and the table is unlocked.
Give it a try!

DPaul1994 29-Mar-15 13:09pm

Omg Smile | :) ) It worked. I will try to learn and understand this way to update with DataTable. Thank you for your patience and wish you the best!

OriginalGriff 29-Mar-15 13:42pm

You're welcome!