vb.net implict inner join syntax error (missing operator) in query expression

Question

This is the code where I am getting exception message. However this code worked perfect in sql server 2005 but generating error in access. This code working fine in sql server project but in access its generating exception as I mentioned..

Public Function CalculateFeeReciept(ByVal monthid As Integer) As DataTable

    Dim cmd1 As New OleDbCommand("Select * from mstFeeHead", sqlcon)
    Dim dtmstFeeHead As New DataTable 'dtmstFeeHead contains all the fee heads id's
    Dim adp1 As New OleDbDataAdapter(cmd1)
    adp1.Fill(dtmstFeeHead)
    cmd1.Dispose()
    Dim selectedmonth As Integer
    Dim feeheadid As Integer
    Dim arr(25) As Integer 'arr contains Fee head id's that should be paid in the selected month

    If monthid = 13 Then
        monthid = 1
    ElseIf monthid = 14 Then
        monthid = 2
    ElseIf monthid = 15 Then
        monthid = 3
    End If

    selectedmonth = monthid + 3
    Dim m As Integer = 0
    For j As Integer = 0 To dtmstFeeHead.Rows.Count - 1
        feeheadid = Convert.ToInt32(dtmstFeeHead.Rows(j)(0))
        If dtmstFeeHead.Rows(j)(selectedmonth) Then
            arr(m) = feeheadid
            m = m + 1
        End If
    Next

    Dim cmd2 As New OleDbCommand("SELECT txnStudentFeeHead.FeeHeadID, mstFeeHead.FeeHeadName, mstFeePlan.Amount " & _
                                 "FROM " & _
                                 "txnStudentFeeHead " & _
                                 "INNER JOIN " & _
                                 "mstFeeHead " & _
                                 "ON " & _
                                 "txnStudentFeeHead.FeeHeadID = mstFeeHead.FeeHeadID " & _
                                 "INNER JOIN " & _
                                 "mstFeePlan " & _
                                 "ON " & _
                                 "mstFeeHead.FeeHeadID = mstFeePlan.FeeHeadID " & _
                                 "WHERE " & _
                                 "txnStudentFeeHead.StudentID = @StudentID) " & _
                                 "AND " & _
                                 "(mstFeePlan.SessionID = @SessionID) " & _
                                 "AND " & _
                                 "(mstFeePlan.ClassID = @ClassID) ", sqlcon)
    cmd2.CommandType = CommandType.Text
    cmd2.Parameters.Add("@StudentID", OleDbType.Integer).Value = StudentID()
    cmd2.Parameters.Add("@ClassID", OleDbType.Integer).Value = Convert.ToInt32(cmbClass.SelectedValue)
    cmd2.Parameters.Add("@SessionID", OleDbType.Integer).Value = Convert.ToInt32(cmbSession.SelectedValue)
    Dim dt As New DataTable 'dt contains all the fee head id's that are alloted to the students
    Dim adp As New OleDbDataAdapter(cmd2)
    adp.Fill(dt)
    cmd2.Dispose()

    Dim dt2 As New DataTable 'dt2 contains all the fee head id's that are alloted to the student and that should be paid in that particular month 
    ' dt2 contains the filtrate of dt and arr
    dt2 = dt.Clone()
    For i As Integer = 0 To arr.Length - 1
        For j As Integer = 0 To dt.Rows.Count - 1
            Dim dtrow As DataRow = dt2.NewRow()
            If arr(i) = dt.Rows(j)(0) Then
                dtrow(0) = arr(i)
                dtrow(1) = dt.Rows(j)(1)
                dtrow(2) = dt.Rows(j)(2)
                dt2.Rows.Add(dtrow)
            End If
        Next
    Next

    cmd2 = New OleDbCommand("Select Sum(TotalFees) as TotalFees, Sum(LateFees) as TotalLateFees, Sum(OldBalance) as TotalOldBalance, Sum(Discount) as TotalDiscount, Sum(Scholarship) as TotalScholarship, Sum(Concession) as TotalConcession, Sum(AmountReceived) as TotalAmountReceived from txnFeePayment where SessionID=@SessionID and StudentID=@studentid and MonthID=@monthid Group by StudentId,MonthID", sqlcon)
    cmd2.CommandType = CommandType.Text
    cmd2.Parameters.Add("@studentid", OleDbType.Integer).Value = StudentID()
    cmd2.Parameters.Add("@SessionID", OleDbType.Integer).Value = cmbSession.SelectedValue

    cmd2.Parameters.Add("@monthid", OleDbType.Integer).Value = monthid
    Dim dtStudentReciept As New DataTable 'dt contains all the fee head id's that are alloted to the students
    adp = New OleDbDataAdapter(cmd2)
    adp.Fill(dtStudentReciept)
    cmd2.Dispose()

    Dim dtrow1 As DataRow = dt2.NewRow()

    If (dtStudentReciept.Rows.Count > 0) Then

        dtrow1(0) = 0
        dtrow1(1) = "Total Late Fees"
        dtrow1(2) = Convert.ToInt32(dtStudentReciept.Rows(0)(1))
        dt2.Rows.Add(dtrow1)

        dtrow1 = dt2.NewRow()
        dtrow1(0) = 0
        dtrow1(1) = "Total Discount"
        dtrow1(2) = Convert.ToInt32(dtStudentReciept.Rows(0)(3)) * -1
        dt2.Rows.Add(dtrow1)

        dtrow1 = dt2.NewRow()
        dtrow1(0) = 0
        dtrow1(1) = "Total Scholarship"
        dtrow1(2) = Convert.ToInt32(dtStudentReciept.Rows(0)(4)) * -1
        dt2.Rows.Add(dtrow1)

        dtrow1 = dt2.NewRow()
        dtrow1(0) = 0
        dtrow1(1) = "Total Concession"
        dtrow1(2) = Convert.ToInt32(dtStudentReciept.Rows(0)(5)) * -1
        dt2.Rows.Add(dtrow1)


        dtrow1 = dt2.NewRow()
        dtrow1(0) = 0
        dtrow1(1) = "Total Amount Received"
        dtrow1(2) = Convert.ToInt32(dtStudentReciept.Rows(0)(6)) * -1
        dt2.Rows.Add(dtrow1)

        Dim totalamount As Integer = 0
        For k As Integer = 0 To dt2.Rows.Count - 1
            totalamount = totalamount + dt2.Rows(k)(2)
        Next

        'dtrow1 = dt2.NewRow()
        'dtrow1(0) = totalamount
        'dtrow1(1) = "Current Month Fee"
        'dtrow1(2) = totalamount
        'dt2.Rows.Add(dtrow1)
    Else



        Dim totalamount As Integer = 0
        For k As Integer = 0 To dt2.Rows.Count - 1
            totalamount = totalamount + dt2.Rows(k)(2)
        Next

        'dtrow1 = dt2.NewRow()
        'dtrow1(0) = totalamount
        'dtrow1(1) = "Current Month Fee"
        'dtrow1(2) = totalamount
        'dt2.Rows.Add(dtrow1)

    End If



    dgvDisplay.DataSource = dt2

    For i As Integer = 0 To dgvDisplay.Columns.Count - 1
        dgvDisplay.Columns.Item(i).SortMode = DataGridViewColumnSortMode.NotSortable
        dgvDisplay.Columns(2).Width = 65
        dgvDisplay.Columns(1).Width = 132
    Next

    dgvDisplay.Columns.Item(0).Visible = False
    'txtTotalFees.Text = dt2.Rows(dt2.Rows.Count - 1)(0)
    Return dt2
