LOOP

Ov1 · July 31st, 2006, 06:34 AM

Hello...

Could u guys help me plz? i'm still scratching my head on this prob. i've searched this and other forums for a solution but didnt find one.

I'm getting the following error msg when running my ASP 3.0 page :

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required
conv.asp, line 31

line 31 is shown in red below

If i remove the main loop ( in blue ), the code runs fine but of course only 1 record is displayed, and i need all the records. What's wrong with the code???

Here's the code (file conv.asp):

Code:

<%@ Language = VBSCRIPT%>
<% Option Explicit%>


<%
Dim rsPat, rsOrd1, rsOrd2, strSQL, strSQL1, strSQL2, row, col

Set rsPat = Server.CreateObject("ADODB.Recordset")
Set rsOrd1 = Server.CreateObject("ADODB.Recordset")
Set rsOrd2 = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT Pat_Code, Pat_CusCode, Pat_Title, Pat_LN, Pat_FN, Pat_************, Pat_DOB, Pat_ClinicDet, Pat_Fast, Pat_Drug, Pat_Contracep, Pat_Pregnant FROM tbl_Patients"

rsPat.Open strSQL, strCon
row=2


Do While Not rsPat.EOF


  Response.Write(rsPat("Pat_Code")) %><br><%
  Response.Write(rsPat("Pat_CusCode"))%><br><%
  Response.Write(rsPat("Pat_LN"))%><br><%
  Response.Write(rsPat("Pat_FN"))%><br><%
  Response.Write(rsPat("Pat_Title"))%><br><%
  Response.Write(rsPat("Pat_DOB"))%><br><%
  Response.Write(rsPat("Pat_************"))%><br><%
  Response.Write(rsPat("Pat_ClinicDet"))%><br><%
  Response.Write(rsPat("Pat_Fast"))%><br><%

  strSQL1 = "SELECT Order_Code FROM tbl_Orders WHERE Cus_Code ='" & rsPat("Pat_CusCode") & "'"

  rsOrd1.Open strSQL1, strCon


  strSQL2 = "SELECT TTest_Code FROM tbl_RS_Order_Test WHERE TOrder_Code ='"& rsOrd1("Order_Code") & "'" & " UNION SELECT PProfile_Code FROM tbl_RS_Order_Profile WHERE POrder_Code ='" & rsOrd1("Order_Code") & "'"

 rsOrd1.Close
 set rsOrd1 = Nothing

 rsOrd2.Open strSQL2, strCon
 col=8

    Do While Not rsOrd2.EOF

         Response.Write(rsOrd2("TTest_Code"))%><br><%
        rsOrd2.MoveNext
        col=col+1
    Loop

    rsOrd2.Close
    Set rsOrd2 = Nothing

    rsPat.MoveNext
    row=row+1
Loop

    rsPat.Close
    Set rsPat = Nothing

    Set strCon = Nothing
%>

File db_con.asp :

Code:

<%

Dim strCon
Set strCon = Server.CreateObject("ADODB.Connection")
strCon.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog = TestOv; User Id = bibo; Password=g;"
strCon.CursorLocation = 3
%>

Regards,
Ov1

dparsons · July 31st, 2006, 09:01 AM

I am willing to bet if you remove either of the loops the application will run fine. What I think is happening here is that you are trying to open your second recordset with the same connection object as the first recordset and you have not yet closed the connection to the first recordset. What I would suggest trying, in your inc file, add a second connection object and use that to open your second recordset.

If it doesnt work post back.

"The one language all programmers understand is profanity."

Ov1 · July 31st, 2006, 09:12 AM

well... if i remove the main loop it works but not if i remove the inner one. :-(

i already tried using a 2nd connection for the 2nd recordset but still got the same error.
i just tried it again just in case... but again same error.
:((((

Regards,
Ov1

Ov1 · July 31st, 2006, 09:14 AM

hey... i've just tried something :

if i use
    rsOrd1 = strCon.Execute(strSQL1)
instead of
    rsOrd1.Open strSQL1, strCon

the error switches to another line, i.e, to the next recordset which is
    rsOrd2.Open strSQL2, strCon

and if i use .EXECUTE for this line too, by replacing it with
    rsOrd2 = strCon.Execute(strSQL2)

i no longer have the object required error. instead i have :

Object doesn't support this property or method: 'EOF'

for the Do While Not rsOrd2.EOF line.

now how do i overcome this? any idea?

Regards,
Ov1

Ov1 · July 31st, 2006, 10:12 AM

Hi there... guess what... i got it working!

i set the 2nd & 3rd recordsets to use .EXECUTE.

then concerning the error
Object doesn't support this property or method: 'EOF'

i just had to use Set before the recordset, i.e the final working code is
Set rsOrd2 = strCon.Execute(strSQL2)

Wa... this thing cost me 2 days. lol

anyway thx for your input and time dparsons.
Cheers.

Regards,
Ov1

dparsons · July 31st, 2006, 10:18 AM

No problem, glad you got it working. (I have and always will hate ASP error messages. The CLR error messages are so much eaiser to figure out and debug ;] )

"The one language all programmers understand is profanity."