Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 31st, 2008, 12:06 PM
Authorized User
 
Join Date: Jun 2006
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default Getting Error: Item cannot be found in the collection corresponding to the requested

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
 
Old December 31st, 2008, 05:09 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
 
Old January 14th, 2009, 10:38 AM
Authorized User
 
Join Date: Jun 2006
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by dparsons View Post
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
 
Old January 14th, 2009, 10:45 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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.
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
 
Old January 14th, 2009, 12:58 PM
Authorized User
 
Join Date: Jun 2006
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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



ADODB.Recordseterror '800a0e78'Operation is not allowed when the object is closed.
 
Old January 14th, 2009, 03:20 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
 
Old January 14th, 2009, 05:27 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default A few minor points about that code...

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.


Last edited by Old Pedant; January 14th, 2009 at 05:34 PM..
 
Old January 14th, 2009, 06:03 PM
Authorized User
 
Join Date: Jun 2006
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by dparsons View Post
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
 
Old January 14th, 2009, 06:05 PM
Authorized User
 
Join Date: Jun 2006
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Old Pedant View Post
(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 January 14th, 2009, 07:12 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.









Similar Threads
Thread Thread Starter Forum Replies Last Post
"Module not found" error, need help flyfish Access 1 April 29th, 2005 06:23 AM
" Error" Page not found shoakat Classic ASP Databases 1 November 15th, 2004 03:59 AM
package not found error nightsurfer JSP Basics 8 October 13th, 2003 10:20 PM
Page Not Found Error ztz02 Classic ASP Basics 2 June 25th, 2003 08:52 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.