[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Getting error "Item cannot be found in the collection corresponding to the requested name or ordinal"

Posted on 2006-10-27
8
Medium Priority
?
651 Views
Last Modified: 2011-04-14
I am getting the following error when I while opening a page in my test environment:

Error Type:
ADODB.Parameters (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/AetnaIBA/ADOUtils.asp, line 176

I am calling series of stored procedures and they are working fine. When it comes to the stored procedure "Get_Member_DataValues", it fails. This happens before the execution of the stored procedure call from the ASP code.

My production environment is a windows 2003 server and my test environment is a windows XP laptop. Rest all required configuration is same. The same code is working fine in the production but fails in my test machine. I suspect it may be due to some differences in the environment like MDAC etc.,.

The code is given here:

FUNCTION GetRSetByADOCmd (strConnection, strSPName, aParams)
%>
<!-- #INCLUDE FILE="NoError.asp" -->
<%
' temp object reference for command
DIM oADOCommand
SET GetRSetByADOCmd = NOTHING

' create the command
SET oADOCommand = CreateADOCommand (strConnection)

IF HaveObject(oADOCommand) THEN
      ' set the command text
      oADOCommand.CommandText = strSPName

      ' add the parameters
      IF NOT IsEmpty(aParams) THEN
            ' refresh the parameter collection
            oADOCommand.Parameters.Refresh

            ' if this is an array then add the values
            ' in the array to the parameter collection
            IF IsArray(aParams) THEN
                  DIM nIdxParam
                  DIM nNumParam
                  DIM oCmdParams

                  nNumParam = UBOUND(aParams)
                  Set oCmdParams = oADOCommand.Parameters
                  ' we are not using the return value which is always
                  ' parameter 0 so bump the index by when when referring
                  ' to the params collection
                  FOR nIdxParam = 0 TO nNumParam
'*************** I AM GETTING THE ERROR IN THE LINE BELOW *********************************
                        oCmdParams(nIdxParam + 1).Value = aParams(nIdxParam)       
'*************** I AM GETTING THE ERROR IN THE LINE ABOVE *********************************
                  NEXT
            END IF
      END IF
      ' now we can execute the command
      IF Err.number = 0 THEN
            SET GetRSetByADOCmd = oADOCommand.Execute
      END IF
END IF
END FUNCTION

The call for the above code is made from a different page and the code is:

SET oRSMemberData = GetRSetByADOCmd(GetMemberAuthConnectionString, kstrMemberDataSPName, ARRAY(nMemberID))

I am getting the error in the line "oCmdParams(nIdxParam + 1).Value = aParams(nIdxParam)" of the function GetRSetByADOCmd. The value of aParams(nIdxParam) is 1835932.

Can anyone give a solution for this problem?

Thanks
Srini
0
Comment
Question by:sriniram
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
8 Comments
 
LVL 6

Expert Comment

by:davbouchard
ID: 17820758
What I would suggest you is to output the value where you're getting the error on both the test and develop environnement.
output nIdxParam,  aParams(nIdxParam), oCmdParams(nIdxParam + 1).Value

If you have the same output that you're probably right, you're missing something on the test machine.
0
 

Author Comment

by:sriniram
ID: 17821773
I tried it. It prints nIdxParam as 0 and  aParams(nIdxParam) as 1835932. But when I print oCmdParams(nIdxParam + 1).Value, it gives the same error.

Any idea what can be different? Is it MDac?

Thanks
Srini
0
 
LVL 2

Accepted Solution

by:
jumpfroggy earned 1500 total points
ID: 17827061
Arrays in vbscript have bounds checking, you use an index that isn't in the array already.  (If the array goes from 0 to 10, you can't set item 11... it's out of bounds).

This line:
> oCmdParams(nIdxParam + 1).Value = aParams(nIdxParam)
Should be:
> set parameter = oADOCommand.createParameter( .....stuff here.... )
> oCmdParams.append( parameter )
> parameter.value = aParams(nIdxParam)

First, the existing oCmdParams.count is 0.  There are no items in the list.  So when you say oCmdParams(1) = something, you get an error because you are setting outside the bounds.  You use .append() to tack something on the end of the collection (which inserts that value and increases the size of the array at the same time).  A useful trick may be to do:
response.write "oCmdParams.count = " & oCmdParams.count & vbnewline
Before and after the error statement.  You'll see before hand it's 0, and if you use .append() it'll be 1 afterwards.

Second, you're trying to set oCmdParams(...).value = aParams(...).  But when you do this, there is no object in oCmdParams(...), so you can't use .value of that object.  Think of it this way: at first, oCmdParams(...) would have been null.  Then you say null.value = something.  Doesn't make sense.  You need an object there, so first you create your parameter object (oADOCommand.createParameter()), then append it to the array ( oCmdParams.append() ) and then set the value ( parameter.value = ....).

Incidentally, if you're just passing in "values" as an array, your function will not know what type they are.  You need the type when creating a parameter (is it a boolean?  A string?  An integer?).  So you may need to pass in an array of types and an array of values.  

http://www.w3schools.com/ado/ado_ref_command.asp
http://www.w3schools.com/ado/ado_ref_parameter.asp
0
Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

 

Author Comment

by:sriniram
ID: 17844402
Thanks for the reply.

As this code is working fine in production, do I need to change the code? The code and the database in the test machine is the same as in production.

The log from Test and Production is given below:

In Test:

10/30/2006 4:18:44 PM      ------------------------------------------ START -----------------------------------------------------      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM      nNumParam is 1      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM      oCmdParams.count before statement Get_Member_Identification is 3      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM       nIdxParam is 0 aParams(nIdxParam) IS 28      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM      oCmdParams.count after statement Get_Member_Identification is 3      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM       nIdxParam is 0 value is 28 aParams(nIdxParam) IS 28      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM      oCmdParams.count before statement Get_Member_Identification is 3      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM       nIdxParam is 1 aParams(nIdxParam) IS NJMG1      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM      oCmdParams.count after statement Get_Member_Identification is 3      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM       nIdxParam is 1 value is NJMJ1 aParams(nIdxParam) IS NJMG1      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM      ------------------------------------------ END -----------------------------------------------------      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM      ------------------------------------------ START -----------------------------------------------------      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM      nNumParam is 0      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM      oCmdParams.count before statement Get_Member_DataValues is 0      /Auth.asp      Non-Fatal
10/30/2006 4:18:44 PM       nIdxParam is 0 aParams(nIdxParam) IS 0      /Auth.asp      Non-Fatal

*************************************************************************************************************************************************************************
In Production:

10/30/2006 2:06:29 PM      ------------------------------------------ START -----------------------------------------------------      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      nNumParam is 1      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      oCmdParams.count before statement Get_Member_Identification is 3      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM       nIdxParam is 0 aParams(nIdxParam) IS 28      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      oCmdParams.count after statement Get_Member_Identification is 3      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM       nIdxParam is 0 value is 28 aParams(nIdxParam) IS 28      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      oCmdParams.count before statement Get_Member_Identification is 3      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM       nIdxParam is 1 aParams(nIdxParam) IS NJMG1      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      oCmdParams.count after statement Get_Member_Identification is 3      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM       nIdxParam is 1 value is NJMG1 aParams(nIdxParam) IS NJMG1      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      ------------------------------------------ END -----------------------------------------------------      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      ------------------------------------------ START -----------------------------------------------------      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      nNumParam is 0      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      oCmdParams.count before statement Get_Member_DataValues is 2      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM       nIdxParam is 0 aParams(nIdxParam) IS 1836088      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      oCmdParams.count after statement Get_Member_DataValues is 2      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM       nIdxParam is 0 value is 1836088 aParams(nIdxParam) IS 1836088      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      ------------------------------------------ END -----------------------------------------------------      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      ------------------------------------------ START -----------------------------------------------------      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      nNumParam is 0      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      oCmdParams.count before statement Get_Member_DataValues is 2      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM       nIdxParam is 0 aParams(nIdxParam) IS 1836088      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM      oCmdParams.count after statement Get_Member_DataValues is 2      /Auth.asp      Non-Fatal
10/30/2006 2:06:29 PM       nIdxParam is 0 value is 1836088 aParams(nIdxParam) IS 1836088      /Auth.asp      Non-Fatal
0
 

Author Comment

by:sriniram
ID: 17844508
Here is the latest log from the test machine.

Thanks
Srini

10/31/2006 2:08:15 PM      ------------------------------------------ START -----------------------------------------------------      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM      oCmdParams.count before statement Get_Member_Identification is 3      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM       nIdxParam is 0 aParams(nIdxParam) IS 28      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM      oCmdParams.count after statement Get_Member_Identification is 3      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM       nIdxParam is 0 value is 28 aParams(nIdxParam) IS 28      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM      oCmdParams.count before statement Get_Member_Identification is 3      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM       nIdxParam is 1 aParams(nIdxParam) IS NJMG1      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM      oCmdParams.count after statement Get_Member_Identification is 3      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM       nIdxParam is 1 value is NJMG1 aParams(nIdxParam) IS NJMG1      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM      ------------------------------------------ END -----------------------------------------------------      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM      nMemberID is 1836088      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM      ------------------------------------------ START -----------------------------------------------------      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM      oCmdParams.count before statement Get_Member_DataValues is 0      /AetnaIBA/Auth.asp      Non-Fatal
10/31/2006 2:08:15 PM       nIdxParam is 0 aParams(nIdxParam) IS 1836088      /AetnaIBA/Auth.asp      Non-Fatal
0
 

Author Comment

by:sriniram
ID: 17902089
Thanks for the response.

I have copied the function "GetRSetByADOCmd" with a different name and changed the line (in my test machine)

oCmdParams(nIdxParam + 1).Value = aParams(nIdxParam)

with

oADOCommand.Parameters.Append = oADOCommand.CreateParameter("@iMemberID", adInteger, adParamInput, , aParams(nIdxParam)).

For the stored procedure call for "Get_Member_DataValues", I am calling the new function which is using oADOCommand.CreateParameter and the program is working fine.

But the function "GetRSetByADOCmd" is a common function and this is used to call many stored procedure. The changing the code in the function, will involve extensive testing.

The question I have is, with the same code and data which is working in production, it is failing in test. How it is working in production but not working in test?

My preference is not to change the code in production.

Can anyone have any suggestions?

Thanks
Srini
0
 

Author Comment

by:sriniram
ID: 17906311
I was trying to analyze the problem by adding the debug statements. I checked for the count after the refresh is done and it was showing me 2. Then I reverted back the code to original code and it is working fine. Also I tried in one more xp system which was not working earlier, now it is working fine.

I think there is a problem in the connection or the parameters set during the creating the connection object.  The instruction oADOCommand.Parameters.Refresh will contact SQL server and get the parameter details of the stored procedure. For this stored procedure, it is not giving any information and hence the code is failing.

Can anyone throw more light into this problem?

Thanks
Murthy
0
 

Author Comment

by:sriniram
ID: 18013167
I could fix the problem. The problem was with the stored procedure. I gave the execute permission on that particular stored procedure to this user and it worked fine.

The statement oADOCommand.Parameters.Refresh will get the parameter details of the stored procedure. Since the stored procedure was not accessable, it was not returning parameter details.

Thanks for supporting me.

Regards
Srini.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
This video teaches users how to migrate an existing Wordpress website to a new domain.
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question