Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007

    Red face Unanswered: stored procedure output parameters are empty from asp page

    I've been up all night with this one and I can't figure it out.

    I'm trying to return an output parameter from a stored procedure to an ASP page.
    The stored procedure works fine in query analyzer, it returns the record set and the output parameter.

    When I try to access the output parameter from my web page I get nothing, (actually Empty)

    Here is my stored procedure.

    CREATE PROCEDURE display_comment 
    	@topic_id varchar(10)	,
    	@comment_count	integer output
    	SELECT author, comment, date_submitted
    	FROM  	comments
    	WHERE topic_id = @topic_id AND flag = 'N' AND publish_ind = 'Y'
    	ORDER BY date_submitted
    	SELECT @comment_count = @@ROWCOUNT	
    RETURN 0
    And here is my asp code:
    set oConn = Server.CreateObject("ADODB.Connection") 
    connection = "Provider=SQLOLEDB; Data Source=; Database=; User ID=; Password="
    oConn.Open connection
    set command = server.CreateObject("ADODB.Command")
    command.ActiveConnection = connection
    command.CommandText = "display_comment"
    command.CommandType = adCmdStoredProc
    command.Parameters.Append  command.createParameter("@topic_id",adInteger,adParamInput, ,num)
    command.Parameters.Append command.createParameter("@comment_count", adInteger, adParamOutput)
    Set oRs = command.Execute
     comment_count= command.Parameters("@comment_count")
    Response.write "Total Comments :" & comment_count
    If comment_count > 2 Then 
      Do While Not oRs.EOF
        Response.Write oRS.Fields("author") & "<br>"
        Response.Write oRS.Fields("comment")
    End If
    set oRS = Nothing
    The loop works fine and returns the record set if I take out the condition of:
    If comment_count > 2 Then
    I having this problem with all my stored procedures, so I know it's the way I'm handling the ASP and stored procedure that is the problem.

    I realize there are other ways to return the total rows and that in this situation it would be easier to just use a return value and adParamReturnValue, but that doesn't work either and besides I would like to know what I am doing wrong.

    Please help!
    Last edited by aish1108; 08-07-08 at 07:27. Reason: I need total count before loop

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 10
    You shouldn't need to return the recordcount like that: ADO can do it for you
    Home | Blog

  3. #3
    Join Date
    Aug 2007
    Thank You!

    But why isn't my stored procedure returning a value in the output parameter?

    Why is the parameter @comment_count EMPTY?

    I have other stored procedures which do require output parameters and I can't get a value from them.

Posting Permissions

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