I have a table which has companies listed that belong to one of two categories, either Development or Service. There is a column in the database called companyType; Development has a value of 1 and Service has a value of 2. I want to use two loops from the same dataset to pull information from the database based on this criteria. The output works perfect but the script throws and error:

error '80020009'
/includes/getAffiliatedCompanies.asp, line 58

I want to use two loops, how can I accomplish this without the error?

strSQLGetList = "SELECT * FROM affiliatedCompanies"

Set adoRecordset = adoConnection.Execute(strSQLGetList)


%>
<table width="100%" cellpadding="5">
    <tr>
        <td colspan="2" class="readText"><b>Project Development:</b><br><br></td>
    </tr>
<% Do While Not adoRecordset.EOF AND adoRecordset("companyType") = "1"%>
    <tr>
        <td class="regText" valign="top" align="left" width="20%">
<%IF companyImage = "" OR isNull(adoRecordset("companyImage")) Then
    Response.Write "&nbsp;"
  Else
      Response.Write "<img src='/image/affiliatedCompanies/" & adoRecordset("companyImage") & "'><br><span class='regText'>Website: <a href='http://" & adoRecordset("companyWebsite") & " target='_blank'>" & adoRecordset("companyWebsite") & "</a></span>"
  End IF
%>
        </td>
        <td width="80%"><span class="readText"><b><%=adoRecordset("companyName") %></b>
<br><br><%=adoRecordset("companyDesc")%></span><br>
        </td>
    </tr>
<%
    adoRecordset.MoveNext
Loop
%>
    <tr>
        <td colspan="2" class="readText"><b>Real Estate Services:</b><br><br></td>
    </tr>

<% Do While Not adoRecordset.EOF AND adoRecordset("companyType") = "2"%>
    <tr>
        <td class="readText" valign="top" align="center" width="5%">
<%IF companyImage = "" OR isNull(adoRecordset("companyImage")) Then
    Response.Write "&nbsp;"
  Else
      Response.Write "<img src='/image/affiliatedCompanies/" & adoRecordset("companyImage") & "'><br><span class='regText'>Website: <a href='http://" & adoRecordset("companyWebsite") & " target='_blank'>" & adoRecordset("companyWebsite") & "</a></span>"
  End IF
%>
        </td>
        <td width="5%"><b><span class="readText"> <%=adoRecordset("companyName")%></b>
<br><br><%=adoRecordset("companyDesc")%></span><br>
        </td>
    </tr>
<%
    adoRecordset.MoveNext
Loop
%>


</table><br><br>
                  <%
adoRecordset.Close
Set adoRecordset = Nothing
adoConnection.Close
Set adoConnection = Nothing
%>


Thanks

desnt look like you have 'show friendly errors' turned off. Turn this off (tools, iternet options, advanced) what does the error say now.

Do you expect people to count your lines. Next time do what you can to help others help you, point out where line 58 is.

Wind is your friend
Matt

Matt,

Thanks for the reply. I have friendly messages disabled. That's it for the error message.

error '80020009'
/includes/testLoop.asp, line 35

I have simplified the code and included line numbers to make it easier to review.


11 strSQLGetList = "SELECT * FROM affiliatedCompanies"
12
13 Set adoRecordset = adoConnection.Execute(strSQLGetList)
14
15 %>
16 <table width="100%" cellpadding="5">
17 <tr>
18 <td colspan="2"><b>Development:</b><br><br></td>
19 </tr>
20 <% Do While Not adoRecordset.EOF AND adoRecordset("companyType") = "1"%>
21 <tr>
22 <td>
23 <%= adoRecordSet("companyName")%>
24 </td>
25 <td><%=adoRecordset("companyDesc")%><br><br>
26 </td>
27 </tr>
28 <%
29 adoRecordset.MoveNext
30 Loop
31 %>
32 <tr>
33 <td colspan="2"><b>Services:</b><br><br></td>
34 </tr>
35 <% Do While Not adoRecordset.EOF AND adoRecordset("companyType") = "2"%>
36 <tr>
37 <td>
38 <%= adoRecordSet("companyName")%>
39 </td>
40 <td><%=adoRecordset("companyDesc")%><br><br>
41 </td>
42 </tr>
43 <%
44 adoRecordset.MoveNext
45 Loop
46 %>
47 </table>
48
49 <%
50 adoRecordset.Close
51 Set adoRecordset = Nothing
52 adoConnection.Close
53 Set adoConnection = Nothing
54 %>

The error message appears at the top of the page but everything is written(output) correctly to the page and includes all the records in the database????

Thank you for your assistance.

You are doing:

Do While Not adoRecordset.EOF AND adoRecordset("companyType") = "1"%

on line 20, then:

Do While Not adoRecordset.EOF AND adoRecordset("companyType") = "2"%

on line 35 - Throw a adoRecordset.moveFirst before line 35. There is a chance line 20 will leave the cursor at the end of the record set - therefore line 35 may never execute even if there are records in your recordset that match the condition

Wind is your friend
Matt

Thanks for the help Matt but unfortunatly that didn't fix the problem.

I have reworked the script to use filters. Works perfectly.

<%
Const adFilterNone = 0
Const adFilterPendingRecords = 1
Const adFilterAffectedRecords = 2
Const adFilterFetchedRecords = 3
Const adFilterConflictingRecords = 5

Dim oRS, strSQL, strConn

    Set oRS = Server.CreateObject("ADODB.RecordSet")
    strConn = "Provider=SQLOLEDB;Data Source=XXXX;" _
    & "Initial Catalog=XXXX;User Id=XXXX;Password=XXXX;" _
    & "Connect Timeout=15;Network Library=dbmssocn;"
    strSQL = "SELECT * FROM affiliatedCompanies"

    oRS.Open strSQL, strConn, 3, 3

    oRS.Filter = "companyType = 1"%>

    Project Development:<br><br>

    <%
    Do While Not oRS.EOF
        Response.Write(oRS("companyName") & "<BR>")
    oRS.MoveNext
    Loop

    'Remove Filter
    oRS.Filter = adFilterNone%>

    <br>Service:<br><br>

    <%oRS.Filter = "companyType = 2"

    Do While Not oRS.EOF
        Response.Write(oRS("companyName") & "<BR>")
    oRS.MoveNext
    Loop

    oRS.Close
    Set oRS = Nothing
%>

Hey try this

in the line 31 after the first loop put the sentense

adoRecordset.Movefirst

In this case when the first loop finish the file is on EOF, then you have to move the cursor at the first record,

Bye