How to compare values?

[prishe]

hi.. i need help in comparing value from the same table for my final project.

example :

table 1 :

SubjectCode Classes
Sub A class 1
Sub A class 2
Sub A class 3
Sub B class 2
Sub B class 3
Sub B class 4
Sub B class 5
Sub C class 6
Sub C class 7
Sub C class 8
...

my task is, i need to compare the classes in the subject. Sub A with class 1, class 2, class 3 have to compare with Sub B and Sub C. if classes from Sub A exist in Sub B then the output is 0, if not the output is 1. then Sub A again will compare with Sub C and the rest. After Sub A compare its value with other subject. Sub B will start comparing with all the subject and so on. Below is the concept.

Sub A Sub B Sub C
Sub A 0 0 1
Sub B 0 0 0
Sub C 1 0 0

i really don't know to do the loop.. seeking for your help.. thank you.

Don't know if this is what you want, but maybe it will give you some ideas.

This produces:
0 0 1
0 0 1
1 1 0

Which from your explanation, Is how I determined the result to be. It doesn't match your example output but I'm assuming that was for "example"

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Table: Data table filled with Subjects and classes
        'Subjects(): Array containing a distinct list of all subjects
        '  in this case A,B,C
        'Output:  Array containing the results.  One string for each subject.
        'Rows: Temporary holder to get the count of subjects with mathcing classes.
        'ClassFilter: String containing the filter string of all the
        '  classes for the current subject.
        '  If subject was A it would be class = 1 or class = 2 or class = 3

        Dim Table As DataTable = GetTable()
        Dim Subjects() As String = GetDistinctSubjects(Table)
        Dim Output(UBound(Subjects)) As String
        Dim Rows() As DataRow
        Dim ClassFilter As String

        'Iterate through each subject.
        For i = 0 To UBound(Subjects)

            'Start off the output string to the current subject.
            'Get the filter string for the current subject.
            Output(i) = "Subject " & Subjects(i)
            ClassFilter = GetClassFilter(Table, Subjects(i))

            'Should always have a value but skip it if it doesn't.
            If String.IsNullOrEmpty(ClassFilter) Then Continue For

            For j = 0 To UBound(Subjects)

                'Check if the subject to be checked is the same as the 
                'current subject "i". If so set its value to 0
                'and keep going.
                If j = i Then
                    Output(i) &= " 0"
                    Continue For
                End If

                'Get the total rows that contain classes that match the 
                'Current subject's classes for the subject being compared.
                Rows = Table.Select("Subject = '" & Subjects(j) & "' And (" & ClassFilter & ")")

                'Set the appropriate value.
                'If the value is = 0 then the boolean value will be true -1
                'if the value is <> 0 then the boolean value will be 0
                'use math.abs to change -1 to 1
                Output(i) &= " " & CStr(Math.Abs(CInt(CBool(Rows.GetLength(0) = 0))))

            Next
        Next
    End Sub

    Private Function GetTable() As DataTable
        'Returns a table filled with subject and class values
        Dim Table As New DataTable

        Table.Columns.Add("Subject", GetType(String))
        Table.Columns.Add("Class", GetType(Integer))

        Table.Rows.Add(New Object() {"A", "1"})
        Table.Rows.Add(New Object() {"A", "2"})
        Table.Rows.Add(New Object() {"A", "3"})
        Table.Rows.Add(New Object() {"B", "2"})
        Table.Rows.Add(New Object() {"B", "3"})
        Table.Rows.Add(New Object() {"B", "4"})
        Table.Rows.Add(New Object() {"B", "5"})
        Table.Rows.Add(New Object() {"C", "6"})
        Table.Rows.Add(New Object() {"C", "7"})
        Table.Rows.Add(New Object() {"C", "8"})

        Return Table
    End Function

    Private Function GetDistinctSubjects(ByVal table As DataTable) As String()
        'returns a string array with no duplicated subjects.
        Dim DistinctRows() As String = New String() {}
        For i = 0 To table.Rows.Count - 1
            If Array.IndexOf(DistinctRows, table.Rows(i).Item("Subject")) = -1 Then
                ReDim Preserve DistinctRows(DistinctRows.GetLength(0))
                DistinctRows(UBound(DistinctRows)) = CStr(table.Rows(i).Item("Subject"))
            End If
        Next
        Return DistinctRows
    End Function

    Private Function GetClassFilter(ByRef table As DataTable, ByVal subject As String) As String
        'returns a string containing each class value for the subject passed in.
        'If subject A return value class = 1 or class = 2 or class = 3
        Dim Rows() As DataRow = table.Select("Subject = '" & subject & "'")
        Dim ClassFilter As String = String.Empty

        For i = 0 To UBound(Rows)
            If Not String.IsNullOrEmpty(ClassFilter) Then ClassFilter &= " Or "
            ClassFilter &= "Class = " & CStr(Rows(i).Item("Class"))
        Next

        Return ClassFilter
    End Function

[prishe]

sir, yes this is what i want. i have modified the code to import the data from MySQL database and tried to display the output in datagridview but i didn't get any output. i don't know where it when wrong. please help me and am a beginner in VB. thank you.

