I am having a problem in my asp in the recorsets running at iis 6.0 and sql 2000. Sometimes it retu

Discussion in 'ASP General' started by Gabriel Mejía, Feb 21, 2005.

  1. Services or applications using ActiveX Data Objects (ADO) 2.0 or greater may
    intermittently return empty recordsets on queries that should be returning
    valid results. At the time the problem occurs, the same queries successfully
    return the expected data when run from non-ADO sources, such as from ISQL in
    Microsoft SQL Server. This problem predominantly occurs on multi-processor
    computers but has also been known to occur on single-processor computers.

    As a side effect, the following error may also occur if the application
    tries to use the empty recordset:
    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.



    Microsoft give a solution at the article Article ID : 230101



    but I did it all (I have mdac 2.8 installed and the problem still ocurrs).

    I have to tell you that this problem doesnt occurs all the time. When my asp
    aplication crashes all the recorset return the message :

    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.

    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")
    Recordset6.ActiveConnection = strConect
    sql ="SELECT sinValorDominical FROM dbo.tblMarcastarifas with(NOLOCK)
    WHERE idtipomarca=" & marcas & " and
    datIniciaVigencia<'"+cstr(month(date))+"/"+cstr(day(date))+"/"+cstr(year(dat
    e))+"' And
    datFinVigencia>'"+cstr(month(date))+"/"+cstr(day(date))+"/"+cstr(year(date))
    +"'"
    Recordset6.Source=sql
    Recordset6.CursorType = 0
    Recordset6.CursorLocation = 2
    Recordset6.LockType = 1
    Recordset6.Open
    Recordset6_numRows = 0




    Recordset6.close
    set Recordset6=nothing



    any suggestions
     
    Gabriel Mejía, Feb 21, 2005
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Feb 21, 2005
    #2
    1. Advertising

  3. Bob Barrows [MVP] wrote:
    > Please use a shorter subject line. Something like:
    > calculate it itself. See below for how I would rewrite your code.
    >

    I failed to include the advice to investigate using a stored procedure.
    Opening 6 recordsets on a page is not a good way to write code. I am
    guessing that most, if not all, of the processing you are doing in your
    vbscript code could be more efficiently done in a single stored procedure.

    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"
     
    Bob Barrows [MVP], Feb 21, 2005
    #3
  4. Bob Barrows [MVP] wrote:
    > calculate it itself. See below for how I would rewrite your code.
    >

    I failed to include the advice to investigate using a stored procedure.
    Opening 6 recordsets on a page is not a good way to write code. I am
    guessing that most, if not all, of the processing you are doing in your
    vbscript code could be more efficiently done in a single stored procedure.

    Bob Barrows
    -
     
    Bob Barrows [MVP], Feb 21, 2005
    #4
  5. 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]" <> escribió en el mensaje
    news:...
    > 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"
    >
    >
     
    Gabriel Mejía, Feb 21, 2005
    #5
  6. Gabriel Mejía wrote:
    > 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)
    >

    No. You seem to misunderstand what pooling is. Here is some info:
    http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.asp
    http://support.microsoft.com/?scid=kb;en-us;Q176056
    http://support.microsoft.com/default.aspx?scid=kb;en-us;191572
    http://support.microsoft.com/default.aspx?scid=kb;en-us;324686

    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"
     
    Bob Barrows [MVP], Feb 21, 2005
    #6
  7. Gabriel Mejía wrote:
    > 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)
    >

    Oh! I misunderstood your question. Yes, you can use SQL Profiler to check on
    pooling. Look for the execution of the sp_resetconnection procedure.
    --
    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"
     
    Bob Barrows [MVP], Feb 21, 2005
    #7
  8. again thanks


    "Bob Barrows [MVP]" <> escribió en el mensaje
    news:%...
    > Gabriel Mejía wrote:
    > > 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)
    > >

    > Oh! I misunderstood your question. Yes, you can use SQL Profiler to check

    on
    > pooling. Look for the execution of the sp_resetconnection procedure.
    > --
    > 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"
    >
    >
     
    Gabriel Mejía, Feb 21, 2005
    #8
  9. Bob Barrows [MVP] wrote:
    > Gabriel Mejía wrote:
    >> 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)
    >>

    > Oh! I misunderstood your question. Yes, you can use SQL Profiler to
    > check on pooling. Look for the execution of the sp_resetconnection
    > procedure.

    Correction: sp_reset_connection
    --
    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"
     
    Bob Barrows [MVP], Feb 21, 2005
    #9
  10. hi bob


    exec sp_reset_connection
    does this has parameters?.

    there is no help about it in sql books

    it says
    Server: Msg 208, Level 16, State 9, Procedure sp_reset_connection, Line 1
    Invalid object name 'sp_reset_connection'.




    "Bob Barrows [MVP]" <> escribió en el mensaje
    news:%...
    > Bob Barrows [MVP] wrote:
    > > Gabriel Mejía wrote:
    > >> 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)
    > >>

    > > Oh! I misunderstood your question. Yes, you can use SQL Profiler to
    > > check on pooling. Look for the execution of the sp_resetconnection
    > > procedure.

    > Correction: sp_reset_connection
    > --
    > 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"
    >
    >
     
    Gabriel Mejía, Feb 22, 2005
    #10
  11. I did not intend for you to run this procedure (which is not recommended
    since it is an internal system procedure). My suggestion was for you to use
    SQL Profiler to monitor for its use to detect whether or not pooling is
    being used.

    Pooling can be controlled only at the client (in this case, the web server).
    It cannot be controlled by the server. If pooling is not disabled, this
    procedure, which is used to implement pooling at the server, should appear
    in a Profiler trace. Or at least, so I've been told. Another way to
    determine if pooling is being used is to load a page that connects to the
    server, look for the new connection using sp_who2, close the page, run
    sp_who2 again to see if the spid goes away or persists for 60 seconds as it
    would if pooling is being used. Just be aware that the bad practices I
    talked about may disable pooling intermittently, so you may need to monitor
    this for a while under various conditions to see if pooling gets disabled.

    Pooling is turned on by default in ASP. It can be turned off, either
    intentionally as described in the articles I provided, or unintentionally
    via the use of bad programming practices. The suggestions I made for your
    code will not only help with pooling, they will also help with the overall
    efficiency and security of your asp pages. I realize you may be looking at a
    large job here, but you should not be looking for excuses to avoid that job.
    Some of the problems in your code are leaving your site extremely vulnerable
    to being hacked. See these links about sql injection:

    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

    HTH,
    Bob Barrows

    Gabriel Mejía wrote:
    > hi bob
    >
    >
    > exec sp_reset_connection
    > does this has parameters?.
    >
    > there is no help about it in sql books
    >
    > it says
    > Server: Msg 208, Level 16, State 9, Procedure sp_reset_connection,
    > Line 1 Invalid object name 'sp_reset_connection'.
    >
    >

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Feb 22, 2005
    #11
  12. I am going to do this changes in some of my asp. If this works in those asp
    and it doesnt crash any more in them, I am going to do than in the rest of
    them

    any way, this I just asked you is only to prove my boss the problem.

    thnks again bob


    "Bob Barrows [MVP]" <> escribió en el mensaje
    news:...
    > I did not intend for you to run this procedure (which is not recommended
    > since it is an internal system procedure). My suggestion was for you to

    use
    > SQL Profiler to monitor for its use to detect whether or not pooling is
    > being used.
    >
    > Pooling can be controlled only at the client (in this case, the web

    server).
    > It cannot be controlled by the server. If pooling is not disabled, this
    > procedure, which is used to implement pooling at the server, should appear
    > in a Profiler trace. Or at least, so I've been told. Another way to
    > determine if pooling is being used is to load a page that connects to the
    > server, look for the new connection using sp_who2, close the page, run
    > sp_who2 again to see if the spid goes away or persists for 60 seconds as

    it
    > would if pooling is being used. Just be aware that the bad practices I
    > talked about may disable pooling intermittently, so you may need to

    monitor
    > this for a while under various conditions to see if pooling gets disabled.
    >
    > Pooling is turned on by default in ASP. It can be turned off, either
    > intentionally as described in the articles I provided, or unintentionally
    > via the use of bad programming practices. The suggestions I made for your
    > code will not only help with pooling, they will also help with the overall
    > efficiency and security of your asp pages. I realize you may be looking at

    a
    > large job here, but you should not be looking for excuses to avoid that

    job.
    > Some of the problems in your code are leaving your site extremely

    vulnerable
    > to being hacked. See these links about sql injection:
    >
    > http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    > http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    > http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    > http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
    >
    > HTH,
    > Bob Barrows
    >
    > Gabriel Mejía wrote:
    > > hi bob
    > >
    > >
    > > exec sp_reset_connection
    > > does this has parameters?.
    > >
    > > there is no help about it in sql books
    > >
    > > it says
    > > Server: Msg 208, Level 16, State 9, Procedure sp_reset_connection,
    > > Line 1 Invalid object name 'sp_reset_connection'.
    > >
    > >

    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    Gabriel Mejía, Feb 22, 2005
    #12
  13. bob.


    I just modified many asp and the way you told me and my web site just
    crash again some minutes ago.

    the next is the asp modified


    Note: (I know that the name of the recodrset is not the most elegant. The
    name of the ONLY recordset is "Recordset14" as you can see)


    <%


    If session("allow") = "" Then
    response.redirect("ingresoperador.asp")
    end if
    response.expires=0
    Response.Buffer = True
    Response.AddHeader "Pragma", "no-cache"
    Response.AddHeader "cache-control", "no-store"


    '----------------llama el cookie
    nombreoficina=Request.Cookies("equipo")("oficina")
    nombreequipo=Request.Cookies("equipo")("nombre")
    conectacadena="Driver={SQL
    Server};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
    ssion("clavesgp")+";pwd="+session("passsgp")


    '-------------------------objeto conexion


    Set cn = CreateObject("adodb.connection")
    cn.open = conectacadena



    '---------------------------------------busco el codigo de la oficina que
    corresponde al nombre
    sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre='" + nombreoficina +
    "'"
    Set cmd=CreateObject("adodb.command")
    cmd.CommandType=1
    cmd.CommandText=sql14
    Set cmd.ActiveConnection = cn


    set Recordset14 = Server.CreateObject("ADODB.Recordset")
    Recordset14=cmd.Execute()

    '----------------------------------en las siguientes lineas se almcenan las
    rutas donde iria a quedar los
    ' archivos txt con los comprobantes de
    impresion y los archivos adjuntos.
    session("strRutaComprobantes")=Recordset14("strRutaComprobantes")
    session("strRutaAdjuntos")=Recordset14("strRutaAdjuntos")

    'Recordset14.close
    set Recordset14=nothing
    cn.Close
    Set cn = nothing
    %>
    <%

    session("dtmfechaaviso")=""
    session("numdias")=""
    session("tipoclasificado")=""
    session("edicion")=""
    session("publicacion")=""
    session("subdivision")=""
    session("idTipoProducto")=""
    session("idColor")=""
    session("idModulo")=""
    session("idTipoEdicion")=""
    Session("nombrev")= ""
    Session("numeroaviso")=""
    Session("nitv")=""
    Session("Terminal")=""
    Session("Oficina")=""
    Session("Seccion")=""
    Session("idSubSeccion")=""
    Session("idTipoMarca")=""
    Session("fondo")=""
    Session("borde")=""
    Session("titulo")=""
    Session("textoTitulo")=""
    session("fechas")=""
    session("diainicial")=""
    nitanterior=Request.QueryString("nitanterior")
    telefonoanterior=Request.QueryString("telefonoanterior")
    nombreanterior=Request.QueryString("nombreanterior")
    direccionanterior=Request.QueryString("direccionanterior")
    nombre2anterior=Request.QueryString("nombre2anterior")
    %>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <frameset cols="62%,*" frameborder="NO" border="0" framespacing="0">
    <frame name="mainFrame" scrolling="NO"
    src="datos%20cliente.asp?nitanterior=<%=nitanterior%>&telefonoanterior=<%=te
    lefonoanterior%>&nombreanterior=<%=nombreanterior%>&direccionanterior=<%=dir
    eccionanterior%>&nombre2anterior=<%=nombre2anterior%>">
    <frame name="rightFrame" scrolling="NO" noresize src="eleccion1.asp">
    </frameset>
    <noframes><body bgcolor="#FFFFFF">

    </body></noframes>
    </html>
     
    Gabriel Mejía, Feb 22, 2005
    #13
  14. this was the error

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


    "Gabriel Mejía" <> escribió en el mensaje
    news:u58ef$...
    > bob.
    >
    >
    > I just modified many asp and the way you told me and my web site just
    > crash again some minutes ago.
    >
    > the next is the asp modified
    >
    >
    > Note: (I know that the name of the recodrset is not the most elegant. The
    > name of the ONLY recordset is "Recordset14" as you can see)
    >
    >
    > <%
    >
    >
    > If session("allow") = "" Then
    > response.redirect("ingresoperador.asp")
    > end if
    > response.expires=0
    > Response.Buffer = True
    > Response.AddHeader "Pragma", "no-cache"
    > Response.AddHeader "cache-control", "no-store"
    >
    >
    > '----------------llama el cookie
    > nombreoficina=Request.Cookies("equipo")("oficina")
    > nombreequipo=Request.Cookies("equipo")("nombre")
    > conectacadena="Driver={SQL
    >

    Server};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
    > ssion("clavesgp")+";pwd="+session("passsgp")
    >
    >
    > '-------------------------objeto conexion
    >
    >
    > Set cn = CreateObject("adodb.connection")
    > cn.open = conectacadena
    >
    >
    >
    > '---------------------------------------busco el codigo de la oficina que
    > corresponde al nombre
    > sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre='" + nombreoficina +
    > "'"
    > Set cmd=CreateObject("adodb.command")
    > cmd.CommandType=1
    > cmd.CommandText=sql14
    > Set cmd.ActiveConnection = cn
    >
    >
    > set Recordset14 = Server.CreateObject("ADODB.Recordset")
    > Recordset14=cmd.Execute()
    >
    > '----------------------------------en las siguientes lineas se almcenan

    las
    > rutas donde iria a quedar los
    > ' archivos txt con los comprobantes de
    > impresion y los archivos adjuntos.
    > session("strRutaComprobantes")=Recordset14("strRutaComprobantes")
    > session("strRutaAdjuntos")=Recordset14("strRutaAdjuntos")
    >
    > 'Recordset14.close
    > set Recordset14=nothing
    > cn.Close
    > Set cn = nothing
    > %>
    > <%
    >
    > session("dtmfechaaviso")=""
    > session("numdias")=""
    > session("tipoclasificado")=""
    > session("edicion")=""
    > session("publicacion")=""
    > session("subdivision")=""
    > session("idTipoProducto")=""
    > session("idColor")=""
    > session("idModulo")=""
    > session("idTipoEdicion")=""
    > Session("nombrev")= ""
    > Session("numeroaviso")=""
    > Session("nitv")=""
    > Session("Terminal")=""
    > Session("Oficina")=""
    > Session("Seccion")=""
    > Session("idSubSeccion")=""
    > Session("idTipoMarca")=""
    > Session("fondo")=""
    > Session("borde")=""
    > Session("titulo")=""
    > Session("textoTitulo")=""
    > session("fechas")=""
    > session("diainicial")=""
    > nitanterior=Request.QueryString("nitanterior")
    > telefonoanterior=Request.QueryString("telefonoanterior")
    > nombreanterior=Request.QueryString("nombreanterior")
    > direccionanterior=Request.QueryString("direccionanterior")
    > nombre2anterior=Request.QueryString("nombre2anterior")
    > %>
    > <head>
    > <title>Untitled Document</title>
    > <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    > </head>
    > <frameset cols="62%,*" frameborder="NO" border="0" framespacing="0">
    > <frame name="mainFrame" scrolling="NO"
    >

    src="datos%20cliente.asp?nitanterior=<%=nitanterior%>&telefonoanterior=<%=te
    >

    lefonoanterior%>&nombreanterior=<%=nombreanterior%>&direccionanterior=<%=dir
    > eccionanterior%>&nombre2anterior=<%=nombre2anterior%>">
    > <frame name="rightFrame" scrolling="NO" noresize src="eleccion1.asp">
    > </frameset>
    > <noframes><body bgcolor="#FFFFFF">
    >
    > </body></noframes>
    > </html>
    >
    >
     
    Gabriel Mejía, Feb 22, 2005
    #14
  15. Gabriel Mejía wrote:
    > bob.
    >
    > conectacadena="Driver={SQL
    >

    Server};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
    > ssion("clavesgp")+";pwd="+session("passsgp")


    No, use SQLOLEDB.

    conectacadena="Provider=SQLOLEDB;" & _
    "Data Source=" & session("SGP") & ";" & _
    "Initial Catalog=" & session("Basedatos") & ";" & _
    "User ID=" & session("clavesgp") & ";" & _
    "Password=" & session("passsgp")

    >
    >
    > '-------------------------objeto conexion
    >
    >
    > Set cn = CreateObject("adodb.connection")
    > cn.open = conectacadena
    >
    >
    >
    > '---------------------------------------busco el codigo de la oficina
    > que corresponde al nombre
    > sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre='" +
    > nombreoficina + "'"


    No, use parameters. That's the whole point of using a Command object:

    sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre=?"


    > Set cmd=CreateObject("adodb.command")
    > cmd.CommandType=1
    > cmd.CommandText=sql14
    > Set cmd.ActiveConnection = cn
    >
    >
    > set Recordset14 = Server.CreateObject("ADODB.Recordset")


    You're missing the "Set" keyword in the following statement. ALWAYS use
    "Set" when dealing with object variables.
    > Recordset14=cmd.Execute()


    This statement should be:

    Set Recordset14=cmd.Execute(,array(nombreoficina))

    Then, don't try to read data from the recordset without checking its EOF
    property:

    If not Recordset14.EOF then

    >
    > '----------------------------------en las siguientes lineas se
    > almcenan las rutas donde iria a quedar los
    > ' archivos txt con los comprobantes
    > de impresion y los archivos adjuntos.
    > session("strRutaComprobantes")=Recordset14("strRutaComprobantes")
    > session("strRutaAdjuntos")=Recordset14("strRutaAdjuntos")


    Else
    Response.Write "The recordset was empty"
    End if
    >
    > 'Recordset14.close
    > set Recordset14=nothing
    > cn.Close
    > Set cn = nothing
    > %>
    > <%
    >


    If the recordset was empty when you think it should contain data, verify
    that nombreoficina contains the data you think it contains, by using
    Response.Write nombreoficina

    For further debugging, use SQL Profiler to trace the commands sent to your
    SQL Server.

    HTH,
    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Feb 22, 2005
    #15
  16. no. it has data

    it works all the day with data. this code is just one that crashes. the
    problem happen in all the asps. not just the one I put here.

    I did the response.write thing a long time ago and is shows data. the
    problem is not the query. I am sure of that because I did the response.write
    of the query itself and paste it in the query analyser with valid results.

    note: I was wrong in something. I am working with IIS 5



    "Bob Barrows [MVP]" <> escribió en el mensaje
    news:...
    > Gabriel Mejía wrote:
    > > bob.
    > >
    > > conectacadena="Driver={SQL
    > >

    >

    Server};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
    > > ssion("clavesgp")+";pwd="+session("passsgp")

    >
    > No, use SQLOLEDB.
    >
    > conectacadena="Provider=SQLOLEDB;" & _
    > "Data Source=" & session("SGP") & ";" & _
    > "Initial Catalog=" & session("Basedatos") & ";" & _
    > "User ID=" & session("clavesgp") & ";" & _
    > "Password=" & session("passsgp")
    >
    > >
    > >
    > > '-------------------------objeto conexion
    > >
    > >
    > > Set cn = CreateObject("adodb.connection")
    > > cn.open = conectacadena
    > >
    > >
    > >
    > > '---------------------------------------busco el codigo de la oficina
    > > que corresponde al nombre
    > > sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre='" +
    > > nombreoficina + "'"

    >
    > No, use parameters. That's the whole point of using a Command object:
    >
    > sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre=?"
    >
    >
    > > Set cmd=CreateObject("adodb.command")
    > > cmd.CommandType=1
    > > cmd.CommandText=sql14
    > > Set cmd.ActiveConnection = cn
    > >
    > >
    > > set Recordset14 = Server.CreateObject("ADODB.Recordset")

    >
    > You're missing the "Set" keyword in the following statement. ALWAYS use
    > "Set" when dealing with object variables.
    > > Recordset14=cmd.Execute()

    >
    > This statement should be:
    >
    > Set Recordset14=cmd.Execute(,array(nombreoficina))
    >
    > Then, don't try to read data from the recordset without checking its EOF
    > property:
    >
    > If not Recordset14.EOF then
    >
    > >
    > > '----------------------------------en las siguientes lineas se
    > > almcenan las rutas donde iria a quedar los
    > > ' archivos txt con los comprobantes
    > > de impresion y los archivos adjuntos.
    > > session("strRutaComprobantes")=Recordset14("strRutaComprobantes")
    > > session("strRutaAdjuntos")=Recordset14("strRutaAdjuntos")

    >
    > Else
    > Response.Write "The recordset was empty"
    > End if
    > >
    > > 'Recordset14.close
    > > set Recordset14=nothing
    > > cn.Close
    > > Set cn = nothing
    > > %>
    > > <%
    > >

    >
    > If the recordset was empty when you think it should contain data, verify
    > that nombreoficina contains the data you think it contains, by using
    > Response.Write nombreoficina
    >
    > For further debugging, use SQL Profiler to trace the commands sent to your
    > SQL Server.
    >
    > HTH,
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    Gabriel Mejía, Feb 22, 2005
    #16
  17. I can replicate the crashes of my web site, and that is when I run a
    desktop visual basic application. this apllication works fine but has a long
    transaction that blocks the sql database. After that aplication runs, the
    web page crashes. Note that this is not the only moment when my page
    crashes, so the solution is not to quit that aplication. besides that
    aplications runs only ones a week.


    "Gabriel Mejía" <> escribió en el mensaje
    news:OzfB%...
    > no. it has data
    >
    > it works all the day with data. this code is just one that crashes. the
    > problem happen in all the asps. not just the one I put here.
    >
    > I did the response.write thing a long time ago and is shows data. the
    > problem is not the query. I am sure of that because I did the

    response.write
    > of the query itself and paste it in the query analyser with valid results.
    >
    > note: I was wrong in something. I am working with IIS 5
    >
    >
    >
    > "Bob Barrows [MVP]" <> escribió en el mensaje
    > news:...
    > > Gabriel Mejía wrote:
    > > > bob.
    > > >
    > > > conectacadena="Driver={SQL
    > > >

    > >

    >

    Server};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
    > > > ssion("clavesgp")+";pwd="+session("passsgp")

    > >
    > > No, use SQLOLEDB.
    > >
    > > conectacadena="Provider=SQLOLEDB;" & _
    > > "Data Source=" & session("SGP") & ";" & _
    > > "Initial Catalog=" & session("Basedatos") & ";" & _
    > > "User ID=" & session("clavesgp") & ";" & _
    > > "Password=" & session("passsgp")
    > >
    > > >
    > > >
    > > > '-------------------------objeto conexion
    > > >
    > > >
    > > > Set cn = CreateObject("adodb.connection")
    > > > cn.open = conectacadena
    > > >
    > > >
    > > >
    > > > '---------------------------------------busco el codigo de la oficina
    > > > que corresponde al nombre
    > > > sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre='" +
    > > > nombreoficina + "'"

    > >
    > > No, use parameters. That's the whole point of using a Command object:
    > >
    > > sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre=?"
    > >
    > >
    > > > Set cmd=CreateObject("adodb.command")
    > > > cmd.CommandType=1
    > > > cmd.CommandText=sql14
    > > > Set cmd.ActiveConnection = cn
    > > >
    > > >
    > > > set Recordset14 = Server.CreateObject("ADODB.Recordset")

    > >
    > > You're missing the "Set" keyword in the following statement. ALWAYS use
    > > "Set" when dealing with object variables.
    > > > Recordset14=cmd.Execute()

    > >
    > > This statement should be:
    > >
    > > Set Recordset14=cmd.Execute(,array(nombreoficina))
    > >
    > > Then, don't try to read data from the recordset without checking its EOF
    > > property:
    > >
    > > If not Recordset14.EOF then
    > >
    > > >
    > > > '----------------------------------en las siguientes lineas se
    > > > almcenan las rutas donde iria a quedar los
    > > > ' archivos txt con los comprobantes
    > > > de impresion y los archivos adjuntos.
    > > > session("strRutaComprobantes")=Recordset14("strRutaComprobantes")
    > > > session("strRutaAdjuntos")=Recordset14("strRutaAdjuntos")

    > >
    > > Else
    > > Response.Write "The recordset was empty"
    > > End if
    > > >
    > > > 'Recordset14.close
    > > > set Recordset14=nothing
    > > > cn.Close
    > > > Set cn = nothing
    > > > %>
    > > > <%
    > > >

    > >
    > > If the recordset was empty when you think it should contain data, verify
    > > that nombreoficina contains the data you think it contains, by using
    > > Response.Write nombreoficina
    > >
    > > For further debugging, use SQL Profiler to trace the commands sent to

    your
    > > SQL Server.
    > >
    > > HTH,
    > > Bob Barrows
    > > --
    > > Microsoft MVP -- ASP/ASP.NET
    > > Please reply to the newsgroup. The email account listed in my From
    > > header is my spam trap, so I don't check it very often. You will get a
    > > quicker response by posting to the newsgroup.
    > >
    > >

    >
    >
     
    Gabriel Mejía, Feb 22, 2005
    #17
  18. You've lost me. It sounds as if you have more problems than your asp code if
    you have blocking occurring on your SQL Server. That long transaction needs
    to be addressed.



    Gabriel Mejía wrote:
    > I can replicate the crashes of my web site, and that is when I run a
    > desktop visual basic application. this apllication works fine but has
    > a long transaction that blocks the sql database. After that
    > aplication runs, the web page crashes. Note that this is not the
    > only moment when my page crashes, so the solution is not to quit that
    > aplication. besides that aplications runs only ones a week.
    >
    >
    > "Gabriel Mejía" <> escribió en el mensaje
    > news:OzfB%...
    >> no. it has data
    >>
    >> it works all the day with data. this code is just one that crashes.
    >> the problem happen in all the asps. not just the one I put here.
    >>
    >> I did the response.write thing a long time ago and is shows data. the
    >> problem is not the query. I am sure of that because I did the
    >> response.write of the query itself and paste it in the query
    >> analyser with valid results.
    >>
    >> note: I was wrong in something. I am working with IIS 5
    >>
    >>
    >>
    >> "Bob Barrows [MVP]" <> escribió en el mensaje
    >> news:...
    >>> Gabriel Mejía wrote:
    >>>> bob.
    >>>>
    >>>> conectacadena="Driver={SQL
    >>>>
    >>>

    >>

    >

    Server};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
    >>>> ssion("clavesgp")+";pwd="+session("passsgp")
    >>>
    >>> No, use SQLOLEDB.
    >>>
    >>> conectacadena="Provider=SQLOLEDB;" & _
    >>> "Data Source=" & session("SGP") & ";" & _
    >>> "Initial Catalog=" & session("Basedatos") & ";" & _
    >>> "User ID=" & session("clavesgp") & ";" & _
    >>> "Password=" & session("passsgp")
    >>>
    >>>>
    >>>>
    >>>> '-------------------------objeto conexion
    >>>>
    >>>>
    >>>> Set cn = CreateObject("adodb.connection")
    >>>> cn.open = conectacadena
    >>>>
    >>>>
    >>>>
    >>>> '---------------------------------------busco el codigo de la
    >>>> oficina que corresponde al nombre
    >>>> sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre='" +
    >>>> nombreoficina + "'"
    >>>
    >>> No, use parameters. That's the whole point of using a Command
    >>> object:
    >>>
    >>> sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre=?"
    >>>
    >>>
    >>>> Set cmd=CreateObject("adodb.command")
    >>>> cmd.CommandType=1
    >>>> cmd.CommandText=sql14
    >>>> Set cmd.ActiveConnection = cn
    >>>>
    >>>>
    >>>> set Recordset14 = Server.CreateObject("ADODB.Recordset")
    >>>
    >>> You're missing the "Set" keyword in the following statement. ALWAYS
    >>> use "Set" when dealing with object variables.
    >>>> Recordset14=cmd.Execute()
    >>>
    >>> This statement should be:
    >>>
    >>> Set Recordset14=cmd.Execute(,array(nombreoficina))
    >>>
    >>> Then, don't try to read data from the recordset without checking
    >>> its EOF property:
    >>>
    >>> If not Recordset14.EOF then
    >>>
    >>>>
    >>>> '----------------------------------en las siguientes lineas se
    >>>> almcenan las rutas donde iria a quedar los
    >>>> ' archivos txt con los
    >>>> comprobantes de impresion y los archivos adjuntos.
    >>>> session("strRutaComprobantes")=Recordset14("strRutaComprobantes")
    >>>> session("strRutaAdjuntos")=Recordset14("strRutaAdjuntos")
    >>>
    >>> Else
    >>> Response.Write "The recordset was empty"
    >>> End if
    >>>>
    >>>> 'Recordset14.close
    >>>> set Recordset14=nothing
    >>>> cn.Close
    >>>> Set cn = nothing
    >>>> %>
    >>>> <%
    >>>>
    >>>
    >>> If the recordset was empty when you think it should contain data,
    >>> verify that nombreoficina contains the data you think it contains,
    >>> by using Response.Write nombreoficina
    >>>
    >>> For further debugging, use SQL Profiler to trace the commands sent
    >>> to your SQL Server.
    >>>
    >>> HTH,
    >>> Bob Barrows
    >>> --
    >>> Microsoft MVP -- ASP/ASP.NET
    >>> Please reply to the newsgroup. The email account listed in my From
    >>> header is my spam trap, so I don't check it very often. You will
    >>> get a quicker response by posting to the newsgroup.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Feb 22, 2005
    #18
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Saunders
    Replies:
    0
    Views:
    471
    John Saunders
    Aug 28, 2003
  2. Nehmo Sergheyev
    Replies:
    1
    Views:
    508
    Andrew Urquhart
    May 9, 2004
  3. C Did
    Replies:
    3
    Views:
    3,970
    Chris Lithgow
    Jun 20, 2006
  4. Replies:
    1
    Views:
    506
    gkelly
    Nov 29, 2006
  5. Kamyk
    Replies:
    2
    Views:
    183
    Thomas 'PointedEars' Lahn
    May 8, 2005
Loading...

Share This Page