Search code examples
sqlvb.netms-accesssyntaxoledb

Syntax Error (Missing Operator) In Query Expression "

Trying to write an SQL query where I'm using an inner join for getting data from one table and display matched contents in another table, however I receive the error 'Syntax error (missing operator) in query expression "'. Not sure where I'm going wrong with this, however I've inserted the code I'm using, and it looks correct to me.

By the way, the code has been written as part of a VB program, hence the extra code beside the SQL code.

Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [classes] INNER JOIN [students] ON classes.StudentForename = students.Forename AND ON classes.StudentSurname = students.Surname AND ON classes.TeacherName ='" & personloggedon.Text & "' AND ON classes.Day ='" & System.DateTime.Now.DayOfWeek.ToString & "' AND ON classes.Period ='" & attendance_reg_periodComboBox.Text & "'", con)

I've also attempted to use the following code also, again, not working.

Dim cmd as OleDbCommand = New OleDbCommand("SELECT [Forename], [Surname] FROM [classes] INNER JOIN [students] ON classes.StudentForename = students.Forename AND ON classes.StudentSurname = students.Surname AND ON classes.TeacherName ='" & personloggedon.Text & "' AND ON classes.Day ='" & System.DateTime.Now.DayOfWeek.ToString & "' AND ON classes.Period ='" & attendance_reg_periodComboBox.Text & "'", con)

All my SQL queries need to do is get the forenames and surnames off of the "classes" database as long as they match up to the criteria given, then take the forenames and surnames given from said database and match up to the "students" database. From there, it is then displayed in a datagridview on my program.

I'm relatively new to SQL coding, so if the error is particularly obvious, I apologise in advance! However, all help is greatly appreciated.

Solution

  • You have too many ONs. Replace all AND ON with just AND.

    Dim cmd as OleDbCommand = New 
OleDbCommand("SELECT [Forename], [Surname] FROM 
[classes] INNER JOIN [students] ON 
classes.StudentForename = students.Forename AND 
classes.StudentSurname = students.Surname WHERE 
classes.TeacherName ='" & personloggedon.Text & "' 
AND  classes.Day ='" & 
System.DateTime.Now.DayOfWeek.ToString & "' AND 
classes.Period ='" & 
attendance_reg_periodComboBox.Text & "'", con)

    You really want to look at using parameterised queries though to avoid injection attacks.