SQL UPDATE Error in C# Console Application

Question

Using MS Visual Studio 2010, MS Access 2010(database converted to .mdb) and the program is based on C# Console Application.

I'm new to C# and would like some help on the following:

1.I'm struggling with the following an error:

Syntax error in UPDATE statement.

I've tried everything but can't seem to find the problem. I've even looked at possible error outside the UPDATE statement but still no luck. The online help does not seem to help me with my problem and most of the help is regarding a connection with a database server.

for (int w = 0; w <= 9; w++)
{
      string PN = names[w];
      int lvl = Convert.ToInt32(level[w]), dmg = Convert.ToInt32(damage[w]), plek = w+1;
      connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=C:\Users\Home\Desktop\game\game.mdb";
      //Tried this one
      // queryString = "UPDATE Scores SET PlayerName=\""+PN+"\", Level=\"" +lvl+"\", DamageReceived=\""+dmg+"\" WHERE Pos="+ plek;
      //Ended with this one
      queryString = "UPDATE Scores SET PlayerName= @pPN, Level= @plvl, DamageReceived= @pdmg WHERE Pos= @pPlek";

      using (OleDbConnection connection = new OleDbConnection(connectionString))
      using (OleDbCommand command = new OleDbCommand(queryString, connection))
      {
           try
           {
                 command.Parameters.AddWithValue("@pPN", PN);
                 command.Parameters.Add("@plvl", SqlDbType.Int).Value = lvl;
                 command.Parameters.Add("@pdmg", SqlDbType.Int).Value = dmg;
                 command.Parameters.Add("@pPlek", SqlDbType.Int).Value = plek;

                 connection.Open();
                 /*command.Parameters.AddWithValue("@PN", PN);
                 command.Parameters.AddWithValue("@lvl", lvl);
                 command.Parameters.AddWithValue("@dmg", dmg);
                 command.Parameters.AddWithValue("@plek", plek);*/

                 command.ExecuteNonQuery();                                   
           }
           catch (Exception ex)
           {
                 Console.WriteLine(ex.Message);
           }
        }
}

input = Console.ReadLine();

The Error that I'm getting

I would appreciate any help. thanks in advance.

not sure if you refer to command or OleDbCommand itself but here is both: command = UPDATE Scores SET PlayerName= @pPN, Level= @plvl, DamageReceived= @pdmg WHERE Pos= @pPlek OleDbCommand = System.Data.OleDb.OleDbCommand — sgtBlueBird
– sgtBlueBird, Commented Apr 20, 2014 at 10:31

Michał Komorowski · Accepted Answer · 2014-04-20 10:27:16Z

2

The problem is that OleDbCommand doesn't support named parameters. You have to use the question mark ? instead. Try something like:

UPDATE Scores SET PlayerName= ?, Level= ?, DamageReceived= ? WHERE Pos= ?

Here you will find the full example.

answered Apr 20, 2014 at 10:27

Michał Komorowski

6,2681 gold badge23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sgtBlueBird Over a year ago

Okay I did the following: queryString = "UPDATE Scores SET PlayerName= ?, Level= ?, DamageReceived= ? WHERE Pos= ?"; and

command.Parameters.Add(PN);                             command.Parameters.Add(lvl);                             command.Parameters.Add(dmg);                             command.Parameters.Add(plek);

But now I get the following Error: The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects.

Gord Thompson Over a year ago

cc: @sgtBlueBird - Actually, OleDb does allow parameters to have names, but it ignores those names and only pays attention to the position of the parameters in the CommandText. Hence the use of ? is recommended but is not strictly required. Also, the parameter names are not the cause of the error in this case.

Gord Thompson · Accepted Answer · 2014-04-20 10:50:57Z

1

LEVEL is a reserved word in Access SQL. Try using this instead:

queryString = "UPDATE Scores SET PlayerName= @pPN, [Level]= @plvl, DamageReceived= @pdmg WHERE Pos= @pPlek";

Also, as noted in another answer, OleDb ignores parameter names and only pays attention to the order in which they appear in the CommandText, so people usually just use the question mark (?) as the parameter placeholder/name, e.g.

queryString = "UPDATE Scores SET PlayerName=?, [Level]=?, DamageReceived=? WHERE Pos=?";
// ...
command.Parameters.AddWithValue("?", PN);
command.Parameters.Add("?", SqlDbType.Int).Value = lvl;
command.Parameters.Add("?", SqlDbType.Int).Value = dmg;
command.Parameters.Add("?", SqlDbType.Int).Value = plek;

answered Apr 20, 2014 at 10:50

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Collectives™ on Stack Overflow

SQL UPDATE Error in C# Console Application

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related