Link to home
Start Free TrialLog in
Avatar of sriniram
sriniram

asked on

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

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
Avatar of davbouchard
davbouchard
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.
Avatar of sriniram
sriniram

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of jumpfroggy
jumpfroggy
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sriniram
sriniram

ASKER

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
Avatar of sriniram
sriniram

ASKER

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
Avatar of sriniram
sriniram

ASKER

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
Avatar of sriniram
sriniram

ASKER

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
Avatar of sriniram
sriniram

ASKER

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.