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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Error message

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.
Thanks
JS

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
Go to Top of Page

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.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)";


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -