CODEWITHSUNDEEP
c#mysqlsyntax
Ramie
Ramie

Reputation: 1201

SQL Syntax Error C#

I am fairly new to C#, and i am trying to make a teacher management program.

This is the function that i am using to execute the query.

string commentString = "sC" + (y + 1) + "Y" + (i + 1) + "";
executeQuery("UPDATE student SET " + 
  commentString + " = '" + s.getStudentCourses(i,y,s)+
  "' WHERE sNumber = '" + s.getStudNumber(s) + "'");

My Query String:

query   "UPDATE student SET 'sComments1-1' = 'wa5235' WHERE sNumber = 68721919" string

The exception i get:

[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 ''sComments1-1' = 'wa5235' WHERE sNumber = 68721919' at line 1"}    MySql.Data.MySqlClient.MySqlException

Here is the SQL data structure:

CREATE TABLE `NewTable` (
`sNumber`  int(9) NOT NULL ,
`sFirstName`  varchar(32) NOT NULL ,
`sLastName`  varchar(32) NOT NULL ,
`sDOB`  varchar(9) NOT NULL ,
`sGrade`  int(1) NOT NULL ,
`sEmail`  varchar(32) NULL ,
`sParentName`  varchar(32) NOT NULL ,
`sParentPhone`  varchar(11) NOT NULL ,
`sHomeAddress`  varchar(32) NOT NULL ,
`sComments1-1`  varchar(255) NOT NULL ,

Using MySQL 5.5

I do not know why, but this is giving me sql errors. Please help me, my assignment is due in 2 days and i really need this finished.

Upvotes: 0

Views: 1005

Answers (4)

paxdiablo
paxdiablo

Reputation: 881103

There doesn't appear to be any way to get sComments1-1 from "sC" + (y + 1) + "Y" + (i + 1) + "" so I'm not sure why you think your query string is of the correct form.

In any case, your column names need to be surrounded by backticks rather than single quotes:

UPDATE student SET `sComments1-1` = ...

Assuming that sComments1-1 is a typo or earlier version, and should actually be one of multiple columns of the form sCaYb, where a and b are distinct integers, the code would look something like this:

// Get column name of form sC*Y* where *'s are distinct integers.

string commentColumn = "sC" + (y + 1) + "Y" + (i + 1);

// Execute query with correct quote types.

executeQuery("UPDATE student SET `" + commentColumn + "` = '" +
    s.getStudentCourses(i,y,s) +
    "' WHERE sNumber = " + s.getStudNumber(s));

I've added the backticks around the column name and removed them from the sNumber value (since it's an integer rather than a character column).

Upvotes: 0

CloudyMarble
CloudyMarble

Reputation: 37566

"UPDATE student SET 'sComments1-1' = 'wa5235' WHERE sN....

The Columnname 'sComments1-1' shouldnt be as a quoted string, just write the column name without the Quotes.

Upvotes: 0

Kyberias
Kyberias

Reputation: 1251

A duplicate of your problem (minus signs in column names) is asked and answered here:

MySQL INSERT - Do field names require backtick/accent delimination?

You need to use 'back-ticks' instead on single quotes when using the column name with a minus sign in your query. Like this:

UPDATE student SET `sComments1-1` = 'wa5235' WHERE sNumber = 68721919

Upvotes: 1

griegs
griegs

Reputation: 22760

Have you tried removing the single quotes around your field names? unsure this will work but it's one thing i'd try.

if this doesn't help, let me know and i'll remove this answer

Upvotes: 0

Related Questions