populating listbox

cfisher440

Well-known member
Joined
Oct 11, 2005
Messages
73
Programming Experience
1-3
I have two drop down boxes(ddlIssue and ddlSubIssue)
ddlIssue populates data from table named issues. Issues table contains columns named issueid, issue, and subidvid
ddlSubIssue populates data from table named subissues. subissues table contains columns named subissueid, subissue, and issuid
issueid is the primary key in issues table and foreign key in subissues table.
Based off whatever issue (value) is selected in ddlIssue, the appropriate subissue(s) (values) should show up in ddlSubIssue
This can be done easy enough like this:
VB.NET:
1 'This is beneath windows form designer, before page load 
2 Dim dsSubDivs As DataSet
3 Public dvSubDivs As DataView
4 Dim dsIssues As DataSet
5 Public dvIssues As DataView
6 Dim dsSubIssues As DataSet
7 Public dvSubIssues As DataView
8 
9 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
10 'Put user code to initialize the page here
11 
12 dsSubIssues = GetSubIssue1orRes()
13 
14 If Not IsPostBack Then
15 dsIssues = GetIssues()
16 Me.BindIssuesListBox()
17 
18 End If
19 
20 End Sub
21 
22 Private Sub BindIssueDropDownList()
23 ddlIssues.DataSource = dvIssuescat
24 'ddlSubDivision.DataMember = "subdiv"
25 ddlIssues.DataTextField = "issuename"
26 ddlIssues.DataValueField = "issueid"
27 ddlIssues.DataBind()
28 End Sub
29 
30 Private Sub BindSubIssueDropDownList()
31 Dim dvSubIssues As New DataView(dsSubIssues.Tables("subIssue1"))
32 
33 dvSubIssues.RowFilter _
34 = "issueid='" & ddlIssues.SelectedValue & "'"
35 ddlSubIssue.DataSource = dvSubIssues
36 'ddlSubDivision.DataMember = "subdiv"
37 ddlSubIssue.DataTextField = "subissue"
38 ddlSubIssue.DataValueField = "subissueid"
39 ddlSubIssue.DataBind()
40 End Sub
41 
42 Function GetIssues() As DataSet
43 Dim dsIssues As New DataSet
44 If HttpContext.Current.Cache("dsIssues") Is Nothing Then
45 Dim sSelect As String = "SELECT issueid, issueName, subdivid " _
46 & "FROM issues ORDER BY issueName"
47 Dim daIssues As New OleDb.OleDbDataAdapter(sSelect, OleDbConnection1)
48 daIssues.MissingSchemaAction = MissingSchemaAction.AddWithKey
49 daIssues.Fill(dsIssues, "issues")
50 HttpContext.Current.Cache("dsIssues") = dsIssues
51 Else
52 dsIssues = HttpContext.Current.Cache("dsIssues")
53 End If
54 Return dsIssues
55 End Function
56 
57 Function GetSubIssue1orRes() As DataSet
58 Dim dsSubIssues As New DataSet
59 If HttpContext.Current.Cache("dsSubIssues") Is Nothing Then
60 Dim sSelect As String = "SELECT subissueID, subissue, issueid " _
61 & "FROM subIssue1 ORDER BY subissue"
62 Dim daSubIssues As New OleDb.OleDbDataAdapter(sSelect, OleDbConnection1)
63 daSubIssues.MissingSchemaAction = MissingSchemaAction.AddWithKey
64 daSubIssues.FillSchema(dsSubIssues, SchemaType.Mapped)
65 'dsSubIssues.Tables("subIssue1").PrimaryKey = New DataColumn
66 daSubIssues.Fill(dsSubIssues, "subIssue1")
67 HttpContext.Current.Cache("dsSubIssues") = dsSubIssues
68 Else
69 dsSubIssues = HttpContext.Current.Cache("dsSubIssues")
70 End If
71 Return dsSubIssues
72 End Function
73 
74 Private Sub ddlIssues_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlIssues.SelectedIndexChanged
75 Me.BindSubIssueDropDownList()
76 End Sub
That's the basics there for getting it done. That will work just fine.

BUT before ddlSubIssue is populated I want to verify that values in the subissues table exist.
In other words, if you select an issue from the ddlIssue (references the primary key of issues table) but there is no corresponding values in the issueid field of the subissues table to populate ddlSubIssue with, how would I first check to see if there are any issueid values in subissues table equal to the ddlissue's selectedvalue?? If there are no values, then I would like it to check another table.

