SQL Syntax Error C#

Question

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.

Also... I suggest you google the term "SQL Injection". Your code has a pretty severe security flaw that you should learn about as soon as you can. — Eric J.
– Eric J., Commented Jan 19, 2012 at 5:21

Community · Accepted Answer · 2017-05-23 11:55:49Z

1

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

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Jan 19, 2012 at 5:22

Kyberias

1,2611 gold badge15 silver badges23 bronze badges

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

Comments

paxdiablo · Accepted Answer · 2012-01-19 05:29:51Z

0

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).

edited Jan 19, 2012 at 5:29

answered Jan 19, 2012 at 5:23

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

1 Comment

Ramie Over a year ago

Thank you, you saved me LOL even though it's been a year, THANK YOU!

griegs · Accepted Answer · 2012-01-19 05:20:38Z

0

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

answered Jan 19, 2012 at 5:20

griegs

22.8k33 gold badges132 silver badges208 bronze badges

Comments

CloudyMarble · Accepted Answer · 2012-01-19 05:24:13Z

0

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

answered Jan 19, 2012 at 5:24

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

Collectives™ on Stack Overflow

SQL Syntax Error C#

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related