CODEWITHSUNDEEP
c#mysqlsyntax
Tayab
Tayab

Reputation: 311

C# MySQL Syntax Error using Update Command

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

Upvotes: 2

Views: 376

Answers (2)

alidrsn
alidrsn

Reputation: 109

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;";

Upvotes: 0

fubo
fubo

Reputation: 45947

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;";

Upvotes: 2

Related Questions