Getting the hang of 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

Getting the hang of ADO

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-07 : 15:08:53

I'm having trouble getting OUTPUT PARAMETERS and RETURN VALUES when I use ASP/ADO and return a recordset.

If there's some obvious mistake with the following code, please let me know.

Thanks,

Sam

on error resume next
DIM cmdAdminLogin
SET cmdAdminLogin = Server.CreateObject("ADODB.Command")
WITH cmdAdminLogin
.ActiveConnection = application("DBaddr")
.CommandText = "dbo.AD_AdminLogin"
.CommandType = adcmdstoredproc
.Parameters.Append .CreateParameter ("RETURN_VALUE", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter ("@Username", adChar, adParamInput, 100, Username)
.Parameters.Append .CreateParameter ("@Password", adChar, adParamInput, 100, Password)
SET rsAdminLogin = .Execute
ReturnValue = .Parameters("RETURN_VALUE")
End With

WITH rsAdminLogin

If CheckADOErrors(cmdAdminLogin.ActiveConnection) Then
' Nothing to do
End If

If CheckADOErrors(rsAdminLogin.ActiveConnection) Then
' Nothing to do
End If
' Play with recordset variables here...

rsAdminLogin.Close
SET rsAdminLogin = Nothing
SET cmdAdminLogin = Nothing
End With

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-11-07 : 15:42:31

Not sure about return values, but here's what you need to change in your code to do OUTPUT params

1. In the Stored Proc, declare the variable as OUTPUT.


CREATE PROCEDURE [dbo].[p_StoredProcName](
@Result INT OUTPUT
@UserID INT
)
AS
  SELECT @Result = myfield
  WHERE username = @UserName
  AND password = @password

2. Change your ADO code to this: (No need for a recordset, thus it's faster.


WITH cmdAdminLogin 
.ActiveConnection = application("DBaddr") 
.CommandText = "dbo.AD_AdminLogin" 
.CommandType = adcmdstoredproc 
.Parameters.Append .CreateParameter ("@Result", adInteger, adParamOutput) 
.Parameters.Append .CreateParameter ("@Username", adChar, adParamInput, 100, Username) 
.Parameters.Append .CreateParameter ("@Password", adChar, adParamInput, 100, Password) 
.Execute 
ReturnValue = .Parameters.Item("@Result").Value
End With

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources