SQLite update syntax

[DPaul1994]

I have a code in C# that should update a column form my table. Table is `accounts`, row is `totalsimyes` and new value that should be inserted in column is `totalsimda`.

string updatecorect = "select totalsimyes from accounts where username = 'DPaul'";
                                SQLiteCommand updatec = new SQLiteCommand(updatecorect, Conexiune.getConnection());
                                SQLiteDataReader upd = updatec.ExecuteReader();
                                while(upd.Read())
                                {
                                        int totalsimda = (Int32)upd["totalsimyes"];
                                        totalsimda++;
                                        using (SQLiteCommand update = new SQLiteCommand("update accounts set totalsimyes=@totalsimda where username='DPaul'", Conexiune.getConnection()))
                                        {
                                            update.Parameters.AddWithValue("@totalsimda", totalsimda);
                                            int rows = update.ExecuteNonQuery();
                                        }
                                }

I don't receive any errors, but when I get in this code at runtime, it just locks and I get do anything.

 

[MakeItSo]

Hi there,

to me, this seems overly complicated. You read from your table and try to update the record you currently have an open connection to, which probably locks the record.
Of course you could try fixing this by setting the reader connection options to read-only.
However, it also seems that all you want to do is increment totalsimyes by 1 for all records where username='DPaul'. Is that assumption correct?
Have you tried simply using the sql update command directly and executing it using SqlCommand ExecuteNonReader method?
Example:

SQLiteConnection Conexiune=new SQLiteConnection("Data Source=C:\\00_Projekte_temp\\sqlite\\test.sqlite;Version=3;");
string updatecorect = "update accounts set totalsimyes=totalsimyes+1 where username = 'DPaul'";
SQLiteCommand updatec = new SQLiteCommand(updatecorect, Conexiune);
updatec.ExecuteNonQuery();

Cheers,
MakeItSo

ôKnowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.ö (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[DPaul1994]

I tried this solution right now and it works just the same.

 

[MakeItSo]

Please post the exact code portion you used.
Did you replace the loop and DataReader portion with my suggestion?
What do you mean with "lock"?


ôKnowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.ö (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[DPaul1994]

I find out where is the problem, but I don't really know how to solve it. That is the code. THe problem is dat `database is locked`. Which means, when I open that select, I can't use that update because i already have an opened select. But I;m looking for a solution without using oepn() and close() for connections cause I use a class for connection which I use for every query (Conexiune.getConnection())

 

[MakeItSo]

when I open that select, I can't use that update

That's what I meant.
Replace this whole shebang:

string updatecorect = "select totalsimyes from accounts where username = 'DPaul'";
                                SQLiteCommand updatec = new SQLiteCommand(updatecorect, Conexiune.getConnection());
                                SQLiteDataReader upd = updatec.ExecuteReader();
                                while(upd.Read())
                                {
                                        int totalsimda = (Int32)upd["totalsimyes"];
                                        totalsimda++;
                                        using (SQLiteCommand update = new SQLiteCommand("update accounts set totalsimyes=@totalsimda where username='DPaul'", Conexiune.getConnection()))
                                        {
                                            update.Parameters.AddWithValue("@totalsimda", totalsimda);
                                            int rows = update.ExecuteNonQuery();
                                        }
                                }

with this:

string updatecorect = "update accounts set totalsimyes=totalsimyes+1 where username = 'DPaul'";
using(SQLiteCommand updatec = new SQLiteCommand(updatecorect, Conexiune))
{
  updatec.ExecuteNonQuery();
}

And you should do fine. There is absolutely no need to do a select in your case.

Hope this helps.
MakeItSo

ôKnowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.ö (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[DPaul1994]

The error persists..

 

[MakeItSo]

Sorry, forgit to open the connection:

string updatecorect = "update accounts set totalsimyes=totalsimyes+1 where username = 'DPaul'";
using(SQLiteCommand updatec = new SQLiteCommand(updatecorect, Conexiune))
{
  Conexiune.Open();
  updatec.ExecuteNonQuery();
  Conexiune.Close();
}

If this does not work either, please post the entire code that handles this.

ôKnowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.ö (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.