Solved

error '80020009' ?

Posted on 2006-07-20
9
271 Views
Last Modified: 2008-02-01
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>
0
Comment
Question by:kwitcom
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 14

Expert Comment

by:CyrexCore2k
Comment Utility
Does this apply? I can't tell what your field types are.

http://support.microsoft.com/default.aspx/kb/175239
0
 
LVL 23

Accepted Solution

by:
apresto earned 250 total points
Comment Utility
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
 
LVL 14

Expert Comment

by:CyrexCore2k
Comment Utility
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
 

Author Comment

by:kwitcom
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 14

Assisted Solution

by:CyrexCore2k
CyrexCore2k earned 250 total points
Comment Utility
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
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
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
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
Sorry, ignore my last post. Should have read the whole statement :)

Lee
0
 
LVL 15

Expert Comment

by:joeposter649
Comment Utility
You should look into using a join instead of nesting the SQL calls.
http://www.w3schools.com/sql/sql_join.asp
0
 

Author Comment

by:kwitcom
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now