Parameter StoredProc and Return Value 1

[behbeh]

Hi!
Does anyone know what's wrong with this ASP code?
Set objCommand=Server.CreateObject("ADODB.Command&quot
With objCommand
.ActiveConnection=objConn
.CommandText="{?=CALL dbo.usp_parm_CheckIfEntityExists (' " & Request.Form("company&quot & " ',' " & Request.Form("street1&quot & " ', ' " & Request.Form("city&quot & " ', ' " & Request.Form("state_abbrev&quot & " ')}"
.CommandType=adCmdStoredProc
End With
objCommand(0).Direction=adParamReturnValue
objCommand.Execute
Set intReturn=objCommand(0)

Am I passing the strings in correctly?
Or is there a better way to call a parm stored porcedure and accept a return value?
Thanks in advance for any help!!!

 

[rac2]

Your code looks just like the code in the WROX book Beginning ASP Databases except for the

Set intReturn = objCmd(0)

Are you looking for an integer like so? The value property might help since intReturn is a Parameter object, but VBScript seems to be able to figure that out in most contexts.

intReturn.value

Safe to assume you have a return statement in the stored procedure, right?

And you have included the VB constants file or assigned
adParamReturnValue = 4?

Just some of the mistakes I have made.

 

[behbeh]

I think the code WAS from a Wrox ASP book

I am looking for an integer return (0, 1 or 2) and I do have my RETURN statements in my SQL parm stored procedure. I also have the adovbs include file in my site.
The weird thing about this is that I use the EXACT command object format to pass integers into a parm sp (less the single quotes)and it grabs a return value on the same .asp page and it works fine! The string parm sp just returns a zero-length string, which cannot happen from my SP.
I did just create this SP and assigned permissions to the internet guest account to execute it.
Maybe the server needs rebooted to take the changes?
I don't know, but I'm about ready to code everything on the ASP page and nix the SP.

 

[rac2]

"The string parm sp just returns a zero-length string, which cannot happen from my SP."

This cannot happen from any stored procedure because stored procedures can only return integers. I take it you find that intReturn.value is blank when you try to write it?

Possibly the stored procedure which uses string input parameters is not doing what you think it is, possibly none of the the RETURN statements are executed? Can you post the stored procedure code?

 

[behbeh]

Here it is:


CREATE PROCEDURE dbo.usp_parm_CheckIfEntityExists
(
@strCompany AS VARCHAR(100),
@strStreet1 AS VARCHAR(100),
@strCity AS VARCHAR(100),
@strState AS CHAR(2)
)
AS
DECLARE @intResponseID AS INTEGER
DECLARE @intEntityID AS INTEGER
DECLARE @strYear AS CHAR(4)
BEGIN

SET @strYear='2003'

SELECT @intEntityID=entity_id
FROM Entities
WHERE company=@strCompany
AND street1=@strStreet1
AND city=@strCity
AND state_abbrev=@strState

IF @intEntityID Is Not Null
BEGIN
SELECT @intResponseID=response_id
FROM SourcesResponseMaster
WHERE entity_id=@intEntityID
AND survey_year=@strYear

IF @intResponseID Is Not Null
RETURN @intResponseID
ELSE
RETURN 1
END
ELSE
RETURN 0
END
GO

I checked what the return value was in the asp page and it was nothing, but it wasn't NULL. When I checked if LEN(intReturn)=0 it was true.
I'm stumped...

 

[rac2]

I found a couple of things.

One (you will kick yourself for this) there are spaces inside the single quote marks around the strings. So you are looking for " Peoria " instead of "Peoria".



Two, there appears to be an error in the WROX example.

.CommandText = "{?=CAll blah_blah_blah( ... ) }"

is a text command, not a stored procedure. Therefore the command type should be adCmdText, not adCmdStoredProc. So it is

.CommandType=adCmdText



FYI, if it were a stored procedure the syntax would be like this

.CommandText = "blah_blah_blah"

In other words the objCommand.CommandText would consist of the name of the stored procedure and nothing else. And it would be necessary to use the Parameters collection to pass values into and out of the stored procedure.

By the way I got an error message with .CommandType=adCmdStoredProc . You never mentioned any error messages?

Hope this solves the problem for you.

 

[behbeh]

Hey!
Thanks for your suggestions and I did kick myself for the spaces. Unfortunately after making the necessary changes it still doesn't work.

This is just too freaky. It's one of those things you encounter as a programmer and due to time constraints, just bow out, accept the defeat and figure out another way to do it.
Again, thanks for your time and suggestions!