Results 1 to 8 of 8

Thread: VS2013 Table Adapter Syntax Error: Expecting identifier or quoted identifier

  1. #1
    dsk96m is offline VB.NET Forum Genius
    .NET Framework
    .NET 4.0
    Join Date
    Jan 2013
    Posts
    173
    Reputation
    58

    VS2013 Table Adapter Syntax Error: Expecting identifier or quoted identifier

    If I am posting this is the wrong area, I apologize.

    I am trying to add a table adapter to one of my datasets. The dataset has oracle as its datasource using ODP.NET. When I put in the query (which works elsewhere) I get this error (Syntax Error: Expecting identifier or quoted identifier). The query has left outer joins in it. If i go to query builder, it then adds stuff like { oj to the query. I knew there was an issue with previous visual studio versions, but for inner joins, but I thought they were fixed. How can I get this to work. I am banging my head against the wall on this one. Couldnt find an answer searching.

    Code:
    SELECT PERS.PERS_ID,
           PERS.PERS_LAST_NM,
           PERS.PERS_FIRST_NM,
           PERS.PERS_MIDDLE_INIT,
           PERS.PERS_MIDDLE_INIT,
           PERS.PERS_PREFERRED_NM,
           PERS.PERS_EMPLOYEE_NO,
           PERS.PERS_EMPLOYEE_TYP,
           PERS.PERS_STAT,
           PERS.PERS_LENT_CD                        AS PERS_ENTITY,
           PERS.PERS_TYP,
           PERS.PERS_MSTP_ID,
           PERS.PERS_BLDG_ID,
           PERS.PERS_TITLE_INIT,
           PERS.PERS_PERS_ID,
           PERS.PERS_COCC_ID,
           PERS.PERS_IORG_CD,
           PERS.PERS_CORPORATE_ID,
           CONCAT('(', CONCAT(PERS.PERS_PHONE_AREA_CD, CONCAT(')',CONCAT(PERS.PERS_PHONE_EXCH_NO,CONCAT('-',PERS.PERS_PHONE_EXTN_NO))))) AS PERS_PHONE_NO,
           UPPER(EMAIL.ELID_LONG_USERID)            AS EMPL_EMAIL,
           UPPER(USERID.ELID_USERID)                AS EMPL_USERID
    FROM   CDAS.TDWHPERS PERS
           LEFT OUTER JOIN (SELECT ELID_PERS_ID, 
                                   ELID_LONG_USERID
                            FROM   CDAS.TDWHELID
                            WHERE  ( ELID_EICT_CD = '0010' )
                                   AND ( ELID_OPEN_IND = 'Y' )) EMAIL
                        ON PERS.PERS_ID = EMAIL.ELID_PERS_ID
           LEFT OUTER JOIN (SELECT ELID_PERS_ID,
                                   ELID_USERID
                            FROM   CDAS.TDWHELID TDWHELID_1
                            WHERE  ( ELID_EICT_CD = '9100' )
                                   AND ( ELID_OPEN_IND = 'Y' )) USERID
                        ON PERS.PERS_ID = USERID.ELID_PERS_ID
    WHERE  ( PERS.PERS_STAT <> 'INACTIVE' )
           AND ( PERS.PERS_STAT <> 'UNKNOWN' )
    ORDER  BY PERS.PERS_LAST_NM

  2. #2
    dsk96m is offline VB.NET Forum Genius
    .NET Framework
    .NET 4.0
    Join Date
    Jan 2013
    Posts
    173
    Reputation
    58
    Can anyone help, I still cant figure this out.

  3. #3
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,815
    Reputation
    1707
    It's hard to say where the issue might be in what is a fairly complex query. Nothing jumps out on first perusal. What you should ALWAYS do in such cases is start with the simplest query you can and then build it up, piece by piece, to your final query and then you can see exactly what changed when it broke. Even if you are still unable to see what's wrong, at least you can provide us with before and after copies of the code and then we don't have to trawl through everything with no idea what we're looking for.

  4. #4
    dsk96m is offline VB.NET Forum Genius
    .NET Framework
    .NET 4.0
    Join Date
    Jan 2013
    Posts
    173
    Reputation
    58
    The query works just fine when run other places, just not when using the tableadapter wizard in visual studio.

  5. #5
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,815
    Reputation
    1707
    Quote Originally Posted by dsk96m View Post
    The query works just fine when run other places, just not when using the tableadapter wizard in visual studio.
    OK. Now do what I said in my previous post.

  6. #6
    dsk96m is offline VB.NET Forum Genius
    .NET Framework
    .NET 4.0
    Join Date
    Jan 2013
    Posts
    173
    Reputation
    58
    its when i add in either left join

  7. #7
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,815
    Reputation
    1707
    If you're going to make no effort here then why should we? I said in an earlier post:
    Even if you are still unable to see what's wrong, at least you can provide us with before and after copies of the code and then we don't have to trawl through everything with no idea what we're looking for.
    I'm not seeing where you've done that. Post the simplest before and after code that you can that demonstrates the issue.

  8. #8
    dsk96m is offline VB.NET Forum Genius
    .NET Framework
    .NET 4.0
    Join Date
    Jan 2013
    Posts
    173
    Reputation
    58
    Didnt know stating that adding the left joins wasnt enough. So this works, when i remove the left joins and the last two items from the select. Once I add in either left join, i get the error.

    It isnt a problem with the SQL, it is an issue with how Visual Studio is trying to parse it, I believe.

    Code:
    SELECT PERS.PERS_ID,
           PERS.PERS_LAST_NM,
           PERS.PERS_FIRST_NM,
           PERS.PERS_MIDDLE_INIT,
           PERS.PERS_MIDDLE_INIT,
           PERS.PERS_PREFERRED_NM,
           PERS.PERS_EMPLOYEE_NO,
           PERS.PERS_EMPLOYEE_TYP,
           PERS.PERS_STAT,
           PERS.PERS_LENT_CD                        AS PERS_ENTITY,
           PERS.PERS_TYP,
           PERS.PERS_MSTP_ID,
           PERS.PERS_BLDG_ID,
           PERS.PERS_TITLE_INIT,
           PERS.PERS_PERS_ID,
           PERS.PERS_COCC_ID,
           PERS.PERS_IORG_CD,
           PERS.PERS_CORPORATE_ID,
           CONCAT('(', CONCAT(PERS.PERS_PHONE_AREA_CD, CONCAT(')',CONCAT(PERS.PERS_PHONE_EXCH_NO,CONCAT('-',PERS.PERS_PHONE_EXTN_NO))))) AS PERS_PHONE_NO
    FROM   CDAS.TDWHPERS PERS
    WHERE  ( PERS.PERS_STAT <> 'INACTIVE' )
           AND ( PERS.PERS_STAT <> 'UNKNOWN' )
    ORDER  BY PERS.PERS_LAST_NM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •