error if db search finds nothing

A

abbylee26

my page works fine if the db search finds at least one record that
satifies the query.
but if it does not find a match I get the following error message.

Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

What do I need to change here to make this just display the page with
no data?

sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
Name]," & _
" Fund, Account,[Activity Code], Description, [Total Charge]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("cmonth") & "'" & _
" AND [Service Number] = '" & Request.Form("cnumb") & "'" & _
" AND cstr([Total Charge]) = '" & cstr(Request.Form("camount")) & "'"
set rs = Connect.Execute(sSQL)

<%
Do until rs.eof
%>
<tr>
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Fund")%></td>
<td><%=rs("Account")%></td>
<td><%=rs("Activity Code")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("Total Charge")%></td>
</tr>
<% End If
rs.MoveNext
Loop %>
 
P

Paxton

[email protected] said:
my page works fine if the db search finds at least one record that
satifies the query.
but if it does not find a match I get the following error message.

Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

What do I need to change here to make this just display the page with
no data?

sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
Name]," & _
" Fund, Account,[Activity Code], Description, [Total Charge]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("cmonth") & "'" & _
" AND [Service Number] = '" & Request.Form("cnumb") & "'" & _
" AND cstr([Total Charge]) = '" & cstr(Request.Form("camount")) & "'"
set rs = Connect.Execute(sSQL)

<%
Do until rs.eof
%>
<tr>
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Fund")%></td>
<td><%=rs("Account")%></td>
<td><%=rs("Activity Code")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("Total Charge")%></td>
</tr>
<% End If
rs.MoveNext
Loop %>
Click to expand...

If not rs.eof then
Do until rs.eof
%>
<tr>
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Fund")%></td>
<td><%=rs("Account")%></td>
<td><%=rs("Activity Code")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("Total Charge")%></td>
</tr>
<% End If
rs.MoveNext
Loop
Else
response.write "No records to show"
End If
%>

/P.
 
P

Paxton

Paxton said:
[email protected] said:
my page works fine if the db search finds at least one record that
satifies the query.
but if it does not find a match I get the following error message.

Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

What do I need to change here to make this just display the page with
no data?

sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
Name]," & _
" Fund, Account,[Activity Code], Description, [Total Charge]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("cmonth") & "'" & _
" AND [Service Number] = '" & Request.Form("cnumb") & "'" & _
" AND cstr([Total Charge]) = '" & cstr(Request.Form("camount")) & "'"
set rs = Connect.Execute(sSQL)

<%
Do until rs.eof
%>
<tr>
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Fund")%></td>
<td><%=rs("Account")%></td>
<td><%=rs("Activity Code")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("Total Charge")%></td>
</tr>
<% End If
rs.MoveNext
Loop %>
Click to expand...

If not rs.eof then
Do until rs.eof
%>
<tr>
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Fund")%></td>
<td><%=rs("Account")%></td>
<td><%=rs("Activity Code")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("Total Charge")%></td>
</tr>
<% End If
rs.MoveNext
Loop
Else
response.write "No records to show"
End If
%>

/P.
Click to expand...

Oops. I didn't spot an odd 'End If' already in your code - just before
rs.movenext. You need to remove that - it serves no purpose that I can
see in the snippet.

/P.
 
A

abbylee26

that didn't work even after I removed my "End If"
I got the error:
Error Type:
Microsoft VBScript compilation (0x800A040E)
'loop' without 'do'
 
A

abbylee26

If not rs.eof then
Do until rs.eof
%>
<tr>
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Fund")%></td>
<td><%=rs("Account")%></td>
<td><%=rs("Activity Code")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("Total Charge")%></td>
</tr>
<%
rs.MoveNext
Loop
Else
response.write "No records to show"
End If
%>


I still get the
Error Type:
Microsoft VBScript compilation (0x800A040E)
'loop' without 'do'
 
P

Paxton

(e-mail address removed) wrote in @g14g2000cwa.googlegroups.com:
If not rs.eof then
Do until rs.eof
%>
<tr>
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Fund")%></td>
<td><%=rs("Account")%></td>
<td><%=rs("Activity Code")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("Total Charge")%></td>
</tr>
<%
rs.MoveNext
Loop
Else
response.write "No records to show"
End If
%>


I still get the
Error Type:
Microsoft VBScript compilation (0x800A040E)
'loop' without 'do'
Click to expand...

I think we need more of your code. There's something else going on. It
could be that the End If you removed closed an if statement you started
somewhere else, although the original position of it is strange. Could you
post all of it?

/P.
 
A

abbylee26

Wait...I've been cutting everything I didn't think was really part of
the code. Here's the full thing.

<%
Dim sBackgroundColor
sBackgroundColor = "#ffffff"

If not rs.eof then
Do until rs.eof

If rs("Service Number") <> "" Then
If sBackgroundColor = "#ffffff" Then
sBackgroundColor = "#f7f7f7"
Else
sBackgroundColor = "#ffffff"
End If
%>
<tr bgcolor="<%=sBackgroundColor%>">
<td class="pagefont"><%=rs("Service Number")%></td>
<td class="pagefont"><%=rs("Subscriber User Name")%></td>
<td class="pagefont"><div align="center"><%=rs("Fund")%></div></td>
<td class="pagefont"><div
align="center"><%=rs("Account")%></div></td>
<td class="pagefont"><div align="center"><%=rs("Activity
Code")%></div></td>
<td class="pagefont"><%=rs("Description")%></td>
<td class="pagefont"><div align="center"><%=rs("Total
Charge")%></div></td>
</tr>
<%
End If
rs.MoveNext
Loop
End If
%>
 
P

Paxton

(e-mail address removed) wrote in @g14g2000cwa.googlegroups.com:
Wait...I've been cutting everything I didn't think was really part of
the code. Here's the full thing.

<%
Dim sBackgroundColor
sBackgroundColor = "#ffffff"

If not rs.eof then
Do until rs.eof

If rs("Service Number") <> "" Then
If sBackgroundColor = "#ffffff" Then
sBackgroundColor = "#f7f7f7"
Else
sBackgroundColor = "#ffffff"
End If
%>
<tr bgcolor="<%=sBackgroundColor%>">
<td class="pagefont"><%=rs("Service Number")%></td>
<td class="pagefont"><%=rs("Subscriber User Name")%></td>
<td class="pagefont"><div align="center"><%=rs("Fund")%></div></td>
<td class="pagefont"><div
align="center"><%=rs("Account")%></div></td>
<td class="pagefont"><div align="center"><%=rs("Activity
Code")%></div></td>
<td class="pagefont"><%=rs("Description")%></td>
<td class="pagefont"><div align="center"><%=rs("Total
Charge")%></div></td>
</tr>
<%
End If
rs.MoveNext
Loop
End If
%>
Click to expand...

The errant End If, before rs.movenext needs to be moved under 'Loop'. so
the last 5 lines will be

<%
rs.Movenext
Loop
End if
End if

Incidentally, your logic concerning the backgound colour will always result
in a back ground with '#f7f7f7'.

/P.
 
P

Paxton

(e-mail address removed) wrote in @g14g2000cwa.googlegroups.com:


The errant End If, before rs.movenext needs to be moved under 'Loop'.
so the last 5 lines will be

<%
rs.Movenext
Loop
End if
End if
Click to expand...

Or... depending on what condition you are using to change the background
colour, that End If might belong further up:

If rs("Service Number") <> "" Then
If sBackgroundColor = "#ffffff" Then
sBackgroundColor = "#f7f7f7"
Else
sBackgroundColor = "#ffffff"
End If
End If <--- here

but your background will still always be grey. You set it to white
earlier on, then you code says, if it's white (which it is) then change
it to grey, otherwise make it white. The otherwise part of the code
will never be run.

/P.
 
A

abbylee26

<%
rs.Movenext
Loop
End if
End if

I still get the
Error Type:
Microsoft VBScript compilation (0x800A040E)
'loop' without 'do'

My background works good every other line is white, gray...always
starting with white.
 
P

Paxton

(e-mail address removed) wrote in @f14g2000cwb.googlegroups.com:
I will try this in the morning...thanks
Click to expand...

If you still have problems, post the code for the whole page. There's more
going on than the selected snippets you've shown so far.

/P.
 
D

dNagel

I believe the correct logic for recordsets is to use

While Not (rs.BOF or rs.EOF)
...
rs.movenext
Wend

D.
 
P

Paxton

A

abbylee26

The code is still having problems. If the number 0.05 is in the db and
I search for either 0.05 or .05 I get all records with that number. If
0.05 is not in the db I get the error message Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

Here is my full code:

<!--#include file="include.asp" -->
<%
Dim connect, rs
Dim sSQL

Set connect = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateOBject("ADODB.Recordset")
connect.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sTelecom
connect.open
%>
<%
Dim TheAmount
Set TheAmount = Request.Form("thefield")

sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
Name]," & _
" Fund, Account,[Activity Code], Description, [Total Charge]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("cmonth") & "'" & _
" AND [Service Number] = '" & Request.Form("cnumb") & "'" & _
" AND CDbl([Total Charge]) = '" & CDbl(Request.Form("camount")) & "'"

set rs = Connect.Execute(sSQL)

End If
%>

<html>
<head>
<title>Untitled Document</title>
<link href="../../documents.css" rel="stylesheet" type="text/css">
</head>

<body bgcolor="#ffffff">
<table border="0" align="center" cellpadding="2" cellspacing="0">
<tr>
<td colspan="7"><div align="center" class="menuHeader"><font
color="#FF0000">Statements</font></div></td>
</tr>
<tr>
<td class="menuHeader2">Billing Date</td>
<td class="menuHeader2" colspan="6"><%=rs("Billing Date")%></td>
</tr>
<tr>
<td colspan="7">&nbsp;</td>
</tr>
<tr>
<td height="20" width="60"> <nobr>
<div class="menuHeader2">Service Number &nbsp;</div>
</nobr></td>
<td width="60"> <nobr><div class="menuHeader2">Subscriber User
Name</div></nobr></td>
<td width="60"> <div class="menuHeader2">
<div align="center">Fund</div>
</div></td>
<td width="30"> <div class="menuHeader2">Account</div></td>
<td width="60"> <div class="menuHeader2">
<div align="center">Activity</div>
</div></td>
<td width="60"> <div class="menuHeader2">Description</div></td>
<td width="60"> <div class="menuHeader2">Amount</div></td>
</tr>
<%
Dim sBackgroundColor
sBackgroundColor = "#ffffff"

If not rs.eof then
Do until rs.eof

If rs("Service Number") <> "" Then
If sBackgroundColor = "#ffffff" Then
sBackgroundColor = "#f7f7f7"
Else
sBackgroundColor = "#ffffff"
End If
End If
%>
<tr bgcolor="<%=sBackgroundColor%>">
<td class="pagefont"><%=rs("Service Number")%></td>
<td class="pagefont"><%=rs("Subscriber User Name")%></td>
<td class="pagefont"><div align="center"><%=rs("Fund")%></div></td>
<td class="pagefont"><div
align="center"><%=rs("Account")%></div></td>
<td class="pagefont"><div align="center"><%=rs("Activity
Code")%></div></td>
<td class="pagefont"><%=rs("Description")%></td>
<td class="pagefont"><div align="center"><%=rs("Total
Charge")%></div></td>
</tr>
<%
rs.MoveNext
Loop
End If
%>
</table>
</body>
</html>
 
D

dNagel

You need to check for EOF or BOF... you're only checking for EOF...

Do while not (rs.eof or rs.bof)

D.


The code is still having problems. If the number 0.05 is in the db and
I search for either 0.05 or .05 I get all records with that number. If
0.05 is not in the db I get the error message Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

Here is my full code:

<!--#include file="include.asp" -->
<%
Dim connect, rs
Dim sSQL

Set connect = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateOBject("ADODB.Recordset")
connect.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sTelecom
connect.open
%>
<%
Dim TheAmount
Set TheAmount = Request.Form("thefield")

sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
Name]," & _
" Fund, Account,[Activity Code], Description, [Total Charge]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("cmonth") & "'" & _
" AND [Service Number] = '" & Request.Form("cnumb") & "'" & _
" AND CDbl([Total Charge]) = '" & CDbl(Request.Form("camount")) & "'"

set rs = Connect.Execute(sSQL)

End If
%>

<html>
<head>
<title>Untitled Document</title>
<link href="../../documents.css" rel="stylesheet" type="text/css">
</head>

<body bgcolor="#ffffff">
<table border="0" align="center" cellpadding="2" cellspacing="0">
<tr>
<td colspan="7"><div align="center" class="menuHeader"><font
color="#FF0000">Statements</font></div></td>
</tr>
<tr>
<td class="menuHeader2">Billing Date</td>
<td class="menuHeader2" colspan="6"><%=rs("Billing Date")%></td>
</tr>
<tr>
<td colspan="7">&nbsp;</td>
</tr>
<tr>
<td height="20" width="60"> <nobr>
<div class="menuHeader2">Service Number &nbsp;</div>
</nobr></td>
<td width="60"> <nobr><div class="menuHeader2">Subscriber User
Name</div></nobr></td>
<td width="60"> <div class="menuHeader2">
<div align="center">Fund</div>
</div></td>
<td width="30"> <div class="menuHeader2">Account</div></td>
<td width="60"> <div class="menuHeader2">
<div align="center">Activity</div>
</div></td>
<td width="60"> <div class="menuHeader2">Description</div></td>
<td width="60"> <div class="menuHeader2">Amount</div></td>
</tr>
<%
Dim sBackgroundColor
sBackgroundColor = "#ffffff"

If not rs.eof then
Do until rs.eof

If rs("Service Number") <> "" Then
If sBackgroundColor = "#ffffff" Then
sBackgroundColor = "#f7f7f7"
Else
sBackgroundColor = "#ffffff"
End If
End If
%>
<tr bgcolor="<%=sBackgroundColor%>">
<td class="pagefont"><%=rs("Service Number")%></td>
<td class="pagefont"><%=rs("Subscriber User Name")%></td>
<td class="pagefont"><div align="center"><%=rs("Fund")%></div></td>
<td class="pagefont"><div
align="center"><%=rs("Account")%></div></td>
<td class="pagefont"><div align="center"><%=rs("Activity
Code")%></div></td>
<td class="pagefont"><%=rs("Description")%></td>
<td class="pagefont"><div align="center"><%=rs("Total
Charge")%></div></td>
</tr>
<%
rs.MoveNext
Loop
End If
%>
</table>
</body>
</html>
Click to expand...
 
B

Bob Barrows [MVP]

This is usually not necessary in an ASP page.
In a VB application where you are likely to be navigating both forward and
backward, yes, it is a good idea to check both. In a forward-only recordset,
which is the default, I cannot think of a case where it is necessary to
check BOF.

I strongly suspect the OP's query is not retrieving any records.

Bob Barrows
dNagel said:
You need to check for EOF or BOF... you're only checking for EOF...

Do while not (rs.eof or rs.bof)

D.


The code is still having problems. If the number 0.05 is in the db
and I search for either 0.05 or .05 I get all records with that
number. If
0.05 is not in the db I get the error message Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

Here is my full code:

<!--#include file="include.asp" -->
<%
Dim connect, rs
Dim sSQL

Set connect = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateOBject("ADODB.Recordset")
connect.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sTelecom
connect.open
%>
<%
Dim TheAmount
Set TheAmount = Request.Form("thefield")

sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
Name]," & _
" Fund, Account,[Activity Code], Description, [Total Charge]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("cmonth") & "'" & _
" AND [Service Number] = '" & Request.Form("cnumb") & "'" & _
" AND CDbl([Total Charge]) = '" & CDbl(Request.Form("camount")) &
"'"

set rs = Connect.Execute(sSQL)

End If
%>

<html>
<head>
<title>Untitled Document</title>
<link href="../../documents.css" rel="stylesheet" type="text/css">
</head>

<body bgcolor="#ffffff">
<table border="0" align="center" cellpadding="2" cellspacing="0">
<tr>
<td colspan="7"><div align="center" class="menuHeader"><font
color="#FF0000">Statements</font></div></td>
</tr>
<tr>
<td class="menuHeader2">Billing Date</td>
<td class="menuHeader2" colspan="6"><%=rs("Billing Date")%></td>
</tr>
<tr>
<td colspan="7">&nbsp;</td>
</tr>
<tr>
<td height="20" width="60"> <nobr>
<div class="menuHeader2">Service Number &nbsp;</div>
</nobr></td>
<td width="60"> <nobr><div class="menuHeader2">Subscriber User
Name</div></nobr></td>
<td width="60"> <div class="menuHeader2">
<div align="center">Fund</div>
</div></td>
<td width="30"> <div class="menuHeader2">Account</div></td>
<td width="60"> <div class="menuHeader2">
<div align="center">Activity</div>
</div></td>
<td width="60"> <div class="menuHeader2">Description</div></td>
<td width="60"> <div class="menuHeader2">Amount</div></td>
</tr>
<%
Dim sBackgroundColor
sBackgroundColor = "#ffffff"

If not rs.eof then
Do until rs.eof

If rs("Service Number") <> "" Then
If sBackgroundColor = "#ffffff" Then
sBackgroundColor = "#f7f7f7"
Else
sBackgroundColor = "#ffffff"
End If
End If
%>
<tr bgcolor="<%=sBackgroundColor%>">
<td class="pagefont"><%=rs("Service Number")%></td>
<td class="pagefont"><%=rs("Subscriber User Name")%></td>
<td class="pagefont"><div
align="center"><%=rs("Fund")%></div></td> <td
class="pagefont"><div
align="center"><%=rs("Account")%></div></td>
<td class="pagefont"><div align="center"><%=rs("Activity
Code")%></div></td>
<td class="pagefont"><%=rs("Description")%></td>
<td class="pagefont"><div align="center"><%=rs("Total
Charge")%></div></td>
</tr>
<%
rs.MoveNext
Loop
End If
%>
</table>
</body>
</html>
Click to expand...
Click to expand...
 
A

abbylee26

Yes, only when there are no records to report am I getting the error
message.
I need the page to come up but empty. or maybe a message "sorry no
records"
 
D

dNagel

Bob said:
This is usually not necessary in an ASP page.
In a VB application where you are likely to be navigating both forward and
backward, yes, it is a good idea to check both. In a forward-only recordset,
which is the default, I cannot think of a case where it is necessary to
check BOF.

I strongly suspect the OP's query is not retrieving any records.
Click to expand...

Which is why it's necessary to check BOF... BOF AND EOF are true when theres
an empty recordset.

D.
 

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

mismatch error 2
Loop results almost right...help. 11
dividing a recordset up for display? 8
Where clause 1
search within a search within a search - looking for better way...my script times out 5
MS SQL ASP Update timestamp 4
Error 80004005 4
Error Problem - User requested cancel of current operation 1

Members online

No members online now.
Total: 16 (members: 0, guests: 16)
Robots: 450

Forum statistics

Threads
473,769
Messages
2,569,580
Members
45,055
Latest member
SlimSparkKetoACVReview

Latest Threads

Top