End Function

Answer 1

Solved it myself by long time of effort and headache

Dim cmd2 As New OleDbCommand("SELECT txnStudentFeeHead.FeeHeadID, mstFeeHead.FeeHeadName, mstFeePlan.Amount, txnStudentFeeHead.StudentID, mstFeePlan.ClassID, mstFeePlan.SessionID FROM (txnStudentFeeHead INNER JOIN mstFeeHead ON txnStudentFeeHead.FeeHeadID = mstFeeHead.FeeHeadID) INNER JOIN mstFeePlan ON mstFeeHead.FeeHeadID = mstFeePlan.FeeHeadID WHERE (((txnStudentFeeHead.StudentID)=@StudentID) AND ((mstFeePlan.ClassID)=@ClassID) AND ((mstFeePlan.SessionID)=@SessionID))", sqlcon)
    cmd2.CommandType = CommandType.Text
    cmd2.Parameters.Add("@StudentID", OleDbType.Integer).Value = StudentID()
    cmd2.Parameters.Add("@ClassID", OleDbType.Integer).Value = Convert.ToInt32(cmbClass.SelectedValue)
    cmd2.Parameters.Add("@SessionID", OleDbType.Integer).Value = Convert.ToInt32(cmbSession.SelectedValue)
    Dim dt As New DataTable 'dt contains all the fee head id's that are alloted to the students
    Dim adp As New OleDbDataAdapter(cmd2)
    adp.Fill(dt)
    cmd2.Dispose()

Answer 2

There are a couple of things wrong with your query.

1. You havent left any spaces once a line in code is finished and whole query may look like they are separate lines but it is a one long string without any spaces.

   I have added spaces in the following piece of code at the end of each line.

   ("SELECT [txnStudentFeeHead].[FeeHeadID],[mstFeeHead].[FeeHeadName]," & _  
                                    "[mstFeePlan].[Amount] " & _
                                    "FROM " & _
                                    "[txnStudentFeeHead] " & _
                                    "INNER JOIN " & _
                                    "[mstFeeHead] " & _
   

2. You have put your variables in sqaure brackets [] , which means SQL Server will treat them as SQL Server Object(table name, Column Name) names and not as Variables. Remove the Square brackets.

   "WHERE " & _
   "([txnStudentFeeHead].[StudentID] = @StudentID) " & _
   "AND" & _
   "([mstFeePlan].[SessionID] = @SessionID) " & _
   "AND" & _
   "([mstFeePlan].[ClassID] = @ClassID) ", sqlcon)