My friend told me that i need to set my prerequisite id as varchar and store the prerequisite separated by comma ?
This is what i have now. i manage to get and check prerequisite id base on the subject id but i can only store and check one prerequisite because i store it as integer but now i change it to varchar and seperated the other prerequisite by comma. See my database
This is all i have right now. This code work for only one prerequiste.
Private Sub EnrollStudent(ByVal StudentID As Integer, ByVal SubjectID As Integer)
'**CHECKING IF SUBJECT HAS A PREREQUISITE
Dim Prerequisite As Integer
Using cn As New MySqlConnection(connstring)
Using cmd As New MySqlCommand("Select pre_id from subject where subject_id = @subject_id", cn)
cmd.Parameters.Add("@subject_id", MySqlDbType.Int32).Value = SubjectID
cn.Open()
Prerequisite = CInt(cmd.ExecuteScalar)
End Using
End Using
'**ANSWER = NO / NO PREREQUISITE ? DO THIS -> CALL THE INSERTENROLLMENT FUNCTION
If Prerequisite = 0 Then
InsertEnrollment(StudentID, SubjectID, lbl_As_SchoolYear.Text)
Return
End If
'**ANSWER = YES / THERE IS A PREREQUISITE
'**CHECKING IF THE SUBJECT IS PASSED
'**CHECKING FOR INC GRADES SINCE INC IS STRING
Dim PassingGradeStr As String
Using cn As New MySqlConnection(connstring)
Using cmd As New MySqlCommand("Select grade FROM student_subject WHERE sub_id =@sub_id AND student_id =@student_id", cn)
cmd.Parameters.Add("@sub_id", MySqlDbType.Int32).Value = Prerequisite
cmd.Parameters.Add("@student_id", MySqlDbType.Int32).Value = StudentID
cn.Open()
PassingGradeStr = CStr(cmd.ExecuteScalar)
End Using
End Using
'**CHECKING IF THE GRADE IS INC / IF INC THEN EXIT SUB
If PassingGradeStr = "INC" Then
MessageBox.Show("GRADE STILL INC")
Exit Sub
End If
'**PASSING GRADE IS NOT INC / SO GRADE MUST BE INTEGER
Dim PassingGrade As Integer
Using cn As New MySqlConnection(connstring)
Using cmd As New MySqlCommand("Select Grade From student_subject Where sub_id = @sub_id And student_id = @student_id", cn)
cmd.Parameters.Add("@sub_id", MySqlDbType.Int32).Value = Prerequisite
cmd.Parameters.Add("@student_id", MySqlDbType.Int32).Value = StudentID
cn.Open()
PassingGrade = CInt(cmd.ExecuteScalar)
End Using
End Using
'**CHECKING IF THE GRADE IS GREATER THAN 0 / IF TRUE THEN IT IS PASSED / CALL INSERTENROLLMENT FUNCTION
If PassingGrade > 0 Then
If PassingGrade = 4 Then
MessageBox.Show("Prerequisite Subject is 4")
Exit Sub
End If
If PassingGrade = 5 Then
MessageBox.Show("Prerequisite Subject is Failed")
Exit Sub
End If
InsertEnrollment(StudentID, SubjectID, lbl_As_SchoolYear.Text)
Else
MessageBox.Show("Student cannot enroll because of prerequisite.")
End If
End Sub
I manage to do it by doing one of the comment suggestion. by adding another column for prerequisite., I know this is not really efficient way to do this so any suggestion i will gladly try it. Thanks.
I'll be posting what i did to fix mine anyway.
UPDATE:
A bit messed up cause i duplicate all the code i have to check for the another column that i add.
'**CHECKING IF SUBJECT HAS A PREREQUISITE
Dim Prerequisite As Integer
Using cn As New MySqlConnection(connstring)
Using cmd As New MySqlCommand("Select pre_id from subject where subject_id = @subject_id", cn)
cmd.Parameters.Add("@subject_id", MySqlDbType.Int32).Value = SubjectID
cn.Open()
Prerequisite = CInt(cmd.ExecuteScalar)
End Using
End Using
'**ANSWER = NO / NO PREREQUISITE ? DO THIS -> CALL THE INSERTENROLLMENT FUNCTION
Dim Prerequisite2 As Integer
Using cn As New MySqlConnection(connstring)
Using cmd As New MySqlCommand("Select pre_id2 from subject where subject_id = @subject_id", cn)
cmd.Parameters.Add("@subject_id", MySqlDbType.Int32).Value = SubjectID
cn.Open()
Prerequisite2 = CInt(cmd.ExecuteScalar)
End Using
End Using
If Prerequisite = 0 Then
InsertEnrollment(StudentID, SubjectID, lbl_As_SchoolYear.Text)
Return
End If
If Prerequisite2 = 0 Then
InsertEnrollment(StudentID, SubjectID, lbl_As_SchoolYear.Text)
Return
End If
'**ANSWER = YES / THERE IS A PREREQUISITE
'**CHECKING IF THE SUBJECT IS PASSED
'**CHECKING FOR INC GRADES SINCE INC IS STRING
Dim PassingGradeStr As String
Using cn As New MySqlConnection(connstring)
Using cmd As New MySqlCommand("Select grade FROM student_subject WHERE sub_id =@sub_id AND student_id =@student_id", cn)
cmd.Parameters.Add("@sub_id", MySqlDbType.Int32).Value = Prerequisite
cmd.Parameters.Add("@student_id", MySqlDbType.Int32).Value = StudentID
cn.Open()
PassingGradeStr = CStr(cmd.ExecuteScalar)
End Using
End Using
Dim PassingGradeStr2 As String
Using cn As New MySqlConnection(connstring)
Using cmd As New MySqlCommand("Select grade FROM student_subject WHERE sub_id =@sub_id AND student_id =@student_id", cn)
cmd.Parameters.Add("@sub_id", MySqlDbType.Int32).Value = Prerequisite2
cmd.Parameters.Add("@student_id", MySqlDbType.Int32).Value = StudentID
cn.Open()
PassingGradeStr2 = CStr(cmd.ExecuteScalar)
End Using
End Using
'**CHECKING IF THE GRADE IS INC / IF INC THEN EXIT SUB
If PassingGradeStr = "INC" Then
MessageBox.Show("GRADE STILL INC")
Exit Sub
End If
If PassingGradeStr2 = "INC" Then
MessageBox.Show("GRADE STILL INC")
Exit Sub
End If
'**PASSING GRADE IS NOT INC / SO GRADE MUST BE INTEGER
Dim PassingGrade As Integer
Using cn As New MySqlConnection(connstring)
Using cmd As New MySqlCommand("Select Grade From student_subject Where sub_id = @sub_id And student_id = @student_id", cn)
cmd.Parameters.Add("@sub_id", MySqlDbType.Int32).Value = Prerequisite
cmd.Parameters.Add("@student_id", MySqlDbType.Int32).Value = StudentID
cn.Open()
PassingGrade = CInt(cmd.ExecuteScalar)
End Using
End Using
Dim PassingGrade2 As Integer
Using cn As New MySqlConnection(connstring)
Using cmd As New MySqlCommand("Select Grade From student_subject Where sub_id = @sub_id And student_id = @student_id", cn)
cmd.Parameters.Add("@sub_id", MySqlDbType.Int32).Value = Prerequisite2
cmd.Parameters.Add("@student_id", MySqlDbType.Int32).Value = StudentID
cn.Open()
PassingGrade2 = CInt(cmd.ExecuteScalar)
End Using
End Using
'**CHECKING IF THE GRADE IS GREATER THAN 0 / IF TRUE THEN IT IS PASSED / CALL INSERTENROLLMENT FUNCTION
If PassingGrade > 0 And PassingGrade2 > 0 Then
If PassingGrade = 4 Or PassingGrade2 = 4 Then
MessageBox.Show("Prerequisite Subject is 4")
Exit Sub
End If
If PassingGrade = 5 Or PassingGrade2 = 5 Then
MessageBox.Show("Prerequisite Subject is Failed")
Exit Sub
End If
InsertEnrollment(StudentID, SubjectID, lbl_As_SchoolYear.Text)
Else
MessageBox.Show("Student cannot enroll because of prerequisite.")
End If
