Problems retrieving from database

[MadAxel]

Im doing an application that runs on vb.net using oracle database.
The function that i did is to select 3 combo boxes for the information to come out.
The problem is i can only retrieve the records from the first selectedindex=0 of the ifelse statement and not from the others.
That means if i choose finance & administration, the next combobox will show controlling and accounting and the last combobox will show the first record of the IDM_IDEA_DATA table. When i changed the sec combobox to procurement & logistics, there are no changes.

The form is at my attachments.
Here are my codes:

Private Sub cboDept_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDept.SelectedIndexChanged
        cboSect.Items.Clear()
        If Me.cboDept.SelectedIndex = 0 Then
            Me.cboSect.Items.Add("CONTROLLING & ACCOUNTING")
            Me.cboSect.Items.Add("PROCUREMENT & LOGISTICS")
            Me.cboSect.Items.Add("INFORMATION TECHNOLOGY")
            cboSect.SelectedIndex = 0
            If Me.cboSect.SelectedIndex = 0 Then
                Dim conn As OleDbConnection
                Dim da As OleDbDataAdapter
                Dim ds As New DataSet
                Dim da1 As OleDbDataAdapter
                Try
                    'cmd = New OleDbCommand("Select Distinct * From IDM_IDEA_DATA Where SECT='CONTROLLING & ACCOUNTING'", conn)
                    'Dim dr As OleDbDataReader = cmd.ExecuteReader
                    'dr.Read()
                    'Me.lblId.Text = dr("ID").ToString
                    conn = New OleDbConnection(My.Settings.ConnectionString)
                    da = New OleDbDataAdapter("Select Distinct SECT, TEAMNAME From IDM_IDEA_DATA Where SECT='CONTROLLING & ACCOUNTING'", conn)
                    da.Fill(ds, "IDM_IDEA_DATA")
                    Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
                    Me.cboTeamName.DisplayMember = "TEAMNAME"
                    da1 = New OleDbDataAdapter("Select Distinct * From IDM_IDEA_DATA Where SECT='CONTROLLING & ACCOUNTING'", conn)
                    da1.Fill(DataSet1, "IDM_IDEA_DATA")
                Catch ex As Exception

                End Try
            ElseIf Me.cboSect.SelectedIndex = 1 Then
                Dim conn As OleDbConnection
                Dim da As OleDbDataAdapter
                Dim da1 As OleDbDataAdapter
                Dim ds As New DataSet
                Try
                    conn = New OleDbConnection(My.Settings.ConnectionString)
                    da = New OleDbDataAdapter("Select Distinct SECT, TEAMNAME From IDM_IDEA_DATA Where SECT='PROCUREMENT & LOGISTICS'", conn)
                    da.Fill(ds, "IDM_IDEA_DATA")
                    Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
                    Me.cboTeamName.DisplayMember = "TEAMNAME"
                    da1 = New OleDbDataAdapter("Select Distinct * From IDM_IDEA_DATA Where SECT='PROCUREMENT & LOGISTICS'", conn)
                    da1.Fill(DataSet1, "IDM_IDEA_DATA")
                Catch ex As Exception

                End Try
             End If

I really need help on this. Hope u guys can help me out. Thx

[Teme64]

You should handle SelectedIndexChanged event for cboDept and cboTeamNames too. If I understand this right, you have an organization hierarchy (dept -> sect -> team)
So, I would use something like this (in pseudo-code):

Private Sub cboDept_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDept.SelectedIndexChanged

<< User selected Dept, retrieve and fill Sect >>
  Select Case cboDept.SelectedIndex
    Case 0 ' Finance & Administration
      << retrieve and fill Sect with Controlling & Accounting >>
    ...
    Case Else ' SelectedIndex = -1, no Dept selected clear/disable cboSect and cboTeamName
  End Select

End Sub

Private Sub cboSect_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboSect.SelectedIndexChanged

<< User selected Sect, retrieve and fill TeamName >>
  Select Case cboSect.SelectedIndex
    Case 0 ' Controlling & Accounting
      << retrieve and fill Team with Fico >>
    ...
    Case Else ' SelectedIndex = -1, no Sect selected clear/disable cboTeamName
  End Select

End Sub

Private Sub cboTeamName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboTeamName.SelectedIndexChanged

<< User selected TeamName, retrieve and show PDesc and IDesc information >>

End Sub

[MadAxel]

There are so many TeamName.
Do u mean i need to hardcode every TeamName there is?

[Teme64]

It seems to me that you do have your team names id DB (field TEAMNAME). So, after user selects Sect, get team names for that Sect from DB and fill combobox. I assume that this is static data and also without knowing how you organized your organization structure to DB.

Suppose you have following (simplified) DB structure:

Departments-table:
DeptID DeptName
1 "dept a"
2 "dept b"

Section-table:
SectID DeptID SectName
1 1 "sect a"
2 1 "sect b"
3 2 "sect c"

Team-table:
TeamID SectID TeamName
1 1 "team a"
2 2 "team b"
3 3 "team c"
4 3 "team d"

First you fill cboDept. When user selects "dept b" (DeptID = 2), you fill cboSect with "sect c", in SQL: "SELECT * FROM Section WHERE DeptID=2". Now when user selects "sect c" (SectID=3), you fill team names with "team c" and "team d", in SQL: "SELECT * FROM Team WHERE SectID=3".

This is just a suggestion, how I would tackle this problem. If you don't have this kind of DB structure it might not work. There's also some "optimization" possibilities, like fetch data only once. And in this scenario you don't hardcode any team names, just map combobox indexes to tables's ID values.

I hope this gives you started.

[MadAxel]

thx..ive solved the problem but now i have a new problem.
AFter doing all that,i need to display information in the textboxes.What i did is i binded the textboxes to the values in the databases.but the thing is,i cannot sort it according to the TEAMNAME.That means everything from the database is displayed.

This is the code that ive used:

Private Sub cboSect_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboSect.SelectedIndexChanged
        'cboTeamName.Items.Clear()
        If cboDept.SelectedIndex = 0 Then
            If cboSect.SelectedIndex = 0 Then
                Dim conn As OleDbConnection
                Dim da As OleDbDataAdapter
                Dim da1 As OleDbDataAdapter
                Dim ds As New DataSet

                conn = New OleDbConnection(My.Settings.ConnectionString)
                da = New OleDbDataAdapter("Select Distinct TEAMNAME From IDM_IDEA_DATA Where SECT='CONTROLLING & ACCOUNTING'", conn)
                da.Fill(ds, "IDM_IDEA_DATA")
                Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
                Me.cboTeamName.DisplayMember = "TEAMNAME"
                da1 = New OleDbDataAdapter("Select Distinct * From IDM_IDEA_DATA Where Sect='CONTROLLING & ACCOUNTING'", conn)
                da1.Fill(DataSet1, "IDM_IDEA_DATA")
            ElseIf cboSect.SelectedIndex = 1 Then
                Dim conn As OleDbConnection
                Dim da As OleDbDataAdapter
                Dim da1 As OleDbDataAdapter
                Dim ds As New DataSet

                conn = New OleDbConnection(My.Settings.ConnectionString)
                da = New OleDbDataAdapter("Select Distinct TEAMNAME From IDM_IDEA_DATA Where SECT='PROCUREMENT & LOGISTICS'", conn)
                da.Fill(ds, "IDM_IDEA_DATA")
                Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
                Me.cboTeamName.DisplayMember = "TEAMNAME"
                da1 = New OleDbDataAdapter("Select Distinct * From IDM_IDEA_DATA Where Sect='CONTROLLING & ACCOUNTING'", conn)
                da1.Fill(DataSet1, "IDM_IDEA_DATA")
            Else
                Dim conn As OleDbConnection
                Dim da As OleDbDataAdapter
                Dim da1 As OleDbDataAdapter
                Dim ds As New DataSet

                conn = New OleDbConnection(My.Settings.ConnectionString)
                da = New OleDbDataAdapter("Select Distinct TEAMNAME From IDM_IDEA_DATA Where SECT='INFORMATION TECHNOLOGY'", conn)
                da.Fill(ds, "IDM_IDEA_DATA")
                Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
                Me.cboTeamName.DisplayMember = "TEAMNAME"
                da1 = New OleDbDataAdapter("Select Distinct * From IDM_IDEA_DATA Where Sect='CONTROLLING & ACCOUNTING'", conn)
                da1.Fill(DataSet1, "IDM_IDEA_DATA")
            End If
        ElseIf cboDept.SelectedIndex = 1 Then
            If cboSect.SelectedIndex = 0 Then
                Dim conn As OleDbConnection
                Dim da As OleDbDataAdapter
                Dim ds As New DataSet

                conn = New OleDbConnection(My.Settings.ConnectionString)
                da = New OleDbDataAdapter("Select Distinct TEAMNAME From IDM_IDEA_DATA Where SECT='HUMAN RESOURCES'", conn)
                da.Fill(ds, "IDM_IDEA_DATA")
                Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
                Me.cboTeamName.DisplayMember = "TEAMNAME"
            End If
        ElseIf cboDept.SelectedIndex = 2 Then
            If cboSect.SelectedIndex = 0 Then
                Dim conn As OleDbConnection
                Dim da As OleDbDataAdapter
                Dim ds As New DataSet

                conn = New OleDbConnection(My.Settings.ConnectionString)
                da = New OleDbDataAdapter("Select Distinct TEAMNAME From IDM_IDEA_DATA Where SECT='WAFERING & HEAT TREAT'", conn)
                da.Fill(ds, "IDM_IDEA_DATA")
                Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
                Me.cboTeamName.DisplayMember = "TEAMNAME"
            Else
                Dim conn As OleDbConnection
                Dim da As OleDbDataAdapter
                Dim ds As New DataSet

                conn = New OleDbConnection(My.Settings.ConnectionString)
                da = New OleDbDataAdapter("Select Distinct TEAMNAME From IDM_IDEA_DATA Where SECT='POLISHING & EPITAXY'", conn)
                da.Fill(ds, "IDM_IDEA_DATA")
                Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
                Me.cboTeamName.DisplayMember = "TEAMNAME"
            End If

