Output Parameter fails in ADO - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Output Parameter fails in ADO

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-12 : 14:51:20

I can't seem to get an Output parameter back when I use a stored procedure that returns a recordset in ADO.

No problem for stored procedures that do not return recordsets.

Sam

This works...
WITH cmdAdminDelete
.ActiveConnection = application("DBaddr")
.CommandText = "dbo.AD_AdminIDDelete"
.CommandType = adcmdstoredproc
.Parameters.Append .CreateParameter ("RETURN_VALUE", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter ("@CallerAdminID", adInteger, adParamInput, , Session("AdminID"))
.Parameters.Append .CreateParameter ("@AdminID", adInteger, adParamInput, , AdminIDinput)
.Parameters.Append .CreateParameter ("@OutputMessage", adChar, adParamOutput, 255)
.Execute ,,adExecuteNoRecords
SQLResult = .Parameters ("@OutputMessage")
ReturnValue = .Parameters("RETURN_VALUE")
End With

AND THIS DOESN'T WORK
WITH cmdGetEmailRecordset
.ActiveConnection = application("DBaddr")
.CommandText = "dbo.EM_GetEmailRecordset"
.CommandType = adcmdstoredproc
.Parameters.Append .CreateParameter ("RETURN_VALUE", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter ("@CallerAdminID", adInteger, adParamInput, , session("AdminID"))
.Parameters.Append .CreateParameter ("@GroupQuery", adChar, adParamInput, 4000, "testing")
.Parameters.Append .CreateParameter ("@OutputMessage", adChar, adParamOutput, 255)
SET rsEmailRecordset = .Execute
OutputMessage = .Parameters("@OutputMessage")
RESPONSE.WRITE br & "Outputmessage = " & Outputmessage & br
ReturnValue = .Parameters("RETURN_VALUE")
End With

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-12 : 15:34:27

Found this in BOL.

The recordset must be read and CLOSED before retrieving the OUTPUT or RETURN parameters.

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-12 : 19:04:49

The output parameter is one of the last things returned from the server.
If you make recordsets disconnected client side then you shouldn't have a problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources