Incorrect Syntax Near The Keyword 'as'

Why do i receive a syntax error as such: Incorrect syntax near the keyword 'AS' I am using microsoft visual studio 2005 and sql server 2005 string strSql = 'SELECT a.MCode, a.Name

Solution 1:

Missing spaces. Your code is creating the string

"... , '' AS TotalTeachUnitFROM (SELECT * ..."

Same goes for the other lines, they need spaces between them.

Solution 2:

I won't answer your question, but I'll give you a strategy:

copy the contents of the strSql string to the clipboard, then to a query in SQL Management Studio, and run it there.

Then it will actually tell you which "AS" is causing the problem. Most likely it's a missing space or appostraphe or comma.

Solution 3:

make "FROM ... as " FROM ...

right now it is ... AS TotalTeachUnitFROM ...

Solution 4:

You are missing spaces.

"...a ON dt.ModuleCode = a.MCode" +
"FROM (SELECT * FROM ..."

results in

"...a ON dt.ModuleCode = a.MCodeFROM (SELECT * FROM ..."
                                ^
                            space missing

Solution 5:

Rather use @ instead of +

string strSql = @"SELECT a.MCode, a.NameOfModule, a.Mod_Abbreviation, dt.ModuleCode, dt.Course, dt.Stage, dt.ModuleGrpFrom, dt.ModuleGrpTo, dt.GrpName, dt.GrpType, dt.StaffID, dt.AcadYear, dt.AcadSemester, dt.TotalHour, dt.WeeklyLectHr, dt.WeeklyPractHr, dt.WeeklyTutHr, dt.ModuleLeader, 0 AS TotalTeach, '' AS ModuleGroups, '' AS ML, 0 AS L, 0 AS P, 0 AS T, 1 AS NofGrp, '' AS TotalTeachUnit
FROM (SELECT * FROM (SELECT a.ModuleCode, a.Course, a.Stage, a.ModuleGrpFrom, a.ModuleGrpTo, a.GrpName, a.GrpType, a.StaffID, b.AcadYear, b.AcadSemester, b.TotalHour, b.WeeklyLectHr, b.WeeklyPractHr, b.WeeklyTutHr, b.ModuleLeader FROM ModuleStrGrp a LEFT JOIN ModuleStr b ON a.AcadYear = b.AcadYear AND a.AcadSemester = b.AcadSemester AND a.Course = b.Course AND a.ModuleCode = b.ModuleCode) AS MSG INNER JOIN Parameter Para ON MSG.Course = Para.Paracode2) AS dt LEFT JOIN Module a ON dt.ModuleCode = a.MCode
WHERE dt.AcadYear LIKE AcadYear AND dt.AcadSemester LIKE AcadSemester AND dt.Course LIKE Course AND dt.Stage LIKE Stage AND dt.StaffID LIKE Staff AND dt.SpecGrp LIKE Specialisation
ORDER BY dt.ModuleCode, dt.GrpType";