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

From: Gabriel Mejía (gabrielme_at_elcolombiano.com.co)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 14:00:35 -0500

thanks for your help bob.

is there any way I could see the pooling when I use your method and the
pooling when I use mine. (Can I use sql-manager or "select @@connections" to
do that)

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> escribió en el mensaje
news:uOCCUDDGFHA.428@TK2MSFTNGP15.phx.gbl...
> 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"
>
>

Loading