Here i am again.
Thank you so much for help.
Here is my Old question on how i can select and display the grade of student in a junction table.
I did it successfully but now i am having trouble updating the grade in the junction table.
Here is my Example table again.
"student"
-----------------------
|studentID | FullName |
-----------------------
|1234 | John |
|1235 | Michael |
|1236 | Bryce |
"subject_bsit"
-----------------------------------
|subject_id| subject_name |grade |
-----------------------------------
| 1 | Programming | 3 |
| 2 | Networking | 2.5 |
| 3 | Algorithm | 1.75|
| 4 | Physical Educ | 2 |
This is the Junction table to connect the
two now.
"student_subject"
----------------------------
| student_id | subject_id |
----------------------------
| 1235 | 1 |
| 1235 | 2 |
| 1235 | 3 |
| 1234 | 1 |
And here is the query that i did.
This is a example im trying to update the grade of studentID 1235 that have subject_id = 1
UPDATE 3:
sql = "UPDATE student_subject " & _
" INNER JOIN subject_bsit " & _
" ON subject_bsit.subject_id = student_subject.sub_id " & _
" SET grade = 1 " & _
" where student_subject.student_id='" & Txtbox.Text & "' AND student_subject.sub_id = 1"
The & Txtbox.Text & is where the user input the studentid don’t mind it. Thank you so much im using visual studion vb.net.
And this is the exact error that i got.
You have an error in SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near ‘FROM
student_subject INNER JOIN subject_bsit ON subject_bsit.subject_id =
studen’ at line 1
2
Answers
Your query is wobbly ,it should be like below
First, you don’t UPDATE student_subject.
Since there is no student_id column in your subject_bsit table you can’t relate the grade to a student.
UPDATE subject_bsit
Set grade = @grade
Where subject_ID = @subject_ID
This would accomplish the same thing.
This is the schema I suggest.
students
grades
Two Foreign Keys
grades.studentID (FK) <- students.studentID (PK)
grades.subjectID (FK) <- subjects.subjectID (PK)
Notice the grades table has a composite key consisting of 2 fields.
subjects
Please always use parameters when communicating with the database. This will help protect your database from Sql Injection.
The Using blocks close and dispose your database objects even if there is an error.
To display a students grades, add a DataGridView to your form.