Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2004
    Posts
    36

    Unanswered: not returning records when it should

    I'm back with the same code, which works fine, except for when i ad the block of code that i noted...i get the error message:

    ADODB.Field error '800a0bcd'
    Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

    /test/testbasket.asp, line 72


    which is referring to the strSurvID=adoRSWEBtest4(0)

    i know that the query above that is supplying webtest4 with its where check is returning a value. when i take out the noted block of code, the page works fine, and emails all information, including the value that is being checked for in webtest4.

    in oracle, the value is set as a varchar2(6). in sql 7 the value that it is being compared to is set to varchar(6). any ideas? i have no clue. help!?!?!?!

    'get work orders whose baskets have changed, with a status of assigned and a dash order or 1, within the last two hours
    StrSQL = "Select t.tsnumb, t.tsid, wb.wcseqn, wb.wcbask, wb.wcstat, wb.wcdate from tsr t, wobaskdtl wb where t.tsnumb=wb.wcnumb and wb.wcseqn='1' and wb.wcdate >= sysdate - interval '2' hour and wb.wcstat='A' and wb.wcbask like 'R0%' and t.tsid is not null order by t.tsnumb"
    Set adoRS = adoConn.Execute(StrSQL)

    do until adoRS.EOF
    if left(adoRS(3),1) = "R" and right(adoRS(3),2) = "-N" then
    strWONumb = adoRS(0)
    strUserID = adoRS(1)
    strSequence = adoRS(2)
    strBasket = adoRS(3)
    strStatus = adoRS(4)
    strDate = adoRS(5)

    'beginning of email. standard for all users
    Dim MyCDONTSMail2
    Dim HTML
    Set MyCDONTSMail2 = CreateObject("CDONTS.NewMail")
    HTML = "<!DOCTYPE HTML PUBLIC""-//IETF//DTD HTML//EN"">"
    HTML = HTML & "<html>"
    HTML = HTML & "<head>"
    HTML = HTML & "<title>Work Order Status</title>"
    HTML = HTML & "</head>"
    HTML = HTML & "<body bgcolor=""FFFFFF"">"
    HTML = HTML & "<font size =""3"" face=""Arial"">"
    HTML = HTML & "test for WO Basket Change.<br><br>"

    '*************************************this section below, is not working for some reason

    StrSQL = "Select sur.SURVID from tblSurveyors sur where sur.SURVBASK = '" & adoRS(3) & "'"
    set adoRSWEBtest4 = adoConnWEBtest.Execute(StrSQL)

    strSurvID = adoRSWEBtest4(0)


    'get surveyor info (id,name,email,phone)
    StrSQL = "Select usr.USERID, usr.CMFNAM, usr.CMMNAM, usr.CMLNAM, usr.CMEMAL, usr.CMPHON, usr.CMEXTN from tblUserInfo usr where usr.USERID='" & strSurvID & "'"
    Set adoRSWEBtest = adoConnWEBtest.Execute(StrSQL)


    strSurvFullName = adoRSWEBtest(1) + " " + adoRSWEBtest(2) + " " + adoRSWEBtest(3)
    strSurvEmail = adoRSWEBtest(4)
    strSurvPhone = adoRSWEBtest(5)
    strSurvExtn = adoRSWEBtest(6)

    '*************************************above this line...not working

    'get person's info (id,email)
    strSQL = "Select usr.CMEMAL, usr.CMORGA from tblUserInfo usr where usr.USERID ='" & strUserID & "'"
    Set adoRSWEBtest2 = adoConnWEBtest.Execute(strSQL)

    strUserEmail = adoRSWEBtest2(0)
    strUserOrg = adoRSWEBtest2(1)

    'get cmo info (id,name,email,phone)
    strSQL = "Select Distinct substr(ORTCON,1,6), ORORGA from ORGCODE where ORORGA = '" & strUserOrg & "' order by ORORGA"
    set adoRS2 = adoConn.Execute(strSQL)

    do until adoRS2.EOF
    strAcctNo = adoRS2(0) + "%"

    strSQL = "Select Distinct cmo.CMOID, cmo.CMOROLE, cmo.ACCTNO, usr.USERID, usr.CMFNAM, usr.CMMNAM, usr.CMLNAM, usr.CMEMAL, usr.CMPHON, usr.CMEXTN, usr.CMPREFIX from tblCMOList cmo, tblUserInfo usr where cmo.CMOID=usr.USERID and cmo.ACCTNO= '" & strAcctNo & "' order by cmo.CMOROLE desc"
    set adoRSWEBtest3 = adoConnWEBtest.Execute(strSQL)

    Do While Not adoRSWEBtest3.EOF
    strRole = adoRSWEBtest3(1)
    strFullName = adoRSWEBtest3(4) + " " + adoRSWEBtest3(5) + " " + adoRSWEBtest3(6)
    strEmail = adoRSWEBtest3(7)
    strExt = adoRSWEBtest3(9)
    strTele = adoRSWEBtest3(8)
    strPrefix = adoRSWEBtest3(10)

    'format telephone number to include "()" and "-"
    if Len(strTele) = 10 then
    strFormatted = "(" & mid(strTele,1,3) & ") " & mid(strTele,4,3) & "-" & mid(strTele,7,4)
    else
    'if not a 10 digit number, don't format the number
    strFormatted = strTele
    end if

    'display cmos' information
    HTML = HTML & "" & strPrefix & " " & strFullName & " (" & strRole & ") <br>"
    HTML = HTML & "" & strEmail & "<br>"
    HTML = HTML & "" & strFormatted & "<br><br>"

    'place cmos' email addresses into one string for "to" field of email
    strListing = trim(strEmail) & "; " & strListing
    adoRSWEBtest3.MoveNext
    Loop
    adoRS2.MoveNext
    loop

    HTML = HTML & "<br><br>"
    HTML = HTML & ""& strCurrentTime &" "
    HTML = HTML & " "& adoRS(0) &" "
    HTML = HTML & " "& adoRS(2) &" "
    HTML = HTML & " "& adoRS(1) &" "
    HTML = HTML & " "& adoRS(3) &" "
    HTML = HTML & " "& adoRS(4) &" "
    HTML = HTML & " "& adoRS(5) &"<br>"
    HTML = HTML & "<br>"

    'closing of email. Standard for all users with the exception of the "to" field
    HTML = HTML & "Thank You,<br>"
    HTML = HTML & "DMATS<br>"
    'HTML = HTML & "Surveyor " & strSurvFullName & "<br>"
    HTML = HTML & "CMOs Email " & strListing & "<br>"
    HTML = HTML & "User's Email " & strUserEmail & "<br>"
    HTML = HTML & "</body>"
    HTML = HTML & "</html>"
    MyCDONTSMail2.From= "aliaga61@earthlink.net"
    MyCDONTSMail2.To="aliaga61@earthlink.net"
    MyCDONTSMail2.Subject="WO Basket Change"
    MyCDONTSMail2.BodyFormat=0
    MyCDONTSMail2.MailFormat=0
    MyCDONTSMail2.Body=HTML
    MyCDONTSMail2.Send
    set MyCDONTSMail2=nothing
    strListing = null
    end if
    adoRS.MoveNext
    loop

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    The problem is in your RecordSet

    Set adoRS = adoConn.Execute(StrSQL)

    the default cursor is adOpenForwardOnly. That means the cursor only allows you to navigate forward in the RecordSet. So after you assign values of the row

    strWONumb = adoRS(0)
    strUserID = adoRS(1)
    strSequence = adoRS(2)
    strBasket = adoRS(3)
    strStatus = adoRS(4)
    strDate = adoRS(5)

    you can not use adoRS(3) in the following query:

    StrSQL = "Select sur.SURVID from tblSurveyors sur where sur.SURVBASK = '" & adoRS(3) & "'"

    Instead, you can use the following query:

    StrSQL = "Select sur.SURVID from tblSurveyors sur where sur.SURVBASK = '" & strBasket & "'"

  3. #3
    Join Date
    Jan 2004
    Posts
    36
    i actually had that to begin with, so i changed it to adoRS(3)...neither worked and gave me the EOF error.

    i just changed it back to strBasket and tried it again...still doesn't work. The only thing i can figure is that it has something to do with the way i set up the database table...but i don't understand how that could be messed up.

    is there an issue with comparing an oracle column that is a varchar2 with a sql column that is a varchar?

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    In MSSQL, the field with VARCHAR datatype can hold up to 8000 characters.

  5. #5
    Join Date
    Jan 2004
    Posts
    36
    they're both set to sizes of 6

  6. #6
    Join Date
    Dec 2003
    Posts
    454
    varchar(6) means the field only can hold 6 characters.

  7. #7
    Join Date
    Jan 2004
    Posts
    36
    right, i know. the data being stored is no longer than 6 characters in either database table, and they are both set to the same length.

    i just didn't know if there's an issue with comparing a varchar2(6) field with a varchar(6) field

  8. #8
    Join Date
    Dec 2003
    Posts
    454
    I think that I know the problem. Try this:

    If Not adoRSWEBtest4.EOF Then
    strSurvID = adoRSWEBtest4(0)
    End If

  9. #9
    Join Date
    Jan 2004
    Posts
    36
    well, that gets me past that query, but now i get the error:

    Microsoft OLE DB Provider for SQL Server error '80040e07'
    Error converting data type varchar to numeric.

    /test/testbasket.asp, line 81

    i assume it's referring to the usr.USERID='" & strSurvID & "' section of the query. but user.userid and strsurvid are both numeric in their tables.

  10. #10
    Join Date
    Dec 2003
    Posts
    454
    Try this:

    StrSQL = "Select usr.USERID, usr.CMFNAM, usr.CMMNAM, usr.CMLNAM, usr.CMEMAL, usr.CMPHON, usr.CMEXTN from tblUserInfo usr where usr.USERID=" & CInt(strSurvID)

  11. #11
    Join Date
    Jan 2004
    Posts
    36
    i'm assuming you meant for the &cint(.... to be inside the quotes?

  12. #12
    Join Date
    Jan 2004
    Posts
    36
    well....it's letting me past that query now....but when it trys to set the variables underneath to the returned records....i get this error

    ADODB.Recordset error '800a0cc1'
    Item cannot be found in the collection corresponding to the requested name or ordinal.

    /test/testbasket.asp, line 85

    again, i know that it's returning a basket in the oracle query....then the first query in my problem section should find that basket and in turn, a survID....i don't get it...this is pretty frustrating.

    i do appreciate you being patient with me and helping me out though

  13. #13
    Join Date
    Dec 2003
    Posts
    454
    Can you post the code at line 85?

  14. #14
    Join Date
    Jan 2004
    Posts
    36
    here's lines 84 and 85....it initially said line 84, but i then commented that line out

    (84) strSurvFullName = adoRSWEBtest("usr.CMFNAM") + " " + adoRSWEBtest("usr.CMMNAM") + " " + adoRSWEBtest("usr.CMLNAM")
    (85) strSurvEmail = adoRSWEBtest("usr.CMEMAL")

  15. #15
    Join Date
    Dec 2003
    Posts
    454
    After the code

    <%
    StrSQL = "Select usr.USERID, usr.CMFNAM, usr.CMMNAM, usr.CMLNAM, usr.CMEMAL, usr.CMPHON, usr.CMEXTN from tblUserInfo usr where usr.USERID=" & CInt(strSurvID)
    %>

    Add

    <%
    Response.Write StrSQL
    Response.End
    %>

    And then you can get the sql statement. Paste the statement into Query Analyzer and run it. See if it is okay.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •