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
789 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

856 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