error '80020009' ?

I am getting an Error with this page, and can't find out why.

Error:
error '80020009'
/test/z1/1.asp, line 17

Code:
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
</head>
<body>
<%'CONNECTION
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=001; User ID=xxxxxxxxxxx; Password=xxxxxxxxxxxxxxxx"
Set ObjRs = Server.CreateObject("ADODB.Recordset")

dim objRS, strSQL, ordno, rs, strSQL1, rs1, docToAdd, strSQL2, strSQL3, rs3
ordno= Request.QueryString("ordno")
strSQL = "SELECT * FROM labels WHERE sf_ord ='" & ordno & "'"
set rs = Conn.Execute(strSQL)
strSQL1 = "SELECT *, ltrim(rtrim(ord_no)) as ord_no FROM sfordfil_sql WHERE ord_no = '" & ordno & "'"
set rs1 = Conn.Execute(strSQL1)
strSQL3 = "SELECT *, ltrim(rtrim(user_def_fld_1)) as user_def_fld_1, ltrim(rtrim(user_def_fld_4)) as user_def_fld_4 FROM imitmidx_sql WHERE item_no = '" & rs1("item_no") & "'"
                                                  ^-------This is the Line with the errors

set rs3 = Conn.Execute(strSQL3)
  docToAdd = 1
  strSQL2 = "update labels set doc = "&docToAdd&", status = 1, doc_dt = '"&NOW()&"' where sf_ord = '" & ordno & "'"
  conn.execute(strSQL2)%>
<%'CLOSE
conn.close
set conn = nothing%>
TEST
</body>
</html>
kwitcomAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
aprestoConnect With a Mentor Commented:
Hi kwitcom,
try this:

response.write rs1("item_no")  '<-- just to see if there is anything in there
if NOT rs1.eof Then
strSQL3 = "SELECT *, ltrim(rtrim(user_def_fld_1)) as user_def_fld_1, ltrim(rtrim(user_def_fld_4)) as user_def_fld_4 FROM imitmidx_sql WHERE item_no = '" & rs1("item_no") & "'"
end if

Apresto
0
 
CyrexCore2kCommented:
Does this apply? I can't tell what your field types are.

http://support.microsoft.com/default.aspx/kb/175239
0
 
CyrexCore2kCommented:
Also I noticed you don't catch just in case you return an empty recordset...

dim objRS, strSQL, ordno, rs, strSQL1, rs1, docToAdd, strSQL2, strSQL3, rs3
ordno= Request.QueryString("ordno")
strSQL = "SELECT * FROM labels WHERE sf_ord ='" & ordno & "'"
set rs = Conn.Execute(strSQL)
strSQL1 = "SELECT *, ltrim(rtrim(ord_no)) as ord_no FROM sfordfil_sql WHERE ord_no = '" & ordno & "'"
set rs1 = Conn.Execute(strSQL1)
If Not rs1.Eof Then ' <-------- If the recordset is empty the following line would cause an error so we'll stay safe.
           strSQL3 = "SELECT *, ltrim(rtrim(user_def_fld_1)) as user_def_fld_1, ltrim(rtrim(user_def_fld_4)) as user_def_fld_4 FROM imitmidx_sql WHERE item_no = '" & rs1("item_no") & "'"
  set rs3 = Conn.Execute(strSQL3)
  docToAdd = 1
  strSQL2 = "update labels set doc = "&docToAdd&", status = 1, doc_dt = '"&NOW()&"' where sf_ord = '" & ordno & "'"
  conn.execute(strSQL2)
End If
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
kwitcomAuthor Commented:
Error:
ADODB.Field error '80020009'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/test/z1/1.asp, line 0

Code:
<html>
<head>
</head>
<body>
<%'CONNECTION
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=001; User ID=molerat; Password=j81wd64l"
Set ObjRs = Server.CreateObject("ADODB.Recordset")

