Go Premium for a chance to win a PS4. Enter to Win

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
?
814 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

824 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