Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2008
    Posts
    36

    Thumbs down Unanswered: Subqueries are not allowed in this context

    Hi ,all
    I am writing a asp page in which i am fetchign data from one table and inserting to other

    ConnString = "Driver=SQL Server;server=****;uid=sa;pwd=admin;initial catalog=****"
    set conn=server.createobject("adodb.connection")

    conn.Open ConnString
    Set rs=conn.execute
    ("insert into tblContainers(Container_ID,Container_No,Seal_No,Ca rrier,Entered_By,Entered_Date,Origin,Arrival_Date, Arrival_Time) values ((SELECT CONVERT(VARCHAR(8), GETDATE(), 112)+'-'+substring(com.container_no,1,11)+'-'+substring(cast(NEWID() as varchar(40)),1,3) as Container_ID ,substring(com.container_no,1,11) as Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' as Entered_By,GETDATE() as Entered_Date,cotr.Origin,select convert(varchar,com.Arrival_Date,101) as Arrival_Date,select convert(varchar,com.Arrival_Date,109) as Arrival_Time from DehartGroup.dbo.Container_Master as com,DehartGroup.dbo.Container_Tracing as cotrwhere com.Container_No=cotr.Container_No and com.Seal_No=cotr.Seal_No))
    ")

    conn.close
    set conn =Nothing

    its showing the error

    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Subqueries are not allowed in this context. Only scalar expressions are allowed.

    please give some inputs
    Thanks and regards
    Ravi

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    Code:
    conn.Open ConnString
    
    Set rs=conn.execute
    ("
    INSERT		tblContainers
    		(
    			Container_ID,
    			Container_No,
    			Seal_No,
    			Carrier,
    			Entered_By,
    			Entered_Date,
    			Origin,
    			Arrival_Date,
    			Arrival_Time
    		)
    SELECT		CONVERT(VARCHAR(8), GETDATE(), 112) + '-' + SUBSTRING(com.container_no, 1, 11) + '-' + SUBSTRING(CAST(NEWID() AS VARCHAR(40)), 1, 3) AS Container_ID,
    		SUBSTRING(com.container_no, 1, 11) AS Container_No,
    		com.Seal_No,
    		com.Carrier,
    		'IGuardTracki ng' AS Entered_By,
    		GETDATE() AS Entered_Date,
    		cotr.Origin,
    		CONVERT(VARCHAR(10), com.Arrival_Date, 101) AS Arrival_Date,
    		CONVERT(VARCHAR(10), com.Arrival_Date,109) AS Arrival_Time
    FROM		DehartGroup.dbo.Container_Master AS com
    INNER JOIN	DehartGroup.dbo.Container_Tracing AS cotr ON cotr.Container_No = com.Container_No
    			AND cotr.Seal_No = com.Seal_No
    ")

  3. #3
    Join Date
    Dec 2008
    Posts
    36
    Hi thanks for reply...
    but its now throwing the error

    # Error Type:
    Microsoft VBScript compilation (0x800A0400)
    Expected statement
    /IguardIntegration/ExportData.asp, line 17
    ("

    please do help...

    Set rs=conn.execute
    ("

    INSERT into tblContainers
    (
    Container_ID,
    Container_No,
    Seal_No,
    Carrier,
    Entered_By,
    Entered_Date,
    Origin,
    Arrival_Date,
    Arrival_Time
    )
    SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + '-' + SUBSTRING(com.container_no, 1, 11) + '-' + SUBSTRING(CAST(NEWID() AS VARCHAR(40)), 1, 3) AS Container_ID,SUBSTRING(com.container_no, 1, 11) AS Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' AS Entered_By,
    GETDATE() AS Entered_Date,
    cotr.Origin,
    CONVERT(VARCHAR(10), com.Arrival_Date, 101) AS Arrival_Date,
    CONVERT(VARCHAR(10), com.Arrival_Date,109) AS Arrival_Time
    FROM DehartGroup.dbo.Container_Master AS com
    INNER JOIN DehartGroup.dbo.Container_Tracing AS cotr ON cotr.Container_No = com.Container_No
    AND cotr.Seal_No = com.Seal_No
    ")

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you run the statement directly on the database and confirm whether it's a syntax issue with the SQL statement or an ASP related error?
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2008
    Posts
    36
    Hi Thanks for the info..
    Its working now...

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What was missing?
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    he forgot to turn on his laptop
    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
  •