not returning records when it should

aliaga61 · #1 (**permalink**) 01-16-04, 08:24

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 & ""
HTML = HTML & "test for WO Basket Change. "

'*************************************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 & ") "
HTML = HTML & "" & strEmail & " "
HTML = HTML & "" & strFormatted & " "

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

HTML = HTML & " "
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) &" "
HTML = HTML & " "

'closing of email. Standard for all users with the exception of the "to" field
HTML = HTML & "Thank You, "
HTML = HTML & "DMATS "
'HTML = HTML & "Surveyor " & strSurvFullName & " "
HTML = HTML & "CMOs Email " & strListing & " "
HTML = HTML & "User's Email " & strUserEmail & " "
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

gyuan · #2 (**permalink**) 01-16-04, 10:44

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 & "'"

aliaga61 · #3 (**permalink**) 01-16-04, 11:27

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?

gyuan · #4 (**permalink**) 01-16-04, 11:39

In MSSQL, the field with VARCHAR datatype can hold up to 8000 characters.

aliaga61 · #5 (**permalink**) 01-16-04, 11:41

they're both set to sizes of 6

gyuan · #6 (**permalink**) 01-16-04, 11:42

varchar(6) means the field only can hold 6 characters.

aliaga61 · #7 (**permalink**) 01-16-04, 11:45

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

gyuan · #8 (**permalink**) 01-16-04, 11:51

I think that I know the problem. Try this:

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

aliaga61 · #9 (**permalink**) 01-16-04, 12:01

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.

gyuan · #10 (**permalink**) 01-16-04, 13:03

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)

aliaga61 · #11 (**permalink**) 01-16-04, 13:05

i'm assuming you meant for the &cint(.... to be inside the quotes?

aliaga61 · #12 (**permalink**) 01-16-04, 13:11

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

gyuan · #13 (**permalink**) 01-16-04, 13:23

Can you post the code at line 85?

aliaga61 · #14 (**permalink**) 01-16-04, 13:25

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")

gyuan · #15 (**permalink**) 01-16-04, 13:34

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech