-2

OBJECTIVE:

I am trying to get all the values in the column "test_results" from the table "test" where the studentID is the equal to some variable. I am trying to use the COUNT function in SQL to do this query however I keep getting an eror:

C# CODE:

MySqlCommand cmd = new MySqlCommand("SELECT COUNT(test_results) FROM test WHERE test.StudentID ='" + student + "';");

ERROR:

"cannot find specificed column in results: test_results"

Which is weird because I have no table named "results". Where did I make an error?

Thank You

EDIT:

MySqlCommand cmd = new MySqlCommand("SELECT COUNT(test_results) AS test WHERE studentID = '" + student + "';");

Gets rid of the previous error however I have another error which says:

you have an error in your SQL syntax near 'WHERE studentID = '6" at line 1

CsharpStudent
  • 61
  • 8

1 Answers1

1

Instead of

"SELECT COUNT(test_results) FROM test WHERE test.StudentID ='" + student + "';"

Do aliasing of the column:

"SELECT COUNT(*) as test_results FROM test WHERE test.StudentID ='" + student + "';"

Also, always use parameterized statement. See this post to know more:

Community
  • 1
  • 1
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • @CsharpStudent - So you don't have a test_results column in your test table.Use `count(*)` then. – Gurwinder Singh Mar 05 '17 at 18:56
  • 1
    Please make a query, run it on the database directly to make sure it's working correctly and then use it in your code. If you need further help, post the table schema – Gurwinder Singh Mar 05 '17 at 19:00
  • Also when you say use parameterised statements, is this statement not parameterised? and if not, do you know any useful resources where I can find more information on parameterised SQL? Thank you – CsharpStudent Mar 05 '17 at 19:17
  • @CsharpStudent - Added a link in the answer for you – Gurwinder Singh Mar 05 '17 at 19:20