i hope u guys can help me..thx alot

[Teme64]

Ok. Your organization structure seems to be "flat" in DB:

Dept Sect TeamName
FINANCE & ADMINISTRATION CONTROLLING & ACCOUNTING FICO
FINANCE & ADMINISTRATION PROCUREMENT & LOGISTICS FICO

am I right?

The sorting problem. Use "ORDER BY":

Select Distinct TEAMNAME From IDM_IDEA_DATA Where SECT='POLISHING & EPITAXY' Order By TEAMNAME

this should solve sorting problem. "Order By" syntax is (taken from above sample) "Order By TEAMNAME Asc" (ascending order) or "Order By TEAMNAME Desc" (descending order).

I didn't quite understand

That means everything from the database is displayed.

You're using "Select Distinct" so atleast there shouldn't be any duplicate entries.

I would also suggest few things to clarify your code:

1) set common Dims to start so that code gets shorter. Atleast following objects could be dimmed at the beginning of the sub:

Dim conn As OleDbConnection
Dim da As OleDbDataAdapter
Dim da1 As OleDbDataAdapter
Dim ds As New DataSet

2) use Select...Case...End Select rather than nested If...ElseIf...Then...End If. You'll get more readable code eg. easier to maintain when you get more Depts, Sects, Teams or something else changes.

[MadAxel]

What i mean is that every section has different teams. Fico should only belong to 'Controlling & Accounting' and it must not appear at 'Procurement & Logistics'.
So for example,when i select Controlling & Accounting,only teams for this section must appear.But the problem is, when i chose section 'Controlling & Accounting', all teams from the 'Finance & Administration' dept information appeared that means information from 'Controlling & Accounting', 'Procurement & Logistics' & 'Information Technology'.
Can anibody help me with this?Thx alot

[Teme64]

One more time :-/

I realized that you can't use SelectedIndex directly because two Depts have both SelectedIndex = 0. So use Text property.

' User selects a Department, set Sections
Private Sub cboDept_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDept.SelectedIndexChanged
'
  ' Clear Sect info
  cboSect.Items.Clear

  ' Which Dept
  Select Case cboDept.SelectedIndex
    Case 0 ' Finance & Administration
       Me.cboSect.Items.Add("CONTROLLING & ACCOUNTING")
       Me.cboSect.Items.Add("PROCUREMENT & LOGISTICS")
       Me.cboSect.Items.Add("INFORMATION TECHNOLOGY")
       ' Show (select 1st)
       Me.cboSect.SelectedIndex = 0
    Case 1 ' Dept next
       ' Some sections here
     ' More Cases if needed
     Case Else ' Dept unknown = error
  End Select

