Decrement value by 1 MS Access SQL and C#

Question

I'm facing a problem with decrementing value with MS Access database.

I get an error

Syntax error in UPDATE Statement

My code:

connection.Open();
command = new OleDbCommand();
command.Connection = connection;
command.CommandText = " update Cards set Count = Count - 1 where Type=" + ct + " ";
command.ExecuteNonQuery();
connection.Close();

Can anyone please help?

Use parameters to avoid sql injection and to fix your problem. — LarsTech, Nov 13 '18 at 17:03
Count is a reserved word. Put it in brackets: `[Count]` Terrible choice for a column name, by the way. Use NumCards or something like that. — LarsTech, Nov 13 '18 at 17:24
you just need to add single quotes around the type value like this: `command.CommandText = " update Cards set Count = Count - 1 where Type='" + ct + "' ";` — themehio, Nov 13 '18 at 17:24
@themehio Stop it. Parameters is the only "safe way" to fix this mistake. If you submit code like that at a job interview, you aren't getting the job. — LarsTech, Nov 13 '18 at 17:29
Thaaaanks Mister @LarsTech the brackets solved it ,It caused by reserved words — Omar, Nov 13 '18 at 17:43
Mister @LarsTech I have another question, I want to change many values in my table by **card type** how can i do it — Omar, Nov 13 '18 at 17:48

score 1 · Accepted Answer · answered Nov 13 '18 at 17:07

1

You should provide an actual error. My guess is that count is a keyword and has to be put in square brackets like so [count] and do use parameters, see Joel's answer

answered Nov 13 '18 at 17:07

Daniel N

1,122
1
8
14

score 0 · Answer 2 · answered Nov 13 '18 at 17:05

It's not certain, but I strongly suspect it's missing single quotes around ct. Fix it like this:

using (var connection = new OleDbConnection("connection string here"))
using (var command = new OleDbCommand("update Cards set Count = Count - 1 where Type= ?", connection))
{
    //have to guess at the OleDbType value. Use the actual column type and length from the database
    cmd.Parameters.Add("?", OleDbType.VarWChar, 10).Value = ct;
    connection.Open();
    command.ExecuteNonQuery();
}

There are several other important fixes in this pattern, too.

Decrement value by 1 MS Access SQL and C#

2 Answers2