Jon H. Jon H. - 5 months ago 7
SQL Question

Error in your SQL syntax - c# - MySQL

I know this is probably something very simple I messed up. Can anyone tell me where I went wrong with my Replace Command Text?

cmd.CommandText = $"UPDATE `{dataTableName}` " +
"SET (`entry`, `zone`, `x_axis`, `y_axis`, `z_axis`, `situation`, `faction`, `type`) " +
"VALUES (@Entry, @Zone, @X, @Y, @Z, @Situation, @Faction, @Type)" +
"WHERE zone = @Zone AND x_axis = @X AND y_axis = @Y";
//Add data value with Parameters.
cmd.Parameters.AddWithValue("@Entry", entry);
cmd.Parameters.AddWithValue("@Zone", zone);
cmd.Parameters.AddWithValue("@X", x);
cmd.Parameters.AddWithValue("@Y", y);
cmd.Parameters.AddWithValue("@Z", z);
cmd.Parameters.AddWithValue("@Situation", situation);
cmd.Parameters.AddWithValue("@Type", type);
cmd.Parameters.AddWithValue("@Faction", faction);

Answer

The space before the where is not the issue. The problem is the list of columns. MySQL requires set col = val, . . . for the syntax. Some databases (such as Oracle) do allow lists of columns, but even those do not use a VALUES keyword.

So, structure the code like this:

cmd.CommandText = $"UPDATE `{dataTableName}`" +
                    " SET `entry` = @Entry, `z_axis` = @Z, . . ." +
                    " WHERE zone = @Zone AND x_axis = @X AND y_axis = @Y";

Note also that you do not need to set zone, x_axis, and y_axis because the where clause requires that these already have the correct values.