Re: I am having a problem in my asp in the recorsets running at iis 6.0 and sql 2000. Sometimes it return Either BOF or EOF is True with no reason. and then start working again

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 11:17:30 -0500

Please use a shorter subject line. Something like:

IIS6, SQL2000: Intermittent 'Either BOF or EOF is True' Error

More below:

Gabriel Mejía wrote:
<snip>
> ADODB.Field error '800a0bcd'
> Either BOF or EOF is True, or the current record has been deleted; the
> operation requested by the application requires a current record.
>
> this occurs for some minutes and then start woking again. I have to
> tell you that the database is complitly functional when this occurs,
> and if I do the same sql querys using query analizer, the sql server
> return valid results. The way I can get it work again manualy is by
> pressing the button "unload" at the IIS in the "home directory" tab
> at the apllication settings frame. this makes the asp work again.

This sounds as if you are failing to close and destroy your ADO objects when
finished with them.

>
> Note: (the iis is in one server and the sql is in other server)
>
>
> this is the way I connect to the database:
>
> set Recordset6 = Server.CreateObject("ADODB.Recordset")

With IIS6, the "Server." is not necessary and may impair performance.
However, it's got nothing to do with your problem.

Also: recordset6?? Are you really opening 6 recordsets on this page? This
may not be necessary, and not only could it be hurting performance, it could
also have something to do with your problem. Also, how does anyone
maintaining your code know what each recordset contains? Why not use
meaningful variable names? something like:

rsValDom

for this particular recordset?

> Recordset6.ActiveConnection = strConect

This is your problem, right here. Always use an explicit connection object.
Failure to use an explicit connection object can disable pooling
(http://support.microsoft.com/?kbid=271128) leading to problems such as the
one you are experiencing..

> sql ="SELECT sinValorDominical FROM dbo.tblMarcastarifas
> with(NOLOCK) WHERE idtipomarca=" & marcas & " and
> datIniciaVigencia<'"+cstr(month(date))+"/"+cstr(day(date))+"/"+cstr(year(dat
> e))+"' And

You should not be passing today's date to your query. Let SQL Server
calculate it itself. See below for how I would rewrite your code.

Dim cn, cmd, rsValDom, sql
Set cn = CreateObject("adodb.connection")

'hopefully strConect contains an OLE DB connection string like:
strConect = "Provider=SQLOLEDB;" & _
"Data source=your_server_name;" & _
"Initial Catalog=your_database_name;" & _
"User ID=username_not_sa;" & _
"Password=password_for_your_user"

cn.open strConect
'this connection object can be used for all the ado objects on your page.

sql ="SELECT sinValorDominical FROM dbo.tblMarcastarifas " & _
"With(NOLOCK) " & _
"WHERE idtipomarca= ? and datIniciaVigencia< GETDATE() " & _
"And datFinVigencia > GETDATE() "

Set cmd=CreateObject("adodb.command")
cmd.CommandType=1
cmd.CommandText=sql

Set cmd.ActiveConnection = cn
'The "Set" keyword in the previous statement is important

Set rsValDom = cmd.Execute(,array(marcas))
if not rsValDom.EOF then
        'process recordset
else
    'handle situation where recordset is empty
end if

'IMPORTANT
On Error Resume Next
rsValDom.close:Set rsValDom=nothing
cn.Close: Set cn = nothing

HTH,
Bob Barrows 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Relevant Pages

  • Re: Geschwindigkeit optimieren ADO
    ... öffnen mit jeweils anderem SQL String ... -SQL Server Stored Procedure mit Command Objekt aufrufen-> Recordset ... adOpenForwardOnly, adLockReadOnly, auch mal Firehose Cursor genannt. ...
    (microsoft.public.de.sqlserver)
  • Moving from Access to Server Express, adodb.recordset
    ... I've migrated into SQL Server 2005 Express from Access. ... I then use sheetID as a foreign key while adding records to a ... feed back into the recordset. ...
    (microsoft.public.sqlserver.programming)
  • Re: " Row cannot be located for updating." WHAT??
    ... when using a SQL backend, you should not attempt to UPDATE in a RECORDSET. ... Server type TIMESTAMP. ... fields of the primar key and the TIMESTAMP field to locate the record. ...
    (microsoft.public.access.formscoding)
  • filtering results by input box?
    ... anyway, i'm using SQL ... Server 2005.. ... I've connected to the DB using dreamweaver and ... sucessfully displayed a recordset on a page as follows: ...
    (comp.databases.ms-sqlserver)
  • Re: Upsizing issue
    ... > I'm trying to upsize a large Jet-based ASP application to use MS SQL ... > of a SQL join within a Recordset. ... > while when using SQL Server, there will be two objects in ...
    (microsoft.public.inetserver.asp.db)