Question Enabling related values backward and forward from a value

[sifar786]

hi,

I have 2 tables called "modul" and "relation" in the attached database.

I have a form called "form1" which has
1] 2 radiobuttons: "All" & "Section" (only Section should be selected for this prob)
2] 1 dropdownlist: "Module" (e.g. B2)
3] 2 checkboxes: "Von" & "Nach" (both checked)
4] 1 NumericUpDown control: "Level" ( level will be 3)

I have successfully created the code for when "Von" is checked & also successfully created the code for when "Nach" is selected.

I am trying to figure out the code for when both "Von" & "Nach" are selected.

    Public Sub SectionModule(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim i, j As Integer
        Dim Node As String


        i = NumericUpDown1.Value
        j = i
        Node = ComboBox1.Text

        connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\sifar786\Desktop\WindowsApplication1.1\WindowsApplication1.1\NRA.mdb"
        myConnection = New OleDbConnection(connStr)
        myConnection.Open()

        'VON
        If CheckBox1.Checked = True And CheckBox2.Checked = False Then
            myCommand = New OleDbCommand("UPDATE relation, Modul SET relation.disabled = True,Modul.disabled = True", myConnection)
            myCommand.ExecuteNonQuery()
            myCommand = New OleDbCommand("UPDATE Modul SET Modul.disabled = False WHERE mod_name = '" & Node & "'", myConnection)
            myCommand.ExecuteNonQuery()

            While i > 0
                myCommand = New OleDbCommand("UPDATE relation SET disabled = false WHERE von IN (SELECT mod_name FROM modul WHERE disabled=false)", myConnection)
                myCommand.ExecuteNonQuery()
                myCommand = New OleDbCommand("UPDATE modul SET disabled=false WHERE mod_name IN(SELECT nach FROM relation WHERE von IN (SELECT mod_name FROM modul WHERE disabled = false))", myConnection)
                myCommand.ExecuteNonQuery()
                i -= 1
            End While

            'NACH
        ElseIf CheckBox2.Checked = True And CheckBox1.Checked = False Then
            myCommand = New OleDbCommand("UPDATE relation, Modul SET relation.disabled = True,Modul.disabled = True", myConnection)
            myCommand.ExecuteNonQuery()
            myCommand = New OleDbCommand("UPDATE Modul SET Modul.disabled = False WHERE mod_name = '" & Node & "'", myConnection)
            myCommand.ExecuteNonQuery()

            While i > 0
                myCommand = New OleDbCommand("UPDATE relation SET disabled = false WHERE nach IN (SELECT mod_name FROM modul WHERE disabled=false)", myConnection)
                myCommand.ExecuteNonQuery()
                myCommand = New OleDbCommand("UPDATE modul SET disabled=false WHERE mod_name IN(SELECT von FROM relation WHERE nach IN (SELECT mod_name FROM modul WHERE disabled = false))", myConnection)
                myCommand.ExecuteNonQuery()
                i -= 1
            End While

            'VON & NACH
        ElseIf CheckBox1.Checked = True And CheckBox2.Checked = True Then

            myCommand = New OleDbCommand("UPDATE relation, Modul SET relation.disabled = True,Modul.disabled = True", myConnection)
            myCommand.ExecuteNonQuery()
            myCommand = New OleDbCommand("UPDATE Modul SET Modul.disabled = False WHERE mod_name = '" & Node & "'", myConnection)
            myCommand.ExecuteNonQuery()

            'VON
            While j > 0
                myCommand = New OleDbCommand("UPDATE relation SET disabled = false WHERE von IN (SELECT mod_name FROM modul WHERE disabled=false)", myConnection)
                myCommand.ExecuteNonQuery()
                myCommand = New OleDbCommand("UPDATE modul SET disabled=false WHERE mod_name IN(SELECT nach FROM relation WHERE von IN (SELECT mod_name FROM modul WHERE disabled = false))", myConnection)
                myCommand.ExecuteNonQuery()
                j -= 1
            End While

            'NACH
            'Dim mod1, rel1 As ArrayList
            While i > 0
                myCommand = New OleDbCommand("UPDATE relation SET disabled = false WHERE nach IN (SELECT mod_name FROM modul WHERE disabled=false)", myConnection)
                myCommand.ExecuteNonQuery()
                myCommand = New OleDbCommand("UPDATE modul SET disabled=false WHERE mod_name IN(SELECT von FROM relation WHERE nach IN (SELECT mod_name FROM modul WHERE disabled = false))", myConnection)
                myCommand.ExecuteNonQuery()
                i -= 1
                'If i = 2 Then

                'myCommand = New OleDbCommand("select mod_name from modul where disabled=false", myConnection)
                'dr = myCommand.ExecuteReader()
                'While dr.Read()
                'mod1.Add(dr.item("mod_name")
                'End While

                'End If
            End While

            'NONE TICKED
        ElseIf CheckBox1.Checked = False And CheckBox2.Checked = False Then

            MsgBox("None checked")
            myCommand = New OleDbCommand("UPDATE relation, Modul SET relation.disabled = false,Modul.disabled = false", myConnection)
            myCommand.ExecuteNonQuery()
            Me.Close()
            End
        End If
        myConnection.Close()

    End Sub

When "Von" is selected, the code iterates Forward as per the selected "Level" and sets the values in disabled columns in both tables modul and relation to "FALSE".
the output if B2 is selected in "Module" with "Von" checked and "Level" as 3 is:

C2, C3, D2, E1 which will have their disabled column value set to false.

When "Nach" is selected, the code iterates Backwards as per the selected "Level" and sets the values in disabled columns in both modul and relation to "FALSE".
the output if B2 is selected in "Module" with "Nach" checked and "Level" as 3 is:

A1 which will have its disabled column value set to false.


now when i check both Von & Nach, i dont want them to set disabled column values for b1, c1 which are not members and are getting selected due to the update queries.

the output should only produce or set disabled columns values to false for the following:

A1, C2, C3, D2, E1

I know this is not so easy to understand, but the person who knows about a GDL file will understand the code easily.

for e.g. when Von selected, level 3, Module B2:
(You will have to run each query on the 2 joined tables to see what i mean)

for 1st iteration (level 3) : Nodes C2, C3
for 2nd iteration (level 2) : Nodes D2, E1
for 3rd iteration (level 1) : only Node E1 as it does not have any corresponding edge.

for e.g. when Nach selected, level 3, Module B2:

for 1st iteration (level 3) : Nodes A1
for 2nd iteration (level 2) : Only Node A1 selected as it does not have corresponding edge.
for 3rd iteration (level 1) : Nothing selected.

So only these values in disabled columns should be set to False.

Help requested.