My SQL code works in Microsoft access but not in VB.net

Question

I wrote SQL code in Microsoft access and ran it. It works. Yay. However when I copy it into visual basic I get the error

'System.Data.OleDb.OleDbException: 'Syntax error in query expression ...'

This is my code:

myconnection.Open()
        Dim command As New OleDbCommand
            command = New OleDbCommand("SELECT [Student data].[Student First Name], [Student data].[Student Last Name], Guardian.[Guardian First Name], Guardian.[Guardian Last Name], Guardian.[Phone number], [Form].[Form group], Medication.[Medication Name] FROM [Form], [Student data], Guardian, Medication, [Student + Guardian], [Student + Medication] WHERE ([Form].[Form group] = [Student data].[Form group]) AND ([Guardian].[Guardian ID] = [Student + Guardian].[Guardian ID]) AND ([Student data].[Student ID] = [Student + Guardian].[Student ID]) AND ([Medication].[Medication ID] = [Student + Medication].[Medication ID]) AND ([Student data].[Student ID] = [Student + Medication].[Student ID]) AND ([Student data].[Severity] IN ('High', 'Moderate', 'Low');", myconnection)
        Dim reader As OleDbDataReader = command.ExecuteReader()

Bellow I have included the exact code I wrote in access

SELECT [Student data].[Student First Name], [Student data].[Student Last Name], Guardian.[Guardian First Name], Guardian.[Guardian Last Name], Guardian.[Phone number], Form.[Form group], Medication.[Medication Name] 

FROM Form, [Student data], Guardian, Medication, [Student + Guardian], [Student + Medication] 

WHERE (Form.[Form group] = [Student data].[Form group]) AND ([Guardian].[Guardian ID] = [Student + Guardian].[Guardian ID]) AND ([Student data].[Student ID] = [Student + Guardian].[Student ID]) AND ([Medication].[Medication ID] = [Student + Medication].[Medication ID]) AND ([Student data].[Student ID] = [Student + Medication].[Student ID]) AND ([Student data].[Severity] IN ( 'High', 'Moderate', 'Low'));

