SQL Update - Not updating multiple rows in a for loop

I have a DataGridView(DGV) linked to a SQlite database开发者_StackOverflow社区. I want to update some info on the DGV. So, I have a context menu that lets me change one column and update the DB as well. I want to have the ability to select multiple rows and edit as well. For ex: if i select five rows and change the type from alarms to errors ; the change is reflected in the DGV and then when i look into the database , the change isnt reflected. Only one row is updated.

My code snippet is below

foreach (DataGridViewRow r in dataGridView1.SelectedRows)
        {
            SQLiteTransaction SQLiteTrans = connection.BeginTransaction();
            SQLiteCommand cmd = connection.CreateCommand();
            MessageBox.Show(r.ToString());
            if (r.Cells["typeDataGridViewTextBoxColumn"].Value.ToString().Contains("#") == false)
            {
                r.Cells["typeDataGridViewTextBoxColumn"].Value = r.Cells["typeDataGridViewTextBoxColumn"].Value.ToString() + " # " + max;
            }
            else
            {
                r.Cells["typeDataGridViewTextBoxColumn"].Value = r.Cells["typeDataGridViewTextBoxColumn"].Value.ToString().Substring(0, r.Cells["typeDataGridViewTextBoxColumn"].Value.ToString().IndexOf("#")) + "# " + max;
            }
            string querytext = "Update LogDatabase set Type = \"" + r.Cells["typeDataGridViewTextBoxColumn"].Value + "\" where HashKey = \"" + r.Cells["hashKeyDataGridViewTextBoxColumn"].Value.ToString() + "\"";
            cmd.CommandText = querytext;
            cmd.ExecuteNonQuery();
            SQLiteTrans.Commit();    
        }

I dont have too much experience with SQL. So, im not sure if something is wrong with how ive updated the database!

What do i have to edit to make sure all the rows are updated in the DB as well?!

Help appreciated.

Edit: Tried checking the query before its sent.

When i try editing multiple rows in the DGV without sorting the DGV under any column it works and updates all the rows simultaneously... But When I try to sort them based on "Type" and then edit the rows, the same query is passed ! :| (Hash Key doesnt change)

Its like, the one row keeps moving up the list of rows and is always the row r in the for loop.

Edit 2: Its definitely a problem with the rows of the DGV Everytime I sort the DGV and then try to edit the fields, the queries have hashkey values different from the once that i selected. Its like the row ids are changed completely after one update. It looks like the DGV automatically sorts once one row is updated !

Is there a way to disable this???!

It looks like you weren't increasing max counter.

foreach (DataGridViewRow row in dataGridView1.SelectedRows)
{
using (SQLiteTransaction SQLiteTrans = connection.BeginTransaction())
{
    SQLiteCommand cmd = connection.CreateCommand();
    MessageBox.Show(row.ToString());

    var txtValue = row.Cells["typeDataGridViewTextBoxColumn"].Value;
    if (txtValue.Contains("#"))
    {
        txtValue = String.Format("{0} # {1}", txtValue, max);
    }else
    {
        txtValue = txtValue.SubString(0, txtValue.IndexOf("#")) + "# " + max.ToString();
    }
    string querytext = "Update LogDatabase set Type = @type where HashKey = @key";
            //Create your SqlParameters here!
    cmd.CommandText = querytext;
    cmd.ExecuteNonQuery();
    SQLiteTrans.Commit();    
    max++; //You weren't increasing your counter!!
}
}

Your problem is that you are using double quotes in your SQL query to delimit your string values. Replace them for single quotes:

string querytext = "Update LogDatabase set Type = '" + 
                    r.Cells["typeDataGridViewTextBoxColumn"].Value + 
                    "' where HashKey = '" +  
                    r.Cells["hashKeyDataGridViewTextBoxColumn"].Value.ToString() + 
                    "'";

Also, beware of SQL Injection. Your code is not dealing with that.