MySQL server error - You have an error in your SQL syntax

Question

I'm trying to update a Database table and getting the error

"MySql.Data.MySqlClient.MySqlException: '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 'group='superadmin' WHERE identifier='steam:steam:1100001098b5888'' at line 1'"

        // Creates query to run
public void UpdateInfo(String jobTitle, int jobGrade, String adminLevel, String identifier) {

    // Opens the database connection if it's not already open
    if (!(databaseConnected)) {
        openConnection();
    }

    // Creates query to run
    String query = "UPDATE " + table + " SET job=@jobTitle, job_grade=@jobGrade, group=@adminLevel WHERE identifier=@identifier";

    // Makes a new command
    MySqlCommand cmd = new MySqlCommand(query, connection);

    // Replaces the @ placeholders with actual variables
    cmd.Parameters.AddWithValue("@jobTitle", jobTitle);
    cmd.Parameters.AddWithValue("@jobGrade", jobGrade);
    cmd.Parameters.AddWithValue("@adminLevel", adminLevel);
    cmd.Parameters.AddWithValue("@identifier", identifier);

    // Executes it and if it's...
    if (cmd.ExecuteNonQuery() > 0) {
        // Successful
        MessageBox.Show("Successfully updated information");

        closeConnection();
        return;
    } else {
        // Not successful
        MessageBox.Show("Error with updating information!");

        // Closes the connection again to prevent leaks
        closeConnection();
        return;
    }

}

Please show us the schema (column types) for the table you are updating. — mjwills
– mjwills, Commented Jan 21, 2019 at 4:51
They're all strings except for job_grade is an int. It's not that, I can fetch the information fine but somehow something is wrong with my update. — Jordan Fleetwood
– Jordan Fleetwood, Commented Jan 21, 2019 at 5:56

J.H · Accepted Answer · 2019-01-21 06:04:37Z

3

I tried your query on https://sqltest.net/ and noticed it highlighted "group" when I tried to create the table. I'm wondering if the problem might be the usage of "group" as a column name since it's a reserved word.

Is it possible to try renaming the column to group_level or adding back ticks around 'group' or "group" and seeing if that works?

So for example

'group'=@grouplevel

I found this thread and this thread on renaming the column where they had issues with "group" as a column name. Adding backticks seemed to solve both problems.

EDIT: As per OP, double quotes (") solved the issue instead of single. Edited answer to include.

edited Jan 21, 2019 at 6:04

answered Jan 21, 2019 at 5:09

J.H

1161 silver badge9 bronze badges

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

Comments

Arphile · Accepted Answer · 2019-01-21 04:52:27Z

-1

Try change query like this

String query = "UPDATE " + table + " SET job='@jobTitle', job_grade=@jobGrade, group='@adminLevel' WHERE identifier='@identifier'";

if you input String value with query, you need to use 'this' for work

I hope this will work for you.

if not, you can use String.Format for that like this.

String Query = String.Format("Update `{0}` Set job='{1}', job_grade={2}, group='{3}' Where identifier='{4}'", table, jobTitle, jobGrade, adminLevel, identifier);

answered Jan 21, 2019 at 4:52

Arphile

8616 silver badges18 bronze badges

2 Comments

Kaushik Over a year ago

Don't you think, this placeholders works without ' also? Have you tried?

mjwills Over a year ago

Please do not do either of those solutions.

Collectives™ on Stack Overflow

MySQL server error - You have an error in your SQL syntax

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related