Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 31st, 2008, 12:06 PM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
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
Reply With Quote
  #2 (permalink)  
Old December 31st, 2008, 05:09 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
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."
===============================================
Reply With Quote
  #3 (permalink)  
Old January 14th, 2009, 10:38 AM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
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
Reply With Quote
  #4 (permalink)  
Old January 14th, 2009, 10:45 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
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."
===============================================
Reply With Quote
  #5 (permalink)  
Old January 14th, 2009, 12:58 PM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
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.
Reply With Quote
  #6 (permalink)  
Old January 14th, 2009, 03:20 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
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."
===============================================
Reply With Quote
  #7 (permalink)  
Old January 14th, 2009, 05:27 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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..
Reply With Quote
  #8 (permalink)  
Old January 14th, 2009, 06:03 PM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
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
Reply With Quote
  #9 (permalink)  
Old January 14th, 2009, 06:05 PM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
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 ")------------------------^
Reply With Quote
  #10 (permalink)  
Old January 14th, 2009, 07:12 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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.




Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:07 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.