Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
806 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 1000 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

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.

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…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 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