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

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

LVL 1
Craig LambieAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you debug to show in which procedure it fails?
also, can you show the procedure header?
0
 
Craig LambieAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Craig LambieAuthor Commented:
Oh.

I have tried taking them out, and none work.

Header is
CREATE PROCEDURE spAddEmail (
                              @intOwnerID int,
                              @vchEmailAddress varchar(300),
                              @vchConfirmedStatus varchar(50))
0
 
Craig LambieAuthor Commented:
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
 
Craig LambieAuthor Commented:
Thanks so much!!
0
All Courses

From novice to tech pro — start learning today.