WPF复选框关联SQL数据库更新报错求助：CASE语句提示‘near =’

Got it, let's work through why your code is throwing that SQL error and get your checkbox toggle working both ways properly.

First, the SQL CASE Statement Mistake #

Your CASE syntax is incorrect—you don't put assignments inside THEN/ELSE, and you had an extra Prova keyword that doesn't belong here. Let's fix that:

Your original broken SQL:

UPDATE tabList SET selection = (CASE Prova WHEN tabList.selection.Value = ' 0 ' THEN tabList.selection.Value = ' 1' ELSE tabList.selection.Value = ' 0 ' END) WHERE idL = @CURRENT

Corrected SQL (depending on your selection column type):

If selection is an integer (0/1, recommended for boolean-like values):

UPDATE tabList 
SET selection = CASE WHEN selection = 0 THEN 1 ELSE 0 END 
WHERE idL = @CURRENT

If selection is a string (not ideal, but if you have to use it):

UPDATE tabList 
SET selection = CASE WHEN selection = '0' THEN '1' ELSE '0' END 
WHERE idL = @CURRENT

Note: I removed the extra spaces around '0'/'1'—if your database actually stores values with spaces, put them back, but that's usually a mistake.

Second, Fixing the Command and Connection Issues #

You're using SqlCommand which is for SQL Server, but you're working with SQLite. You should use SQLiteCommand instead (from the System.Data.SQLite namespace). Also, using using statements will automatically handle closing connections and disposing resources, which is better than manually opening/closing.

Third, Don't Use DataGrid Index for Database ID #

Your current code uses currentRowIndex + 1 as the idL value—this is a big risk! If you ever sort, filter, or delete rows in your DataGrid, the index won't match the database's idL anymore. Instead, get the idL directly from the bound item.

Full Corrected Code #

Assuming your DataGrid is bound to a collection of objects with an idL property (e.g., a TabListItem class):

private void Row_DoubleClick(object sender, MouseButtonEventArgs e)
{
    // Get the actual bound item instead of relying on index
    var currentItem = dataGrid1.CurrentItem as TabListItem;
    if (currentItem == null)
    {
        MessageBox.Show("No row selected!");
        return;
    }

    // Use using statements to auto-manage connection/command resources
    using (var sqliteCon = new SQLiteConnection("your_connection_string_here"))
    {
        sqliteCon.Open();
        if (sqliteCon.State == ConnectionState.Open)
        {
            // Corrected SQL toggle logic
            string q = @"UPDATE tabList 
                         SET selection = CASE WHEN selection = 0 THEN 1 ELSE 0 END 
                         WHERE idL = @CURRENT";

            using (var cmd = new SQLiteCommand(q, sqliteCon))
            {
                // Use the actual idL from the selected item
                cmd.Parameters.AddWithValue("@CURRENT", currentItem.idL);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Dato Modificato");
            }
        }
    }
}

Quick Notes to Avoid Future Issues #

• Make sure your selection column in SQLite matches the value type you're using (integer vs string). If it's a boolean column, you can use CASE WHEN selection = 0 THEN 1 ELSE 0 END too, since SQLite stores booleans as integers.
• Always use the actual database ID from your bound entity instead of DataGrid indexes—indexes are volatile and don't map to database keys reliably.
• Using using statements for connections and commands prevents resource leaks, which is crucial for desktop apps.

内容的提问来源于stack exchange，提问作者user11094517