skip to Main Content

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

2

Answers


  1. 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
    
    Login or Signup to reply.
  2. 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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search