Click here to Skip to main content
15,877,168 members
Please Sign up or sign in to vote.
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
Updated 22-Dec-20 22:33pm

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?
 
Share this answer
 
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 :))
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
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900