C# MySQL Syntax Error using Update Command

Question

Good day all. I have encountered yet another issue interacting with my database. This time with the update command.

I have a table with the following columns:

Identity Number (int, auto-increment)
Username (varchar)
Password (varchar)
Authority Level (int)
Last Login (timestamp)

I'm trying to update the username, password, and authority level using the identity number.

Here's the code I'm trying to execute:

public static async Task<bool> UpdateAdministrator(
    int identityNumber,
    string username,
    string password,
    int authorityLevel)
{
    try
    {
        await OpenConnection();
        MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.CommandText = "UPDATE Administrators SET Username = @Username, Password = @Password, 'Authority Level' = @Authority_Level WHERE 'Identity Number' = @Identity_Number;";
        mySqlCommand.Parameters.Add("@Identity_Number", MySqlDbType.Int32).Value = identityNumber;
        mySqlCommand.Parameters.Add("@Username", MySqlDbType.VarChar).Value = username;
        mySqlCommand.Parameters.Add("@Password", MySqlDbType.VarChar).Value = password;
        mySqlCommand.Parameters.Add("@Authority_Level", MySqlDbType.Int32).Value = authorityLevel;
        await mySqlCommand.ExecuteNonQueryAsync();
        mySqlCommand.Dispose();
        await CloseConnection();
        return true;
    }
    catch
    {
        return false;
    }
}

Here's the exception I'm getting:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Authority Level' = 0 WHERE 'Identity Number' = 1' at line 1

Could someone help me find out what's wrong? Thanks in advance

spaces are not allowed in columns i guess Authority Level this is wrong — Ehsan Sajjad
– Ehsan Sajjad, Commented Feb 16, 2016 at 12:57

fubo · Accepted Answer · 2016-02-16 13:08:45Z

2

just repalce ' with `

in mySQL the ` character is used to identify table / column names

The identifier quote character is the backtick (“`”):

so your code should look like

mySqlCommand.CommandText = "UPDATE Administrators SET Username = @Username, Password = @Password, `Authority Level` = @Authority_Level WHERE `Identity Number` = @Identity_Number;";

edited Feb 16, 2016 at 13:08

answered Feb 16, 2016 at 12:59

fubo

46.2k20 gold badges109 silver badges139 bronze badges

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

1 Comment

Tayab Over a year ago

Thank you so much! The code works now. The single quote and back tick characters look so similar to each other it's easy to make the mistake the first time. I'll keep this in mind and will try not to repeat this mistake again. Cheers :)

Ehsan Sajjad · Accepted Answer · 2016-02-16 14:46:24Z

0

It is all about syntax ' You can use the actual fields of tables like [...]

SET [Username] = @Username, [Password] = @Password, [Authority Level] = @Authority_Level WHERE [Identity Number] = @Identity_Number;";

edited Feb 16, 2016 at 14:46

Ehsan Sajjad

62.6k16 gold badges112 silver badges171 bronze badges

answered Feb 16, 2016 at 13:10

alidrsn

1091 silver badge10 bronze badges

Collectives™ on Stack Overflow

C# MySQL Syntax Error using Update Command

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related