Solved

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

Posted on 2006-10-27
8
629 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
  • 6
8 Comments
 
LVL 6

Expert Comment

by:davbouchard
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
 

Author Comment

by:sriniram
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:sriniram
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now