Recordset Errors / Friendly Message needed

D

dmiller23462

My brain is nuked....Can anybody tell me right off the bat what is
wrong with this code? Along with any glaring errors, please let me
know the syntax to display a message (Response.Write would be fine I
think) that will say "I'm sorry but the data you requested cannot be
found" or something along those lines....

This code is on an archive page I have on my company's intranet....The
end result is to show 3 records at a time pulled from an Access DB and
display on the screen....If the information IS found, everything seems
to work alright but if it's not I get the error msg listed....Instead
of that error msg I'd like to get the friendly message for the user
basically saying "Sorry but your info can't be found" (see
above)....Any help/suggestions would be great....I'm fried....In the
process of trying to improve my work I broke it....I'm a newbie to ASP
so please, hook a brother up with some advice...

My error message....
*****
ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

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

My code from search_results_wave.asp....
*****
<!-- #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")
waveyear = request.form("waveyear")
wavemonth = request.form("wavemonth")
waveday = request.form("waveday")
wavenumber = request.form("wavenumber")

'*****************************************************************************
'* DATABASE APPENDING
*
'*****************************************************************************
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
rs.open "shortage", dbconn

'sql statement to return input values drawn from html fields within
previous week
SQLqry = "SELECT * FROM shortage WHERE wave = '"&waveyear& wavemonth&
waveday& wavenumber&"'"

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

'execute SQL statement
'Set db = dbconn.Execute(SQLqry)

rs.Close
rs.Open SQLqry

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
If rs.EOF Then Exit For
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

If curPage > 1 then
Response.Write "<a href='search_results_wave.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_wave.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

%>

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

Aaron [SQL Server MVP]

First, better paging techniques:

http://www.aspfaq.com/2120

Second,

set rs = conn.execute(sql)
if rs.eof then
response.write "Hi, I'm a friendly error message. You won't be
seeing any data today."
else
' do stuff
end if
 
S

Steven Burn

The error you mentioned means the record thats been requested does not exist
in the database.

All I do to get past this is use a function that queries the database for
the record # thats requested, and re-dir to a friendly "stop requesting
invalid records" page ;o)

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!
 
D

dmiller23462

How exactly can I go about doing that? I mean what is the syntax for
that option? I've been playing around with If statements and "Do Until
rs.EOF" statements and I keep breaking it....Things work great if the
data is found but if the string that is being searched for is not
found, it crashes...
 
S

Steven Burn

'// Replace the_database.mdb with the filename of your database
Const objDB = "the_Database.mdb"

Function ConnectToDB(dbPath)
'// The code you use to connect to your database goes here
Set DB = Server.Createobject("ADODB.Connection")
DB.Mode = adModeReadWrite
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" +
Server.MapPath(dbPath))
Set ConnectToDB = DB
End Function

Function RecordExists(sID)
Set DB = ConnectToDB(objDB)
Set rst = Server.CreateObject("ADODB.Recordset")
rst.open "Select ID from tblSomeTable", DB, adOpenStatic, adLockReadOnly
Do While Not rst.eof
If CLng(rst("ID"))=CLng(sID) Then
RecordExists = true
Exit Do
End If
rst.Movenext
Loop
End Function

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!
 
D

dmiller23462

I have inserted my own variables into this code and still no luck....I
kept getting an error message that it an object was
required....Tracking it down to the line containing "rs.Open SQLqry" I
think it's because my first mention of the recordset variable "rs" is
within the "Function RecordExists(sID)" code...I've tried opening and
closing it OUTSIDE of the function and it's not working....Once again,
I'm a bit frazzled (still new to me) and this is the code I currently
have...

I took the code OUTSIDE of the function....Probably not a good idea
but that was part of my experimenting....Take a look and then e-slap
me...
*****
<%

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")
waveyear = request.form("waveyear")
wavemonth = request.form("wavemonth")
waveday = request.form("waveday")
wavenumber = request.form("wavenumber")
entirewave = request.form("waveyear") & request.form("wavemonth") &
request.form("waveday") & request.form("wavenumber")

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

'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")

Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.CursorType = 3
rs.PageSize = 3
rs.open "shortage", dbconn

Do While Not rs.eof
If CLng(entirewave)=Null Then
RecordExists = false
Exit Do
End If
rs.Movenext
Loop
rs.Close

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

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

'sql statement to return input values drawn from html fields within
previous week
SQLqry = "SELECT * FROM shortage WHERE wave = '"&waveyear& wavemonth&
waveday& wavenumber&"'"

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

rs.Open SQLqry

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
If rs.EOF Then Exit For
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


If curPage > 1 then
Response.Write "<a href='search_results_wave.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_wave.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
%>

*****
 
A

Aaron [SQL Server MVP]

UGH. No wonder your brain is nuked.

Let's start here. What is the RESULT of:

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

It's great to see that you are attempting to perform some debugging, but
unless you show us what that produced, there's not much we can do.
 
S

Steven Burn

Just a guess but, I'd say the problem is your using the following;

SQLqry = "SELECT * FROM shortage WHERE wave = '"&waveyear& wavemonth&
waveday& wavenumber&"'"

Instead of first checking that the record actually exists....... (you need
to check the record actually exists, BEFORE it gets to this line)

i.e.

If RecordExists(Request.Querystring("Whatever")) Then
'// Your code goes here.....
Else
Response.Write "No record found"
Response.End
End if

I'd also advise not using "Select * FROM" and instead using "Select <table
names> FROM" (there's an ASPFAQ article on it somewhere concerning the why's
and why not's)

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error code 80020009 "Exception Occured" 2
Arrays / Tables / Anything for a "clean" display 4
SQL statement error fixed, now wrong data appending 1
Recordset Paging - Not Showing records on next page 3
Paging 3
print friendly function with asp 1
Error 80004005 4
ASP Search problem 14

Members online

No members online now.
Total: 31 (members: 1, guests: 30)
Robots: 449

Forum statistics

Threads
473,768
Messages
2,569,574
Members
45,051
Latest member
CarleyMcCr

Latest Threads

Top