Stored procedure Output Parameter headache

Hey all, and apologies if this is in the wrong place
I have the following code chunk for a page which accesses an SQL Server
2000 stored procedure that takes an SSN and a Last Name and checks our
database for a match.
The stored procedure works fine in Query analyzer, returning the
parameter I am looking for (a text message of success or varying
failures) but if I try to return either output parameter in my ASP page
I only get "1" if there was a match or "0" if there wasn't.
Any ideas?

~~~Start ASP Code~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim cnDWE
Dim cmdDWExport
Dim txtSSN
Dim txtLName
Dim str3

' Get the entered values
txtSSN = Request.Form("SSN1")
txtLName = Request.Form("LName")

' Initiate the command object
Set cnDWE = Server.CreateObject("ADODB.Connection")
Set cmdDWExport = Server.CreateObject("ADODB.Command")

cnDWE.Open strDWCnn

With cmdDWExport
.ActiveConnection = cnDWE
.CommandText = "spCheckDWdCResume"
.CommandType = adCmdStoredProc
' Add the parameters
.Parameters.Append .CreateParameter("@SSN", adVarChar, adParamInput, 9,
txtSSN)
.Parameters.Append .CreateParameter("@LastName", adVarChar,
adParamInput, 25, txtLName)
.Parameters.Append .CreateParameter("@RetMsg", adVarChar,
adParamOutput, 100)
.Parameters.Append .CreateParameter("@RetCode", adInteger, adParamOutput)

'Execute the command
.execute, , adExecuteNoRecords
str3 = .Parameters("@RetCode").Value
End With

Response.Write str3
Set cmdDWExport = Nothing
~~~End ASP Code ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If I run the following against my stored procedure in Query Analyzer, I
get my return message and Nothing Else

~~~Start QA Input~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
declare @RetMsg varchar(100)
exec spCheckDWdCResume @SSN = '000000025', @LastName = 'Loposky',
@RetMsg = @RetMsg output
print @RetMsg
~~~End QA Input~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Super-Thanks in advance if anyone can help me
MP

Re: Stored procedure Output Parameter headache

Monkey Pi wrote:
> Hey all, and apologies if this is in the wrong place
> I have the following code chunk for a page which accesses an SQL
> Server 2000 stored procedure that takes an SSN and a Last Name and
> checks our database for a match.
> The stored procedure works fine in Query analyzer, returning the
> parameter I am looking for (a text message of success or varying
> failures) but if I try to return either output parameter in my ASP
> page I only get "1" if there was a match or "0" if there wasn't.
> Any ideas?

Hard to say without seeing the stored procedure. My first thought is
that you have failed to include a "SET NOCOUNT ON" statement in your
procedure to prevent the informational "x records were effected"
messages that get returned as empty recordsets.

If that's not the case, could you create a repro procedure that produces
the symptoms you describe but does not depend on any of the tables in
your database so we can try it for ourselves?

Oh wait! i just spotted something:

> ' Add the parameters

The first parameter that should be added is the return parameter:
..Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
adParamReturnValue)


> .Parameters.Append .CreateParameter("@SSN", adVarChar, adParamInput,
> 9, txtSSN)
> .Parameters.Append .CreateParameter("@LastName", adVarChar,
> adParamInput, 25, txtLName)
> .Parameters.Append .CreateParameter("@RetMsg", adVarChar,
> adParamOutput, 100)
> .Parameters.Append .CreateParameter("@RetCode", adInteger,
> adParamOutput)


This is strange. You've created 4 parameters, but in the QA input below,
you only supply 3 ...
Are you thinking that @RetCode is the value returned by the RETURN
statement in your proc? If so, read this:
http://www.google.com/groups?hl=en&lr=&c2coff=1&selm=OgYK94S gEHA.4092%40TK2MSFTNGP10.phx.gbl

You really should show us the CREATE PROCEDURE statement - just the part
where the parameters are declared: the body of the procedure is useless.


> If I run the following against my stored procedure in Query Analyzer,
> I get my return message and Nothing Else
>
> ~~~Start QA Input~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> declare @RetMsg varchar(100)
> exec spCheckDWdCResume @SSN = '000000025', @LastName = 'Loposky',
> @RetMsg = @RetMsg output
> print @RetMsg
> ~~~End QA Input~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>

