return value from a stored procedure

You may get more response in a more appropriate Topic Area such as:
https://www.experts-exchange.com/Web/Web_Languages/ASP/

Having said that, you need to change:
oCmd.Parameters.Append(oCmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4))

To:

oCmd.Parameters.Append oCmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)

Also, if that does not solve it, post all the relevant ASP code and the stored Procedure, so that we can see what is wrong.

Not sure about ASP side...

but from SQL side, make sure it always returns a value...(initiate it to some value)

begin
     declare @exists int

select @exists = 1

if exists (Select 1 From cases Where streetnum=@streetnum and streetname=@streetname and roads=@roads)
     select @exists = 55555

return @exists

Thanks for the fast response. I have tried both suggestion and none works.

Then..the problem would be on ASP side...


so..as Acperkin said ...better if you post this on ASP topic...
 
https://www.experts-exchange.com/Web/Web_Languages/ASP/

>>Thanks for the fast response. I have tried both suggestion and none works.<<
That is to be expected, without having all the facts.  Now, if you can post as I requested we should be able to reach a solution as this is a trivial problem.

I will make one more WAG before I move on:  Make sure that the ADO parameters are declared, either using a Meta tag or using the ADOVBS.INC include file.

here's the asp code:

set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = objConn
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "sp_createReferralWithCaseNumber"

'Set parameters
oCmd.Parameters.Append(oCmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4))
oCmd.Parameters.Append(oCmd.CreateParameter("@casenumber",adVarChar,adParamInput,20,strCaseNumber))
oCmd.Parameters.Append(oCmd.CreateParameter("@referral",adVarChar,adParamInput,100,strReferral))

'Execute the stored procedure
set oRSInsert = oCmd.Execute
On Error Resume Next
errCode = oCmd.Parameters("RETURN_VALUE")
response.write ("errCode="&strErrCode)



ms sql stored procedure:

CREATE procedure dbo.sp_createReferralWithCaseNumber
@casenumber varchar(20),
@referral varchar(100)

as
begin
      declare @exists int
      select @exists = 1
if exists (Select 1 From cases Where streetnum=@streetnum and streetname=@streetname and roads=@roads)
      select @exists = 55555
...

return @exists

end

well for starters put begin and end around the statements the sql must execute if exists condition is true

CREATE procedure dbo.sp_createReferralWithCaseNumber
@casenumber varchar(20),
@referral varchar(100)

as
begin
     declare @exists int
     select @exists = 1
if exists (Select 1 From cases Where streetnum=@streetnum and streetname=@streetname and roads=@roads)
  BEGIN
     select @exists = 55555
     ...
 END
 return @exists
end

Again, without seeing the WHOLE stored procedure, it is difficult to know what your intent is, so we can only guess.

In any case make the following changes:

set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = objConn                                  ' if objConn is a Connection object than make this change
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "sp_createReferralWithCaseNumber"

'Set parameters
oCmd.Parameters.Append oCmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)                     ' Correct this syntax
oCmd.Parameters.Append oCmd.CreateParameter("@casenumber",adVarChar,adParamInput,20,strCaseNumber)          ' Correct this syntax
oCmd.Parameters.Append oCmd.CreateParameter("@referral",adVarChar,adParamInput,100,strReferral)                      ' Correct this syntax

'Execute the stored procedure
oCmd.Execute ,, adExecuteNoRecords                                                      ' Change this
' On Error Resume Next                                                                          ' Lose this
errCode = oCmd.Parameters("RETURN_VALUE")
response.write ("errCode="&strErrCode)

what's wrong with the syntax?

oCmd.Parameters.Append oCmd.CreateParameter("@casenumber",adVarChar,adParamInput,20,strCaseNumber)          ' Correct this syntax
oCmd.Parameters.Append oCmd.CreateParameter("@referral",adVarChar,adParamInput,100,strReferral)                      ' Correct this syntax


change this to what?

oCmd.Execute ,, adExecuteNoRecords                                                      ' Change this


i'm still not able to get a return value. this is very frustrating.

the problem seems like to be in the stored procedure, because it tries to insert a record into the table. that means the " if exists..." doesn't work. and it skips the "if exists..." part and goes directly to the else ....  I have tested out the sql statement and it seems fine.


as
begin
      declare @exists int
      select @exists = 1
if exists (Select 1 From cases Where streetnum='@streetnum' and streetname='@streetname' and roads='@roads')
      begin
            select @exists = 55555
            return @exists
      end
else
      begin
                   insert into cases (casenumber, streetnum, streetname, roads, apt, city, state, zip)
            values (@casenumber, @streetnum, @streetname, @roads, @apt, @city, @state, @zip)
      end

>>what's wrong with the syntax?<<
Nothing.  Yours was wrong.

>>change this to what?<<
Change your code:
set oRSInsert = oCmd.Execute

To:
oCmd.Execute ,, adExecuteNoRecords      

>>the problem seems like to be in the stored procedure<<
I agree.  I wish we could help you without seeing the WHOLE stored procedure, unfortunately while many of us are pretty good at debugging stored procedures, very few are clairvoyant.

here's the full stored procedure:


CREATE procedure dbo.sp_createReferralWithCaseNumber
@casenumber varchar(20),
@referral varchar(100),
@email varchar(50),
@clientfirstname varchar(50),
@clientlastname varchar(50),
@streetnum varchar(4),
@streetname varchar(20),
@roads varchar(8),
@apt varchar(5),
@city varchar(50),
@state varchar(20),
@zip varchar(12),
@homephone varchar(12),
@workphone varchar(12),
@cellphone varchar(12),
@primarylanguage varchar(50),
@rodents varchar(8),
@cockroaches varchar(8),
@mold varchar(8),
@moisture varchar(8),
@comments varchar(2000),
@consent varchar(8),
@IsdDate varchar(12),
@inspection varchar(8),
@InspectionDate varchar(12),
@inspector varchar(50)

as
begin
      declare @exists int
      select @exists = 1
if exists (Select 1 From cases Where streetnum='@streetnum' and streetname='@streetname' and roads='@roads')
      begin
            select @exists = 55555
            return @exists
      end
else
      
                   insert into cases (casenumber, referral, email, clientfirstname, clientlastname,
            streetnum, streetname, roads, apt, city, state, zip, homephone, workphone, cellphone, primarylanguage,
            rodents, cockroaches, mold, moisture, comments, consent, IsdDate, inspection,
            InspectionDate, inspector)
            values (@casenumber, @referral, @email, @clientfirstname, @clientlastname,
            @streetnum, @streetname, @roads, @apt, @city, @state, @zip, @homephone, @workphone, @cellphone,
            @primarylanguage, @rodents, @cockroaches, @mold, @moisture, @comments,
            @consent, @IsdDate, @inspection, @InspectionDate, @inspector)
      
if not exists (Select 1 From isd Where casenumber='@casenumber')
      begin
                   insert into isd (caseID, casenumber, inspector, InspectionDate)
            values (@@identity, @casenumber, @inspector, @InspectionDate)
      end
else
      return @exists

end
GO

Make the following change to your stored procedure:

CREATE procedure dbo.sp_createReferralWithCaseNumber
@casenumber varchar(20),
@referral varchar(100),
@email varchar(50),
@clientfirstname varchar(50),
@clientlastname varchar(50),
@streetnum varchar(4),
@streetname varchar(20),
@roads varchar(8),
@apt varchar(5),
@city varchar(50),
@state varchar(20),
@zip varchar(12),
@homephone varchar(12),
@workphone varchar(12),
@cellphone varchar(12),
@primarylanguage varchar(50),
@rodents varchar(8),
@cockroaches varchar(8),
@mold varchar(8),
@moisture varchar(8),
@comments varchar(2000),
@consent varchar(8),
@IsdDate varchar(12),
@inspection varchar(8),
@InspectionDate varchar(12),
@inspector varchar(50)

as
begin
      declare @exists int
      select @exists = 1
      if exists (Select 1 From cases Where streetnum='@streetnum' and streetname='@streetname' and roads='@roads')
     begin
          select @exists = 55555
          return @exists
     end
      else
                  insert into cases (casenumber, referral, email, clientfirstname, clientlastname,
                              streetnum, streetname, roads, apt, city, state, zip, homephone, workphone, cellphone, primarylanguage,
                              rodents, cockroaches, mold, moisture, comments, consent, IsdDate, inspection,
                              InspectionDate, inspector)
                              values (@casenumber, @referral, @email, @clientfirstname, @clientlastname,
                              @streetnum, @streetname, @roads, @apt, @city, @state, @zip, @homephone, @workphone, @cellphone,
                              @primarylanguage, @rodents, @cockroaches, @mold, @moisture, @comments,
                              @consent, @IsdDate, @inspection, @InspectionDate, @inspector)
     
      if not exists (Select 1 From isd Where casenumber='@casenumber')
            begin
                  insert into isd (caseID, casenumber, inspector, InspectionDate)
                                     values (@@identity, @casenumber, @inspector, @InspectionDate)
                        return @exists                       -- Add this line so that at least you get a value back
            end
      else
            return @exists

End
GO


Call the above stored procedure as follows:

Set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = objConn                                  ' if objConn is a Connection object than make this change
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "sp_createReferralWithCaseNumber"

'Set parameters
oCmd.Parameters.Append oCmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)
oCmd.Parameters.Append oCmd.CreateParameter("@casenumber", adVarChar, adParamInput,20, strCaseNumber)
oCmd.Parameters.Append oCmd.CreateParameter("@referral", adVarChar, adParamInput, 100, strReferral)
oCmd.Parameters.Append oCmd.CreateParameter("@email", adVarChar, adParamInput, 50, ValueOfEmailGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@clientfirstname", adVarChar, adParamInput, 50, ValueOfclientfirstnameGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@clientlastname", adVarChar, adParamInput, 50, ValueOfclientlastnameGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@streetnum", adVarChar, adParamInput, 4, ValueOfstreetnumGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@streetname", adVarChar, adParamInput, 20, ValueOfstreetnameGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@roads", adVarChar, adParamInput, 8, ValueOfroadsGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@apt", adVarChar, adParamInput, 5, ValueOfaptGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@city", adVarChar, adParamInput, 50, ValueOfcityGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@state", adVarChar, adParamInput, 20, ValueOfstateGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@zip", adVarChar, adParamInput, 12, ValueOfzipGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@homephone", adVarChar, adParamInput, 12, ValueOfhomephoneGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@workhone", adVarChar, adParamInput, 12, ValueOfworkphoneGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@cellphone", adVarChar, adParamInput, 12, ValueOfcellphoneGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@primarylanguage", adVarChar, adParamInput, 50, ValueOfprimarylanguageGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@rodents", adVarChar, adParamInput, 8, ValueOfrodentsGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@cockroaches", adVarChar, adParamInput, 8, ValueOfcockroachesGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@mold", adVarChar, adParamInput, 8, ValueOfmoldGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@moisture", adVarChar, adParamInput, 8, ValueOfmoistureGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@comments", adVarChar, adParamInput, 2000, ValueOfcommentsGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@consent", adVarChar, adParamInput, 8, ValueOfconsentGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@IsdDate", adVarChar, adParamInput, 12, ValueOfIsdDateGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@inspection", adVarChar, adParamInput, 8, ValueOfinspectionGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@InspectionDate", adVarChar, adParamInput, 12, ValueOfInspectionDateGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@inspector", adVarChar, adParamInput, 8, ValueOfinspectorGoesHere)

'Execute the stored procedure
oCmd.Execute ,, adExecuteNoRecords

errCode = oCmd.Parameters("RETURN_VALUE").Value
response.write "errCode=" & strErrCode

acperkins,

you have this :
'Set parameters
oCmd.Parameters.Append oCmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)

>> note the @RETURN_VALUE

and

errCode = oCmd.Parameters("RETURN_VALUE").Value

those two have to match?


also i have tried exactly what you suggested, but no cigars.

as i have mentioned above, the "if statement" in the stored procedure is executed, because i'm still getting a duplicate key error return back to the browser.

>>those two have to match?<<
Yes.  It was a typo.

>>the "if statement" in the stored procedure is executed,<<
Which if statement (there are two)?

>> i'm still getting a duplicate key error return back to the browser.<<
You need to take a look at the structure of the table in question (cases or isd) and see which unique index is getting violated.  This will give  you a clue where you need to modify your stored procedure.

mm well  
<<  if exists (Select 1 From cases Where streetnum='@streetnum' and streetname='@streetname' and roads='@roads') >>
AND
<<  if not exists (Select 1 From isd Where casenumber='@casenumber')>>
I suspect are not producing the correct results... try REMOVING the apostrophe around the parameters..

IE
 if exists (Select 1 From cases Where streetnum=@streetnum and streetname=@streetname and roads=@roads)

AND
 if not exists (Select 1 From isd Where casenumber=@casenumber)

That might help.

it's violating the 1st "if statement", it tries to insert into the "cases" table.

i put apostrophe around the parameters, because they are all varchars.

<< i put apostrophe around the parameters, because they are all varchars. >>

you are passing in a paramters, DONT put the apostrophes around the parameters, they are already
varchars.  You only put apostrophes around actual strings  ie  street = 'abc'

i remove the apostrophe in the stored procedures. now i no longer get the "primary duplicate key" error return to the web browser because i believe it has skipped the "if statement".

now it's trying to print out the errCode, which is still getting a blank value.

my thought:

since i declare a variable in stored procedure: @exists

in the asp, should i be using the same variable - @exists instead of @RETURN_VALUE?

does it matter?

any ideas?

note on the ado return_value parm :

<<oCmd.Parameters.Append oCmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)>>
<<errCode = oCmd.Parameters("RETURN_VALUE").Value>>

If you are using SQL2000 you must reference it as @RETURN_VALUE  if you are using SQL 7.0 then "RETURN_VALUE"

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q285295

i try running a very simple test sp, and still getting no value

CREATE procedure dbo.sp_test

as
Begin
     declare @exists int
     set @exists = 1
          return @exists
End
GO

increase to 100. hoping someone can help me resolve this bug quickly.

in your simple example please post your asp code as you have it now

also do you have sql2000 or sql 7.0

oCmd.ActiveConnection = objConn
oCmd.CommandType = adCmdStoredProc

oCmd.CommandText="sp_test"

oCmd.Parameters.Append oCmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)

 oCmd.Execute ,, adExecuteNoRecords

            errCode = oCmd.Parameters("@RETURN_VALUE").Value
            response.write "errCode=" & strErrCode


and i'm running sql2000

can you test the following in query analyser

declare @rtnvalue int
exec @rtnvalue = sp_test

select convert(varchar(2), @rtnvalue)

does it give you a result of 1 ?

yes

i have found the bug!

errCode = oCmd.Parameters("@RETURN_VALUE").Value
          response.write "errCode=" & strErrCode


that's the bug

errCode

and trying to print out strErrCode

One other thing to look out for:   "SQL Server Returns Output Parameters Only After Resultsets" so set   connection.CursorLocation = adUseClient   to force everything back. Reference -
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q256234&

MrLogin

Not sure if you realized:
1.  There is NO resultset involved in this thread.  Take a look at the Stored Procedure and you will see what I mean.  It is just simple INSERT statements.
2.  The thread is nearly 3 years old.  Were you on some archealogical trip?

Was using a return code to flag the absence of a result set from a stored proc but the rc was not set until I included adUseClient - even when no result set was present.      Presume this is an implementation issue.

Also when returning multiple record sets about 3% of the time some of the later record sets would not return (remote db server).  The use of adUseClient appears to have resolved this as well - if I encounter further issues here will raise as a new question.