That probably sounds very confusing.

I have seen a way of doing this in which you test if a row with the specified key value exists
VB.NET:
If dsSubIssues.Tables("subissue").Rows.Contains(ddlIssues.SelectedValue.ToString) Then ...
The only problem there, is your testing the primary key value field of that table.
My case is very similar, but I am referencing the foreign value field. Referencing the foreign value field does mean there could be several ddlIssues.SelectedValue.ToStrings, as opposed to finding the one primary key. But as long as I can tell if there is just one of those foreign key values in the table, then I can flow.
Any help would be greatly appreciated.
 
Last edited by a moderator:

cfisher440

Well-known member
Joined
Oct 11, 2005
Messages
73
Programming Experience
1-3
Is there a way to do this?

I mentioned above you can check to see if a primary key value exists in a table using

If dsSubIssues.Tables("subissue").Rows.Contains(ddlIs sues.SelectedValue.ToString) Then ...
and you have to set the MissingSchemaAction property of the data adapter to AddWithKey

Is there a way to do this for another value (column) in the table other than the primary key????

Any answers would be appreciated?
 
Last edited:

cfisher440

Well-known member
Joined
Oct 11, 2005
Messages
73
Programming Experience
1-3
figured it out

I figured it out. Had to loop through the tables row, looking to see where the values matched. Anways, for future reference, I'll post what I did.

VB.NET:
Private Sub FindBind()
        Dim dvSubIssues As New DataView(dsSubIssues.Tables("subIssue1"))

        Dim dvresolutionslink As New DataView(dsResolutionsLink.Tables("resolutionwithlink"))
        Dim Issue As String = ddlIssues.SelectedValue.ToString
        'Dim drSubIssue As DataRowcur
        'Dim drRes As DataRow
        'Dim drResLink As DataRow
        Dim iCurrentSubIssue As Integer = 0
        Dim iCurrentResolution As Integer = 0
        Dim iCurrentResolutionLink As Integer = 0
        Dim subIssueValue As String
        Dim resolutionValue As String
        Dim resolutionLinkValue As String
        For Each drSubIssue As DataRow In dsSubIssues.Tables("subIssue1").Rows
            subIssueValue = dsSubIssues.Tables("subIssue1").Rows(iCurrentSubIssue).Item("issueid")
            If subIssueValue = ddlIssues.SelectedValue.ToString Then
                Me.BindSubIssue()
                Exit For
            Else
                iCurrentSubIssue = iCurrentSubIssue + 1
                'lblMessage.Text = "not there yet  " & iCurrentSubIssue
            End If
        Next
        For Each drResolution As DataRow In dsResolutions.Tables("resolution").Rows
            resolutionValue = dsResolutions.Tables("resolution").Rows(iCurrentResolution).Item("subIssueID")
            If resolutionValue = ddlIssues.SelectedValue Then
                Dim ddIssue As String = ddlIssues.SelectedValue.ToString
                Dim drRes As DataRow = dsResolutions.Tables("resolution").Rows(iCurrentResolution)
                Dim resolution As String = drRes("labeltext")
                'Dim resolution As String = dsResolutions.Tables("resolution").Rows(iCurrentResolution).Item("labeltext")
                endFlow(resolution)
                Exit For
            Else
                iCurrentResolution = iCurrentResolution + 1
                'lblMessage.Text = "we are at   " & iCurrentResolution
            End If
        Next
End Sub

Private Sub BindSubIssue()
        Dim dvSubIssues As New DataView(dsSubIssues.Tables("subIssue1"))
        dvSubIssues.RowFilter _
        = "issueid='" & ddlIssues.SelectedValue & "'"
        ddlSubIssue.DataSource = dvSubIssues
        'ddlSubDivision.DataMember = "subdiv"
        ddlSubIssue.DataTextField = "subissue"
        ddlSubIssue.DataValueField = "subissueid"
        ddlSubIssue.DataBind()
    End Sub

 Private Sub endFlow(ByVal label As String)
        lblMessage.Text = label
        lblMessage.Font.Bold = True
        lblMessage.Visible = True
        'lblOutput.BackColor = Color.White

    End Sub
 
Top Bottom