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
779 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 142

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 142

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 142

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now