Syntax Error in FROM Clause in C#(MS Access)

Question

I am getting Syntax error in from clause while accessing data from MS Access database but if i run the same query in access i get the desired results without any error.

Any help is appreciated. This is my code

string strQuery = string.Empty  ;   


            strQuery = "SELECT TUCLRII.LUSID,TUCLRII.UserID,TUCLRII.LastName,TUCLRII.FirstName,TUCLRII.MI,TUCLRII.Sex,TUCLRII.WhichEye,TUCLRII.Memo1,TUCLRII.Memo2,TUCLRII.CreationDT, TUCLRII.LastModifiedDT,TUCLRII.CardID,TUCLRI.UCID,TUCLRII.WiegandData,TUCLRII.LIrisCodeID,TUCLRII.LWhichEye,TUCLRII.LIrisCode,TUCLRII.RIrisCodeID,TUCLRII.RWhichEye,TUCLRII.RIrisCode,TUCLRII.LIrisImage,TUCLRII.LImageFormat,TUCLRII.LImageSize,TUCLRII.LWhichEye,TUCLRII.RIrisImage,TUCLRII.RImageFormat,TUCLRII.RImageSize,TUCLRII.RWhichEye,TIM.ImageFormat,TIM.ImageSize,TIM.FaceImage,TIM.PictureTakenDT"; 
            strQuery += " FROM (SELECT TUCLRI.LUSID,TUCLRI.UUID,TUCLRI.UserID,TUCLRI.LastName,TUCLRI.FirstName,TUCLRI.MI,TUCLRI.Sex,TUCLRI.WhichEye,TUCLRI.Memo1,TUCLRI.Memo2,TUCLRI.CreationDT,TUCLRI.LastModifiedDT, TUCLRI.CardID, TUCLRI.UCID, TUCLRI.WiegandData, TUCLRI.LIrisCodeID ,TUCLRI.LWhichEye,TUCLRI.LIrisCode,TUCLRI.RIrisCodeID,TUCLRI.RWhichEye,TUCLRI.RIrisCode,TUCLRI.LIrisImage,TUCLRI.LImageFormat,TUCLRI.LImageSize,TUCLRI.LWhichEye,TRIM.IrisImage as RIrisImage,TRIM.ImageFormat as RImageFormat, TRIM.ImageSize as RImageSize, TRIM.WhichEye as RWhichEye";                 
            strQuery += " FROM (SELECT TUCLR.LUSID,TUCLR.UUID,TUCLR.UserID,TUCLR.LastName,TUCLR.FirstName,TUCLR.MI,TUCLR.Sex,TUCLR.WhichEye,TUCLR.Memo1,TUCLR.Memo2,TUCLR.CreationDT,TUCLR.LastModifiedDT,TUCLR.CardID,TUCLR.UCID,TUCLR.WiegandData,TUCLR.LIrisCodeID,TUCLR.LWhichEye,TUCLR.LIrisCode,TUCLR.RIrisCodeID,TUCLR.RWhichEye,TUCLR.RIrisCode,TLIM.IrisImage as LIrisImage,TLIM.ImageFormat as LimageFormat,TLIM.ImageSize as LImageSize,TLIM.WhichEye as LWhichEye";
            strQuery += " FROM (SELECT TUCI.LUSID,TUCI.UUID,TUCI.UserID,TUCI.LastName,TUCI.FirstName,TUCI.MI,TUCI.Sex,TUCI.WhichEye,TUCI.Memo1,TUCI.Memo2,TUCI.CreationDT,TUCI.LastModifiedDT,TUCI.CardID,TUCI.UCID,TUCI.WiegandData, TUCI.LIrisCodeID,TUCI.LWhichEye,TUCI.LIrisCode,TRI.IrisCodeID as RIrisCodeID,TRI.WhichEye as RWhichEye,TRI.IrisCode as RIrisCode";  
            strQuery += " FROM (SELECT TUC.LUSID,TUC.UUID,TUC.UserID,TUC.LastName,TUC.FirstName,TUC.MI,TUC.Sex,TUC.WhichEye,TUC.Memo1,TUC.Memo2,TUC.CreationDT,TUC.LastModifiedDT,TUC.CardID,TUC.UCID,TUC.WiegandData,TLI.IrisCodeID as LIrisCodeID,TLI.WhichEye as LWhichEye,TLI.IrisCode as LIrisCode";
            strQuery += " FROM (SELECT TU.LUSID,TU.UUID,TU.UserID,TU.LastName,TU.FirstName,TU.MI,TU.Sex,TU.WhichEye,TU.Memo1,TU.Memo2,TU.CreationDT,TU.LastModifiedDT,TC.CardID,TC.UCID,TC.WiegandData FROM UserInfo TU left join CardInfo TC on (TU.UUID=TC.UUID and TU.LUSID = 0)) TUC LEFT JOIN IrisCodeInfo TLI ON(TUC.UUID = TLI.UUID and TLI.WhichEye=1))TUCI LEFT JOIN IrisCodeInfo TRI ON(TUCI.UUID = TRI.UUID and TRI.WhichEye=2))TUCLR LEFT JOIN IrisImageInfo TLIM ON(TUCLR.UUID = TLIM.UUID and TLIM.WhichEye=1))TUCLRI LEFT JOIN IrisImageInfo TRIM ON(TUCLRI.UUID = TRIM.UUID and TRIM.WhichEye=2))TUCLRII LEFT JOIN FaceImageInfo TIM ON(TUCLRII.UUID = TIM.UUID)";