Hmm - no @RetCode parameter supplied ...
I suspect you need to get rid of it in the Parameters collection and
change

str3 = .Parameters("@RetCode").Value
to
str3 = .Parameters("@RetMsg").Value

You may be interested in trying out my stored procedure code generator
available at
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Stored procedure Output Parameter headache

Bob Barrows [MVP] wrote:
> Monkey Pi wrote:
-Large Snip-
>
>
> Hard to say without seeing the stored procedure. My first thought is
> that you have failed to include a "SET NOCOUNT ON" statement in your
> procedure to prevent the informational "x records were effected"
> messages that get returned as empty recordsets.
>
> If that's not the case, could you create a repro procedure that produces
> the symptoms you describe but does not depend on any of the tables in
> your database so we can try it for ourselves?
>
> Oh wait! i just spotted something:
>
>
>>' Add the parameters
>
>
> The first parameter that should be added is the return parameter:
> .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
> adParamReturnValue)
>

Do I need this if I am only looking for the output parameter and not any
sort of Return Value?

>
>>.Parameters.Append .CreateParameter("@SSN", adVarChar, adParamInput,
>>9, txtSSN)
>>.Parameters.Append .CreateParameter("@LastName", adVarChar,
>>adParamInput, 25, txtLName)
>>.Parameters.Append .CreateParameter("@RetMsg", adVarChar,
>>adParamOutput, 100)
>>.Parameters.Append .CreateParameter("@RetCode", adInteger,
>>adParamOutput)
>
>
>
> This is strange. You've created 4 parameters, but in the QA input below,
> you only supply 3 ...
> Are you thinking that @RetCode is the value returned by the RETURN
> statement in your proc? If so, read this:
> http://www.google.com/groups?hl=en&lr=&c2coff=1&selm=OgYK94S gEHA.4092%40TK2MSFTNGP10.phx.gbl

I actually have 5 parameters as you'll see in my Proc (which will be
below) I only use 3 in my ASP page and the 4th was added to see if
switching to a different output parameter would yield different results.

> You really should show us the CREATE PROCEDURE statement - just the part
> where the parameters are declared: the body of the procedure is useless.
>
>
>
>>If I run the following against my stored procedure in Query Analyzer,
>>I get my return message and Nothing Else
>>
>>~~~Start QA Input~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>declare @RetMsg varchar(100)
>>exec spCheckDWdCResume @SSN = '000000025', @LastName = 'Loposky',
>>@RetMsg = @RetMsg output
>>print @RetMsg
>>~~~End QA Input~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>
>
> Hmm - no @RetCode parameter supplied ...
> I suspect you need to get rid of it in the Parameters collection and
> change
>
> str3 = .Parameters("@RetCode").Value
> to
> str3 = .Parameters("@RetMsg").Value
>

Yes, that's right and what I've been testing up to the end. @RetMsg is
what I'm looking for as a result of running my Proc.
I accidentally left it in this stage before cutting and pasting...Sorry.
Here's my Stored Procedure.

~~~Start Proc~~~~~~~~~~~~~~~~~~~~
-- Check for a match between SSN and Last Name entered
CREATE PROCEDURE [dbo].[spCheckDWdCResume]
@SSN varchar(9),
@LastName varchar(25),
@Verified int = NULL OUTPUT,
@RetCode int = NULL OUTPUT,
@RetMsg varchar(100) = NULL OUTPUT

AS

SET NOCOUNT ON

/* Validate input parameters.
Assume success. */
SELECT @RetCode = 1, @RetMsg = ''

-- no SSN
IF @SSN IS NULL
SELECT @RetCode = 0,
@RetMsg = @RetMsg +
'An SSN required.' + CHAR(13) + CHAR(10)

-- SSN isn't complete
IF Len(@SSN) < 9
SELECT @RetCode = 0,
@RetMsg = @RetMsg +
'The SSN must be complete.' + CHAR(13) + CHAR(10)

-- The last name is missing
IF @LastName IS NULL
SELECT @RetCode = 0,
@RetMsg = @RetMsg +
'A Last Name is required.' + CHAR(13) + CHAR(10)

