Error code 80020009 "Exception Occured"

[dmiller23462]

Hey guys, I'm back again....I've got the results displaying that I
wanted but instead of having a "next" or "previous" link displayed I
am getting the error msg below (I actually get the data that I'm
supposed to get meaning that my query seems to be working fine but
instead of making its way to the INCLUDE file (bottom navigation bar)
it displays the following error msg instead of the included nav bar...

***
error '80020009'
Exception occurred.

/jax/wh/Online_Forms/Secured_Archives/search_files/search_results_hour.asp,
line 57
***

"Line 57" is the line that says

Response.Write "<b>Name :</b> " & rs("name")

(the start of my display)...

I think that it's having trouble figuring out what to do to "close up"
my recordset query....Can you take a look and let me know what I'm
doing wrong or any suggestions?

I searched thru all previous posts and couldn't find anything that
explained it sufficiently....I also found a post at <a
href="http://www.aspfaq.com/show.asp?id=2188">ASP Faq's</a> that
explained to some degree so instead of "SELECT *" I input all the
corresponding columns in the database...Still no luck....

<!-- #INCLUDE VIRTUAL="/_borders/top_nav.asp" -->
<%

Mode = request.form("mode")
Name = request.form("name")
Shift = request.form("shift")
Wave = request.form("wave")
Carton = request.form("carton")
Location = request.form("location")
License = request.form("license")
Sku = request.form("sku")
Qty = request.form("quantity")
Reason = request.form("reason")
Comments = request.form("comments")
Results = DateAdd("h", -1, Now())

'************************************************* ****************************
'* DATABASE APPENDING
*
'************************************************* ****************************
'create db connection
Set dbconn = Server.CreateObject("ADODB.Connection")

'create recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

'open db in a DSN-less method
dbconn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&
Server.MapPath("/jax/wh/Online_Forms/Secured_Archives/search_files/shortage.mdb")

If IsEmpty (Request.QueryString("pageNum")) Then
curPage = 1
Else
curPage = CInt(Request.QueryString("pageNum"))
End If

'specify more info about rs
rs.CursorLocation = 3
rs.CursorType = 3
rs.PageSize = 3
rs.open "shortage", dbconn

'sql statement to return input values drawn from html fields within
previous week
SQLqry = "SELECT name, shift, wave, carton, location, license, sku,
qty, reason, comments, date FROM shortage WHERE date >= #" & Results &
"# ORDER BY date"

'display results of statement on screen for debugging
'Response.Write "<h3><b><u>" & (SQLqry) & "</u></b></h3><br>"

rs.Close
rs.Open SQLqry

If Not rs.EOF Then
rs.AbsolutePage = curPage
Response.Write "<p>You are on page: " & curPage & " of " &
rs.PageCount
Response.Write "<br>"
Response.Write "<br>"

For i = 1 to rs.PageSize
Response.Write "<b>Name :</b> " & rs("name")
Response.Write "<br>"
Response.Write "<b>Shift :</b> " & rs("shift")
Response.Write "<br>"
Response.Write "<b>Wave Number :</b> " & rs("wave")
Response.Write "<br>"
Response.Write "<b>Carton Number :</b> " & rs("carton")
Response.Write "<br>"
Response.Write "<b>Location :</b> " & rs("location")
Response.Write "<br>"
Response.Write "<b>License :</b> " & rs("license")
Response.Write "<br>"
Response.Write "<b>SKU :</b> " & rs("sku")
Response.Write "<br>"
Response.Write "<b>Quantity :</b> " & rs("qty")
Response.Write "<br>"
Response.Write "<b>Reason :</b> " & rs("reason")
Response.Write "<br>"
Response.Write "<b>Comments :</b> " & rs("comments")
Response.Write "<br>"
Response.Write "<b>Date Submitted :</b> " & rs("date")
Response.Write "<hr>"
rs.MoveNext
Next

ElseIf rs.EOF Then
Response.Write "<h3><b>I'm sorry but no records were found matching
that description!</b></h3>"
End If

If curPage > 1 then
Response.Write "<a href='search_results_hour.asp?pageNum=" &
curPage-1 & "'>Previous</a>"
If curPage < rs.PageCount then
Response.Write " | "
end if
end if
If curPage < rs.PageCount then
Response.Write "<a href='search_results_hour.asp?pageNum=" &
curPage+1 & "'>Next</a>"
end if
'close recordset
rs.Close

'zero out recordset object
Set rs = Nothing

'smack around the db connection until it lets go
dbconn.Close

'terminate db connection with extreme prejudice
set dbconn = nothing

Response.Write "<br><a href='../Archives.asp'>Return to Archives</a>"
Response.Write "<br><a href='../../Shortage.asp'>Return to Shortage
Submission Form</a>"
Response.Write "<br><a href='../../../default.asp'>Return to Warehouse
Operations</a>"
%><!-- #INCLUDE VIRTUAL="/_borders/bottom_nav.asp" -->

[Bã§TãRÐ]

My guess is that you close the recordset before calling anything with it then you close it again at the end - get rid of the first one here

'display results of statement on screen for debugging
'Response.Write "<h3><b><u>" & (SQLqry) & "</u></b></h3><br>"

rs.Close <--- Most likely your problem
rs.Open SQLqry
On 17 Aug 2004 09:09:39 -0700, dm**********@yahoo.com (dmiller23462) wrote:

rs.Close
rs.Open SQLqry

[dmiller23462]

Bã§TãRÐ <no****@msn.com> wrote in message news:<kj********************************@4ax.com>. ..

My guess is that you close the recordset before calling anything with it then you close it again at the end - get rid of the first one here

'display results of statement on screen for debugging
'Response.Write "<h3><b><u>" & (SQLqry) & "</u></b></h3><br>"

rs.Close <--- Most likely your problem
rs.Open SQLqry
On 17 Aug 2004 09:09:39 -0700, dm**********@yahoo.com (dmiller23462) wrote:

rs.Close
rs.Open SQLqry

All advice on my previous post was very helpful....Those pages are
working now....On to another issue I have now that's confusing the
stew out of me...

Sort of off topic (error code 8002009) but still the same pages....I
have the option of listing all Access db entries in the past
month/week/day/15 minutes and then also by a specific set of standards
input by the user doing the searching (user/wave number/shift,etc)The
problem I have with THESE pages is displaying records that have been
queried....If you take a look at my code you should get a good idea of
what I'm trying to do....

For some reason the page will display the fact that I have a total of
7 pages ("You are on page 1 of 7" for instance) and the first link
will come up the way it should (exact_wave_search.asp?pageNum=2) but
when I actually click on the aforementioned link (page 2) it actually
takes me back to the main search page of "exact_wave_search.asp" (my
html form) while displaying the address as
"exact_wave_search.asp?pageNum=2"....Huh? Here's the code, hopefully
I've explained myself now....

************
Start Code *
************

<!-- #INCLUDE VIRTUAL="/_borders/top_nav.asp" -->
<%

Mode = request.form("mode")
Name = request.form("name")
Shift = request.form("shift")
Wave = request.form("wave")
Carton = request.form("carton")
Location = request.form("location")
License = request.form("license")
Sku = request.form("sku")
Qty = request.form("quantity")
Reason = request.form("reason")
Comments = request.form("comments")

Response.Write "<p><img border='0'
src='../../../../images/shortage_archives.jpg'></p>"
Response.Write "<p><img border='0'
src='../../../../images/search_by_shift.jpg'></p>"

'************************************************* ****************************
'* HTML FORM
*
'************************************************* ****************************
Sub Form()
Response.Write "<form method='POST' action='exact_shift_search.asp'
name='archive_d-or-n'><div align='left'>"
Response.Write "&nbsp;<table border='0' cellpadding='0' width='782'>"
Response.Write "<b>Shift</b> : <select name='shift'>"
Response.Write "<option>Day</option>"
Response.Write "<option>Night</option>"
Response.Write "</select></td>"
Response.Write "<input type='submit' value='Search by Shift'
name='search'>"
Response.Write "<input type='hidden' name='Mode' value='Send'>"
Response.Write "</form>"
End Sub
'************************************************* ****************************
'* END HTML FORM
*
'************************************************* ****************************

'************************************************* ****************************
'* DATABASE QUERY
*
'************************************************* ****************************
Sub Query()
If IsEmpty(Request.QueryString("pageNum")) Then
curPage = 1
Else
curPage = CInt(Request.QueryString("pageNum"))
End If

'create db connection
Set dbconn = Server.CreateObject("ADODB.Connection")

'open db in a DSN-less method
dbconn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&
Server.MapPath("/jax/wh/Online_Forms/Secured_Archives/search_files/shortage.mdb")

'create recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

'specify more info about rs
rs.CursorLocation = 3
rs.CursorType = 3
rs.PageSize = 3

'sql statement to return input values drawn from html fields within
previous week
SQLqry = "SELECT * FROM shortage WHERE shift = '" & shift & "' ORDER
BY date DESC"

'display results of statement on screen for testing purposes
'Response.Write "<h3><b><u>" & (SQLqry) & "</u></b></h3><br>"

rs.Open SQLqry, dbconn
rsPageSize = rs.PageSize

If Not rs.EOF Then
rs.AbsolutePage = curPage
Response.Write "<p>You are on page: " & curPage & " of " &
rs.PageCount
Response.Write "<br>"
Response.Write "<br>"

rsRecordsShown = 0

Do While rsRecordsShown < rsPageSize and Not rs.EOF
Response.Write "<b>Name :</b> " & rs("name")
Response.Write "<br>"
Response.Write "<b>Shift :</b> " & rs("shift")
Response.Write "<br>"
Response.Write "<b>Wave Number :</b> " & rs("wave")
Response.Write "<br>"
Response.Write "<b>Carton Number :</b> " & rs("carton")
Response.Write "<br>"
Response.Write "<b>Location :</b> " & rs("location")
Response.Write "<br>"
Response.Write "<b>License :</b> " & rs("license")
Response.Write "<br>"
Response.Write "<b>SKU :</b> " & rs("sku")
Response.Write "<br>"
Response.Write "<b>Quantity :</b> " & rs("qty")
Response.Write "<br>"
Response.Write "<b>Reason :</b> " & rs("reason")
Response.Write "<br>"
Response.Write "<b>Comments :</b> " & rs("comments")
Response.Write "<br>"
Response.Write "<b>Date Submitted :</b> " & rs("date")
Response.Write "<hr>"
rsRecordsShown = rsRecordsShown + 1
rs.MoveNext
Loop

ElseIf rs.EOF Then
Response.Write "<h3><b>I'm sorry but no records were found matching
that description!</b></h3>"
End If

If curPage > 1 then
Response.Write "<a href='exact_shift_search.asp?pageNum=" &
curPage-1 & "'>Previous</a>"
If curPage < rs.PageCount then
Response.Write " | "
end if
end if
If curPage < rs.PageCount then
Response.Write "<a href='exact_shift_search.asp?pageNum=" &
curPage+1 & "'>Next</a>"
end if

'close recordset
rs.Close

'zero out recordset object
Set rs = Nothing

'smack around the db connection until it lets go
dbconn.Close

'terminate db connection with extreme prejudice
set dbconn = nothing
End Sub
'************************************************* ****************************
'* END DATABASE QUERY
*
'************************************************* ****************************

If Mode <> "Send" Then
Call Form
End If

If Mode = "Send" Then
Call Query
End If

Response.Write "<title>Shortage Archive File</title>"

Response.Write "<br><a href='../Archives.asp'>Return to Archives</a>"
Response.Write "<br><a href='../../Shortage.asp'>Return to Shortage
Submission Form</a>"
Response.Write "<br><a href='../../../default.asp'>Return to Warehouse
Operations</a>"

%><!-- #INCLUDE VIRTUAL="/_borders/bottom_nav.asp" -->
************
End Code *
************