[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.

[hassa046]

Maybe the use of Linq?

Dim Query = FROM Q in ds WHERE (Q.ProductName = lstProducts.SelectedValue)

If isnothing(Query) = false then
Return Query(0).Product_ID (??? field you're looking for)
end if

[evanrich]

Maybe the use of Linq?

Dim Query = FROM Q in ds WHERE (Q.ProductName = lstProducts.SelectedValue)

If isnothing(Query) = false then
Return Query(0).Product_ID (??? field you're looking for)
end if

Thanks for the reply. I actually just found something that worked for what I wanted, DataRowView...I used the following (the textitem.text is just a text box to visually show me the results to see if its working):

 Dim drv As DataRowView = lstProducts.SelectedItem
        Dim ProductID As Integer = drv.Item("Product_ID")
        txtItem.Text = ProductID.ToString
        PopulateTopicsList(ProductID)