Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: object closed error

    I'm getting object closed when returning a recordset from a stored procedure. I've tested the select statement in Query Analyzer and a value is getting returned. So, I'm not sure if my code for my stored procedure is incorrect?

    Here is the code for the procedure:
    Code:
    CREATE Procedure dbo.GetRepEmailByZip
    
    @sessionid varchar(50),
    @zip varchar(5)
    
    AS
    
    Begin Transaction
        
        INSERT INTO dbo.SupportRequests(firstname,lastname,schoolname,address,city,state,zip,phone,email,currentcustomer,implementationtype,producttype,comment)
    	(SELECT FirstName,LastName,SchoolName,Address,City,State,Zip,Phone,Email,CurrentCustomer,ImplementationType,ProductType,Comment
    	FROM dbo.Temp_ContactInfo
    	WHERE sessionid = @sessionid)
        
    	
        --If Transacation fails, stop execution of procedure, return error code and Rollback Transaction
    
        IF @@ERROR<>0 OR @@RowCount = 0 
        BEGIN 
            ROLLBACK TRANSACTION
            --return value
    	RETURN 1
        END
        
        --If Transaction succeeds, commit transaction, continue and process the select statement
        COMMIT TRANSACTION 
    
        SELECT r.email
        FROM PostalCodes p
        INNER JOIN  TerritoryList z ON  p.ZipID = z.ZipID
        INNER JOIN RepList r ON r.RepID = z.RepID
        WHERE p.ZipCode = @zip
    GO
    This is the code I'm calling to execute the procedure and return the recordset:

    Code:
    set GetRepEmail = Server.CreateObject("ADODB.Command")
    With GetRepEmail
    .ActiveConnection = MM_DBConn_STRING
    .CommandText = "dbo.GetRepEmailByZip"
    .CommandType = 4
    .CommandTimeout = 0
    .Prepared = true
    .Parameters.Append .CreateParameter("@RETURN_VALUE", 3, 4)
    .Parameters.Append .CreateParameter("@sessionid", 200, 1,50,usrid)
    .Parameters.Append .CreateParameter("@zip", 200, 1,5,zip)
    set RepEmail = .Execute()
    End With
    			
    Dim x, y
    x = RepEmail.RecordCount
    y = RepEmail.State
    Response.Write(x)
    Response.Write("<br>")
    Response.Write(y)
    'Response.Write(RepEmail("email"))
    Response.End()
    I'm getting "Operation is not allowed when the object is closed", which is occuring on the following line:

    x = RepEmail.RecordCount

    I'm trying to determine if this is problem within my procedure or in the application code.

    Thanks in advance for any help.

  2. #2
    Join Date
    Jun 2004
    Posts
    50
    This may sound newbish, but does a command object have a recordcount property?
    Monk
    The person who confesses ignorance shows it once; the person who conceals it shows it many times.

  3. #3
    Join Date
    Jan 2004
    Posts
    124
    Thanks for replying to my post.

    I might be mistaken, but I'm creating a recordset from the command object:

    set RepEmail = .Execute()

    So, I would think RepEmail would have a recordcount property? Is this not correct?

  4. #4
    Join Date
    Jun 2004
    Posts
    50
    Maybe you need to open the recordset (rs)? I think it's definately the app code. Does the rs object know about the connection and such? It seems like it's missing some property values. hth
    Monk
    The person who confesses ignorance shows it once; the person who conceals it shows it many times.

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Try adding SET NOCOUNT ON to the beginning of your PROC. This will suppress DONE_IN_PROC messages that are returned as empty closed recordsets.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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