Problems retrieving from database

Please support our VB.NET advertiser: Programming Forums - DaniWeb Sister Site
Reply
1 2

•
•
Join Date: Sep 2008
Posts: 13
Reputation: MadAxel is an unknown quantity at this point 
Solved Threads: 0
MadAxel MadAxel is offline Offline
Newbie Poster

Problems retrieving from database

 
0
  #1
Sep 16th, 2008
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
Last edited by MadAxel; Sep 16th, 2008 at 4:39 am.
Attached Images
File Type: bmp form2.bmp (683.0 KB, 9 views)
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 115
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

Re: Problems retrieving from database

 
0
  #2
Sep 16th, 2008
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):

VB.NET Syntax (Toggle Plain Text) 
  1. Private Sub cboDept_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDept.SelectedIndexChanged
  2.  
  3. << User selected Dept, retrieve and fill Sect >>
  4.   Select Case cboDept.SelectedIndex
  5.     Case 0 ' Finance & Administration
  6.       << retrieve and fill Sect with Controlling & Accounting >>
  7.     ...
  8.     Case Else ' SelectedIndex = -1, no Dept selected clear/disable cboSect and cboTeamName
  9.   End Select
  10.  
  11. End Sub
  12.  
  13. Private Sub cboSect_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboSect.SelectedIndexChanged
  14.  
  15. << User selected Sect, retrieve and fill TeamName >>
  16.   Select Case cboSect.SelectedIndex
  17.     Case 0 ' Controlling & Accounting
  18.       << retrieve and fill Team with Fico >>
  19.     ...
  20.     Case Else ' SelectedIndex = -1, no Sect selected clear/disable cboTeamName
  21.   End Select
  22.  
  23. End Sub
  24.  
  25. Private Sub cboTeamName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboTeamName.SelectedIndexChanged
  26.  
  27. << User selected TeamName, retrieve and show PDesc and IDesc information >>
  28.  
  29. End Sub
Teme64 @ Windows Developer Blog
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 13
Reputation: MadAxel is an unknown quantity at this point 
Solved Threads: 0
MadAxel MadAxel is offline Offline
Newbie Poster

Re: Problems retrieving from database

 
0
  #3
Sep 16th, 2008
There are so many TeamName.
Do u mean i need to hardcode every TeamName there is?
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 115
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

Re: Problems retrieving from database

 
0
  #4
Sep 17th, 2008
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.
Teme64 @ Windows Developer Blog
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 13
Reputation: MadAxel is an unknown quantity at this point 
Solved Threads: 0
MadAxel MadAxel is offline Offline
Newbie Poster

Re: Problems retrieving from database

 
0
  #5
Sep 18th, 2008
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 115
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

Re: Problems retrieving from database

 
0
  #6
Sep 18th, 2008
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":
VB.NET Syntax (Toggle Plain Text) 
  1. 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:
VB.NET Syntax (Toggle Plain Text) 
  1. Dim conn As OleDbConnection
  2. Dim da As OleDbDataAdapter
  3. Dim da1 As OleDbDataAdapter
  4. 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.
Teme64 @ Windows Developer Blog
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 13
Reputation: MadAxel is an unknown quantity at this point 
Solved Threads: 0
MadAxel MadAxel is offline Offline
Newbie Poster

Re: Problems retrieving from database

 
0
  #7
Sep 18th, 2008
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
Last edited by MadAxel; Sep 18th, 2008 at 10:19 pm.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 115
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

Re: Problems retrieving from database

 
0
  #8
Sep 19th, 2008
One more time

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

VB.NET Syntax (Toggle Plain Text) 
  1. ' User selects a Department, set Sections
  2. Private Sub cboDept_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDept.SelectedIndexChanged
  3. '
  4.   ' Clear Sect info
  5.   cboSect.Items.Clear
  6.  
  7.   ' Which Dept
  8.   Select Case cboDept.SelectedIndex
  9.     Case 0 ' Finance & Administration
  10.        Me.cboSect.Items.Add("CONTROLLING & ACCOUNTING")
  11.        Me.cboSect.Items.Add("PROCUREMENT & LOGISTICS")
  12.        Me.cboSect.Items.Add("INFORMATION TECHNOLOGY")
  13.        ' Show (select 1st)
  14.        Me.cboSect.SelectedIndex = 0
  15.     Case 1 ' Dept next
  16.        ' Some sections here
  17.      ' More Cases if needed
  18.      Case Else ' Dept unknown = error
  19.   End Select
  20.  
  21. End Sub

Handle Section selection:

VB.NET Syntax (Toggle Plain Text) 
  1. ' User selected some Section, set TeamNames for selected Section
  2. Private Sub cboSect_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboSect.SelectedIndexChanged
  3. '
  4. Dim conn As OleDbConnection
  5. Dim da As OleDbDataAdapter
  6. Dim da1 As OleDbDataAdapter
  7. Dim ds As New DataSet
  8.  
  9.   ' Clear Team info
  10.   cboTeamName.Items.Clear
  11.  
  12.   ' Set conn
  13.   conn = New OleDbConnection(My.Settings.ConnectionString)
  14.  
  15.   ' Which Sect
  16.   Select Case cboSect.Text
  17.      Case "CONTROLLING & ACCOUNTING"
  18.         Try
  19.           da = New OleDbDataAdapter("Select Distinct TEAMNAME From IDM_IDEA_DATA Where SECT='CONTROLLING & ACCOUNTING' Order By TEAMNAME", conn)
  20.           da.Fill(ds, "IDM_IDEA_DATA")
  21.           Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
  22.           Me.cboTeamName.DisplayMember = "TEAMNAME"
  23.           da1 = New OleDbDataAdapter("Select Distinct * From IDM_IDEA_DATA Where  SECT='CONTROLLING & ACCOUNTING'", conn)
  24.           da1.Fill(DataSet1, "IDM_IDEA_DATA")
  25.       Catch ex As Exception
  26.  
  27.       End Try
  28.       ' Show (select 1st)
  29.       Me.cboTeamName.SelectedIndex = 0
  30.    Case "PROCUREMENT & LOGISTICS"
  31.       Try
  32.          da = New OleDbDataAdapter("Select Distinct TEAMNAME From IDM_IDEA_DATA Where SECT='PROCUREMENT & LOGISTICS' Order By TEAMNAME", conn)
  33.          da.Fill(ds, "IDM_IDEA_DATA")
  34.          Me.cboTeamName.DataSource = ds.Tables("IDM_IDEA_DATA")
  35.          Me.cboTeamName.DisplayMember = "TEAMNAME"
  36.          da1 = New OleDbDataAdapter("Select Distinct * From IDM_IDEA_DATA Where SECT='PROCUREMENT & LOGISTICS'", conn)
  37.          da1.Fill(DataSet1, "IDM_IDEA_DATA")
  38.       Catch ex As Exception
  39.  
  40.       End Try
  41.       ' Show (select 1st)
  42.       Me.cboTeamName.SelectedIndex = 0
  43.    Case "INFORMATION TECHNOLOGY"
  44.       ' And similar code here too
  45.     Case "" ' <- Title of Dept #2, Sect #1
  46.       ' And more code here too. NOTICE: Here you are handling Sects for Dept #2
  47.     Case "" ' <- Title of Dept #2, Sect #2
  48.       ' And code here too
  49.      ' Add more Cases here, one for each possible Sect name
  50.     Case Else ' Sect unknown = error
  51.   End Select
  52.  
  53. End Sub

Handle TeamName selection:

VB.NET Syntax (Toggle Plain Text) 
  1. Private Sub cboTeamName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboTeamName.SelectedIndexChanged
  2. '
  3. Dim conn As OleDbConnection
  4. Dim da As OleDbDataAdapter
  5. Dim da1 As OleDbDataAdapter
  6. Dim ds As New DataSet
  7.  
  8.   ' Which TeamName
  9.    Select Case cboTeamName.Text
  10.      Case "FIDO"  ' Now you have selected Dept #1, Sect #1 and Team FIDO
  11.         ' And code here
  12.      Case ""  ' <- Title of Dept #1, Sect #1 and Team #2
  13.        ' And code here
  14.      ' Add more Cases here, one for each possible TeamName
  15.      Case Else ' TeamName unknown = error
  16.    End Select
  17.  
  18. 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...
Teme64 @ Windows Developer Blog
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 13
Reputation: MadAxel is an unknown quantity at this point 
Solved Threads: 0
MadAxel MadAxel is offline Offline
Newbie Poster

Re: Problems retrieving from database

 
0
  #9
Sep 19th, 2008
does this means that if there are 100 TeamNames, i need to code for all 100?
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 115
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

Re: Problems retrieving from database

 
0
  #10
Sep 19th, 2008
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:

VB.NET Syntax (Toggle Plain Text) 
  1. da = New OleDbDataAdapter("Select * From IDM_IDEA_DATA Where TEAMNAME='selected teamname'", conn)

then you can just use:

VB.NET Syntax (Toggle Plain Text) 
  1. 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.
Teme64 @ Windows Developer Blog
Reply With Quote Quick reply to this message  
Reply
1 2

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the VB.NET Forum


Views: 1082 | Replies: 10
Thread Tools Search this Thread



Tag cloud for VB.NET
"crystal .net .net2005 30minutes 2008 access add application array assignment basic binary box button buttons center code connectionstring convert cpu data database databasesearch datagrid datagridview design designer dissertation dissertations dissertationthesis dll dosconsolevb.net editvb.net employees error excel exists firewall folder function image images isnumericfuntioncall listview login math memory mobile module msaccess mssqlbackend mysql navigate net opacity page pan peertopeervideostreaming picturebox plugin port print printing printpreview problem record refresh reports" reuse save savedialog search serial sorting sql sqldatbase storedprocedure string structures studio temp textbox timer upload useraccounts usercontrol vb vb.net vb.netcode vb.nettoolboxvisualbasic2008sidebar vb2008 vbnet vista visual visualbasic visualbasic.net visualstudio2008 web wpf xml
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC