Can't get LINQ To DataSet for 2 tables to work in my Project

Question

I'm working on a project for school. This application has multiple forms that are each capable of running queries with multiple methods. I'm stuck on my last form as I can't seem to get the LINQ To DataSet to work. The Faculty Table has both the faculty_name and faculty_id columns (faculty_id is the primary key). My Course Table has the faculty_id and the course_id columns (course_id is the primary key). I need to run a query that matches the faculty_name to the faculty_id so that I can then use the faculty_id to list the course_ids in my list box. The form has a ComboBox with 3 methods of running the query. Depending on user selection, it will run the query using that method. The TableAdapter and DataReader methods work fine, it's only the LINQ To DataSet that won't work. The sqlConnection is at the module level. I am pretty new to LINQ so I understand that my code may be very far off. I will post first the code for the form I am working on followed by the code for a different form that also performs a LINQ To Dataset that works, but it's only querying one table.

Imports System.Data
Imports System.Data.SqlClient

Public Class CourseForm
    Private CourseTextBox(5) As TextBox                'We only have 6 columns in Course table
    Private Sub CourseForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If sqlConnection.State <> ConnectionState.Open Then
            MessageBox.Show("Database has not been opened!")
            Exit Sub
        End If

        ComboName.Items.Add("Ying Bai")
        ComboName.Items.Add("Davis Bhalla")
        ComboName.Items.Add("Black Anderson")
        ComboName.Items.Add("Steve Johnson")
        ComboName.Items.Add("Jenney King")
        ComboName.Items.Add("Alice Brown")
        ComboName.Items.Add("Debby Angles")
        ComboName.Items.Add("Jeff Henry")
        ComboName.SelectedIndex = 0
        ComboMethod.Items.Add("TableAdapter Method")
        ComboMethod.Items.Add("DataReader Method")
        ComboMethod.Items.Add("LINQ To DataSet Method")
        ComboMethod.SelectedIndex = 0
    End Sub

    Private Sub cmdSelect_Click(sender As Object, e As EventArgs) Handles cmdSelect.Click
        Dim cString1 As String = "SELECT Course.course_id, Course.course FROM Course JOIN Faculty "
        Dim cString2 As String = "ON (Course.faculty_id = Faculty.faculty_id) AND (Faculty.faculty_name = @name)"
        Dim cmdString As String = cString1 & cString2
        Dim CourseTableAdapter As New SqlDataAdapter
        Dim FacultyTableAdapter As New SqlDataAdapter
        Dim paramFacultyName As New SqlParameter
        Dim sqlCommand As New SqlCommand
        Dim sqlDataReader As SqlDataReader
        Dim sqlDataTable As New DataTable
        Dim ds As New DataSet

        sqlCommand.Connection = sqlConnection
        sqlCommand.CommandType = CommandType.Text
        sqlCommand.CommandText = cmdString
        sqlCommand.Parameters.Add("@name", SqlDbType.Char).Value = ComboName.Text

        If ComboMethod.Text = "TableAdapter Method" Then
            CourseTableAdapter.SelectCommand = sqlCommand
            CourseTableAdapter.Fill(sqlDataTable)
            If sqlDataTable.Rows.Count > 0 Then
                Call FillCourseTable(sqlDataTable)
            Else
                MessageBox.Show("No matched course found!")
            End If
            sqlDataTable.Dispose()
            sqlDataTable = Nothing
            CourseTableAdapter.Dispose()
            CourseTableAdapter = Nothing
        ElseIf ComboMethod.Text = "DataReader Method" Then            ' ------------------------------------------ DataReader method is selected
            sqlDataReader = sqlCommand.ExecuteReader
            If sqlDataReader.HasRows = True Then
                Call FillCourseReader(sqlDataReader)
            Else
                MessageBox.Show("No matched course found!")
            End If
            sqlDataReader.Close()
            sqlDataReader = Nothing
        ElseIf ComboMethod.Text = "LINQ To DataSet Method" Then                    ' ------------------------------------------LINQ To DataSet is selected
            CourseTableAdapter.SelectCommand = sqlCommand
            FacultyTableAdapter.SelectCommand = sqlCommand
            FacultyTableAdapter.Fill(ds, "Faculty")
            CourseTableAdapter.Fill(ds, "Course")
            Dim facultyid = From fi In ds.Tables("Faculty").AsEnumerable()
                            Where fi.Field(Of String)("faculty_name").Equals(ComboName.Text) Select fi.Field(Of String)("faculty_id")

            MessageBox.Show(String.Join(", ", facultyid))
            Dim courseid = From ci In ds.Tables("Course").AsEnumerable()
                           Where ci.Field(Of String)("faculty_id").Equals(facultyid) Select ci.Field(Of String)("course_id")
            CourseList.Items.Clear()
            For Each cRow In courseid
                CourseList.Items.Add(courseid)
            Next
        End If

