Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    3

    Red face Unanswered: SQL/XML/ASP Error Item cannot be found in the collection corresponding to the request

    Hi all,
    I'm going completely nuts. The following code works fine on the test server... but causes the following error on the production server. I am trying to read an XML string created by a stored procedure into a stream object in ASP. I am connecting via a UDL that DOES use the SQLOLEDB db provider:

    ADODB.Command error '800a0cc1'

    Item cannot be found in the collection corresponding to the requested name or ordinal.

    referencing this line:
    mycmd.properties("Output Stream").Value = myStream


    If SOMEONE could help I would appreciate it so very, very much!!! Thank you!


    -----ASP CODE----
    dim myConn, myCmd, myStream
    dim param1, param2, param3, param4, param5, param6, param7, param8, param9
    dim strConn

    strConn= "FILE NAME=C:\WINNT\SYSTEM32\ProvLookup.udl"
    set myConn=server.createObject("Adodb.connection")
    set myCmd = server.createObject("Adodb.command")
    set myStream = server.createObject("Adodb.stream")
    myConn.open = strConn
    myCmd.activeConnection = myConn
    myCmd.CommandType = 4
    myCmd.CommandText = "GetWebProviders"

    set param1 = myCmd.createParameter("RefLon", 14, 1)
    mycmd.parameters.append param1
    set param2 = myCmd.createParameter("RefLat", 14, 1)
    mycmd.parameters.append param2
    set param3 = myCmd.createParameter("SpecCode", 200, 1, 4)
    mycmd.parameters.append param3
    set param4 = myCmd.createParameter("ProvName", 200, 1, 70)
    mycmd.parameters.append param4
    set param5 = mycmd.createParameter("NetworkIndex", 2, 1)
    mycmd.parameters.append param5
    set param6 = mycmd.createParameter("RowStart", 3, 1)
    mycmd.parameters.append param6
    set param7 = mycmd.createParameter("RowEnd", 3, 1)
    mycmd.parameters.append param7
    set param8 = mycmd.createParameter("totalRecords", 3, 2)
    mycmd.parameters.append param8
    set param9 = mycmd.createParameter("SessionID", 3, 2)
    mycmd.parameters.append param9



    mycmd("RefLon").precision = 11
    mycmd("RefLon").numericScale = 6
    mycmd("RefLon").value = lon

    mycmd("RefLat").precision = 11
    mycmd("RefLat").numericScale = 6
    mycmd("RefLat").value = lat
    mycmd("SpecCode").value = spec
    mycmd("ProvName").value = FS
    mycmd("NetworkIndex").value = GetCorvelNetwork(FC)
    mycmd("RowStart").value = startrec
    mycmd("RowEnd").value = 5


    dim errStr, errNum
    mystream.open
    mycmd.properties("Output Stream").Value = myStream

    mycmd.Execute ,,1024


    ---Stored Procedure--- (Note, real stored proc is way too long to post... I have cut/pasted the important parts)


    CREATE PROCEDURE [DBO].[GetWebProviders]
    (
    @RefLong Decimal (12,7),
    @RefLat Decimal (12,7),
    @SpecCode varChar(4) = NULL,
    @ProvName Varchar(70) = NULL,
    @NetWorkIndex SmallInt = 1,
    @RowStart Int = 1,
    @RowCount Int = 5,
    @totalRecords Int Output,
    @SessionID Int Output

    )

    AS
    --declarations, processing, etc

    SELECT @SessionID = SessionID
    FROM WebSessionLocations
    WHERE Longitude = @RefLong
    AND Latitude = @RefLat
    AND ISNull(@ProvName, '') = ProvName
    AND IsNull(@SpecCode, '') = SpecCode
    AND FlagIndex = @NetworkIndex

    IF (@SessionID IS NOT NULL)
    BEGIN


    select @totalRecords = count (*) from
    (SELECT RowNumber, ProvName, Degree, Address1, Address2, City, State, Zip, ZipPlus4, Phone, Distance, Longitude, Latitude, Specialty, SpecCode, ProvID
    FROM WebSessionProLookup
    WHERE RowNumber >= @RowStart
    AND SessionID = @SessionID) t

    SELECT RowNumber, ProvName, Degree, Address1, Address2, City, State, Zip, ZipPlus4, Phone, Distance, Longitude, Latitude, Specialty, SpecCode, ProvID
    FROM WebSessionProLookup
    WHERE RowNumber >= @RowStart
    AND RowNumber < @RowStart + @RowCount
    AND SessionID = @SessionID
    ORDER BY SessionID, RowNumber
    for xml auto
    END
    ELSE --Session ID IS NULL
    BEGIN
    --processing etc.

    select @totalRecords=count(*) from #websessionprolookup

    SELECT RowNumber, ProvName, Degree, Address1, Address2, City, State, Zip, ZipPlus4, Phone, Distance, Longitude, Latitude, Specialty, SpecCode, ProvID
    FROM WebSessionProLookup with (nolock)
    WHERE RowNumber >= @RowStart
    AND RowNumber < @RowStart + @Rowcount
    AND SessionID = @SessionID
    ORDER BY SessionID, RowNumber
    for xml auto

    END




    GO

  2. #2
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    the error means that a field you are asking for from the database doesn't exist...check to make sure all the fields exist and check your spelling.

  3. #3
    Join Date
    Mar 2003
    Posts
    3
    Originally posted by Memnoch1207
    the error means that a field you are asking for from the database doesn't exist...check to make sure all the fields exist and check your spelling.
    Nope, that's not the problem. I've checked and re-checked the fields... and, as I said, the code works in the test environment perfectly. But the production environment causes the error. The one difference between environments is that Visual Studio is installed in the test environment whereas it does not exist in the production environment. The code is in straight ASP (no .NET) though and MDAC, IIS, SQL Server etc. are all the same version in both environments. Any other ideas?

  4. #4
    Join Date
    Nov 2009
    Posts
    3
    should you not use square brackets around the "[Output Stream]"

    just a thought

Posting Permissions

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