C# local database update error

Question

The issue is that I'm trying to write into a table called Login. It has a few columns, there's a column with no data in (null) that I'm trying to insert / update the column is SuperSecretKey - nvarchar(100).

Heres the code:

public void InsertSecretKey(String SuperKey, int IDKey)
{
    conn = new SqlCeConnection(@"Data Source=|DataDirectory|\Database1.sdf");
    conn.Open();

    SqlCeCommand cmd = new SqlCeCommand();
    cmd.CommandText = "UPDATE Login SET SuperSecretKey = @SuperKey WHERE Key=@IDKEY;";
    cmd.Connection = conn;

    cmd.Parameters.AddWithValue("@SuperKey", SuperKey);
    cmd.Parameters.AddWithValue("@IDKey", IDKey);

    cmd.ExecuteNonQuery();
    conn.Close();
}

Here's the error:

There was an error parsing the query.
[ Token line number = 1,Token line offset = 51,Token in error = Key ]

Any help? Thanks!

You are using Sql Server Compact then do not tag this question with MySql — Steve
– Steve, Commented Jan 20, 2015 at 17:47

RobIII · Accepted Answer · 2015-01-20 17:59:18Z

5

Key is a reserved word. The error is pretty specific: offset = 51 => at position 51 there's the word key. It actually tells you: Token in error = Key.

You need to escape it, e.g. [key]. It is a good practice to escape all your fieldnames, tables etc.:

cmd.CommandText = "UPDATE [Login] SET [SuperSecretKey] = @SuperKey WHERE [Key]=@IDKEY;";

What isn't a reserved keyword in the current version of SQL Server may be in a new/future release¹. Most ORM's etc. that generate ("dynamic") queries escape these values by default, just to be "on the safe side".

¹ Even if a keyword isn't on that list currently, it may be added in a future-future release. You'll probably be safe with names like CustomerId and MyVerySpecificName but generic words like Key _{(you just found that out)}, GUID, Version or Descending, though currently not reserved, are just not (future-)safe to use.

edited Jan 20, 2015 at 17:59

answered Jan 20, 2015 at 17:47

RobIII

8,8414 gold badges48 silver badges99 bronze badges

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

Comments

meda · Accepted Answer · 2015-01-20 17:46:27Z

3

Key is a reserverd keyword

use brackets to escape it [key]

For the full list check Reserved Keywords (Transact-SQL)

answered Jan 20, 2015 at 17:46

meda

45.5k14 gold badges97 silver badges123 bronze badges

Collectives™ on Stack Overflow

C# local database update error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related