How to update row in junction table for student grade

Question

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.

Select and display all rows belonging to a specific ID

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

Answer 1

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

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| studentID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| studentName | varchar(100) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

grades

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| studentID | int(11)      | NO   | PRI | NULL    |       |
| subjectID | int(11)      | NO   | PRI | NULL    |       |
| grade     | decimal(3,2) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

Two Foreign Keys

1. grades.studentID (FK) <- students.studentID (PK)

2. grades.subjectID (FK) <- subjects.subjectID (PK)

Notice the grades table has a composite key consisting of 2 fields.

subjects

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| subjectID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| subjectName | varchar(100) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

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.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim query As String = "Update grades 
                           Set grade = @grade
                           Where studentId = @studentId And subjectID = @subjectID"
    Using cn As New MySqlConnection(My.Settings.StudentConnection)
        Using cmd As New MySqlCommand(query, cn)
            cmd.Parameters.Add("@grade", MySqlDbType.Decimal).Value = 1
            cmd.Parameters.Add("@studentID", MySqlDbType.Int32).Value = CInt(TextBox1.Text)
            cmd.Parameters.Add("@subjectID", MySqlDbType.Int32).Value = 1
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

To display a students grades, add a DataGridView to your form.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim dt As New DataTable
    Dim query As String = "Select st.studentName, sub.subjectName, g.grade
                            From grades g
                            Inner Join  students st  on g.studentID = st.studentID
                            Inner Join subjects sub on g.subjectID = sub.subjectID
                            Where st.studentID = @studentID;"
    Using cn As New MySqlConnection(My.Settings.StudentConnection)
        Using cmd As New MySqlCommand(query, cn)
            cmd.Parameters.Add("@studentID", MySqlDbType.Int32).Value = CInt(TextBox1.Text)
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    DataGridView1.DataSource = dt
End Sub

Answer 2

Your query is wobbly ,it should be like below

UPDATE student_subject 
INNER JOIN subject_bsit 
ON subject_bsit.subject_id = student_subject.sub_id 
SET grade=? 
where student_subject.student_id='" & Txtbox.Text & "' AND student_subject.sub_id = 1