dim objRS, strSQL, ordno, rs, strSQL1, rs1, docToAdd, strSQL2, strSQL3, rs3
ordno= Request.QueryString("ordno")
strSQL = "SELECT * FROM labels WHERE sf_ord ='" & ordno & "'"
set rs = Conn.Execute(strSQL)
strSQL1 = "SELECT *, ltrim(rtrim(ord_no)) as ord_no FROM sfordfil_sql WHERE ord_no = '" & ordno & "'"
set rs1 = Conn.Execute(strSQL1)
response.write rs1("item_no")  '<-- just to see if there is anything in there

if NOT rs1.eof Then
strSQL3 = "SELECT *, ltrim(rtrim(user_def_fld_1)) as user_def_fld_1, ltrim(rtrim(user_def_fld_4)) as user_def_fld_4 FROM imitmidx_sql WHERE item_no = '" & rs1("item_no") & "'"
set rs3 = Conn.Execute(strSQL3)
end if

  docToAdd = 1
  strSQL2 = "update labels set doc = "&docToAdd&", status = 1, doc_dt = '"&NOW()&"' where sf_ord = '" & ordno & "'"
  conn.execute(strSQL2)%>
<%'CLOSE
conn.close
set conn = nothing%>
TEST
</body>
</html>
0
 
CyrexCore2kConnect With a Mentor Commented:
Try this:

<html>
<head>
</head>
<body>
<%'CONNECTION
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=001; User ID=molerat; Password=j81wd64l"
'Set ObjRs = Server.CreateObject("ADODB.Recordset")

dim strSQL, ordno, rs, strSQL1, rs1, docToAdd, strSQL2, strSQL3, rs3
ordno= Request.QueryString("ordno")

'strSQL = "SELECT * FROM labels WHERE sf_ord ='" & ordno & "'"
'set rs = Conn.Execute(strSQL) '<---------------------- you never use this?

strSQL1 = "SELECT *, ltrim(rtrim(ord_no)) as ord_no FROM sfordfil_sql WHERE ord_no = '" & ordno & "'"
set rs1 = Conn.Execute(strSQL1)

if NOT rs1.eof Then
response.write rs1("item_no")  '<-- just to see if there is anything in there
strSQL3 = "SELECT *, ltrim(rtrim(user_def_fld_1)) as user_def_fld_1, ltrim(rtrim(user_def_fld_4)) as user_def_fld_4 FROM imitmidx_sql WHERE item_no = '" & rs1("item_no") & "'"
set rs3 = Conn.Execute(strSQL3)
end if

  docToAdd = 1
  strSQL2 = "update labels set doc = "&docToAdd&", status = 1, doc_dt = '"&NOW()&"' where sf_ord = '" & ordno & "'"
  conn.execute(strSQL2)%>
<%'CLOSE
conn.close
set conn = nothing%>
TEST
</body>
</html>
0
 
Lee SavidgeCommented:
Hi,

The line:

strSQL3 = "SELECT *, ltrim(rtrim(user_def_fld_1)) as user_def_fld_1, ltrim(rtrim(user_def_fld_4)) as user_def_fld_4 FROM imitmidx_sql WHERE item_no = '" & rs1("item_no") & "'"

reads as SELECT *,

Sure the comma after the asterisk isn't right?

Before you run this line:

set rs3 = Conn.Execute(strSQL3)

put a

reponse.write(strSQL3)
response.end()

and see what is in strSQL3. If what is in that variable executes in query analyser then the problem is elsewhere.

Regards,

Lee
0
 
Lee SavidgeCommented:
Sorry, ignore my last post. Should have read the whole statement :)

Lee
0
 
joeposter649Commented:
You should look into using a join instead of nesting the SQL calls.
http://www.w3schools.com/sql/sql_join.asp
0
 
kwitcomAuthor Commented:
Thnx guys found out that I needed to change:

strSQL1 = "SELECT *, ltrim(rtrim(ord_no)) as ord_no FROM sfordfil_sql WHERE ord_no = '" & ordno & "'"
set rs1 = Conn.Execute(strSQL1)

To:
strSQL1 = "SELECT *, ltrim(rtrim(ord_no)) as ord_no FROM sfordfil_sql WHERE ord_no like '%" & ordno & "'"
set rs1 = Conn.Execute(strSQL1)

Because the people that setup the database allowed spaces to be at the front of the numbers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.