Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    Round Rock, Texas
    Posts
    443
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Why does calling subroutine fail?

    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?
    Code:
        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

  • #2
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,075
    Thanks
    0
    Thanked 256 Times in 252 Posts
    How did you put it in a subroutine and how are you calling it?
    Glenn
    ____________________________________

    My Blog
    Tower of Hanoi Android app (FREE!)
    Tower of Hanoi Leaderboard
    Samegame Facebook App
    vBulletin Plugins
    ____________________________________

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    Round Rock, Texas
    Posts
    443
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by RadarBob; 12-15-2003 at 05:48 PM.

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    Round Rock, Texas
    Posts
    443
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by RadarBob; 12-15-2003 at 05:50 PM.

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Location
    Round Rock, Texas
    Posts
    443
    Thanks
    0
    Thanked 0 Times in 0 Posts
    And here's a kicker... The following two blocks of code do execute properly. They are executed before the above offending code:

    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

    Code:
    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

  • #7
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #8
    Regular Coder
    Join Date
    Jun 2002
    Location
    Round Rock, Texas
    Posts
    443
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #9
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Do you have any "on error" vbscript error handling code on your page?
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #10
    Regular Coder
    Join Date
    Jun 2002
    Location
    Round Rock, Texas
    Posts
    443
    Thanks
    0
    Thanked 0 Times in 0 Posts
    uh... ok. so I did this... (checkforerror is a subroutine call)
    Code:
    on error resume next
        cmdOrgUpdate.execute      
    checkforerrors objconnection
    and I get this error

    Code:
    ----------------------------------------------------------
    
    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.
    Code:
    CREATE PROCEDURE dbo.up_scOrgAddUpdate 
    (  
      @SessPerson_ID     int,
      @OrgID             int output,
      @Name              varchar (80),      
      @Acronym           char (12), 
      @Org_Code          char (4) 
    ) 
    AS

  • #11
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #12
    Regular Coder
    Join Date
    Jun 2002
    Location
    Round Rock, Texas
    Posts
    443
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:
    Code:
    Dim   myObject          ' declare 
    set myObject = server.createObject ("adodb.command")   ' instantiate

  • #13
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #14
    Regular Coder
    Join Date
    Jun 2002
    Location
    Round Rock, Texas
    Posts
    443
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    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:
    Code:
    strCommandText = "dbo.up_scOrgAddUpdate"
    cmdOrgUpdate.CommandType      = adCmdStoredProc
    
    ' define parameters for data passing here
    cmdOrgUpdate.Parameters.Append _
          cmdOrgUpdate.CreateParameter(. . .)
    and these go together
    Code:
    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)
    Code:
    ' 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:
    Code:
    ' don't use the word exec(ute) in the command text for a stored procedure call
    strCommandText = "exec dbo.up_scOrgAddUpdate"

  • #15
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •