none
LINQ queries RRS feed

  • Question

  • I am learning to use LINQ queries and have run into a problem that I am unable to understand.

    This is the routine containing the join query, after creating a dataset and connecting to it.

        Sub DataSetCrossTable()
            Dim strFaculty As String = "SELECT faculty_id, faculty_name FROM Faculty"
            Dim strCourse As String = "SELECT course_id, faculty_id, From Course"
            Dim facultyAdapter As New OleDbDataAdapter()
            Dim courseAdapter As New OleDbDataAdapter()
            Dim accConnection As New OleDbConnection()
            Dim facultyCommand As New OleDbCommand()
            Dim courseCommand As New OleDbCommand()
            Dim ds As New DataSet()
            Dim connString As String = "Provider=Microsoft.Ace.OLEDB.12.0;" &
                "Data Source=F:\SourceCode\Database\Access\CSE_DEPT.accdb;"
            accConnection = New OleDbConnection(connString)
            accConnection.Open()
            'Set properties
            facultyCommand.Connection = accConnection
            facultyCommand.CommandType = CommandType.Text
            facultyCommand.CommandText = strFaculty
            facultyAdapter.SelectCommand = facultyCommand
            facultyAdapter.Fill(ds, "Faculty")
    
            courseCommand.Connection = accConnection
            courseCommand.CommandType = CommandType.Text
            courseCommand.CommandText = strCourse
            courseAdapter.SelectCommand = courseCommand
            courseAdapter.Fill(ds, "Course")
    
            Dim courseinfo = From ci In ds.Tables("Course").AsEnumerable()
                             Join fi In ds.Tables("Faculty").AsEnumerable()
                             On ci.Field(Of String)("faculty_id") Equals fi.Field(Of String)("faculty_id")
                             Where fi.Field(Of String)("faculty_name").Equals("Ying Bai")
                             Select New With {.course_id = ci.Field(Of String)("course_id")}
            For Each cid In courseinfo
                Console.WriteLine(cid.course_id)
            Next
            accConnection.Close()
            facultyCommand.Dispose()
            facultyCommand.Dispose()
            facultyAdapter.Dispose()
            courseAdapter.Dispose()
            Console.WriteLine("Press any key to continue...")
            Console.ReadKey()
        End Sub

    At the line: courseAdapter.Fill(ds, "Course"), I get the following error

    I am reading that as telling me that on the SELECT line: Select New With {.course_id = ci.Field(Of String)("course_id")}

    One of the above mentioned errors occurred.  I realize that I might not even be close to what is actually going on, but on the assumption that I am, I have been unable to see anything in that line that is wrong.


    gwboolean

    Wednesday, July 25, 2018 10:14 PM

Answers

  • There is a trailing comma in this command text:

    ""SELECT course_id, faculty_id, From Course""

    Remove the comma after faculty_id and see if that fixes it.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by gwboolean Thursday, July 26, 2018 12:25 AM
    Thursday, July 26, 2018 12:14 AM
    Moderator

All replies

  • Hello,

    After close review of the code presented the only conclusion I can come to us a misspelling as there are no reserve words or punctuations present.

    I assume you have created and tested the SELECT statements inside of the database to validate they are correct.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, July 25, 2018 10:31 PM
    Moderator
  • First, am I looking at the correct place for the error?

    I have opened the database and can see nothing.  I did not create the database, but I can see nothing that should be a problem with the query.

    I do not know what SELECT statements might be in the database or where to look for them.

     


    gwboolean

    Wednesday, July 25, 2018 10:39 PM
  • I've looked over the SQL, see no issues. Have you executed the offending SQL SELECT inside of MS-Access?

    Nothing else to offer without a hands-on session with the project and database.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, July 25, 2018 11:57 PM
    Moderator
  • There is a trailing comma in this command text:

    ""SELECT course_id, faculty_id, From Course""

    Remove the comma after faculty_id and see if that fixes it.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by gwboolean Thursday, July 26, 2018 12:25 AM
    Thursday, July 26, 2018 12:14 AM
    Moderator
  • Reed,

    I never even looked there.  I was so caught up in that one line that I never looked above.

    Thanks

    And thanks too Karen.


    gwboolean

    Thursday, July 26, 2018 12:25 AM
  • Reed,

    I never even looked there.  I was so caught up in that one line that I never looked above.

    Thanks

    And thanks too Karen.


    gwboolean

    This is the reason why I asked you to run the SELECT statement in MS-Access. When I write SQL it's always written in the database editor MS-Access in create new query, SQL-Server in SSMS (SQL-Server Management Studio) so this way you know the query syntax is correct. Anyways I did recommend this over three hours ago.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, July 26, 2018 1:49 AM
    Moderator

  • This is the reason why I asked you to run the SELECT statement in MS-Access. When I write SQL it's always written in the database editor MS-Access in create new query, SQL-Server in SSMS (SQL-Server Management Studio) so this way you know the query syntax is correct. Anyways I did recommend this over three hours ago.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    You also said: "I've looked over the SQL, see no issues. "

    Obviously there was an issue you overlooked.  No big deal, we all make mistakes.  That said, I'm not sure why you are giving the OP a hard time about not trying Access... maybe he doesn't even own a copy of Access.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, July 26, 2018 3:10 AM
    Moderator

  • This is the reason why I asked you to run the SELECT statement in MS-Access. When I write SQL it's always written in the database editor MS-Access in create new query, SQL-Server in SSMS (SQL-Server Management Studio) so this way you know the query syntax is correct. Anyways I did recommend this over three hours ago.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    You also said: "I've looked over the SQL, see no issues. "

    Obviously there was an issue you overlooked.  No big deal, we all make mistakes.  That said, I'm not sure why you are giving the OP a hard time about not trying Access... maybe he doesn't even own a copy of Access.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    In regards to me saying I see no issue, it's easy to miss a comma. In regards to giving them a hard time, don't see it that way but instead providing an easy way to determine the error. Do they have MS-Access, seems that way from the database schema they showed in an image earlier.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, July 26, 2018 12:58 PM
    Moderator