...

View Full Version : Why does calling subroutine fail?



RadarBob
12-12-2003, 09:18 PM
The following code works if it is executed in-line. However if I put it into a subroutine and call that subroutine from the original spot the code sat, the code fails with an error: "Object expected". Any ideas?


OrgName = -22222
Acronym = -22222
OrgCode = ""

if(Request("FOrgName") <> "") then OrgName = Request("FOrgName")
if(Request("FAcronym") <> "") then Acronym = Request("FAcronym")
if(Request("FOrgCode") <> "") then OrgCode = Request("FOrgCode")

Set cmdOrgUpdate = Server.CreateObject("ADODB.Command")
cmdOrgUpdate.CommandText = "dbo.up_scOrgUpdate"
cmdOrgUpdate.ActiveConnection = objConnection
cmdOrgUpdate.CommandType = adCmdStoredProc


cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("retVal", adInteger, adParamReturnValue)

cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("p01_SessPersonID", adInteger, adParamInput,, SessPersonID)
cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("p02_Org_ID", adInteger, adParamInput,, OrgID)
cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("p03_OrgName", adChar, adParamInput, 80, OrgName)
cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("p04_Acronym", adChar, adParamInput, 12, Acronym)
cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("p05_OrgCode", adChar, adParamInput, 4, OrgCode)

cmdOrgUpdate.Execute()

cmdRetVal = cmdOrgUpdate.Parameters(0).Value

IF(cmdRetVal="" AND objConnection.Errors.Count > 0) THEN
cmdRetVal = objConnection.errors(0).NativeError
IF(cmdRetVal="") THEN cmdRetVal = objConnection.errors(0).SQLState
END IF

IF(cmdRetVal<>"") THEN iRetVal = cmdRetVal

Set cmdOrgUpdate.ActiveConnection = Nothing
Set cmdOrgUpdate = Nothing

glenngv
12-13-2003, 02:39 PM
How did you put it in a subroutine and how are you calling it?

