Solved

ADO.Command Object not working? Erroring "Item cannot be found in the collection corresponding to the requested name or ordinal."

Posted on 2008-09-30
7
791 Views
Last Modified: 2012-06-21
Hi Experts,

I have a silly error, not sure what it is?
Maybe ADO Version? If so, how do I check, how do I change?

../GenericAjaxCom.asp?strSP=spAddEmail&strParamName0=@intOwnerID&strParamValue0=1&strParamName1=@vchEmailAddress&strParamValue1=test@test.com.au&strParamName2=@vchConfirmedStatus&strParamValue2=UnConfirmed

Error Type:
ADODB.Command (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/gigtxt/forms/GenericAjaxCom.asp, line 41

I can connect, get the Parameter List, and all with no issues, seems to be something to do with
Result without the line 41 that is erroring

<Table Border=1><TR><TD><B>PARAMETER NAME</B></TD><TD><B>DATA-TYPE</B></TD><TD><B>DIRECTION</B></TD><TD><B>DATA-SIZE</B></TD></TR><TR><TD>@RETURN_VALUE</TD><TD>3</TD><TD>4</TD><TD>0</TD></TR><TR><TD>@intOwnerID</TD><TD>3</TD><TD>1</TD><TD>0</TD></TR><TR><TD>@vchEmailAddress</TD><TD>200</TD><TD>1</TD><TD>300</TD></TR><TR><TD>@vchConfirmedStatus</TD><TD>200</TD><TD>1</TD><TD>50</TD></TR>@intOwnerID = 1<br>@vchEmailAddress = test@test.com.au<br>@vchConfirmedStatus = UnConfirmed<br>

<!--#include Virtual="gigtxt/includes/inc_db_connect.asp"-->
 
 
<%
'Generic Com Object for Inputting data to Db
 
dim objCom
 
Set objCom = CreateObject("ADODB.Command")
	
	
	objCom.ActiveConnection = objConn
	objCom.CommandText = request.querystring("strSP")
	objCom.CommandType = adCmdStoredProc
	objCom.NamedParameters = True
	objCom.Parameters.refresh
	
	 Response.Write "<Table Border=1>" & _
				    "<TR>" & _
				       "<TD><B>PARAMETER NAME</B></TD>" & _
				       "<TD><B>DATA-TYPE</B></TD>" & _
				       "<TD><B>DIRECTION</B></TD>" & _
				       "<TD><B>DATA-SIZE</B></TD>" & _
				    "</TR>"
					
					 For Each param In objCom.Parameters 
				    Response.write "<TR>" & _
				       "<TD>" & param.name & "</TD>" & _
				       "<TD>" & param.type & "</TD>" & _
				       "<TD>" & param.direction & "</TD>" & _
				       "<TD>" & param.size & "</TD>" & _
				    	"</TR>"
				   
				   	Next
  
  for i = 0 to 20
  	
  	if request.querystring("strParamName" & i) <> "" then
  		response.write request.querystring("strParamName" & i) & " = "
  		response.write request.querystring("strParamValue" & i) & "<br>"
  		objCom.Parameters(request.querystring("strParamName" & i)) = request.querystring("strParamValue" & i)
  	end if
  	
  next
'  
'  objCom.execute
'  
'  response.write objCom.Parameters("@RETURN_VALUE") 
	
	
set objCom = nothing
 
%>

Open in new window

0
Comment
Question by:Craig Lambie
[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
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22603383
can you debug to show in which procedure it fails?
also, can you show the procedure header?
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 22603469
angelll,

By Which one it fails in, I don't understand?
That is the one it fails in?
As in that is the file GenericAjaxCom.asp which I am calling with the above mentioned Parameters.

By Header I am assuming you mean the Connection Include?

<%
 
dim objConn
 
set objConn = CreateObject("ADODB.Connection")
 
objConn.ConnectionTimeout = Application("WhatWasThat_ConnectionTimeout")
objConn.CommandTimeout = Application("WhatWasThat_CommandTimeout")
 
ObjConn.Open "Provider=SQLOLEDB;Data Source=xxxxxx;User Id=xxxx;Password=xxx;Initial Catalog=xxxx;"
 
 
%>

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22603533
>By Which one it fails in, I don't understand?
I wonder if you could debug to find out as by which parameter the code fails.
ie, removing the parameters from the url one by one, to see which one is the "culprit".

>By Header I am assuming you mean the Connection Include?
no, I wanted to see the

CREATE PROCEDURE spAddEmail ....
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 1

Author Comment

by:Craig Lambie
ID: 22603583
Oh.

I have tried taking them out, and none work.

Header is
CREATE PROCEDURE spAddEmail (
                              @intOwnerID int,
                              @vchEmailAddress varchar(300),
                              @vchConfirmedStatus varchar(50))
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 22603653
can you please try this URL:

../GenericAjaxCom.asp?strSP=spAddEmail&strParamName0=intOwnerID&strParamValue0=1&strParamName1=vchEmailAddress&strParamValue1=test%40test.com.au&strParamName2=vchConfirmedStatus&strParamValue2=UnConfirmed

add this code in your project (ie the function URLDecode):
http://aspcompress.com/samples.asp?view=URLDecode.asp

and change your code to this:
for i = 0 to 20
        
        if request.querystring("strParamName" & i) <> "" then
                response.write request.querystring("strParamName" & i) & " = "
                response.write URLDecode(request.querystring("strParamValue" & i)) & "<br>"
                objCom.Parameters("@" & request.querystring("strParamName" & i)) = URLDecode(request.querystring("strParamValue" & i))
        end if
        
  next

Open in new window

0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 22610358
That is great work!!

I didn't use the URLDecode function, as I don't think it is required, and it worked!!
Not sure why? But it worked, which is all that is important.

Enjoy, thanks, tops!!
0
 
LVL 1

Author Closing Comment

by:Craig Lambie
ID: 31501482
Thanks so much!!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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