Solved

error '80020009' ?

Posted on 2006-07-20
9
275 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
ID: 17148720
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
ID: 17148727
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
ID: 17148750
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:kwitcom
ID: 17148752
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
 
LVL 14

Assisted Solution

by:CyrexCore2k
CyrexCore2k earned 250 total points
ID: 17148814
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
ID: 17148882
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
ID: 17148884
Sorry, ignore my last post. Should have read the whole statement :)

Lee
0
 
LVL 15

Expert Comment

by:joeposter649
ID: 17148913
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
ID: 17149156
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
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/…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

830 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