error if db search finds nothing

[Bob Barrows [MVP]]

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

You did not read what I said. When a recordset is opened, if it contains any
records, it is already pointing at the first record. If it contains no
records, EOF will be true. Yes, BOF will also be true, but there is no need
to check for it. The only time you need to check for BOF is
a) if you delete records from the recordset (or in the case of a dynamic
cursor, a record is deleted from the database table)
b) if you attempt to MovePrevious or MoveFirst

Checking BOF immediately after opening a recordset is never necessary.
Checking BOF in a forward-only loop is never necessary. It will never be
true when moving forward.

 

[abbylee26]

ok, I found the following from a google search

Another possible reason is that you are using a stored procedure that
operates on a temp table, or does other row-affecting operation prior
to your select statement. To get around this, issue the following at
the beginning of your stored procedure:

SET NOCOUNT ON

This will prevent "(n) row(s) affected" messages from being interpreted
by the provider as a resultset.


Could this be my problem...if so how do I put this in my code?

 

[Bob Barrows [MVP]]

ok, I found the following from a google search

Another possible reason is that you are using a stored procedure that
operates on a temp table, or does other row-affecting operation prior
to your select statement. To get around this, issue the following at
the beginning of your stored procedure:

SET NOCOUNT ON

This will prevent "(n) row(s) affected" messages from being
interpreted by the provider as a resultset.


Could this be my problem

No, you are
1. not using a stored procedure
2. not using SQL Server

I am working on a reply containing code for you to test. Wait for it

 

[Bob Barrows [MVP]]

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.

Which line of code generates this error?


Let's reduce your code to its bare essentials to try and make it easier to
spot the problem. You should always start small. Don't try to do everything
at once. Create a new test page to try out the recommendations I am about to
make:

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
%>

Why do you close the script block and open a new one here?

<%
Dim TheAmount
Set TheAmount = Request.Form("thefield")

What is the purpose of this variable? It doesn't appear anywhere after this
point

sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
Name]," & _

Why return billing date and service number? You already have form variables
containing these values ...

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

Do you really need to use CDbl([Total Charge]) here? Why?
And why are you surrounding the numeric value in the comparison with quotes
(which turns it into a string)? This makes absolutely no sense.

"'"

set rs = Connect.Execute(sSQL)

End If

Why is this End If here? I don't see an If statement prior to this.

Let's simplify things now, as well as switching to using parameters (I
really can't stand dynamic sql<grin>). Here is the code for the new test
page - obviously, i have not been able to test it to verify that no typos
were committed:

<!--#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
sSQL = "SELECT [Subscriber User Name]," & _
" Fund, Account,[Activity Code], Description, [Total Charge]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = ? AND [Service Number] = ?" & _
" AND [Total Charge] = ?"

'The ?s are called parameter markers. We will use a command
'object to pass values to these markers

Dim cmd, arParms

'First create a variant array to contain the parameter values
arParms = Array(Request.Form("cmonth"),Request.Form("cnumb") _
Request.Form("camount"))
Set cmd=createobject("adodb.command")
cmd.commandtype=1 'adCmdText
cmd.commandtext=sSQL
set cmd.activeconnection=connect
set rs=cmd.execute(,arParms)

Dim arData, i,j, fld, sTitle
if not rs.eof then arData = rs.GetRows
sTitle = "<tr><th>"
for each fld in rs.Fields
sTitle=sTitle & fld.Name & "</th><th>"
next
sTitle = left(sTitle,len(sTitle)-4) & "</tr>"
rs.close: set rs = nothing
connect.close: set connect = nothing
if isArray(arData) then
response.write "<table border=""0"" align=""center""" & _
" cellpadding=""2"" cellspacing=""0""><tr>"
response.write sTitle
for i = 0 to ubound(arData,2)
response.write "<tr>"
for j = 0 to ubound(arData,1)
response.write "<td>" & arData(j,i) & "</td>"
next
response.write "</tr>"
next
response.write "</table>"
else
response.write "no records were returned"
end if
%>

See if this exhibits the same symptoms.

Bob Barrows

 

[abbylee26]

That works perfectly Bob!
Thank you all.

 

[Bob Barrows [MVP]]

That works perfectly Bob!
Thank you all.

Can you handle putting in your formatting code? And displaying the billing
date and service number?

Let us know if you need help with these things.