End Sub

Handle Section selection:

' User selected some Section, set TeamNames for selected Section
Private Sub cboSect_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboSect.SelectedIndexChanged
'
Dim conn As OleDbConnection
Dim da As OleDbDataAdapter
Dim da1 As OleDbDataAdapter
Dim ds As New DataSet

  ' Clear Team info
  cboTeamName.Items.Clear

  ' Set conn
  conn = New OleDbConnection(My.Settings.ConnectionString)

  ' Which Sect
  Select Case cboSect.Text
     Case "CONTROLLING & ACCOUNTING"
        Try
          da = New OleDbDataAdapter("Select Distinct TEAMNAME From IDM_IDEA_DATA Where SECT='CONTROLLING & ACCOUNTING' Order By TEAMNAME", conn)
          da.Fill(ds, "IDM_IDEA_DATA")
          Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
          Me.cboTeamName.DisplayMember = "TEAMNAME"
          da1 = New OleDbDataAdapter("Select Distinct * From IDM_IDEA_DATA Where  SECT='CONTROLLING & ACCOUNTING'", conn)
          da1.Fill(DataSet1, "IDM_IDEA_DATA")
      Catch ex As Exception

      End Try
      ' Show (select 1st)
      Me.cboTeamName.SelectedIndex = 0
   Case "PROCUREMENT & LOGISTICS"
      Try
         da = New OleDbDataAdapter("Select Distinct TEAMNAME From IDM_IDEA_DATA Where SECT='PROCUREMENT & LOGISTICS' Order By TEAMNAME", conn)
         da.Fill(ds, "IDM_IDEA_DATA")
         Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
         Me.cboTeamName.DisplayMember = "TEAMNAME"
         da1 = New OleDbDataAdapter("Select Distinct * From IDM_IDEA_DATA Where SECT='PROCUREMENT & LOGISTICS'", conn)
         da1.Fill(DataSet1, "IDM_IDEA_DATA")
      Catch ex As Exception

      End Try
      ' Show (select 1st)
      Me.cboTeamName.SelectedIndex = 0
   Case "INFORMATION TECHNOLOGY"
      ' And similar code here too
    Case "" ' <- Title of Dept #2, Sect #1
      ' And more code here too. NOTICE: Here you are handling Sects for Dept #2
    Case "" ' <- Title of Dept #2, Sect #2
      ' And code here too
     ' Add more Cases here, one for each possible Sect name
    Case Else ' Sect unknown = error
  End Select

End Sub

Handle TeamName selection:

Private Sub cboTeamName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboTeamName.SelectedIndexChanged
'
Dim conn As OleDbConnection
Dim da As OleDbDataAdapter
Dim da1 As OleDbDataAdapter
Dim ds As New DataSet

  ' Which TeamName
   Select Case cboTeamName.Text
     Case "FIDO"  ' Now you have selected Dept #1, Sect #1 and Team FIDO
        ' And code here
     Case ""  ' <- Title of Dept #1, Sect #1 and Team #2
       ' And code here
     ' Add more Cases here, one for each possible TeamName
     Case Else ' TeamName unknown = error
   End Select

End Sub

Try this approach. Now you shouldn get TeamNames only for selected Sect (and Dept). If this doesn't work then I'll be damn... :$

[MadAxel]

does this means that if there are 100 TeamNames, i need to code for all 100?

[Teme64]

In this way, yes. It depends totally, what you do after a TeamName is selected. If the TeamName in itself doesn't matter eg. you use it only to get something from DB, you won't need Select Case... End Select in cboTeamName_SelectedIndexChanged event. What I mean is, if your code is just simple:

da = New OleDbDataAdapter("Select * From IDM_IDEA_DATA Where TEAMNAME='selected teamname'", conn)

then you can just use:

da = New OleDbDataAdapter("Select * From IDM_IDEA_DATA Where TEAMNAME='" & cboTeamName.Text  & "'", conn)

It all depends on what you need to do at this point.

[MadAxel]

Various TeamName has various values,so i need to get the values for the various TeamName.I really need help on this cuz i've tried alot of things but it doesnt work.I cant code for the TeamName one by one cuz its nt a gd practice.I hope really hope u guys could help me

Thx