1

This is getting pretty annoying. This SQL statement runs fine in Access:

SELECT payrecords.*, employees.name AS employee
FROM Payrecords 
INNER JOIN employees ON payrecords.employee_id = employees.id
WHERE payrecords.payrun_id = ?
ORDER BY employees.name;

But the tableadapter wizard insists there is a syntax error. Even when when I replace the "?" with an actual id it whines. This happens on a number of non-vanilla SQL statements. Sometimes when I ignore the "error" the code runs fine, but sometimes not. This happens in VS 2010 and 2015. Is there a work-around maybe?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Joe Gillon
  • 107
  • 10
  • 1
    and the actual error message is? – Marc B Jun 08 '16 at 14:16
  • Instead of showing us what actually works, you should show us what *isn't working*, and the full error message(s) that it spits out. – Siyual Jun 08 '16 at 14:20
  • 1
    Try the same query without the semicolon at the end. I just tried your query in VS2010 and it complained about the ORDER BY clause until I got rid of the semicolon. – Gord Thompson Jun 08 '16 at 14:35
  • Hmm. Maybe I didn't make it clear that the "error", which should NOT be an error, is in Visual Studio. I showed what worked in Access to prove there was nothing wrong with the query. – Joe Gillon Jun 08 '16 at 15:05
  • Give Gord a kewpie doll!. Zapped the semi-colon and now Visual Studio is happy. Grr. How do I give you credit for this one? – Joe Gillon Jun 08 '16 at 15:06
  • One other thing. VS handles the semi-colon just fine on simple, plain vanilla SQL statements. Double grr. – Joe Gillon Jun 08 '16 at 15:09

1 Answers1

2

The TableAdapter query parser in Visual Studio seems to have difficulty dealing with (moderately) complex SQL statements that end with a semicolon (;). In this case, simply removing the semicolon from the end of the statement resolved the issue.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Don't even know how you thought to try this. Especially considering the semi-colon was supplied by VS, not by me. Clearly a bug that has lasted through VS 2015. I can't believe I'm the first person this has happened to. – Joe Gillon Jun 08 '16 at 16:37