[RESOLVED] How to select a column from a dataset based on the value in another column?

[evanrich]

I'm writing a help application for the Customer support team at my company to help agents ask better questions when customers report problems. In it, I've got a SQL database that contains all the products, topics, and questions. The layout is similar to the following:

Products Table:
Product_ID Product Name:

Topics Table:
Topic_ID Product_ID Topic

Questions Table:
Question_ID Topic_ID Question


In my App (VB.NET), I've got the following code that populates the Products Table upon program launch:

Public Sub PopulateProductsList()

    Dim DBCon As New SqlClient.SqlConnection
    Dim ds As New DataSet
    Dim da As SqlClient.SqlDataAdapter
    Dim sql As String

        'establish a connection to the Help conetent database
        DBCon.ConnectionString = My.Settings.DBString
        DBCon.Open()

        sql = "SELECT * FROM Products"
        da = New SqlClient.SqlDataAdapter(sql, DBCon)

        da.Fill(ds, "Products")

        lstProducts.DataContext = ds
        lstProducts.DisplayMemberPath = "ProductName"
        lstProducts.Items.SortDescriptions.Add(New ComponentModel.SortDescription("ProductName", ComponentModel.ListSortDirection.Ascending))
  


        DBCon.Close()


    End Sub

In the SelectionChanged handler, I need to pass the product Id from the first dataset in so that it will then fill a listbox with the Topics based on the selected Product Id. I tried doing something similar to this:

txtItem.Text = (ds.Tables(0).Rows(lstProducts.SelectedIndex)("Product_ID")).ToString

but this doesn't work.

I don't know if I'm making myself clear, but I basically need to do the equivalent to the following SQL statement: "SELECT PRODUCT_ID FROM Products WHERE PRODUCT_Name = LstProducts.SelectedValue"

Hope someone knows what I'm talking about and can help. Thanks.