stored procedure/asp/ado issue ... ?

Discussion in 'OT Technology' started by johnnywallywallace, Jan 27, 2004.

  1. when executing the following stored procedure:

    Code:
    CREATE PROCEDURE [dbo].[isp_NewStory]
    @dtPublish datetime, @createdBy varchar(500)
    AS
    
    INSERT INTO tStory (dtPublish, createdBy) VALUES (@dtPublish, @createdBy)
    
    SELECT @@IDENTITY AS 'idStory'
    RETURN
    GO
    
    as part of the following ASP script:

    Code:
    	dtEditionDB = yrNow & moNow & dayNow
    	Set objParameter1 = comm.CreateParameter
    	objParameter1.Type = 133
    	objParameter1.Direction = 1
    	objParameter1.Value = CDate(dtEditionDB)
    	Set objParameter2 = comm.CreateParameter ("createdBy", 200, 1, 500, CStr(loggedUser))
    	With comm
    		.Parameters.Append objParameter1
    		.Parameters.Append objParameter2
    
            .CommandText = "isp_NewStory"
            .CommandType = &H0004
    		.ActiveConnection = conn
        End With
    	
    	Set objParameter1 = Nothing
    	Set objParameter2 = Nothing
    
    	Set rs = comm.Execute
    	If Not rs.EOF Then
    		idStory = rs.Collect(0)
    	End If
    
    	rs.Close
    
    I receive the following error:
    ADODB.Recordset error '800a0e78'

    Operation is not allowed when the object is closed.

    /ipt/display.asp, line 31

    line 31:
    If Not rs.EOF Then

    any thoughts?
     
  2. fixed:

    ASP:
    Code:
    	dtEditionDB = yrNow & moNow & dayNow
    	Set objParam1 = comm.CreateParameter
    	objParam1.Type = adDBDate
    	objParam1.Direction = 1
    	objParam1.Value = CDate(dtEditionDB)
    	Set objParam2 = comm.CreateParameter ("createdBy", 200, 1, 500, CStr(loggedUser))
    	Set objParam3 = comm.CreateParameter ("idStory", adDecimal, adParamReturnValue)
    	objParam3.Precision = 18
    	objParam3.NumericScale = 0
    	With comm
    		.Parameters.Append objParam1
    		.Parameters.Append objParam2
    		.Parameters.Append objParam3
    
    		.CommandText = "isp_NewStory"
    		.CommandType = adCmdStoredProc
    		.ActiveConnection = conn
    	End With
    	
    	comm.Execute
    	idStory= comm.Parameters ("idStory").Value
    
    
    	Set objParam1 = Nothing
    	Set objParam2 = Nothing
    	Set objParam3 = Nothing
    
    SP:
    Code:
    CREATE PROCEDURE [dbo].[isp_NewStory]
    @dtPublish datetime, @createdBy varchar(500), @idStory decimal (18,0) OUTPUT
    AS
    
    INSERT INTO tStory (dtPublish, createdBy) VALUES (@dtPublish, @createdBy)
    set @idStory = @@IDENTITY
    RETURN
    
    GO
    
     
  3. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    i really didn't look at your asp code, but i've seen that error a million and a half times, and what you usually have to do it "SET NOCOUNT ON" at the top of your procedure....

    what happens is that when you execute your insert, it will actually send 2 recordsets back to your ado code with the first one: "rows affected: 5" or whatever, and the second will actually be your data that you're expecting. so by setting NOCOUNT to ON, you don't get all the insert/update count recordsets back with your actual data...
     
  4. SET NOCOUNT ON is on by default I believe, but thanks :)

    I messed up in a million ways. I was up 'till 1am, and up again at 7am, rebuilding a bunch of stored procs. looking good now tho :) informative default values, cascaded deletes, stored procs for just about every DB query ... :cool:
     
  5. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    unless you specify in your default session properties, NOCOUNT is off by default.
     
  6. eh, either way, without setting it in script, it's not coming up in my results ... not in query analyzer, nor anywhere else so far as I can see. I think I'm in the clear :cool:
     

Share This Page