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.
Code:
CREATE PROCEDURE display_comment
(
@topic_id varchar(10) ,
@comment_count integer output
)
AS
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
GO
And here is my asp code:
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")
oRS.MoveNext
Loop
End If
set oRS = Nothing
The loop works fine and returns the record set if I take out the condition of:
Quote:
|
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!
dBforums
>
stored procedure output parameters are empty from asp page
08-07-08, 07:20
stored procedure output parameters are empty from asp page
Linear Mode