-- If validation failed, exit proc
IF @RetCode = 0
RETURN

-- Start transaction by comparing the web info to the CT-based view
BEGIN TRAN
SELECT @Verified = Count(SSN)
FROM SSNCheck
WHERE SSNCheck.SSN = @SSN And SSNCheck.LastName = @LastName

-- Check if comparison matched, if so, insert for export
IF @Verified = 1
INSERT INTO dbo.DWExport(SSN) VALUES(@SSN)
ELSE
SELECT @RetCode = 0,
@RetMsg = 'The SSN and Name are not a match to any entry in our database'

-- If no match, exit.
IF @Retcode = 0
BEGIN
ROLLBACK TRAN
RETURN
END

-- Looking for the transaction. if it's there, then commit it.
IF @@TRANCOUNT = 1
--Return success
BEGIN
COMMIT TRAN
SELECT @RetCode = 1,
@RetMsg = 'SSN Added to export table Successfully.'
END

RETURN
GO
~~~End OF Proc~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~

Again, hitting this procedure from Query Analyzer gives me exactly the
results I want/expect in all cases of success or failure. Before you
think that @RetCode *is* what I've accidentally returned, note that
changing all failure results of @RetCode to 3 in the stored Procedure
still returns the number 0 on failure.

Thanks for the quick response and the interest so far!
MP

Re: Stored procedure Output Parameter headache

Monkey Pi wrote:
> Bob Barrows [MVP] wrote:
>> Monkey Pi wrote:
> -Large Snip-
>> The first parameter that should be added is the return parameter:
>> .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
>> adParamReturnValue)
>>
>
> Do I need this if I am only looking for the output parameter and not
> any sort of Return Value?
>

Yes.

>>
>>> .Parameters.Append .CreateParameter("@SSN", adVarChar, adParamInput,
>>> 9, txtSSN)
>>> .Parameters.Append .CreateParameter("@LastName", adVarChar,
>>> adParamInput, 25, txtLName)
>>> .Parameters.Append .CreateParameter("@RetMsg", adVarChar,
>>> adParamOutput, 100)
>>> .Parameters.Append .CreateParameter("@RetCode", adInteger,
>>> adParamOutput)
>>
>>
>>
>> This is strange. You've created 4 parameters, but in the QA input
>> below, you only supply 3 ...
>> Are you thinking that @RetCode is the value returned by the RETURN
>> statement in your proc? If so, read this:
>>
http://www.google.com/groups?hl=en&lr=&c2coff=1&selm=OgYK94S gEHA.4092%40TK2MSFTNGP10.phx.gbl
>
> I actually have 5 parameters as you'll see in my Proc (which will be
> below) I only use 3 in my ASP page and the 4th was added to see if
> switching to a different output parameter would yield different
> results.
>
>> You really should show us the CREATE PROCEDURE statement - just the
>> part where the parameters are declared: the body of the procedure is
>> useless.
>>
>>
> Yes, that's right and what I've been testing up to the end. @RetMsg
> is what I'm looking for as a result of running my Proc.
> I accidentally left it in this stage before cutting and
> pasting...Sorry. Here's my Stored Procedure.
>
> ~~~Start Proc~~~~~~~~~~~~~~~~~~~~
> -- Check for a match between SSN and Last Name entered
> CREATE PROCEDURE [dbo].[spCheckDWdCResume]
> @SSN varchar(9),
> @LastName varchar(25),
> @Verified int = NULL OUTPUT,
> @RetCode int = NULL OUTPUT,
> @RetMsg varchar(100) = NULL OUTPUT
>
>
> Again, hitting this procedure from Query Analyzer gives me exactly the
> results I want/expect in all cases of success or failure. Before you
> think that @RetCode *is* what I've accidentally returned, note that
> changing all failure results of @RetCode to 3 in the stored Procedure
> still returns the number 0 on failure.
>
> Thanks for the quick response and the interest so far!

If you declare 5 parameters in your procedure, you must create 5
parameter objects (in addition to the return parameter) when executing
it via ADO. ADO does not support named parameters, so the only way to
know which parameter object applies to which parameter is its ordinal
position. Here is the output from my code generator:

Dim cmd, param

Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "spCheckDWdCResume"
set .ActiveConnection=cnSQL
set param = .createparameter("RETURN_VALUE", adInteger,
adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@SSN", adVarChar, adParamInput, 9, [put
value here])
.parameters.append param
set param = .createparameter("@LastName", adVarChar, adParamInput, 25,
[put value here])
.parameters.append param
set param = .createparameter("@Verified", adInteger,
adParamInputOutput, 0, [put value here])
.parameters.append param
set param = .createparameter("@RetCode", adInteger,
adParamInputOutput, 0, [put value here])
.parameters.append param
set param = .createparameter("@RetMsg", adVarChar, adParamInputOutput,
100, [put value here])
.parameters.append param
.execute ,,adexecutenorecords
end with

If you do not wish to supply values for your output parameters, replace
adParamInputOutput with adParamOutput and leave out the last argument:
set param = .createparameter("@RetMsg", adVarChar, adParamOutput, 100)

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Stored procedure Output Parameter headache

Hey Bob

Wow, You are fantastic!!
By setting the parameters as such

' Add the parameters
.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("@SSN", adVarChar, adParamInput, 9,
txtSSN)
.Parameters.Append .CreateParameter("@LastName", adVarChar,
adParamInput, 25, txtLName)
.Parameters.Append .CreateParameter("@Verified", adInteger,
adParamOutput, 0)
.Parameters.Append .CreateParameter("@RetCode", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@RetMsg", adVarChar,
adParamOutput, 100)

It works great!

Thank you very much for your help!!
MP

Re: Stored procedure Output Parameter headache

"Bob Barrows [MVP]" wrote in message
news:%23T9MATljGHA.4660@TK2MSFTNGP03.phx.gbl...



> You may be interested in trying out my stored procedure code generator
> available at
> http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>



I tried it and failed it is looking for an additional file


also getting a vbscript error

Line: 19
Char: 1
Object Required: 'parent.document.all.tags(...),item(...)'
Code: 0



running XP Professional SP2
IIS version 5.1

Re: Stored procedure Output Parameter headache

Michael Kujawa wrote:
> "Bob Barrows [MVP]" wrote in message
> news:%23T9MATljGHA.4660@TK2MSFTNGP03.phx.gbl...
>
>
>
>> You may be interested in trying out my stored procedure code
>> generator available at
>> http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip
>>
> I tried it and failed it is looking for an additional file
>

You have the source. You can either
1. modify the line to make it point to the actual location of adovbs.inc
on your machine
2. copy the adovbs.inc file into the virtual directory containing this
page
3. remove the #include line and reference the ado type library per:
http://www.aspfaq.com/show.asp?id=2112

>
> also getting a vbscript error
>
> Line: 19
> Char: 1
> Object Required: 'parent.document.all.tags(...),item(...)'
> Code: 0
>
I suspect this is related to the missing ado constants which you will
fix by one of the 3 methods mentioned above.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Stored procedure Output Parameter headache

"Bob Barrows [MVP]" wrote in message
news:%23MMyXRvjGHA.5020@TK2MSFTNGP02.phx.gbl...

> You have the source. You can either
> 1. modify the line to make it point to the actual location of adovbs.inc
> on your machine
> 2. copy the adovbs.inc file into the virtual directory containing this
> page
> 3. remove the #include line and reference the ado type library per:
> http://www.aspfaq.com/show.asp?id=2112
>
> I suspect this is related to the missing ado constants which you will
> fix by one of the 3 methods mentioned above.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>


Wasn't sure if your file had special constraints added
I have one that I use and copied it to the test folder

Looks very nice Bob

Re: Stored procedure Output Parameter headache

Michael Kujawa wrote:
> Looks very nice Bob

:-)
Well, I don't know how "nice" it looks. It works.
It started off as a quicky tool for my own use. I did not spend any time
on appearance ...

If you feel like taking the time to make it pretty (preferably using a
css file), pass it back to me and I'll replace the one on the website.

Some things I never got to, if anyone has the time to tackle them:
1. Include a way to toggle between integrated security and sql login
2. user-configurable variable names
3. allow user to specify whether or not the procedure returns records
(as of now, it assumes the procedure does not return records, requiring
you to tweak the generated code to allow it to populate a recordset)


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.