BOF/EOF or "Unspecified Error", results exist

[endoflux]

I'm trying to search for/display a specific record in my Access Database, but I'm getting the following error upon posting the search.

Error Type:
Provider (0x80004005)
Unspecified error
/sterilization/update_view_sln.asp, line 10

Occasionally I'll also get an "EOF/BOF or file has been deleted" message as well.

The obvious answer is that my record doesn't exist, but I've copied the querystrings from the URL and the record I'm looking for DOES exist in the database.

URL:

//localhost/sterilization/update_view_sln.asp?ID=5AAH31&PN=05AC87

Code:

<%
Dim adoCon
Dim rsSterile2
Dim strSQL
Dim RecNum
Dim PNum
RecNum = Request.QueryString("ID")
PNum = Request.QueryString("PN")
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("sterilization.mdb")
Set rsSterile2 = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM SterilizationLog WHERE Packaging_Lot_Number = '" & PNum & "' & Sterile_Lot_Number = '" & RecNum & "'"
rsSterile2.Open strSQL, adoCon
%>

<table>
<tr>
<td valign=middle>Packaging Lot Number</td>
<td width=7></td>
<td height=40><% Response.Write(rsSterile2("Packaging_Lot_Number"))%></td>
<td width=14></td>
<td valign=middle>Sterile Lot Number</td>
<td height=40><% Response.Write(rsSterile2("Sterile_Lot_Number"))%></td>
</tr>
</table>

<form name="form" method="post" action="postupdatesterile.asp">
<table border=0>
<tr>
<td valign=middle>Quantity</td>
<td width=7></td>
<td height=40><input type="number" name="quantity" maxlength="10" value="<% = rsSterile2("Quantity") %>"></td>
</tr>

<tr>
<td valign=middle>Part Number</td>
<td width=7></td>
<td height=40><input type="text" name="partnumber" maxlength="20" value="<% = rsSterile2("Part_Number") %>"></td>
</tr>

<tr>
<td valign=middle>Reorder Number</td>
<td width=7></td>
<td height=40><input type="text" name="reordernumber" maxlength="20" value="<% = rsSterile2("Reorder_Number") %>"></td>
</tr>
</table>

<input type="submit" name="Submit" value="Submit">
</form>
</td>
</tr>
</table>
</body>
</html>

<%
rsSterile2.Close
Set rsSterile2 = Nothing
Set adoCon = Nothing
%>

 

[DotNetGnat]

you need to do this:

if rsSterile2.EOF and rsSterile2.BOF then
response.write "Sorry, No Records Found"
else
'display your form
end if

-DNG

 

[endoflux]

Did that, and it gives me "Sorry, No Records Found", even though the record exists. I tried a different record just to be sure; somehow that query isn't working.

Meanwhile, another clue to the mystery is that if I move between pages too quickly, I get that same error; it's as if Access isn't opening/closing quickly enough for it to open a new connection on the next page in time. Any idea what that's all about?

Thanks!

 

[Chopstik]

I've had instances where things didn't convert correctly in my SQL string. Try to response.write your SQL string to the screen before you attempt to retrieve your recordset just to be surer you don't have a problem. This will write it to the screen and then stop processing so you see it before it does anything else.

Set rsSterile2 = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM SterilizationLog WHERE Packaging_Lot_Number = '" & PNum & "' & Sterile_Lot_Number = '" & RecNum & "'"
[COLOR=red]response.write strSQL
response.end [/color]
rsSterile2.Open strSQL, adoCon

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Chopstik]

Sorry, meant to add that you will then want to copy the screen output to your database query (for SQL Server, this would be Query Analyzer) and then test it to make sure that it does what you expect it to do.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[DotNetGnat]

make sure that your code looks like this:

<%
Dim adoCon
Dim rsSterile2
Dim strSQL         
Dim RecNum  
Dim PNum  
RecNum = Request.QueryString("ID")
PNum = Request.QueryString("PN")
Set adoCon = Server.CreateObject("ADODB.Connection") 
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("sterilization.mdb")
Set rsSterile2 = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM SterilizationLog WHERE Packaging_Lot_Number = '" & PNum & "' & Sterile_Lot_Number = '" & RecNum & "'"
rsSterile2.Open strSQL, adoCon
[red]if rsSterile2.EOF and rsSterile2.BOF then
Response.Write "No Records Found"
else[/red]
%> 

<table>
<tr>
<td valign=middle>Packaging Lot Number</td>
<td width=7></td>
<td height=40><% Response.Write(rsSterile2("Packaging_Lot_Number"))%></td>
<td width=14></td>
<td valign=middle>Sterile Lot Number</td>
<td height=40><% Response.Write(rsSterile2("Sterile_Lot_Number"))%></td>
</tr>
</table>

<form name="form" method="post" action="postupdatesterile.asp">
<table border=0>
    <tr>
    <td valign=middle>Quantity</td>
    <td width=7></td>
    <td height=40><input type="number" name="quantity" maxlength="10" value="<% = rsSterile2("Quantity") %>"></td>
    </tr>

    <tr>
    <td valign=middle>Part Number</td>
    <td width=7></td>
    <td height=40><input type="text" name="partnumber" maxlength="20" value="<% = rsSterile2("Part_Number") %>"></td>
    </tr>

    <tr>
    <td valign=middle>Reorder Number</td>
    <td width=7></td>
    <td height=40><input type="text" name="reordernumber" maxlength="20" value="<% = rsSterile2("Reorder_Number") %>"></td>
    </tr>
    </table>

   <input type="submit" name="Submit" value="Submit">
</form>
</td>
</tr>
</table>
</body> 
</html> 

<%
[red]end if[/red]
rsSterile2.Close 
Set rsSterile2 = Nothing 
Set adoCon = Nothing 
%>

-DNG

 

[Cirrus9]

How many records are you dealing with? Have you tried to return all of the records to see if the problem lies within the querystructure or the connection/performance?
For example on a test page you can write a simple query and return all results

ex.

rs.MaxRecords=200
sql="SELECT * FROM SterilizationLog"
rs.Open sql,connrs.Close

This is a simple to locate the source of an erroras originating on the server or code side.

Hope this helps...

 

[Tarwn]

Interesting, I would think that this query would blow up when you tried to execute it:

strSQL = "SELECT * FROM SterilizationLog WHERE Packaging_Lot_Number = '" & PNum & "' [highlight]&[/highlight] Sterile_Lot_Number = '" & RecNum & "'"

I didn't realize that Access let you use an & instead of AND. Is it possible that & is allowable in Access but doesn't work quite like an "And"?

 

[endoflux]

Sorry for the delay folks; I've been on a project where I have no connection: the &/and syntax was the key here; I realized it this weekend. Like Tarwn noted, Access doesn't allow the usage of "&".

THanks all!