RadarBob
12-15-2003, 04:17 PM
well, just like anyone who knows how to code would do it. Sorry, this is frustrating the (*$& out of me!

Suffice it to say that execution enters this subroutine, the command object is built w/ the parameters - looks good up so far... The code fails at the execute command. The stored procedure does not get executed. I know this because a forced error (@@RAISEERROR) does not appear.

Roy Sinclair
12-15-2003, 04:44 PM
How do you know it's failing at the execute? They way I'd know where it's failing is by using the LINE NUMBER provided as a part of the error display.

There's nothing that stands out as a problem in the code you posted.

RadarBob
12-15-2003, 04:47 PM
I put response.write in various places. I also print out (response.write) the command parameters.

I put a response.write with a response.end immediately after the execute command. These are not executed.

RadarBob
12-15-2003, 04:54 PM
And here's a kicker... The following two blocks of code do execute properly. They are executed before the above offending code:



sub fetchOrgData(thisOrg)
Dim strCmdString

strCmdString = "dbo.up_scorgGetData " & thisOrg
response.write (strCmdString & "<br>")

Set objCmdInitialOrgVal = Server.CreateObject("ADODB.Command")
objCmdInitialOrgVal.CommandText = strCmdString
objCmdInitialOrgVal.ActiveConnection = objConnection
objCmdInitialOrgVal.CommandType = adCmdText

Set rsInitialOrgValues = Server.CreateObject("ADODB.Recordset")
rsInitialOrgValues.CursorLocation = adUseClient
rsInitialOrgValues.Open objCmdInitialOrgVal,, adOpenStatic

Set objCmdInitialOrgVal.ActiveConnection = Nothing ' DISCONNECT
Set objCmdInitialOrgVal = Nothing

end sub



and



sub fetchOrgTypeCodes()
Dim stringVar
stringVar = "{? = call up_scGetCodeList('ORGT')}"


Set objCmdOrgCode = Server.CreateObject("ADODB.Command")
objCmdOrgCode.CommandText = stringVar
objCmdOrgCode.ActiveConnection = objConnection
objCmdOrgCode.CommandType = adCmdText
objCmdOrgCode(0).Direction = adParamReturnValue

Set rsOrgCode = objCmdOrgCode.Execute

Set objCmdOrgCode.ActiveConnection = Nothing ' DISCONNECT
Set objCmdOrgCode = Nothing
end sub

Roy Sinclair
12-15-2003, 06:32 PM
Check your browser settings. Under Tools > Internet Options > Advanced > Show Friendly HTTP Error Messages checkbox. Make sure that box is not checked because when it is checked the lousy browser will deftly remove all information that's useful for debugging problems from the output page. In other words that check box does exactly the opposite of what it implies and it sounds like you aren't getting the whole picture here as you're trying to debug your page.

RadarBob
12-15-2003, 06:58 PM
Check your browser settings. Under Tools > Internet Options > Advanced > Show Friendly HTTP Error Messages checkbox. Make sure that box is not checked
Done. No effect.

Roy Sinclair
12-15-2003, 07:16 PM
Do you have any "on error" vbscript error handling code on your page?

RadarBob
12-15-2003, 08:46 PM
uh... ok. so I did this... (checkforerror is a subroutine call)


on error resume next
cmdOrgUpdate.execute
checkforerrors objconnection


and I get this error



----------------------------------------------------------

Database Error(s) encountered executing:

----------------------------

Native Error = 201 SQLState = 42000 Error# = -2147217904
Error description = Procedure 'up_scOrgAddUpdate' expects
parameter '@SessPerson_ID', which was not supplied.
Error source = Microsoft OLE DB Provider for SQL Server


Which brings me to the question of the 1st parameter (parameters[0].

What I don't clearly understand is how parameter[0] (in this case) is used as the return value. It magically is supposed to receive the value passed back from the stored procedure from the RETURN xxx (xxx is an integer) statement.

Here is the stored procedure declaration. Far as I can tell the parameters match the command object parameters in order, type, and size.


CREATE PROCEDURE dbo.up_scOrgAddUpdate
(
@SessPerson_ID int,
@OrgID int output,
@Name varchar (80),
@Acronym char (12),
@Org_Code char (4)
)
AS

Roy Sinclair
12-15-2003, 09:09 PM
Ok. I've never used the long process you followed to call a stored procedure, I've always found it simpler to create the SQL text calling the procedure instead.

Looking at the documentation for doing it the way you have I see that you're not doing it according to the documentation examples I have. You're doing:

cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("retVal", adInteger, adParamReturnValue)

While the documentation I have suggests:

set paramObject = cmdOrgUpdate.CreateParameter("retVal", adInteger, adParamReturnValue)
cmdOrgUpdate.Parameters.Append paramObject

The "set" operator has some special signifigance in VBScript/ASP and not using it may be causing the problem you're seeing.

RadarBob
12-15-2003, 09:26 PM
Roy;
Looks like your code is saying the same thing but in a different way. The style I show is used throughout our web site and works. it is essentially 'chaining' two directives/commands into one statement

I think of "set" as analogous to "new" in C, Java, JavaScript, etc. "set" is used whenever instantiating a new object. for example:


Dim myObject ' declare
set myObject = server.createObject ("adodb.command") ' instantiate

Roy Sinclair
12-15-2003, 10:12 PM
Well there are other possibilities, your Stored Procedure declaration makes it obvious that @OrgID is the parameter where the output will be returned and that's not going to be Parameters[0] since it's not the first parameter. You're adding an extra parameter for the output which you're declaring first so that may be the problem. Also the names of the parameters you're providing don't match the declaration for the stored procedure (the names have "p0x_" appended) so there's no way for ADO to match up those parameters by their declared names so the order they're declared in is key and it looks like the extra output parameter declared first may be the cause of the problem.

RadarBob
12-16-2003, 01:24 PM
Roy;
Believe it our not I got it to work. I'll show the code changes but first I want to address your last post.

The parameter names starting w/ "pox.."
These are just variable names so the parameter can be referenced by name. It does not have to correspond to the name in the SQL's parameter list.

The parameters must match in order, type, size; not in name. BTW I noted a specific point in the MSDN documentation that says not to define parameter data size as the maximum possible, but rather match the size as defined in the stored procedure. Non-specified problems could arrise.

parameter[0]
I've never fully grocked this, but here's what I understand. Note that the first parameter (index zero) is defined as "adParamReturnValue". Evidentally this has a special relationship with the stored procedure - this parameter is not part of the parameter list as seen by the stored procedure. As you point out it is not accounted for in the SQL parameter list.

But from the behavior it is clear that RETURN xxx in the stored procedure puts that xxx value into this ADO command parameter. I am in fact getting the xxx value thru this parameter.

The code changes
I think I've discovered that certain syntax and attributes "go togethter" when defining and setting up an ADO object....

Here is the now-working code



strCommandText = "dbo.up_scOrgAddUpdate"

Set cmdOrgUpdate = Server.CreateObject("ADODB.Command")
cmdOrgUpdate.CommandText = strCommandText
cmdOrgUpdate.ActiveConnection = objConnection
cmdOrgUpdate.CommandType = adCmdStoredProc

cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("retVal", adInteger, adParamReturnValue)

cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("p01_SessPersonID", adInteger, adParamInput,, SessPersonID)
cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("p02_Org_ID", adInteger, adParamInputOutput,, OrgID)
cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("p03_OrgName", adVarChar, adParamInput, 80, OrgName)
cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("p04_Acronym", adVarChar, adParamInput, 12, Acronym)
cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("p05_OrgCode", adVarChar, adParamInput, 4, OrgCode)

cmdOrgUpdate.execute



Apparently These go together:


strCommandText = "dbo.up_scOrgAddUpdate"
cmdOrgUpdate.CommandType = adCmdStoredProc

' define parameters for data passing here
cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter(. . .)



and these go together


stringVar = "{? = call up_scGetCodeList('ORGT')}"
objCmdOrgCode.CommandType = adCmdText

' The parameters are part of the command string, one does not create/define parameters


Thus, the following does not work. (problem in bold)


' do not define a parameter when using the following syntax:
stringVar = "{? = call up_scGetCodeList('ORGT')}"

' don't define a parameter
cmdOrgUpdate.Parameters.Append _
cmdOrgUpdate.CreateParameter("p01_ORGT", adChar, adParamInput, 4, OrgType)

This is also an error:


' don't use the word exec(ute) in the command text for a stored procedure call
strCommandText = "exec dbo.up_scOrgAddUpdate"

Roy Sinclair
12-16-2003, 03:41 PM
Glad to see you've got it working. I did mention I don't call stored procedures that way myself, I've always found it simpler to write out the SQL text and process it that way so this was a learning experience for me too.

RadarBob
12-16-2003, 06:06 PM
We use Stored procedure calls for a several reasons.

Keeps the calling code clean
some of our stored procedures are long and complex, grabbing data from several tables.

Reuse
Some SPs are called from several pages

security
We're set up such that direct SQL cannot touch the tables. You'd have to ask our DB admin guy how that's set up.

Value added
We do some error trapping at the SQL level. We also pass back error codes (home-grown / defined codes).



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum