Pat Pat - 1 year ago 222
SQL Question

C# MySQL Insert

The following C# code is producing an "SQL Syntax Error..."

string query = "INSERT INTO jobscollection (number,desc,client,jobtype,statustype,initials) VALUES(@number,@desc,@client,@jobtype,@statustype,@initials)";

MySqlCommand cmd = new MySqlCommand( query, connection );

cmd.Parameters.Add( "@number", MySqlDbType.VarChar ).Value = JobNumber;
cmd.Parameters.Add( "@desc", MySqlDbType.VarChar ).Value = Description;
cmd.Parameters.Add( "@client", MySqlDbType.Int32 ).Value = clientId;
cmd.Parameters.Add( "@jobtype", MySqlDbType.Int32 ).Value = typeID;
cmd.Parameters.Add( "@statustype", MySqlDbType.Int32 ).Value = statusId;
cmd.Parameters.Add( "@initials", MySqlDbType.VarChar ).Value = Initials;


Link below is an image of the table in MySQL workbench. Also the clientID, jobtype and status types are foreign key to IDs in other tables (and the IDs do exist)

MySQL Table

Can anyone spot my error?

Here is the full exception message

{MySql.Data.MySqlClient.MySqlException (0x80004005): 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 'desc,client,jobtype,statustype,initials) VALUES('1007.01','Test',3,2,3,'PI')' at line 1
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at PCE.DatabaseUtility.InsertNewJob(String JobNumber, String Description, Int32 clientId, Int32 typeID, Int32 statusId, String Initials)}

And the connection string

string connectionString;
connectionString = "SERVER=" + server + ";" + "DATABASE=" +
database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";"
+ "Allow User Variables=true;";

Answer Source

DESC is a reserved (My)SQL keyword.

You'll need to either rename your column or escape it in the query, like this:

INSERT INTO jobscollection (number,`desc`,client,jobtype,statustype,initials) VALUES(@number,@desc,@client,@jobtype,@statustype,@initials)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download