ADODB.Recordset error '800a0cc1'

[aspdeveloper09]

Hi,
I getting the data from a sql server stored procedure into a recordset.
but when i reference a specific field it gives me this error
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

so to check if that column is not being returned i executed the stored procedure and it returns the column. Then I checked the recordset field count and it matched the result of executed SP. Then I did
response.Write myRs.fields(4).name
response.Write rsExists("fieldname")
fieldname was copy paste from InternetExplorer.

but still :( this error. am at wits end now. Plz. can anyone help me where I am going wrong?: :eek:

[yamaharuss]

We can't help if we don't see your code.

[aspdeveloper09]

my stored procedure is a simple select statment with parameter in where clause.
It returns 5 columns from the table.
I execute it as rs.open
then i embedd the values of different fields in recordset at various locations in the page.
i am able to do so fine with all the other fields except one field . so i have double triple checked if my name is correct. It is correct. i did a response.write and then copied it back from IE.

[yamaharuss]

Are you expecting someone to tell you you have a typo in your code? You need to remove an apostrophe?

Like I said, if you don't post your code, sproc and asp code used to call it... we can't help.

[Kuriyama]

All guessing since no code was given.

1.) Your recordset is empty. *This is probably what is happening.
2.) Typo in code.

Good luck

[aspdeveloper09]

my SP

ALTER PROCEDURE [dbo].[usp_issuesRightMenu1]
@strPrefix varchar(150),
@strTableName varchar(150),
@varID varchar(150)

AS
BEGIN
SET NOCOUNT ON

BEGIN TRY

DECLARE
@Sql varchar(1000)

SET @Sql = 'SELECT ' +QUOTENAME(@strPrefix + '_Progress') +', ' +QUOTENAME(@strPrefix + '_Background') + ', ' + QUOTENAME(@strPrefix + '_pressReleaseLink') + ', ' + QUOTENAME(@strPrefix + '_contactName' ) + ',' +QUOTENAME(@strPrefix + '_contact_c_id ') +
'FROM ' + QUOTENAME(@strTableName) + '
WHERE '+ QUOTENAME(@strPrefix + '_id') +'=''' + @varID + ''''

Exec (@Sql)
END TRY


--I've removed the error handling code.


asp code which gives error:
If rsExists(strPrefix + "_contactName") <> "" OR (rsExists(strPrefix + "_contact_c_id") <> "" AND rsExists(strPrefix + "_contact_c_id") <> 0 ) Then %>


I removed the rsExists(strPrefix + "_contactName") from it and still get error so it is confirmed that error is for rsExists(strPrefix + "_contact_c_id") this part

I did PRINT (sql) in SP to check if field is coming and found it does come in o/p when I execute that SQL.
Also I did response.Write rsExists.fields.count in ASP and it says 5
then I did response.Write rsExists.fields(4).name and it gave me issue_contact_c_id which I copied it from browser and put back in ASP , even hardcoded it , but still this error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

[aspdeveloper09]

also my recordset is not empty

[Kuriyama]

also my recordset is not empty
Are you looping through the fields to display them? Like this?


for i = 0 to rsExists.fields.count
Response.write rsExists.field(i).name & "<br />"
next

[aspdeveloper09]

thanx kuriyama, i am getting the field name already as I wrote above .n I copied it back from browser and used in my ASP code but... :(
same error.

[Kuriyama]

I'm stumped. Can you attack all of your ASP code to this thread so I can examine the whole or it?

[aspdeveloper09]

' **** NEW CODE ****
Set cmdStoredProc = Server.CreateObject("ADODB.Command")

'**** Create and set its parameters ****
Set paramDiv = cmdStoredProc.CreateParameter("@strPrefix",adVarChar , adParamInput,150 ,strPrefix)
cmdStoredProc.Parameters.Append paramDiv

Set paramDiv = cmdStoredProc.CreateParameter("@strTableName",adVarChar , adParamInput,150 ,strTableName)
cmdStoredProc.Parameters.Append paramDiv

Set paramDiv = cmdStoredProc.CreateParameter("@varID",adVarChar , adParamInput,150 ,varID)
cmdStoredProc.Parameters.Append paramDiv

Set rsExists = myDBHelper.GetDataSet("[usp_issuesRightMenu1]", cmdStoredProc, MM_db_STRING)


<ul>
<li><a href="issuezone.htm" title="Issues home">Issues home</a></li>
<li class="<%if InStr(strCurrentPage,"issuesdetails") then%>se">Introduction<%else%>"><a href="issuesdetails.htm?id=<%=varID%>" title="Introduction Page">Introduction</a><%end if%></li>
<% If rsExists(strPrefix + "_Background") <> "" Then %>
<li class="<%if InStr(strCurrentPage,"issuesbackground") then%>se">Background<%else%>"><a href="issuesbackground.htm?id=<%=varID%>&pageType=Background" title="Background">Background</a><%end if%></li>
<% End If %>
<% If rsExists(strPrefix + "_pressReleaseLink") <> "" Then %>
<li class=""><a href="http://www.northernireland.gov.uk/news" title="Press Releases (Note: This opens a new browser window.)" target="_blank">Press Releases </a><img src='extLink.gif' alt='Opens new browser window' /></li>
<% End If %>

INBETWN SOME UNRELATED CODE OMMITTED.

'TO BE REMOVED AFTER SUCCESSFUL

RESPONSE.Write rsExists(strPrefix + "_contactName")
RESPONSE.Write rsExists.FIELDS.COUNT
RESPONSE.Write rsExists.FIELDS(0).NAME
RESPONSE.Write rsExists.FIELDS(1).NAME
RESPONSE.Write rsExists.FIELDS(4).NAME

If rsExists(strPrefix + "_contactName") <> "" OR (rsExists(strPrefix + "_contact_c_id") <> "" AND rsExists(strPrefix + "_contact_c_id") <> 0 ) Then %> <li class="<%if InStr(strCurrentPage,"issuescontact") then%>se">Making Contact<%else%>"><a href="issuescontact.htm?id=<%=varID%>" title="Contact">Making Contact</a><%end if%></li>
<% End If %>

GIVES ME ERROR AT Italiced line of code. i EVEN HARDCODED THE VALUES FROM BROWSER WHICH i GOT IN RESPONSE .WRITE BUT NO LUCK...

[Kuriyama]

Just before your if statement that breaks do this.


Response.write strPrefix + "_contact_c_id" & "<br />"
Response.write strPrefix + "_contactName" & "<br />"


All your syntax looks good. I think something strange is happening to the strPrefix variable.

Also try breaking that if logic out into individual lines. That might help with the debugging.

Finally, if all else fails just access your recordset via index like you did above.

rsExists.FIELDS(4)

[aspdeveloper09]

Thanx Kuriyama,
I've tried this
dunno whatz wrong... at wits end by now...

[aspdeveloper09]

i've done this
Response.write strPrefix + "_contact_c_id" & "<br />"
Response.write strPrefix + "_contactName" & "<br />"

it breaks at first line and same error
if i write second line and then first line same... gives o/p for first one but somehow not for "_contact_c_id" although by now i've copied it ffrom browser by doing RESPONSE.Write rsExists.FIELDS(4).NAME
i did same for all other fields as well.

[Kuriyama]

Response.write isNull(strPrefix)

something is wrong with strPrefix if that is breaking.

[aspdeveloper09]

nope...
strPrefix hhas value and it breaks only for _contact_c_id
although i've copied this field name also from browser, as others after
rsExists.FIELDS(0 to 4).NAME