How to access values stored in a dataset in VB?

Question

So i have a DB with 3 tables. User Table with U_ID and U_Name, Class with C_ID, and C_Name and User_Class Table with U_ID and C_ID. Foreign keys are set.

I want to display the classes associated with the selected user. The user is identified by their name. I know these are the two SQL statements I need to execute:

SELECT U_ID FROM User WHERE U_Name = current_U_name

then

SELECT C_ID FROM User_Class WHERE U_ID = current_U_ID

then

SELECT C_Name FROM Class WHERE C_ID = C_IDforAllMatchingRows

I wanted to use a dataset to store the results of each statement but I could not figure out how to access one value in the dataset i.e.

(SELECT C_ID FROM User_Class WHERE U_ID = *dataset.Tables(0).Column(0)*) - does not work

I started using gridviews because i could access the values stored in each cell like above.

I must be missing a simple way to retrieve the information i need from these three simple tables.

Any help is appreciated, thanks

score 0 · Answer 1 · answered Feb 17 '12 at 03:16

If you are ultimately using this based on the user logged in, why not do a join on the three tables if there is some commonality and return the requested fields.

SELECT C_NAME 
FROM CLASS AS A
INNER JOIN
(
    SELECT C_ID
    FROM
    USER_CLASS AS B 
    INNER JOIN
    USER AS C
    ON 
        B.U_ID = C.U_ID
    WHERE 
        U_Name = current_U_name
 ) AS D
 ON 
    A.C_ID = D.C_ID

From what I understand of your question it seems like for the student you are trying to return a class list, which this query should do. If you store it in a data table the your could use linq or a for each and output each row if that is what you are trying to do with it.

score 0 · Answer 2 · edited Aug 01 '14 at 11:28

Have you looked at the DataTable.Select() Method? It is a bit long winded but If all the data is already in the data set you could use something like:

    Dim userRows As DataRow() = DataSet.Tables("User").Select(String.Format("WHERE U_Name = '{0}'", Current_User))
    If (userRows.Length = 0) Then
        Throw New Exception("User Not Found")
    End If

    Dim UID As Int32 = Convert.ToInt32(userRows(0).Item(0))
    Dim userClassRows As DataRow() = DataSet.Tables("User_Class").Select(String.Format("WHERE U_ID = {0}", UID))

    If (userClassRows.Length = 0) Then
        Throw New Exception("User Not Found")
    End If

    Dim classRows As New List(Of DataRow)
    For i As Int32 = 0 To userClassRows.Length - 1
        classRows.AddRange(DataSet.Tables("Class").Select(String.Format("WHERE C_ID = {0}", classRows(i).Item("C_ID"))))
    Next

    YourGridView.DataSource = classRows

(I primarily use C# so please excuse any errors/faux pas in my VB. Hopefully the logic can still be followed).

Although Like the earlier answer I would use an SQL approach unless there is a good reason not to retrieve the class last direct from the database for each user:

SELECT  *
FROM    Class
WHERE   C_ID IN 
        (   SELECT  C_ID 
            FROM    User_Class 
            WHERE   U_ID IN (SELECT U_ID FROM user WHERE U_Name = @CurrentUser)
        )

ADDENDUM

@Aprillion has raised a good point about joins probably being faster, which is mostly true, I have assumed that C_ID and U_ID are not uniquely contrained in the table User_Class, therefore went for subqueries as this removes the need for DISTINCT or GROUP BY, and as such will optimise better. However if U_ID and C_ID are uniquely constrained in User_Class then it could be beneficial to use Joins depending on your RDBMS. SQL-Server will optimise both in almost exactly the same way:

Execution Plan in SQL Server

In summary the SQL approach is probably beneficial, but the exact SQL you should use will depend on your RDBMS, data structures, indicies and keys.

score 0 · Answer 3 · edited May 23 '17 at 10:34

0

Like previous answers, i recommend single SQL query to retrieve "the classes associated with the selected user" - but joins should be faster than subqueries:

query = "select c.c_name" & _
        " from user u" & _ 
        " join user_class uc on u.u_id = uc.u_id" & _ 
        " join class c on uc.c_id = c.c_id" & _ 
        " where u.u_name = '" & current_U_name & "'"

edited May 23 '17 at 10:34

Community

1
1

answered Feb 17 '12 at 09:58

Aprillion

21,510
5
55
89

score 0 · Answer 4 · answered Feb 21 '12 at 15:30

0

My favourite is:

query = "select c.c_name from user u, user_class uc, class c 
  where u.u_name = '" & current_U_name & "'" 
  and u.u_id = uc.u_id and uc.c_id = c.c_id

answered Feb 21 '12 at 15:30

Martin

1,430
10
19

How to access values stored in a dataset in VB?

4 Answers4