I can't say for certain, but does it work if you escape `Form`, as in `[Form]`? - because that is a [reserved word in Access](https://support.office.com/en-gb/article/learn-about-access-reserved-words-and-symbols-ae9d9ada-3255-4b12-91a9-f855bdd9c5a2). — Andrew Morton, Feb 21 '20 at 14:09
Did you get all of them? In a couple of similar questions [1](https://stackoverflow.com/q/28915543/1115360) [2](http://www.vbforums.com/showthread.php?335540-RESOLVED-SQL-query-works-in-Access-but-not-VB&p=1990614#post1990614), it turned out that square brackets fixed it, and I notice that `Form` appears several times in your query. — Andrew Morton, Feb 21 '20 at 14:26
@AndrewMorton I have gone through the code several times trying to see if I have missed one however I can't see any that i have missed. I have updated the code in my question to reflect my corrections. — Mary Oak, Feb 21 '20 at 14:39
You could narrow down where it's unhappy by using `SELECT 'hello'` instead of the actual columns, and removing the `WHERE` clause. — Andrew Morton, Feb 21 '20 at 15:27
Are [`Student + Guardian], [Student + Medication]` (in your `FROM` clause) actual table names ? MS Access must be more lax than other DBMSes when it comes to naming objects but I could easily imagine that another SQL middleware would choke on special characters like the plus sign. To debug this, I would simplify the statement by removing one table at a time until the query succeeds (beginning with the tables containing special characters). — Kate, Feb 21 '20 at 16:07
If you avoid silly table and column names in future, you'll likely avoid such issues. As always, you should start by reducing your query to the absolute simplest form you can and then build it up piecemeal until it breaks. — jmcilhinney, Feb 21 '20 at 16:08
@AndrewMorton when I comment out `AND ([Student data].[Severity] IN ('High', 'Moderate', 'Low');` it works however as soon as I add that essential part of the query back it fails to work again. — Mary Oak, Feb 21 '20 at 16:09
@jmcilhinney I have to name the columns and tables that as that is what I have instructed to do by course. I have removed part of the query and built it back up again until it breaks. My problem is why does it break when in visual basic and the line `AND ([Student data].[Severity] IN ('High', 'Moderate', 'Low');` is added however it runs perfectly fine when in MS. — Mary Oak, Feb 21 '20 at 16:19
@Anonymous Yes those are actual table names. I have also copied them from my database exactly to avoid any errors in that area. — Mary Oak, Feb 21 '20 at 16:20
It *can't* be it, but in the one that works there is a space after the opening bracket: `( 'High', 'Moderate', 'Low')` and now I think I see it: there should be two closing brackets `( 'High', 'Moderate', 'Low'));` — Andrew Morton, Feb 21 '20 at 16:20
@jmcilhinney what do you consider to be column and table names which arent silly. Just so i know for the future. — Mary Oak, Feb 21 '20 at 16:21
@AndrewMorton It's fixed! Thank you so much!!!! Sorry for the stupid error. Thank you for helping me and being patient. — Mary Oak, Feb 21 '20 at 16:24
There are a few articles to be found on [SQL naming conventions](https://www.red-gate.com/simple-talk/blogs/sql-naming-conventions/). — Andrew Morton, Feb 21 '20 at 16:24
You're welcome :) As is the convention, I voted to close this question because "it was caused by a typo". — Andrew Morton, Feb 21 '20 at 16:26
You have traced the issue. If you look carefully, the problem is indeed with that final `IN` clause. You have: `AND ([Student data].[Severity] IN ('High', 'Moderate', 'Low');", myconnection)`. So you need one more parenthesis after `'Low'` or just remove the opening parenthesis right after the `AND` - it's not needed. — Kate, Feb 21 '20 at 16:27
No spaces and no special characters is a good place to start. Reserved words should be avoided but sometimes you don't realise something is a reserved word. They are much easier to track down if you don't have to escape everything because of spaces and symbols though. Anything out of the ordinary is easier to track down without loads of brackets obscuring things. I'd also recommend laying out complex SQL on multiple lines, which also improves readability. — jmcilhinney, Feb 21 '20 at 16:27
@jmcilhinney yeh thats why when I have spaces and characters I always copy the table and column names into the query to avoid errors like the ones you are describinig. — Mary Oak, Feb 21 '20 at 16:31
In your query, is `Form` the name of a table or query, or is that referring to a property (var, field) on the current form? — tgolisch, Feb 21 '20 at 17:09

HardCode · Answer 1 · 2020-02-25T15:08:13.930

While not exactly a direct answer to the question, which issue was a result of a typo, I feel this answer serves to avoid the issue in the first place. Combined with the OP's question of "what's a good naming convention" within the comments, here is a better way to name database objects, apply proper ANSI joins, and use table aliases.

This cleaner format would make a typo, such as in the question, stand out and be easy to find. Note that the offending missing parenthesis isn't even needed for the OP's query.

SELECT
    s.FirstName AS StudentFirstName,
    s.LastName AS StudentLastName,
    g.FirstName AS GuardianFirstName,
    g.LastName AS GuaridanLastName,
    g.TelephoneNumber AS GuardianTelephoneNumber,
    f.FormGroup, -- Not "Group" as that's an SQL reserved word.
    m.Name AS MedicationName 
FROM
    Forms f
    INNER JOIN Students s ON f.FormGroup = s.FormGroup
    INNER JOIN StudentGuardians sg ON s.StudentID = sg.StudentID
    INNER JOIN Guardians g ON sg.GuardianID = g.GuardianID
    INNER JOIN StudentMedications sm ON s.StudentID = sm.StudentID
    INNER JOIN Medications m ON sm.MedicationID = m.MedicationID
WHERE
    s.Severity IN ('High', 'Moderate', 'Low')

If this revised query had a missing paren, it would clearly stick out.

EDIT: You may need to tweak the JOIN syntax for MS Access.

If the OP does choose to use inner joins: [If you are writing a query against an Access database backend, you need to use the following join syntax](https://stackoverflow.com/a/19370416/1115360) may still apply. — Andrew Morton, Feb 22 '20 at 09:27
I haven't used Access is many years, but I remember distinctly removing all of the fluff parenthesis that the query designer added. I'm not 100% sure how that still works today, however. — HardCode, Feb 25 '20 at 15:07

My SQL code works in Microsoft access but not in VB.net

1 Answers1