Getting Error: Item cannot be found in the collection corresponding to the requested

dalezjc · December 31st, 2008, 12:06 PM

I've got dozens of ASP scripts running without errors, but for some reason with this script, I'm getting the following error:

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

Here's my code:

Code:

 
<%
Option Explicit
Dim rsqdb
Dim strConnection, conn
Dim raction_summary
set Conn=Server.CreateObject("ADODB.Connection")
set rsqdb = server.CreateObject("ADODB.Recordset")
set raction_summary =server.CreateObject("ADODB.Recordset")
Conn.open "Provider=sqloledb;Server=myserver;Initial Catalog=qdb;UID=Reports;PWD=;"
set rsqdb = conn.Execute ("use msdb select distinct j.Name as JobName, j.description as JobDescription, h.run_date as LastStatusDate,  " _
& " case h.run_status  " _
& " when 0 then 'Failed'  " _
& " when 1 then 'Successful'  " _
& " when 3 then 'Cancelled'  " _
& " when 4 then 'In Progress'  " _
& " end as JobStatus " _
& " from sysJobHistory h, sysJobs j " _
& " where j.job_id = h.job_id and h.run_date =  " _
& " (select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id) " _
& " order by JobStatus ")
%>
         <%response.write rsqdb("name")%>
<%do while not rsqdb.EOF%></do>
<%rsqdb.MoveNext%>
<%loop%>
 
<%
set rsqdb = nothing
set conn = nothing
%>

Sysjobs have name and description fields so I'm not sure why I'm getting this.

Thanks,
Dale

dparsons · December 31st, 2008, 05:09 PM

Hmm. Two things:

First by doing this:

asp Code:

<%response.write rsqdb("name")%>

You are making the assumption that your recordset will always have data in it which you should not do.

You might want to try something like this:

asp Code:

if not rsqdb.EOF then
    response.write rsqdb("name")
end if

Second, the following code is unnecessary:

asp Code:

<%do while not rsqdb.EOF%></do>
<%rsqdb.MoveNext%>
<%loop%>

Since you are looping the recordset just to loop it. Also I am not sure what '</do>' is, but it is not a valid tag.

Finally, the answer to your question: The reason you are getting the error about the column not existing is because it doesn't. While you select j.Name in your sql query you alias it as JobName so, therefore, you must reference it as such:

<%response.write rsqdb("JobName")%>

hth.
-Doug

dalezjc · January 14th, 2009, 10:38 AM

Quote:

Originally Posted by dparsons

Hmm. Two things:

First by doing this:

asp Code:

<%response.write rsqdb("name")%>

You are making the assumption that your recordset will always have data in it which you should not do.

You might want to try something like this:

asp Code:

if not rsqdb.EOF then
response.write rsqdb("name")
end if

Second, the following code is unnecessary:

Finally, the answer to your question: The reason you are getting the error about the column not existing is because it doesn't. While you select j.Name in your sql query you alias it as JobName so, therefore, you must reference it as such:

<%response.write rsqdb("JobName")%>

hth.
-Doug

Thanks for all the tips regarding my code. I just tried to cut and pasted only the relevant parts, but I didn't do a good job of that.

I also tried "JobName" in the response.write, but I'm still getting the same error.

Dale

dparsons · January 14th, 2009, 10:45 AM

Did you paste in the logic to check if the recordset was at the end of file? Does your code enter into that if? I would start there because if the code does not pass into there then your query is not retuning any results.

dalezjc · January 14th, 2009, 12:58 PM

Added the EOF check, and now I'm getting the following:

ADODB.Recordseterror '800a0e78'Operation is not allowed when the object is closed.

dparsons · January 14th, 2009, 03:20 PM

Not sure why that would happen. Set your code up to look like this:

asp Code:

<%
Option Explicit
Dim rsqdb
Dim strConnection, conn
Dim raction_summary
set Conn=Server.CreateObject("ADODB.Connection")
set rsqdb = server.CreateObject("ADODB.Recordset")
set raction_summary =server.CreateObject("ADODB.Recordset")
Conn.open "Provider=sqloledb;Server=myserver;Initial Catalog=qdb;UID=Reports;PWD=;"
set rsqdb = conn.Execute ("use msdb select distinct j.Name as JobName, j.description as JobDescription, h.run_date as LastStatusDate,  " _
& " case h.run_status  " _
& " when 0 then 'Failed'  " _
& " when 1 then 'Successful'  " _
& " when 3 then 'Cancelled'  " _
& " when 4 then 'In Progress'  " _
& " end as JobStatus " _
& " from sysJobHistory h, sysJobs j " _
& " where j.job_id = h.job_id and h.run_date =  " _
& " (select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id) " _
& " order by JobStatus ")


if not rsqdb.EOF then response.write rsqdb("name")

set rsqdb = nothing
set conn = nothing


%>

hth.
-Doug

Old Pedant · January 14th, 2009, 05:27 PM