Imports System.Data.Odbc
Public Class Form1
    Dim oc As OdbcConnection
    Dim cmd As OdbcCommand
    Dim dr As OdbcDataReader
    Dim Table As DataTable
    Dim ClassFilter As String
    Dim Rows() As DataRow
    Dim Subjects() As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        oc = New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=EXAM; User=root;Password=;Option=3;")
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Table: Data table filled with Subjects and classes
        'Subjects(): Array containing a distinct list of all subjects
        '  in this case A,B,C
        'Output:  Array containing the results.  One string for each subject.
        'Rows: Temporary holder to get the count of subjects with mathcing classes.
        'ClassFilter: String containing the filter string of all the
        '  classes for the current subject.
        '  If subject was A it would be class = 1 or class = 2 or class = 3

        Table = GetTable()
        Subjects = GetDistinctSubjects(Table)
        Dim Output(UBound(Subjects)) As String
        'Iterate through each subject.
        For i = 0 To UBound(Subjects)

            'Start off the output string to the current subject.
            'Get the filter string for the current subject.
            Output(i) = "Subject_Code" & Subjects(i)
            ClassFilter = GetClassFilter(Table, Subjects(i))

            'Should always have a value but skip it if it doesn't.
            If String.IsNullOrEmpty(ClassFilter) Then Continue For

            For j = 0 To UBound(Subjects)

                'Check if the subject to be checked is the same as the 
                'current subject "i". If so set its value to 0
                'and keep going.
                If j = i Then
                    Output(i) &= " 0"
                    Continue For
                End If

                'Get the total rows that contain classes that match the 
                'Current subject's classes for the subject being compared.
                Rows = Table.Select("Subject_Code = '" & Subjects(j) & "' And (" & ClassFilter & ")")

                'Set the appropriate value.
                'If the value is = 0 then the boolean value will be true -1
                'if the value is <> 0 then the boolean value will be 0
                'use math.abs to change -1 to 1
                Output(i) &= " " & CStr(Math.Abs(CInt(CBool(Rows.GetLength(0) = 0))))

            Next
        Next
        DataGridView1.DataSource = Output '******to display*********
    End Sub

    Private Function GetTable() As DataTable
        oc.Open()
        Try
            cmd = New OdbcCommand("SELECT * FROM DIP_DATA", oc)
            dr = cmd.ExecuteReader
            dr.Read()
            If dr.HasRows Then
                cmd = New OdbcCommand("SELECT * FROM DIP_DATA", oc)
                Table = New DataTable("DIP_DATA")
            Else
                MessageBox.Show("Data doesn't exist!!")
            End If

        Catch ex As Exception
            MessageBox.Show("CONNECTION ERROR")
        End Try
        dr.Close()
        oc.Close()
        Return Table
    End Function

    Private Function GetDistinctSubjects(ByVal table As DataTable) As String()
        'returns a string array with no duplicated subjects.
        Dim DistinctRows() As String = New String() {}
        For i = 0 To table.Rows.Count - 1
            If Array.IndexOf(DistinctRows, table.Rows(i).Item("Subject_Code")) = -1 Then
                ReDim Preserve DistinctRows(DistinctRows.GetLength(0))
                DistinctRows(UBound(DistinctRows)) = CStr(table.Rows(i).Item("Subject_Code"))
            End If
        Next
        Return DistinctRows
    End Function

    Private Function GetClassFilter(ByRef table As DataTable, ByVal Subject_Code As String) As String
        'returns a string containing each class value for the subject passed in.
        'If subject A return value class = 1 or class = 2 or class = 3
        Rows = table.Select("Subject_Code = '" & Subject_Code & "'")
        ClassFilter = String.Empty

        For i = 0 To UBound(Rows)
            If Not String.IsNullOrEmpty(ClassFilter) Then ClassFilter &= " Or "
            ClassFilter &= "Course_ID = " & CStr(Rows(i).Item("Course_ID"))
        Next

        Return ClassFilter
    End Function

End Class

This version uses a datatable to store the output.

Heres a modified version. I took out the if statement of If j = i to make the post shorter and show it doesn't actually need to be there. If its checking itself then there will always be a value so the result will always be 0.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Table As DataTable = GetTable()
        Dim Subjects() As String = GetDistinctSubjects(Table)
        Dim Rows() As DataRow
        Dim ClassFilter As String
        Dim OutputTable As New DataTable
        Dim OutputRow As DataRow

        'Add a name column and add a column for each subject.
        OutputTable.Columns.Add(New DataColumn("Name", GetType(String)))
        For i = 0 To UBound(Subjects)
            OutputTable.Columns.Add(New DataColumn("Subject" & Subjects(i), GetType(Integer)))
        Next


        For i = 0 To UBound(Subjects)

            ClassFilter = GetClassFilter(Table, Subjects(i))

            'create a new row and set the first column to the
            'name of the current column and add it to the table
            OutputRow = OutputTable.NewRow
            OutputRow(0) = "Subject " & Subjects(i)
            OutputTable.Rows.Add(OutputRow)

            If String.IsNullOrEmpty(ClassFilter) Then Continue For

            For j = 0 To UBound(Subjects)

                Rows = Table.Select("Subject = '" & Subjects(j) & "' And (" & ClassFilter & ")")
                
                'add the result.  the subjects array is 0 based but we added a 
                'name column in front of the subjects in the table.  The subjects
                'in the table are 1 based.  So just add 1 to the iterator.
                'removed the cstr from the first example. No longer adding to a 
                'string array but columns that are integers.
                OutputRow(j + 1) = Math.Abs(CInt(CBool(Rows.GetLength(0) = 0)))

            Next
        Next

        DataGridView1.DataSource = OutputTable

    End Sub

[prishe]

sir, when i run the code, i got an error "Syntax error: Missing operand after 'DIT' operator." at line :

Rows = Table.Select("Subject_Code = '" & Subjects(j) & "' And (" & ClassFilter & ")")

the class imported for the corresponding subject is " 1DIT and 2DIT ".
what does it means?? thank you.

Add a ' and ' where it creates the filter string. It currently only works with numbers.

ClassFilter &= "Class = '" & CStr(Rows(i).Item("Class")) & "'"

[prishe]

it works.. thousands of thanks for your help sir.