Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Shep
Starting Member
19 Posts |
Posted - 2013-07-22 : 13:52:15
|
I'm getting an error message that reads:Incorrect syntax near '.'.Here's the code: sSQL = @"SELECT RequestForms.RequestFormID, RequestTypes.RequestType, Employees.EmployeeID, Employees.FirstName, Employees.LastName, RequestForms.SubmitDate, Status.status approvals.comments, mgr.FirstName, mgr.LastName FROM RequestForms INNER JOIN Employees ON Employees.EmployeeID = RequestForms.EmployeeID INNER JOIN RequestTypes ON RequestTypes.RequestTypeID = RequestForms.RequestTypeID INNER JOIN status ON Status.StatusID = RequestForms.StatusID LEFT JOIN Approvals on RequestForms.RequestFormID = Approvals.RequestFormID LEFT JOIN Employees mgr ON Approvals.ManagerEmployeeID = mgr.EmployeeID WHERE (requestforms.RequestFormID = @requestID)"; cmd.CommandText = sSQL; using (SqlDataReader rdr = cmd.ExecuteReader())It only broke when I added the LEFT JOINS parts. The query works in SQL but not in Visual Studio. Its the last line that is giving the error, but I'm sure it has something to do with the query.What did I do wrong or am I overlooking something.Please help.ThanksJS |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 13:55:26
|
Add a comma after the line with STATUS column in your select statement. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 13:56:01
|
sSQL = @"SELECT RequestForms.RequestFormID, RequestTypes.RequestType,Employees.EmployeeID, Employees.FirstName, Employees.LastName,RequestForms.SubmitDate,Status.status,approvals.comments,mgr.FirstName,mgr.LastNameFROM RequestForms INNER JOIN Employees ON Employees.EmployeeID = RequestForms.EmployeeID INNER JOIN RequestTypes ON RequestTypes.RequestTypeID = RequestForms.RequestTypeIDINNER JOIN status ON Status.StatusID = RequestForms.StatusIDLEFT JOIN Approvals on RequestForms.RequestFormID = Approvals.RequestFormIDLEFT JOIN Employees mgr ON Approvals.ManagerEmployeeID = mgr.EmployeeIDWHERE (requestforms.RequestFormID = @requestID)"; Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
Shep
Starting Member
19 Posts |
Posted - 2013-07-22 : 14:01:24
|
Thanks, you stare at a screen long enough and you overlook the little things.J.E.Shepler |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 14:04:17
|
Could be. I just read the error message saying there was a syntax error on line 9. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 14:11:52
|
As a best practice, learn to prefix your tables with proper schema. And use table aliases.sSQL = @" SELECT rf.RequestFormID, rt.RequestType, e.EmployeeID, e.FirstName, e.LastName, rf.SubmitDate, s.[Status], a.comments, m.FirstName, m.LastName FROM dbo.RequestForms AS rf INNER JOIN dbo.Employees AS e ON e.EmployeeID = rf.EmployeeID INNER JOIN dbo.RequestTypes AS rt ON rt.RequestTypeID = rf.RequestTypeID INNER JOIN dbo.[Status] AS s ON s.StatusID = rf.StatusID LEFT JOIN dbo.Approvals AS a ON a.RequestFormID = rf.RequestFormID LEFT JOIN dbo.Employees AS m ON m.EmployeeID = a.ManagerEmployeeID WHERE rf.RequestFormID = @requestID; "; Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
Shep
Starting Member
19 Posts |
Posted - 2013-07-22 : 14:22:01
|
I'm still on a heavy learning curve right now.Thanks for the pointers.I appreciate everything anyone can help me with.J.E.Shepler |
|
|
|
|
|
|
|