Here is a different form in my project that runs perfectly. The LINQ To DataSet query works fine here, but it is only querying one table.

Imports System.Data
Imports System.Data.SqlClient

Public Class FacultyForm
    Private FacultyTextBox(7) As TextBox           'Faculty table has 8 columns
    Private Sub FacultyForm_Load(sender As Object, e As EventArgs) Handles Me.Load
        If sqlConnection.State <> ConnectionState.Open Then
            MessageBox.Show("Database has not been opened!")
            Exit Sub
        End If

        ComboName.Items.Add("Ying Bai")
        ComboName.Items.Add("Davis Bhalla")
        ComboName.Items.Add("Black Anderson")
        ComboName.Items.Add("Steve Johnson")
        ComboName.Items.Add("Jenney King")
        ComboName.Items.Add("Alice Brown")
        ComboName.Items.Add("Debby Angles")
        ComboName.Items.Add("Jeff Henry")
        ComboName.SelectedIndex = 0
        ComboMethod.Items.Add("TableAdapter Method")
        ComboMethod.Items.Add("DataReader Method")
        ComboMethod.Items.Add("LINQ To DataSet Method")
        ComboMethod.SelectedIndex = 0
    End Sub

    Private Sub cmdSelect_Click(sender As Object, e As EventArgs) Handles cmdSelect.Click
        Dim cmdS1 As String = "SELECT faculty_id, faculty_name, office, phone, college, title, email, fimage FROM Faculty "
        Dim cmdS2 As String = "WHERE faculty_name = @facultyName"
        Dim cmdString As String = cmdS1 & cmdS2
        Dim paramFacultyName As New SqlParameter
        Dim FacultyTableAdapter As New SqlDataAdapter
        Dim sqlCommand As New SqlCommand
        Dim sqlDataReader As SqlDataReader
        Dim sqlDataTable As New DataTable
        Dim ds As New DataSet()

        paramFacultyName.ParameterName = "@facultyName"
        paramFacultyName.Value = ComboName.Text
        sqlCommand.Connection = sqlConnection
        sqlCommand.CommandType = CommandType.Text
        sqlCommand.CommandText = cmdString
        sqlCommand.Parameters.Add(paramFacultyName)

        Call ShowFaculty(FacultyTableAdapter, sqlCommand, ds)

        If ComboMethod.Text = "TableAdapter Method" Then
            'FacultyTableAdapter.SelectCommand = sqlCommand         'moved to ShowFaculty()
            FacultyTableAdapter.Fill(sqlDataTable)
            If sqlDataTable.Rows.Count > 0 Then
                Call FillFacultyTable(sqlDataTable)
            Else
                MessageBox.Show("No matched faculty found!")
            End If
            sqlDataTable.Dispose()
            sqlDataTable = Nothing
            FacultyTableAdapter.Dispose()
            FacultyTableAdapter = Nothing
        ElseIf ComboMethod.Text = "DataReader Method" Then  '------------ Data Reader Method
            sqlDataReader = sqlCommand.ExecuteReader

            If sqlDataReader.HasRows = True Then
                Call FillFacultyReader(sqlDataReader)
            Else
                MessageBox.Show("No matched faculty found!")
            End If
            sqlDataReader.Close()
            sqlDataReader = Nothing
        Else        '---------------------- LINQ To DataSet method is selected
            FacultyTableAdapter.SelectCommand = sqlCommand
            FacultyTableAdapter.Fill(ds, "Faculty")
            Dim facultyinfo = From fi In ds.Tables("Faculty").AsEnumerable()
                              Where fi.Field(Of String)("faculty_name").Equals(ComboName.Text) Select fi
            For Each fRow In facultyinfo
                txtID.Text = fRow.Field(Of String)("faculty_id")
                txtName.Text = fRow.Field(Of String)("faculty_name")
                txtTitle.Text = fRow.Field(Of String)("title")
                txtOffice.Text = fRow.Field(Of String)("office")
                txtPhone.Text = fRow.Field(Of String)("phone")
                txtCollege.Text = fRow.Field(Of String)("college")
                txtEmail.Text = fRow.Field(Of String)("email")
            Next
        End If
        sqlCommand.Dispose()
        sqlCommand = Nothing
    End Sub

If at all possible, I'd like the Course Form query to work in a similar way to that of the Faculty Form query, only that it is querying 2 tables instead of 1. Any help would be greatly appreciated. The main error I keep recieving is that my column names do not belong to my Tables. They cleary do. I've checked the tables multiple times and these column names work perfectly for all the other methods, including the single table LINQ To DataSet query on the Faculty Form. If you have any questions about the project forms please ask me and I will answer to the best of my ability. Sorry if this has already been asked in a similar question, I'm afraid I'm not familiar enough with LINQ to translate an answer not specific to my project. Thank you in advance.

Answer 1

First, what you are doing is not good, that code needs revisions in itself.

I wouldn't suggest using Linq To Dataset unless you already have a dataset to start with. Instead directly using Linq To SQL (or Linq To EF) to get the data from source is a much better option and easier. Anyway since you want to use it this way, let's do it.

In your code there are FillCourseTable(), FillCourseReader() methods which I have no idea about. Second, it is not clear what you are trying to do when "Linq To DataSet Method" is selected. Within that block, although current code is faulty, it doesn't make sense why you are using Linq there at all. If all the data were in Dataset already and you were trying to filter using Linq, then it would make more sense (ie: Your Faculty and Course tables are not large, so you might have loaded them into a dataset on form load and then using from that Dataset, without querying again from SQL server).

I will try to walk you, starting from your own code (but eliminating first 2 if, elseif blocks as those don't smell good as well):

• You have a single select string, you are filling in the same thing into 2 tables in the Dataset. Both tables only have Course_id and Course columns.
• In your attempt to use Linq on that dataset, you are trying to query "faculty_name" field, which doesn't exist.
• Again in your second attempt to use Linq on that dataset, you are trying to query "faculty_id" field, which again doesn't exist.
• Finally, at the end you are trying to fill in CourseList with Course Ids (assuming as if former lines worked).

I hope now you understood why it wouldn't work in the first place, let's now try doing it at least in a way that would work:

    Dim FacultyAndCourses As New DataSet()
    Using adp As SqlDataAdapter = New SqlDataAdapter("select * from Faculty where faculty_name=@name", sqlConnection.ConnectionString)
        adp.SelectCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = ComboName.Text
        adp.Fill(FacultyAndCourses, "Faculty")
    End Using
    
    Dim faculty = (From f In FacultyAndCourses.Tables("Faculty").AsEnumerable() Select f).FirstOrDefault()
    
    If Not faculty Is Nothing
    
        Dim facultyId As Integer = faculty("faculty_id")
    
        MessageBox.Show(String.Join(", ", facultyid)) ' Are you expecting multiple faculties with the same name???
        
        Using adp As SqlDataAdapter = New SqlDataAdapter("select * from Course where faculty_id=@id", sqlConnection.ConnectionString)
            adp.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = facultyId
            adp.Fill(FacultyAndCourses, "Courses")
        End Using
        
        Dim courses = From c In FacultyAndCourses.Tables("Courses").AsEnumerable() Select c
    
        CourseList.Items.Clear()
        For Each cRow In courses
            CourseList.Items.Add(Cstr(cRow("course_id"))) ' I am not sure what you are trying to add to this list
        Next
    
    Else
     ' Whatever ...
    End If

Still it doesn't make very sense but would work.