Answer 1

I took your string statements and put it into Instant SQL Formatter

And got this

SELECT tuclrii.lusid, 
       tuclrii.userid, 
       tuclrii.lastname, 
       tuclrii.firstname, 
       tuclrii.mi, 
       tuclrii.sex, 
       tuclrii.whicheye, 
       tuclrii.memo1, 
       tuclrii.memo2, 
       tuclrii.creationdt, 
       tuclrii.lastmodifieddt, 
       tuclrii.cardid, 
       tuclri.ucid, 
       tuclrii.wieganddata, 
       tuclrii.liriscodeid, 
       tuclrii.lwhicheye, 
       tuclrii.liriscode, 
       tuclrii.ririscodeid, 
       tuclrii.rwhicheye, 
       tuclrii.ririscode, 
       tuclrii.lirisimage, 
       tuclrii.limageformat, 
       tuclrii.limagesize, 
       tuclrii.lwhicheye, 
       tuclrii.ririsimage, 
       tuclrii.rimageformat, 
       tuclrii.rimagesize, 
       tuclrii.rwhicheye, 
       tim.imageformat, 
       tim.imagesize, 
       tim.faceimage, 
       tim.picturetakendt 
FROM   (SELECT tuclri.lusid, 
               tuclri.uuid, 
               tuclri.userid, 
               tuclri.lastname, 
               tuclri.firstname, 
               tuclri.mi, 
               tuclri.sex, 
               tuclri.whicheye, 
               tuclri.memo1, 
               tuclri.memo2, 
               tuclri.creationdt, 
               tuclri.lastmodifieddt, 
               tuclri.cardid, 
               tuclri.ucid, 
               tuclri.wieganddata, 
               tuclri.liriscodeid, 
               tuclri.lwhicheye, 
               tuclri.liriscode, 
               tuclri.ririscodeid, 
               tuclri.rwhicheye, 
               tuclri.ririscode, 
               tuclri.lirisimage, 
               tuclri.limageformat, 
               tuclri.limagesize, 
               tuclri.lwhicheye, 
               TRIM.irisimage   AS ririsimage, 
               TRIM.imageformat AS rimageformat, 
               TRIM.imagesize   AS rimagesize, 
               TRIM.whicheye    AS rwhicheye 
        FROM   (SELECT tuclr.lusid, 
                       tuclr.uuid, 
                       tuclr.userid, 
                       tuclr.lastname, 
                       tuclr.firstname, 
                       tuclr.mi, 
                       tuclr.sex, 
                       tuclr.whicheye, 
                       tuclr.memo1, 
                       tuclr.memo2, 
                       tuclr.creationdt, 
                       tuclr.lastmodifieddt, 
                       tuclr.cardid, 
                       tuclr.ucid, 
                       tuclr.wieganddata, 
                       tuclr.liriscodeid, 
                       tuclr.lwhicheye, 
                       tuclr.liriscode, 
                       tuclr.ririscodeid, 
                       tuclr.rwhicheye, 
                       tuclr.ririscode, 
                       tlim.irisimage   AS lirisimage, 
                       tlim.imageformat AS limageformat, 
                       tlim.imagesize   AS limagesize, 
                       tlim.whicheye    AS lwhicheye 
                FROM   (SELECT tuci.lusid, 
                               tuci.uuid, 
                               tuci.userid, 
                               tuci.lastname, 
                               tuci.firstname, 
                               tuci.mi, 
                               tuci.sex, 
                               tuci.whicheye, 
                               tuci.memo1, 
                               tuci.memo2, 
                               tuci.creationdt, 
                               tuci.lastmodifieddt, 
                               tuci.cardid, 
                               tuci.ucid, 
                               tuci.wieganddata, 
                               tuci.liriscodeid, 
                               tuci.lwhicheye, 
                               tuci.liriscode, 
                               tri.iriscodeid AS ririscodeid, 
                               tri.whicheye   AS rwhicheye, 
                               tri.iriscode   AS ririscode 
                        FROM   (SELECT tuc.lusid, 
                                       tuc.uuid, 
                                       tuc.userid, 
                                       tuc.lastname, 
                                       tuc.firstname, 
                                       tuc.mi, 
                                       tuc.sex, 
                                       tuc.whicheye, 
                                       tuc.memo1, 
                                       tuc.memo2, 
                                       tuc.creationdt, 
                                       tuc.lastmodifieddt, 
                                       tuc.cardid, 
                                       tuc.ucid, 
                                       tuc.wieganddata, 
                                       tli.iriscodeid AS liriscodeid, 
                                       tli.whicheye   AS lwhicheye, 
                                       tli.iriscode   AS liriscode 
                                FROM   (SELECT tu.lusid, 
                                               tu.uuid, 
                                               tu.userid, 
                                               tu.lastname, 
                                               tu.firstname, 
                                               tu.mi, 
                                               tu.sex, 
                                               tu.whicheye, 
                                               tu.memo1, 
                                               tu.memo2, 
                                               tu.creationdt, 
                                               tu.lastmodifieddt, 
                                               tc.cardid, 
                                               tc.ucid, 
                                               tc.wieganddata 
                                        FROM   userinfo tu 
                                               LEFT JOIN cardinfo tc 
                                                 ON ( tu.uuid = tc.uuid 
                                                      AND tu.lusid = 0 )) tuc 
                                       LEFT JOIN iriscodeinfo tli 
                                         ON( tuc.uuid = tli.uuid 
                                             AND tli.whicheye = 1 ))tuci 
                               LEFT JOIN iriscodeinfo tri 
                                 ON( tuci.uuid = tri.uuid 
                                     AND tri.whicheye = 2 ))tuclr 
                       LEFT JOIN irisimageinfo tlim 
                         ON( tuclr.uuid = tlim.uuid 
                             AND tlim.whicheye = 1 ))tuclri 
               LEFT JOIN irisimageinfo TRIM 
                 ON( tuclri.uuid = TRIM.uuid 
                     AND TRIM.whicheye = 2 ))tuclrii 
       LEFT JOIN faceimageinfo tim 
         ON( tuclrii.uuid = tim.uuid ) 

It would appear that you created a inline view for every join.

Syntactically it looks fine to me but perhaps Access didn't like it. Its a pretty inefficient way of joining anyway when you can just use standard joins

SELECT tu.lusid, 
       tu.userid, 
       tu.lastname, 
       tu.firstname, 
       tu.mi, 
       tu.sex, 
       tu.whicheye, 
       tu.memo1, 
       tu.memo2, 
       tu.creationdt, 
       tu.lastmodifieddt, 
       tc.cardid, 
       tc.ucid, 
       tc.wieganddata, 
       tli.iriscodeid AS liriscodeid, 
       tli.whicheye   AS lwhicheye, 
       tli.iriscode   AS liriscode 
       tri.iriscodeid AS ririscodeid, 
       tri.whicheye   AS rwhicheye, 
       tri.iriscode   AS ririscode,  
       tlim.irisimage   AS lirisimage, 
       tlim.imageformat AS limageformat, 
       tlim.imagesize   AS limagesize, 
       tlim.whicheye    AS lwhicheye 
       TRIM.irisimage   AS ririsimage, 
       TRIM.imageformat AS rimageformat, 
       TRIM.imagesize   AS rimagesize, 
       TRIM.whicheye    AS rwhicheye 
       tim.imageformat, 
       tim.imagesize, 
       tim.faceimage, 
       tim.picturetakendt
FROM   
    userinfo tu 
    LEFT JOIN cardinfo tc 
    ON ( tu.uuid = tc.uuid 
         AND tu.lusid = 0 )
    LEFT JOIN iriscodeinfo tli 
    ON( tu.uuid = tli.uuid 
        AND tli.whicheye = 1 )
    LEFT JOIN iriscodeinfo tri 
    ON( tu.uuid = tri.uuid 
        AND tri.whicheye = 2 )
    LEFT JOIN irisimageinfo tlim 
        ON( tli.uuid = tlim.uuid 
        AND tlim.whicheye = 1 )
    LEFT JOIN irisimageinfo TRIM 
            ON( tri.uuid = TRIM.uuid 
               AND TRIM.whicheye = 2 )
       LEFT JOIN faceimageinfo tim 
         ON( tu.uuid = tim.uuid ) 

Answer 2

You're missing some spaces around the parentheses in your JOIN clauses. I'm not sure if this is the issue, though.