0x800A0E7D error--don't get it

Discussion in 'ASP General' started by middletree, Jan 7, 2004.

  1. middletree

    middletree Guest

    I had some text links at the top of all my pages (in one include file),
    which worked just fine. But I was asked to make it so that people in a
    certain department, (this is an Intranet app) would see a particular link
    that nobody else would. You'd find their name by getting their network
    logon. This worked fine on all my pages, but for some reason, on one page,
    it gives me an error. First, the error:

    Error Type:
    ADODB.Recordset (0x800A0E7D)
    The connection cannot be used to perform this operation. It is either closed
    or invalid in this context.
    /includes/header.inc, line 32

    Before we go any farther, please note that I went to ASPFAQ.com, and
    couldn't find an answer there which fit my situation. The article that
    seemed to have the best shot for me was
    http://www.aspfaq.com/show.asp?id=2191, but no dice.

    Anyway, here's my code:

    'this first line is there because when you get the network logon name, it is
    prefixed by the domain name and a backslash. Since the db I am pulling data
    from has only the logon name, I had to get rid of the first part:

    strLogon= Replace(Request.ServerVariables("LOGON_USER"),"domainname\","")
    Set RSLogon = Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT EmployeeID, DepartmentID "
    strSQL = strSQL & "FROM Employee "
    strSQL = strSQL & "WHERE NetworkID = '"&strLogon&"'"
    'this next line is the infamous line 32
    RSLogon.Open strSQL, objConnection
    objConnection.execute strSQL,,&h00000080

    strDepartmentID = RSLogon("DepartmentID")
    strEmployeeID = RSLogon("EmployeeID")
    RSLogon.Close
    Set RSLogon = Nothing

    If strDepartmentID = "9" then%>
    do some stuff
    End if



    Keep in mind that this code works fine on other pages, just not this one. In
    fact, it works when you first bring this page up, but it does submit to
    itself, and when it does, you should see a "thank-you"-type message, but get
    the error instead. But in the code at the top of the page, before it decides
    if it's the original page or the repost, I have the same includes which
    establish the same db connection. I mention this because the first thing
    you look for is the difference between the pages that work and the ones that
    don't. In fact, this code is in an include file which is used on all pages,
    and that rules out any differences in code, you'd think.
     
    middletree, Jan 7, 2004
    #1
    1. Advertising

  2. middletree

    Mark Schupp Guest

    First, you do not seem to be opening a database connection.
    Second, you seem to be executing the query twice.
    Third, you are not checking to see if the query returns anything before
    attempting to access column data.
    Fourth, you should not explicitly create a recordset for that type of query.
    Try:

    strSQL = "SELECT EmployeeID, DepartmentID "
    strSQL = strSQL & "FROM Employee "
    strSQL = strSQL & "WHERE NetworkID = '"&strLogon&"'"
    Set RSLogon = objConnection.execute(strSQL,,1)
    If Not RSLogon.EOF Then
    strDepartmentID = RSLogon("DepartmentID")
    strEmployeeID = RSLogon("EmployeeID")
    End If
    RSLogon.Close
    Set RSLogon = Nothing


    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com


    "middletree" <> wrote in message
    news:...
    > I had some text links at the top of all my pages (in one include file),
    > which worked just fine. But I was asked to make it so that people in a
    > certain department, (this is an Intranet app) would see a particular link
    > that nobody else would. You'd find their name by getting their network
    > logon. This worked fine on all my pages, but for some reason, on one

    page,
    > it gives me an error. First, the error:
    >
    > Error Type:
    > ADODB.Recordset (0x800A0E7D)
    > The connection cannot be used to perform this operation. It is either

    closed
    > or invalid in this context.
    > /includes/header.inc, line 32
    >
    > Before we go any farther, please note that I went to ASPFAQ.com, and
    > couldn't find an answer there which fit my situation. The article that
    > seemed to have the best shot for me was
    > http://www.aspfaq.com/show.asp?id=2191, but no dice.
    >
    > Anyway, here's my code:
    >
    > 'this first line is there because when you get the network logon name, it

    is
    > prefixed by the domain name and a backslash. Since the db I am pulling

    data
    > from has only the logon name, I had to get rid of the first part:
    >
    > strLogon= Replace(Request.ServerVariables("LOGON_USER"),"domainname\","")


    > Set RSLogon = Server.CreateObject("ADODB.Recordset")
    > strSQL = "SELECT EmployeeID, DepartmentID "
    > strSQL = strSQL & "FROM Employee "
    > strSQL = strSQL & "WHERE NetworkID = '"&strLogon&"'"
    > 'this next line is the infamous line 32
    > RSLogon.Open strSQL, objConnection
    > objConnection.execute strSQL,,&h00000080
    >
    > strDepartmentID = RSLogon("DepartmentID")
    > strEmployeeID = RSLogon("EmployeeID")
    > RSLogon.Close
    > Set RSLogon = Nothing
    >
    > If strDepartmentID = "9" then%>
    > do some stuff
    > End if
    >
    >
    >
    > Keep in mind that this code works fine on other pages, just not this one.

    In
    > fact, it works when you first bring this page up, but it does submit to
    > itself, and when it does, you should see a "thank-you"-type message, but

    get
    > the error instead. But in the code at the top of the page, before it

    decides
    > if it's the original page or the repost, I have the same includes which
    > establish the same db connection. I mention this because the first thing
    > you look for is the difference between the pages that work and the ones

    that
    > don't. In fact, this code is in an include file which is used on all

    pages,
    > and that rules out any differences in code, you'd think.
    >
    >
    >
     
    Mark Schupp, Jan 7, 2004
    #2
    1. Advertising

  3. middletree

    middletree Guest

    OK, I'll try it out. Thanks.

    I am not sure why I shouldn't use a recordset, though.

    I'm also not sure why this works on every other page which uses this same
    exact code.


    "Mark Schupp" <> wrote in message
    news:...
    > First, you do not seem to be opening a database connection.
    > Second, you seem to be executing the query twice.
    > Third, you are not checking to see if the query returns anything before
    > attempting to access column data.
    > Fourth, you should not explicitly create a recordset for that type of

    query.
    > Try:
    >
    > strSQL = "SELECT EmployeeID, DepartmentID "
    > strSQL = strSQL & "FROM Employee "
    > strSQL = strSQL & "WHERE NetworkID = '"&strLogon&"'"
    > Set RSLogon = objConnection.execute(strSQL,,1)
    > If Not RSLogon.EOF Then
    > strDepartmentID = RSLogon("DepartmentID")
    > strEmployeeID = RSLogon("EmployeeID")
    > End If
    > RSLogon.Close
    > Set RSLogon = Nothing
    >
    >
    > --
    > Mark Schupp
    > Head of Development
    > Integrity eLearning
    > www.ielearning.com
    >
    >
    > "middletree" <> wrote in message
    > news:...
    > > I had some text links at the top of all my pages (in one include file),
    > > which worked just fine. But I was asked to make it so that people in a
    > > certain department, (this is an Intranet app) would see a particular

    link
    > > that nobody else would. You'd find their name by getting their network
    > > logon. This worked fine on all my pages, but for some reason, on one

    > page,
    > > it gives me an error. First, the error:
    > >
    > > Error Type:
    > > ADODB.Recordset (0x800A0E7D)
    > > The connection cannot be used to perform this operation. It is either

    > closed
    > > or invalid in this context.
    > > /includes/header.inc, line 32
    > >
    > > Before we go any farther, please note that I went to ASPFAQ.com, and
    > > couldn't find an answer there which fit my situation. The article that
    > > seemed to have the best shot for me was
    > > http://www.aspfaq.com/show.asp?id=2191, but no dice.
    > >
    > > Anyway, here's my code:
    > >
    > > 'this first line is there because when you get the network logon name,

    it
    > is
    > > prefixed by the domain name and a backslash. Since the db I am pulling

    > data
    > > from has only the logon name, I had to get rid of the first part:
    > >
    > > strLogon=

    Replace(Request.ServerVariables("LOGON_USER"),"domainname\","")
    >
    > > Set RSLogon = Server.CreateObject("ADODB.Recordset")
    > > strSQL = "SELECT EmployeeID, DepartmentID "
    > > strSQL = strSQL & "FROM Employee "
    > > strSQL = strSQL & "WHERE NetworkID = '"&strLogon&"'"
    > > 'this next line is the infamous line 32
    > > RSLogon.Open strSQL, objConnection
    > > objConnection.execute strSQL,,&h00000080
    > >
    > > strDepartmentID = RSLogon("DepartmentID")
    > > strEmployeeID = RSLogon("EmployeeID")
    > > RSLogon.Close
    > > Set RSLogon = Nothing
    > >
    > > If strDepartmentID = "9" then%>
    > > do some stuff
    > > End if
    > >
    > >
    > >
    > > Keep in mind that this code works fine on other pages, just not this

    one.
    > In
    > > fact, it works when you first bring this page up, but it does submit to
    > > itself, and when it does, you should see a "thank-you"-type message, but

    > get
    > > the error instead. But in the code at the top of the page, before it

    > decides
    > > if it's the original page or the repost, I have the same includes which
    > > establish the same db connection. I mention this because the first

    thing
    > > you look for is the difference between the pages that work and the ones

    > that
    > > don't. In fact, this code is in an include file which is used on all

    > pages,
    > > and that rules out any differences in code, you'd think.
    > >
    > >
    > >

    >
    >
     
    middletree, Jan 8, 2004
    #3
  4. middletree

    Bob Barrows Guest

    Mark Schupp wrote:
    > First, you do not seem to be opening a database connection.


    Right, I'm not sure of this as well. Middletree, have you simply not shown
    us the code where you opened the objConnection object?

    > Second, you seem to be executing the query twice.


    That is true. Middletree, was one of these staements supposed to be
    commented out?
    RSLogon.Open strSQL, objConnection
    objConnection.execute strSQL,,&h00000080

    The second statement is simply not correct. It should not be there. That is
    the statement that should be used to execute a non-records-returning query.
    Actually, I prefer to explicitly set the Options argument in the Open
    statement (it's got nothing to do with your problem)

    RSLogon.Open strSQL, objConnection,,,1 '1 = adCmdText

    > Third, you are not checking to see if the query returns anything
    > before attempting to access column data.


    Right. Always check the recordset's EOF property before attempting to access
    any of its data.

    > Fourth, you should not explicitly create a recordset for that type of
    > query. Try:


    Why not? What possible difference can that make? Whether you explicitly
    create a recordset and then call its Open method, or use Execute to tell ADO
    to create a recordset and open it should make absolutely no difference. I
    hate when this advice is given because:
    1. It's a waste of time - it's never the root of the problem
    2. It confuses newcomers who think you are telling them not to use a
    recordset - we're here to help people, not to further confuse them.

    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, Jan 8, 2004
    #4
  5. middletree

    Bob Barrows Guest

    middletree wrote:
    > OK, I'll try it out. Thanks.
    >
    > I am not sure why I shouldn't use a recordset, though.
    >

    To Mark: See? He thought you were telling him not to use a recordset!

    middletree, just to clarify: you are always using a recordset, even when you
    use Execute (unless you use that &h00000080 constant (adExecuteNoRecords) to
    tell ADO not to create a recordset).

    FWIW, I have never seen a problem solved by switching from

    Set rs=server.createobject("adodb.recordset")
    rs.open ...

    to

    set rs = conn.execute(...)

    I have seen cases where problems were masked by the switch, but the root
    problem was never addressed because the switch made everything "work".

    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, Jan 8, 2004
    #5
  6. middletree

    middletree Guest

    Bob:

    As always, thanks for you help. I have comments below:

    "Bob Barrows" <> wrote in message
    news:...
    > Mark Schupp wrote:
    > > First, you do not seem to be opening a database connection.

    >
    > Right, I'm not sure of this as well. Middletree, have you simply not shown
    > us the code where you opened the objConnection object?


    Well, I have an include file for every page which establishes the
    connection. But that's on the pages where this works, and the one where it
    doesn't.
    Anyway, here's that code:
    Dim strDBConnection
    strDBConnection = _
    "Provider=SQLOLEDB;" & _
    "Persist Security Info=False;" & _
    "Data Source=xxxxxx;" & _
    "User ID=xxxxx;" & _
    "Password=xxxxx;" & _
    "Database=xxxxxx;"

    Dim objConnection
    Set objConnection = Server.CreateObject("ADODB.Connection")
    objConnection.Open strDBConnection

    >
    > > Second, you seem to be executing the query twice.

    >
    > That is true. Middletree, was one of these statements supposed to be
    > commented out?
    > RSLogon.Open strSQL, objConnection
    > objConnection.execute strSQL,,&h00000080
    >


    What happened there was I tried a million things before being so exhausted
    by the whole thing that I posted a question.
    Here's the code which is currently in use, and which works on all pages
    except the one. Note that the Recordset is simply called RS, not RSLogon,
    as the use of a rs name that was used elsewhere was one of many things I
    thought might be a conflict.
    (I replaced our network domain name with x's)

    strLogon= Replace(Request.ServerVariables("LOGON_USER"),"xxxxx\","")

    Set RS = Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT EmployeeID, DepartmentID "
    strSQL = strSQL & "FROM Employee "
    strSQL = strSQL & "WHERE NetworkID = '"&strLogon&"'"
    RS.Open strSQL, objConnection

    strDepartmentID = rs("DepartmentID")
    strEmployeeID = rs("EmployeeID")
    rs.Close
    Set rs = Nothing

    If strDepartmentID = "9" then%>
    <img src="images/spacer.gif" width="22" border="0">
    <a href="DisplaySortableTickets.asp?selectTSE=<%=strEmployeeID%>">
    <span class="cellsmall">Your open Tickets</span></a>
    <%Else
    end if



    > The second statement is simply not correct. It should not be there. That

    is
    > the statement that should be used to execute a non-records-returning

    query.
    > Actually, I prefer to explicitly set the Options argument in the Open
    > statement (it's got nothing to do with your problem)
    >
    > RSLogon.Open strSQL, objConnection,,,1 '1 = adCmdText
    >
    > > Third, you are not checking to see if the query returns anything
    > > before attempting to access column data.

    >
    > Right. Always check the recordset's EOF property before attempting to

    access
    > any of its data.
    >


    Noted. thanks.
     
    middletree, Jan 8, 2004
    #6
  7. middletree

    Mark Schupp Guest

    > > Fourth, you should not explicitly create a recordset for that type of
    > > query. Try:

    >
    > Why not? What possible difference can that make? Whether you explicitly
    > create a recordset and then call its Open method, or use Execute to tell

    ADO
    > to create a recordset and open it should make absolutely no difference. I
    > hate when this advice is given because:
    > 1. It's a waste of time - it's never the root of the problem
    > 2. It confuses newcomers who think you are telling them not to use a
    > recordset - we're here to help people, not to further confuse them.


    2 lines of code = 2 chances to mess up
    1 line of code = 1 chance.

    I probably would not have bothered with that bit of advice except that he
    use both rs.open and conn.execute on the same SQL statement which indicates
    a bit of confusion about recordset usage anyway. My guess as to his actual
    problem is that he is not getting any records back. Since he was not
    checking for EOF this would cause an error.

    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com


    "Bob Barrows" <> wrote in message
    news:...
    > Mark Schupp wrote:
    > > First, you do not seem to be opening a database connection.

    >
    > Right, I'm not sure of this as well. Middletree, have you simply not shown
    > us the code where you opened the objConnection object?
    >
    > > Second, you seem to be executing the query twice.

    >
    > That is true. Middletree, was one of these staements supposed to be
    > commented out?
    > RSLogon.Open strSQL, objConnection
    > objConnection.execute strSQL,,&h00000080
    >
    > The second statement is simply not correct. It should not be there. That

    is
    > the statement that should be used to execute a non-records-returning

    query.
    > Actually, I prefer to explicitly set the Options argument in the Open
    > statement (it's got nothing to do with your problem)
    >
    > RSLogon.Open strSQL, objConnection,,,1 '1 = adCmdText
    >
    > > Third, you are not checking to see if the query returns anything
    > > before attempting to access column data.

    >
    > Right. Always check the recordset's EOF property before attempting to

    access
    > any of its data.
    >
    > > Fourth, you should not explicitly create a recordset for that type of
    > > query. Try:

    >
    > Why not? What possible difference can that make? Whether you explicitly
    > create a recordset and then call its Open method, or use Execute to tell

    ADO
    > to create a recordset and open it should make absolutely no difference. I
    > hate when this advice is given because:
    > 1. It's a waste of time - it's never the root of the problem
    > 2. It confuses newcomers who think you are telling them not to use a
    > recordset - we're here to help people, not to further confuse them.
    >
    > 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.
    >
    >
     
    Mark Schupp, Jan 8, 2004
    #7
  8. middletree

    middletree Guest

    "Mark Schupp" <> wrote in message
    news:...
    >
    > I probably would not have bothered with that bit of advice except that he
    > use both rs.open and conn.execute on the same SQL statement which

    indicates
    > a bit of confusion about recordset usage anyway. My guess as to his actual
    > problem is that he is not getting any records back. Since he was not
    > checking for EOF this would cause an error.



    You are correct when you say that I am confused about recordsets. For some
    reason, in 4 years of doing ASP (although not 40 hours per week), I have not
    gotten connection strings, recordsets, anything related to that sort of
    thing. Not sure why. I'm not dumb. 2 college degrees, won the spelling bee
    in 5th grade, etc.

    But it (lack of understanding about rs's and connections) usually doesn't
    pose a problem, as I have code I can re-use. But I cannot emphasize enough
    that what you saw of my code was the last in a chain of trying a million
    different things. I can assure you that most of the day, I wasn't trying to
    connect twice.
     
    middletree, Jan 8, 2004
    #8
  9. middletree

    middletree Guest

    Resolution! : 0x800A0E7D error--don't get it

    Well, I put in Mark's code below, and still got an "Object Required" error.
    Only on that one page. Not sure why. But since the line it pointed to was
    the objConnection.execute, I simply pasted in this code from the db
    connection include file that I have at the top of all pages:

    Set objConnection = Server.CreateObject("ADODB.Connection")
    objConnection.Open strDBConnection


    I have no idea why this works, but it does. Doesn't seem to affect any
    other pages negatively, so I'm thankful for that. But it shouldn't have been
    a problem on this one page, as the include file is here on this page, as
    well, so objConnection should be there. I have no idea why this fixed it,
    but am not up for spending any more time on this, as I have other fish to
    fry.

    thanks for your help, guys!

    James W
    www.middletree.net


    "Mark Schupp" <> wrote in message
    news:...
    > First, you do not seem to be opening a database connection.
    > Second, you seem to be executing the query twice.
    > Third, you are not checking to see if the query returns anything before
    > attempting to access column data.
    > Fourth, you should not explicitly create a recordset for that type of

    query.
    > Try:
    >
    > strSQL = "SELECT EmployeeID, DepartmentID "
    > strSQL = strSQL & "FROM Employee "
    > strSQL = strSQL & "WHERE NetworkID = '"&strLogon&"'"
    > Set RSLogon = objConnection.execute(strSQL,,1)
    > If Not RSLogon.EOF Then
    > strDepartmentID = RSLogon("DepartmentID")
    > strEmployeeID = RSLogon("EmployeeID")
    > End If
    > RSLogon.Close
    > Set RSLogon = Nothing
    >
    >
    > --
    > Mark Schupp
    > Head of Development
    > Integrity eLearning
    > www.ielearning.com
    >
    >
     
    middletree, Jan 8, 2004
    #9
    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. DP
    Replies:
    0
    Views:
    1,144
  2. Replies:
    2
    Views:
    728
    Roedy Green
    Dec 12, 2005
  3. jalkadir
    Replies:
    2
    Views:
    378
  4. Jason
    Replies:
    0
    Views:
    204
    Jason
    Jul 6, 2004
  5. Tom Petersen

    Connection error: ADODB.Recordset (0x800A0E7D)

    Tom Petersen, Sep 16, 2003, in forum: ASP General
    Replies:
    2
    Views:
    219
    Tom Petersen
    Sep 16, 2003
Loading...

Share This Page