SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Error message
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Shep
Starting Member

USA
19 Posts

Posted - 07/22/2013 :  13:52:15  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 07/22/2013 :  13:55:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
29910 Posts

Posted - 07/22/2013 :  13:56:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
19 Posts

Posted - 07/22/2013 :  14:01:24  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 07/22/2013 :  14:04:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
29910 Posts

Posted - 07/22/2013 :  14:11:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
19 Posts

Posted - 07/22/2013 :  14:22:01  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000