Retrieving multiple ID's from a query

[Ugluth]

Hello there, I'm rather new to vb.net and also to the forum. I'm developing an application connecting to an access database. My project has 3 tables, one named students, one classes and one student_class. The first 2 keep information on students and classes respectively. The third one has only 3 fields, one for a registration id, one for a student id and one for a class id. There are foreign key relationships between them from the database.

What i want to do is have a combo box which will be displaying the list of available classes and once the user chooses the class he wants and presses the button i want the information of the students enrolled in that class to show up on a data grid. I've created the combo box showing all the classes, and i also have taken the id of the class that the user wants to display.

What i want to do now is get all the id's from the student_class table that match the id selected on the combo box and retrieve all the student id's that are on the same record with that id on the student_class table, and after wards i want to display some data of those students enrolled in the specified class and show their data on the data grid.

I'm using visual studio 2010 running on windows xp sp2 and using an access database with ending .mdb i'm not really sure which version it is, but its not the .accdb that access 2007 and onwards save it to.

Hope this post made some sense and thank you in advance. If you need any more information please do ask.

 

[kulrom]

You have to join the results from the tables. INNER JOIN is what you need.
Please post the demo database and we will provide a better help. And of course, welcome to the forum

 

[Ugluth]

Thank you very much for your quick reply kulrom and good to find you on the forums

I'm posting a jpg file of my database relationships.

Looking forward to your answer and thank you very much again

 

[kulrom]

Try this:

SELECT 
Class.Class_ID, Class.Class_Name, Class.Class_Duration, Class.Teacher, Stud.Student_ID, Stud.FirstName, Stud.SurName
FROM (Student_Class AS Stc 
INNER JOIN Classes AS Class ON Stc.Class_ID = Class.Class_ID) 
INNER JOIN Students AS Stud ON Stc.Student_ID = Stud.Student_ID;

I've attached a MS Access database for you. Take a look at the query and see how it works actually.

 

[kulrom]

I just didn't add the WHERE clause as you wanted originally. To do that you just have to add a WHERE clause e.g.

SELECT 
Class.Class_ID, Class.Class_Name, Class.Class_Duration, Class.Teacher, Stud.Student_ID, Stud.FirstName, Stud.SurName
FROM (Student_Class AS Stc 
INNER JOIN Classes AS Class ON Stc.Class_ID = Class.Class_ID) 
INNER JOIN Students AS Stud ON Stc.Student_ID = Stud.Student_ID
WHERE Stc.Class_ID = 1;

Fixed Access file attached

 

[cjard]

Note that you'd only need one join to get the data you require. Let us assume that your combo has the Class names as its DisplayMember and its ID as its ValueMember. The query you'd need is:

SELECT 
Stud.*
FROM Student_Class AS Stc 
INNER JOIN Students AS Stud ON Stc.Student_ID = Stud.Student_ID
WHERE Stc.Class_ID = @classID

Thats the query you'd put in your tableadapter. Note that this may result in a non-editable dataset, because of the join. There's a trick to get around that, come and ask if you need it..

If you have never used a TableAdapter, read the DW4 link in my signature, section Creating a Simple Data App

 

[Ugluth]

Thank you both very much for your replies, i think i understand what needs to be done, but the thing is that the only assignment i make to my combo box is that of the class name. Could you please also tell me how to assign a different value and different text on the combo box? at the moment I'm using

For Me.inc_class = 0 To MaxClasses cmbClass.Items.Add(ds_class.Tables("Class").Rows(inc_class).Item(1))
Next Me.inc_class

Where ds_class is the data set i use to store the classes table, inc_class is the increment I'm using and maxclasses is the number of rows returned by the query. But I'm not really sure on how to assign a different value and a different text for it.

Could you help me out on this one? Thank you very much in advance, you are very helpful!

*Edit* I replaced the code inside for with:

        For Me.inc_class = 0 To MaxClasses
            cmbClass.DataSource = ds_class.Tables("Class")
            cmbClass.DisplayMember = "Class_name"
            cmbClass.ValueMember = "Class_ID"
        Next Me.inc_class

and i think its working fine, gonna try to implement the solution you suggested and will let you know

 

[Ugluth]

I used the query you suggested here is the code for it:

"SELECT Class.Class_ID, Class.Class_Name, Stud.ID, Stud.FirstName, Stud.Surname " & _
                      "FROM (Student_Class AS Stc " & _
                      "INNER JOIN Classes AS Class ON Stc.Class_ID = Class.Class_ID) " & _
                      "INNER JOIN Students AS Stud ON Stc.Student_ID = Stud.ID " & _
                      "WHERE Stc.Class_ID = '" & cmbClass.SelectedValue.ToString & "';"

but I'm getting a Data type mismatch in criteria expression error.

Any idea on what might be the problem? i think the query is correct. Thanks in advance

*Edit* Sorry i should have posted all the code trying to attach the dataset to the data grid so i could get some help, so here's the whole code for it

Public Class Registered_students
    Dim con As New OleDb.OleDbConnection
    Dim constring As String
    Dim ds_class As New DataSet
    Dim da_class As OleDb.OleDbDataAdapter
    Dim ds_student As New DataSet
    Dim da_student As OleDb.OleDbDataAdapter
    Dim sql_class As String
    Dim sql_student As String
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim inc_class As Integer
    Dim MaxClasses As Integer

    Private Sub Registered_students_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        constring = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source = C:/DB/AddressBook.mdb"
        con.ConnectionString = constring
        con.Open()
        sql_class = "SELECT Classes.Class_ID, Classes.Class_name FROM Classes"
        da_class = New OleDb.OleDbDataAdapter(sql_class, con)
        da_class.Fill(ds_class, "Class")
        con.Close()
        MaxClasses = ds_class.Tables("Class").Rows.Count
        MaxClasses = MaxClasses - 1
        inc_class = 0
        For Me.inc_class = 0 To MaxClasses
            cmbClass.DataSource = ds_class.Tables("Class")
            cmbClass.DisplayMember = "Class_name"
            cmbClass.ValueMember = "Class_ID"
        Next Me.inc_class
    End Sub

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da_student)
        sql_student = "SELECT Class.Class_ID, Class.Class_Name, Stud.ID, Stud.FirstName, Stud.Surname " & _
                      "FROM (Student_Class AS Stc " & _
                      "INNER JOIN Classes AS Class ON Stc.Class_ID = Class.Class_ID) " & _
                      "INNER JOIN Students AS Stud ON Stc.Student_ID = Stud.ID " & _
                      "WHERE Stc.Class_ID = '" & cmbClass.SelectedValue & "';"
        con.Open()
        da_student = New OleDb.OleDbDataAdapter(sql_student, con)
        da_student.Fill(ds_student, "Student")
        con.Close()
        DataGridView1.DataSource = ds_student.DefaultViewManager

    End Sub
End Class

All the class and student id are of type auto number and the class id and student id on student_class is of type number, so i suppose there shouldn't be any type miss match problems, but there are.
Hope it helps and thanks in advance.

 

[kulrom]

Please make a quick test and see what the statement cmbClass.SelectedValue returns.
It should be numeric value. Just add the SelectedIndexChanged event to the combo box and show the msgbox - MessageBox.Show(cmbClass.SelectedValue)

 

[Ugluth]

Hey i created this, which is what you told me

 Private Sub cmbClass_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbClass.SelectedIndexChanged
        MessageBox.Show(cmbClass.SelectedValue)
    End Sub

When i run the program i get: Conversion from type 'DataRowView' to type 'String' is not valid error

I also tried it with .toString the first time i load the form i get on the msg box System.Data.DataRowView, after pressing 3 times ok ( i have 3 records in the class table) when i change the value i get the id's as supposed 1, 2, 3. If you want me to try something else please do tell me and thank you again for your time!

*Edit*
After some tries, the mistake was on the query, i had single quotes around the variable i passed, which shouldn't be that way since it s a number, now just have to get the correct command to show data on the data grid and it should work as intended (i hope at least)

 

[kulrom]

I am not able to reproduce your issue. It works just fine for me. I have attached a working demonstration project so you can take a look and possibly find the mistake.

 

[Ugluth]

Thank you very much for your help and your time guys, really appreciated, finally got it to work, used this for datagrid datasource binding

DataGridView1.DataSource = ds_student
        DataGridView1.DataMember = "Student"

Thank you very much again, and will let you know if i run on any trouble again

 

[kulrom]

You don't have to use DataSet unless you updates the data using the same. If you just retrieve the data you should rather use DataTable ... just like in the example i posted to you.

         If cmbClass.SelectedIndex < 0 Then
            MessageBox.Show("please select a class")
            Exit Sub
        End If
        Dim connection As New OleDb.OleDbConnection( _
        "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source = " & Application.StartupPath & "/Classes.mdb")
        Try
            connection.Open()
            Dim command As OleDb.OleDbCommand = connection.CreateCommand
            command.CommandText = "SELECT Class.Class_ID, Class.Class_Name, Stud.Student_ID, Stud.FirstName, Stud.Surname " & _
                      "FROM (Student_Class AS Stc " & _
                      "INNER JOIN Classes AS Class ON Stc.Class_ID = Class.Class_ID) " & _
                      "INNER JOIN Students AS Stud ON Stc.Student_ID = Stud.Student_ID " & _
                      "WHERE Stc.Class_ID = ?"
            command.Parameters.AddWithValue("Class_ID", cmbClass.SelectedValue.ToString)

            Dim table As New DataTable
            table.Load(command.ExecuteReader)
            DataGridView1.DataSource = table

        Catch ex As Exception
            Console.Write("Error: " & ex.Message)
        Finally
            connection.Close()
        End Try

 

[cjard]

Ugluth, you'd have found life a lot easier by doing:

Add a new dataset to your project
Right click the surface and chose Add..Query
Go through the wizard, setting connectionstring etc
Enter the SQL query:

SELECT Class.Class_ID, Class.Class_Name, Stud.Student_ID, Stud.FirstName, Stud.Surname
FROM Student_Class AS Stc
INNER JOIN Classes AS Class ON Stc.Class_ID = Class.Class_ID
INNER JOIN Students AS Stud ON Stc.Student_ID = Stud.Student_ID
WHERE Stc.Class_ID = ?

A datatable and tableadapter appear
Open your form
Make sure the DataSources tool window (on data menu) is visible
Drag the tree node representing your datatable, out of data sources and onto the form

Several things appear, including a textbox to enter the ID

You can now run the project and see it work. Then change the textbox for a combo

I can do all these steps faster than it took me to write this post (about 2 minutes)

For more info and tutorials followthe DW4 link in my signature.. Start with the section Creating a Simple Data App, also read Creating a Form To Search Data

 

[Ugluth]

To be honest I'm not really into visual basic, i had taken a course back in college but that is 4 years ago and it was vb 6 not .net. Will give a look at the link and will also try it with the data table instead of the datagrid, thank you very much both for your time and help, you both are very kind!