Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Posts
    6

    Unanswered: Insert Syntax Error

    In SQL 7.0 SP3 , I am receiving this message....

    Server: Msg 170, Level 15, State 1, Line 17
    Line 17: Incorrect syntax near ')'.

    When I try to execute this code from SQL Query Analyzer...

    DECLARE @DPPNumberCursor INT
    DECLARE DPPNumberCursor Cursor for Select PPAP_ID
    from ppap
    where ppap_cancel <> "1" and
    ppap_close <> "1" and
    projectonhold <> "1"

    OPEN DPPNumberCursor

    Fetch Next From DPPNumberCursor

    INTO @dppnumbercursor

    While @@Fetch_Status = 0
    Begin
    INSERT INTO APQPSubformTable (apqpsub_id)
    Values (@dppnumbercursor)

    Bsically, I want to insert the number held in @dppnumbercursor in the APQPSub_id field.

    Any help would be appreciated.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    How about END at the end?

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and DEALLOCATE/CLOSE would be very appropriate

  4. #4
    Join Date
    Jul 2003
    Posts
    6
    OK, I'm an idiot....can you tell I'm a newbie??? Thanks for not tearing me up too bad :-)

    Thanks to both for your help.....

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You're welcome...from both of us

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nobody is going to mention the double quotes?

    Or the fact that s/he doesn't need a cursor at all?

    Code:
      INSERT INTO APQPSubformTable(apqpsub_id)
           SELECT PPAP_ID
    	 FROM ppap 
    	WHERE ppap_cancel   <> '1' 
    	  AND ppap_close    <> '1' 
    	  AND projectonhold <> '1'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I was thinking about it, but then decided not to...when he/she thanked "both" of us

  8. #8
    Join Date
    Jul 2003
    Posts
    6
    It's been one of those days:-)

    Just for my own edification, what about the double quotes?

    The reason I used the cursor was because I needed to add two records into the APQPSubformtable for each open project in the ppap table. Here is the completed working code...

    DECLARE @DPPNumberCursor INT
    DECLARE DPPNumberCursor Cursor for Select PPAP_ID
    from ppap
    where ppap_cancel <> "1" and
    ppap_close <> "1" and
    projectonhold <> "1"

    OPEN DPPNumberCursor

    Fetch Next From DPPNumberCursor

    INTO @dppnumbercursor

    While @@Fetch_Status = 0
    Begin
    INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
    Values (@dppnumbercursor,"31","Start Of Production (SOP) [F4]","OP","0","G")

    INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
    Values (@dppnumbercursor,"32","Stable Production (Review) [F5]","OP","0","G")

    FETCH NEXT from DPPNumberCursor

    INTO @DPPNumberCursor

    End

    Close DPPNumberCursor

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Double-quotes are harmless as long as you know what your connection settings are when you compile your procedure/function/trigger/view (should be SET QUOTED_IDENTIFIER OFF).

  10. #10
    Join Date
    May 2003
    Posts
    26
    Just for my own edification, what about the double quotes?
    It is a better practice to use single quotes to delimit strings. Double quotes can serve two purposes: delimit a string and delimit an identifyer (ie table name or column name). There is a QUOTED_IDENTIFYER property the determines which. Set to ON, everything within double quotes is supposed to be considered an Identifyer.

    I've read in BOL that SLQ Server is 'not very rigorous' in enforcing this rule, depending on the length of the string in question. Small strings such as the ones you are using seem to work OK, but you'd be better off delimiting the strings correctly than relying on SQL Analyzer to interpret what it is you really mean.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't Play with Settings....

    Code:
    INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
         SELECT @dppnumbercursor,'31','Start Of Production (SOP) [F4]','OP','0','G'
      UNION ALL
         SELECT @dppnumbercursor,'32','Stable Production (Review) [F5]','OP','0','G'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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