CODEWITHSUNDEEP
c#mysql
tuskcode
tuskcode

Reputation: 337

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;

cmd.ExecuteNonQuery();

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

Upvotes: 1

Views: 161

Answers (1)

Tobia Tesan
Tobia Tesan

Reputation: 1936

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)

Upvotes: 2

Related Questions