Code:

<%
Option Explicit
Dim rsqdb, SQL
Dim strConnection, conn
Dim raction_summary
set Conn=Server.CreateObject("ADODB.Connection")
' set rsqdb = server.CreateObject("ADODB.Recordset") ' *** SEE BELOW ***
set raction_summary =server.CreateObject("ADODB.Recordset")
Conn.open "Provider=sqloledb;Server=myserver;Initial Catalog=qdb;UID=Reports;PWD=;"
SQL = "use msdb; " _
    & " select distinct j.Name as JobName, j.description as JobDescription, " _
    &           " h.run_date as  LastStatusDate, " _
    & " ( case h.run_status  " _
    & " when 0 then 'Failed'  " _
    & " when 1 then 'Successful'  " _ 
    & " when 3 then 'Cancelled'  " _
    & " when 4 then 'In Progress'  " _
    & " else 'INVALID RUN STATUS' " _
    & " end ) as JobStatus " _
    & " from sysJobHistory h, sysJobs j " _
    & " where j.job_id = h.job_id and h.run_date =  " _
    & " (select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id) " _
    & " order by JobStatus ")

Response.Write "DEBUG SQL: " & SQL & "<HR>" & vbNewLine

set rsqdb = conn.Execute (SQL)

if rsqdb.EOF then 
    response.Write "No records returned from query"
Else
    response.write "At least on record returned; first name = " & rsqdb("name")
End If
set rsqdb = nothing
set conn = nothing
%>

(1) The line set rsqdb = server.CreateObject("ADODB.Recordset")
is a waste of code. Because you will later do
set rsqdb = conn.Execute( SQL )
which will WIPE OUT the prior reference to the created recordset. So the one created via createObject will just have to be garbage collected without ever having been used.

(2) By assigning the SQL string to a variable, first, you are able to do a DEBUG output of the SQL. Thus you can easily go test that same SQL in some DB tool (e.g, Query Analyzer).

(3) I added a semicolon after the "use msdb". Yes, I know. It shouldn't be necessary. SHOULDN'T and ISN'T are nearly synonyms. Nearly.

(4) I added an ELSE to the CASE WHEN. Just a bit of paranoia on my part. But "a little paranoia is healthy for programmers and their programs."

(5) I added a positive check for the recordset EOF along with a message indicating it happened. More paranoia.

None of my changes are necessary or even important. Just things that I've found myself doing in my old age as part of my ongoing feeling that computers are out to get me.

dalezjc · January 14th, 2009, 06:03 PM

Quote:

Originally Posted by dparsons

Not sure why that would happen. Set your code up to look like this:

hth.
-Doug

Doug,

Your code generates this:

ADODB.Recordseterror '800a0e78'
Operation is not allowed when the object is closed. /test2.asp, line 23

dalezjc · January 14th, 2009, 06:05 PM

Quote:

Originally Posted by Old Pedant

(1) The line set rsqdb = server.CreateObject("ADODB.Recordset")
is a waste of code. Because you will later do
set rsqdb = conn.Execute( SQL )
which will WIPE OUT the prior reference to the created recordset. So the one created via createObject will just have to be garbage collected without ever having been used.

(2) By assigning the SQL string to a variable, first, you are able to do a DEBUG output of the SQL. Thus you can easily go test that same SQL in some DB tool (e.g, Query Analyzer).

(3) I added a semicolon after the "use msdb". Yes, I know. It shouldn't be necessary. SHOULDN'T and ISN'T are nearly synonyms. Nearly.

(4) I added an ELSE to the CASE WHEN. Just a bit of paranoia on my part. But "a little paranoia is healthy for programmers and their programs."

(5) I added a positive check for the recordset EOF along with a message indicating it happened. More paranoia.

None of my changes are necessary or even important. Just things that I've found myself doing in my old age as part of my ongoing feeling that computers are out to get me.

Your code generates this error:

Microsoft VBScript compilation error '800a0401'
Expected end of statement
/test3.asp, line 22 & " order by JobStatus ")------------------------^

Old Pedant · January 14th, 2009, 07:12 PM

I just forgot to remove the right parenthesis from the line. It should be

Code:

    & " order by JobStatus "

*******************

But if the other code doesn't work, I don't see why this will.

WHICH is "line 23"????

By my count, in Doug's code, it would be

Code:

if not rsqdb.EOF then response.write rsqdb("name")

Yes?

In which case we can't really tell which part of that line is the culprit. So if you use my version, instead, we could narrow it down. *PROBABLY* it is the "rsqdb.EOF". And, quite frankly, if you copy/pasted Doug's code exactly, that doesn't make sense.

I could see getting an error from the CONN.EXECUTE, but not from the EOF check.

The *only* thing I can think of is that the culprit might be the
use msdb
at the head of the query. Did you try putting a semicolon after it, as I suggested???

But try my (corrected) paranoid code. Maybe we'll get more info.