[digitaldevelopment]

there is a database, containing a table with personnel. in the personnel table there are names and ID's
there is a second table departemens, containing the names of departements and their ID's.
in the third table it says which departement ID and which personnel ID are to be linked,
example
the employee called ruben has an ID 5
the employee called kevin has an ID 10
the departement webdesign has an ID 2
the departement android has an ID 6
if ruben works in webdesign this means employee number 5 is linked to departement ID 2
the problem is that ruben can also works for android departement. so his ID should also be linked to departement ID 6
kevin works for android to, sho personnel 10 is linked to departement 6
now I have a form, containing a listbox where all the departemens will be loaded into.

now let's say the user selects the option android.
how can I make the program return every name of the persons working in android (or if I select webdevelopment, all the personnel of webdevelopment, etc)

This post has been edited by digitaldevelopment: 27 May 2012 - 06:11 AM

[zeeshanef]

Please show your code.
And what error you receive!

[digitaldevelopment]

no error, the problem I have is I have absolutely NO idea how to start on this. I get as far as retrieving names by searching names:

    Private Sub QuerySelections()
        'Declare variables and objects
        Dim strConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\bedrijf.accdb;Persist Security Info=True;Jet OLEDB:Database Password=miniemen"
        Dim objConnection As New OleDbConnection(strConnectionString)
        Dim strSQL As String = "SELECT personeel.naam FROM personeel,afdelingnaam WHERE (personeel.beschikbaar = True) and (afdelingnaam.naam = '" & ListBox1.SelectedItem.ToString & "')"
        Console.WriteLine(strSQL)
        Dim objCommand As New OleDbCommand(strSQL, objConnection)
        Dim objDataAdapter As New OleDbDataAdapter(objCommand)
        Dim objDataTable As New Data.DataTable("personeel")
        Dim objDataRow As DataRow

        Try
            'Open the database connection
            objConnection.Open()
        Catch OleDbExceptionErr As OleDbException
            'Write the exception
            Console.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            'Write the exception
            Console.WriteLine(InvalidOperationExceptionErr.Message)
        End Try
        'Declare an OleDbDataReader object
        Dim objDataReader As OleDbDataReader

        Try
            'Execute the SQL text
            objDataReader = objCommand.ExecuteReader()

            'Check to see if we have data
            If objDataReader.HasRows Then
                'Process all rows
                While objDataReader.Read()
                    'Get the data in each column
                    lstFound.Items.Add(objDataReader.Item(0).ToString) 'returns the data
                End While
            End If
            'Close the reader
            objDataReader.Close()

        Catch OleDbExceptionErr As OleDbException
            Console.WriteLine(OleDbExceptionErr.Message)
        End Try
        'Close the database connection
        objConnection.Close()
        'Clean up
        objDataRow = Nothing
        objDataTable.Dispose()
        objDataTable = Nothing
        objDataAdapter.Dispose()
        objDataAdapter = Nothing
        objCommand.Dispose()
        objCommand = Nothing
        objConnection.Dispose()
        objConnection = Nothing
        Console.WriteLine(ListBox1.SelectedItem.ToString)
    End Sub
#End Region
    Private Sub zoek_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        QueryOptions("naam", "afdelingnaam") 'retrieves every departement
    End Sub
    Private Sub o_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
        'lstFound.Items.Clear()
        'Dim needed As String = ListBox1.SelectedItem.ToString()
        'Console.WriteLine(needed)
        'QuerySelections()
        Dim mypath = Application.StartupPath & "\bedrijf.accdb"
        Dim mypassword = ""
        Dim conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\bedrijf.accdb;Persist Security Info=True;Jet OLEDB:Database Password=miniemen")
        'Dim TblName As String = "personeel"
        Dim TblName As String = "personeel"
        Dim searchText As String
        Dim sqlQuery As String
        searchText = txtSearch.Text
        Dim sql = "SELECT naam FROM personeel WHERE Lcase(naam) LIKE '" & searchText.ToLower() & "'"
        Console.WriteLine(sql)
        Dim cmd = New OleDbCommand(sql, conn)
        conn.Open()
        Try
            Dim dr As OleDbDataReader = cmd.ExecuteReader
            Do While dr.Read()
                dr(0).ToString()
                Console.WriteLine(dr(0).ToString)
            Loop
            If Label3.Text = "" Then
                Label1.Visible = False
                Label2.Visible = True
                Label2.Text = "No records for the selected criteria"
                Label3.Visible = False
                ' no matches found / ничего не найдено
            Else
                Label1.Visible = True
                Label2.Visible = False
                ' records found
            End If
            conn.Close()
        Catch ex As NullReferenceException
            Console.WriteLine(ex)
        Catch ex2 As OleDbException
            Console.WriteLine(ex2)
        End Try
    End Sub

[luamage]

Hai der ;3 Post your SQL data pl0x :3

[luamage]

Okay Problem solves :3

[digitaldevelopment]

basicly I need to retrieve the information marked on the picture below...

the relations between the tables look like this (some extra info highlighted)

[luamage]

Niice...

Do Not:
Ask us to do your homework or project for you [Digit: CHECK]
Copy/Paste your homework assignment [Digit: CHECK]

Do:
Post your code between

tags [Digit: CHECK]
Include details about errors and what your question is [Digit: CHECK]
Use proper English and spelling [Digit: UNCHECK]
Tell us what you've tried to solve your problem [Digit: CHECK]

[digitaldevelopment]

ahum, luamage this answer does not support the question at all!
we can all read the rules ourselves.