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

Add spaces with your line breaks. From what I'm seeing there will be no space before the FROM and INNER JOIN statements. — NoAlias
– NoAlias, Commented Jul 21, 2015 at 21:45

M.Ali · Accepted Answer · 2015-07-21 21:49:42Z

2

There are a couple of things wrong with your query.

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] " & _

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)
```

answered Jul 21, 2015 at 21:49

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Munawar Hussain Over a year ago

Syntax error (missing operator) in query expression '[txnStudentFeeHead].[FeeHeadID] = [mstFeeHead].[FeeHeadID] INNER JOIN [mstFeePlan] ON [mstFeeHead].[FeeHeadID] = [mstFeePlan].[FeeHeadID]'.

M.Ali Over a year ago

@MunawarHussain Update your question with what you are executing now?

Munawar Hussain Over a year ago

Updated question any help will be appreciated . .

M.Ali Over a year ago

You just have to look closer and error is right there 1st you havent added a space after "[txnStudentFeeHead] " & _ 2nd in the very last line you have missed out the closing square bracket for ClassID Column (mstFeePlan.[ClassID] = @ClassID)

Munawar Hussain Over a year ago

No avail. Still stuck at that Oledbexception

|

Munawar Hussain · Accepted Answer · 2015-07-21 23:09:12Z

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()

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related