Solved

Using 2 recordsets If .EOF Else not working

Posted on 2008-10-15
18
366 Views
Last Modified: 2012-05-05
Hi,

Please see the code below. I open the first recordset which displays all vehicles from a table. Then for each vehicle, i open another recordset with a SQL statement that queries a seperate table to see if that vehicle has any parts assigned to it, if they are not assigned, then i want it just to list to vehicle details. If there are parts, then i need it to move through the loop adding each part that fits that vehicle underneath it.

The issue is, when SQL2 is executed, the response is no parts matching, even when there is ! I've physically put the SQL statement into access and there are parts that match, but these do not loop through and show.
<% 

		'NOW CONNECTS TO DISPLAY DATA FROM TABLE VINDEX

Dim adoCon             

Dim SQL

Dim Recordset2

Dim oddsevens

'oddsevens = 1
 

Set adoCon = Server.CreateObject("ADODB.Connection")
 

adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database.mdb"))

SQL= "SELECT * FROM vindex WHERE Make='" & vmake & "' AND Model='" & vmodel & "' AND Litre='" & vlitre & "' ORDER BY InModelSort"
 

Set recordset2 = adoCon.Execute(SQL)
 

If recordset2.eof then

	no_rows = True

          End If

		  

If no_rows Then

response.write "No Vehicle Matches"

Else    

    Dim lastValue, theColor

    lastValue  = ""

	theColor = "#b4def5"

  

   do while not recordset2.eof

   

	

	If Cstr(lastValue) <> Cstr(recordset2.fields("VehID")) Then

	If theColor = "#b4def5" Then

		theColor = "#ffffff"

	Else

		theColor = "#b4def5"

	End If

	%>

    <tr bgcolor="<%=theColor%>"  >

      <td><input name="pub2" type="checkbox" id="pub2" value="<%response.write recordset2.fields("VehID")%>" /></td>

      <td colspan="22" bgcolor="<%=theColor%>"><span class="style2">

        <%Response.write recordset2.fields("model") %>

        &nbsp;

        <%Response.write recordset2.fields("SubModel")%>

      </span></td>

    </tr>

	<%End If%>

    

    

    <% 

		'NOW CONNECTS TO DISPLAY DATA FROM PARTTABLE

Dim adoCon2             

Dim SQL2

Dim Recordset3

Dim no_rows2

'oddsevens = 1
 

Set adoCon2 = Server.CreateObject("ADODB.Connection")
 

adoCon2.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database.mdb"))

SQL2 =" SELECT vehiclepart.*, parttable.*, vehiclepart.VehID, parttable.Category FROM parttable RIGHT JOIN vehiclepart ON parttable.Partno = vehiclepart.partno WHERE (((vehiclepart.VehID)=" & recordset2.fields("VehID") &") AND ((parttable.Category)='"& vapplication &"'));"
 

Set Recordset3 = adoCon2.Execute(SQL2)

response.write(SQL2)
 

If Recordset3.eof then

	no_rows2 = True

          End If

		  

If no_rows2 Then 
 

response.write ("no parts matching")%>

    <tr bgcolor="<%=theColor%>">

      <td height="12">&nbsp;</td>

      <td valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Ident")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Litre")%>

      </span></td>

      <td width="5%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Fuel")%>

      </span></td>

      <td width="12%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Body")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Drive")%>

      </span></td>

      <td width="8%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("EngNo")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("cc")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Valves")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("bhp")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("PS")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("kW")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Cam")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Cyl")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("FrmM")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("FrmY")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("ToM")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("ToY")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("CarParc")%>

      </span></td>

      <td width="4%">&nbsp;</td>

      <td width="4%"><span class="style14">&nbsp;</span></td>

      <td width="2%">&nbsp;</td>

      <td width="3%">&nbsp;</td>

    </tr>

    <% Else 

	

	do while not recordset3.eof

    

    response.write("parts matching")%>

    

        <tr bgcolor="<%=theColor%>">

      <td height="12">&nbsp;</td>

      <td valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Ident")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Litre")%>

      </span></td>

      <td width="5%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Fuel")%>

      </span></td>

      <td width="12%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Body")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Drive")%>

      </span></td>

      <td width="8%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("EngNo")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("cc")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Valves")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("bhp")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("PS")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("kW")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Cam")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("Cyl")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset3.fields("FrmM")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset3.fields("FrmY")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset3.fields("ToM")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset3.fields("ToY")%>

      </span></td>

      <td width="4%" valign="middle"><span class="style14">

        <%Response.write recordset2.fields("CarParc")%>

      </span></td>

      <td width="4%"><input name="pub" type="checkbox" id="pub" value="<%response.write recordset2.fields("VehID")%>" /></td>

      <td width="4%"><span class="style14">

        <% Response.write recordset3.fields("partno")%>

        &nbsp;</span></td>

      <td width="2%"><span class="style14">

        <% Response.write recordset3.fields("Shortdesc")%>

      </span></td>

      <td width="3%"><input name="del" type="checkbox" id="del" value="<%response.write recordset2.fields("VehID")%>" /></td>

    </tr>

    <% recordset3.MoveNext%>

    <% Loop

	End If %>

    

    <%

    lastValue  = recordset2.fields("VehID")

    recordset2.MoveNext%>

    <%Loop

        End If%>

    <tr bgcolor="#FFFFFF">

      <td height="6" colspan="23">&nbsp;</td>

    </tr>

  </table>

  <p>&nbsp;</p>

  <p>&nbsp;    </p>

  <p>

    <%

rstSimple.Close

Set rstSimple = Nothing

cnnSimple.Close

Set cnnSimple = Nothing

%>

Open in new window

0
Comment
Question by:emzi19
  • 11
  • 7
18 Comments
 
LVL 29

Expert Comment

by:Badotz
ID: 22722786
Abandon your use of "With...End With". It does not provide any benefit, unless you are deep into the Object structure, and it is confusing to both man and machine.

Change your code, try it again and if you still have problems, post another response here.
0
 

Author Comment

by:emzi19
ID: 22722810
My code does not use With, sorry, i cannot see why this would cause the issue.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 22722948
hmmm...must've posted to the wrong question, sorry. Not enough coffee, I guess...let me try it again...
0
 

Author Comment

by:emzi19
ID: 22722980
no problem mate, i'm frustrated and tired too :)
0
 
LVL 29

Expert Comment

by:Badotz
ID: 22723004
This:

If recordset2.eof then
        no_rows = True
          End If
                 
If no_rows Then
response.write "No Vehicle Matches"
Else

can be shortened to this:

If recordset2.eof then
        no_rows = True
      response.write "No Vehicle Matches"
Else

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
This:

If Cstr(lastValue) <> Cstr(recordset2.fields("VehID")) Then

should be this:

If Cstr(lastValue) <> Cstr(recordset2.fields("VehID").Value) Then

Never rely on default properties; they are subject to change, and you will regret assuming what they are.

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Move Line 62 to Line 60, and report back what it is.
0
 

Author Comment

by:emzi19
ID: 22723178
nah, recorsdet3 still saying no matches when there is !
0
 
LVL 29

Expert Comment

by:Badotz
ID: 22723240
What did

response.write(SQL2)

display? Show me the SQL, please.
0
 

Author Comment

by:emzi19
ID: 22723879
SELECT vehiclepart.*, parttable.*, vehiclepart.VehID, parttable.Category FROM parttable RIGHT JOIN vehiclepart ON parttable.Partno = vehiclepart.partno WHERE (((vehiclepart.VehID)=6674) AND ((parttable.Category)='Catalytic Converters'));no parts matching     2.2i   Estate   ABY  2226  20  230    SOHC  5  01  1993  12  1994          
 80   RS2  
SELECT vehiclepart.*, parttable.*, vehiclepart.VehID, parttable.Category FROM parttable RIGHT JOIN vehiclepart ON parttable.Partno = vehiclepart.partno WHERE (((vehiclepart.VehID)=6675) AND ((parttable.Category)='Catalytic Converters'));no parts matching  
0
 

Author Comment

by:emzi19
ID: 22723885
yet there are parts matching for the RS2 vehicle (tested in access)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Expert Comment

by:Badotz
ID: 22723982
Change the RIGHT JOIN to INNER JOIN and try it again.
0
 

Author Comment

by:emzi19
ID: 22724068
Still the same
0
 
LVL 29

Expert Comment

by:Badotz
ID: 22724209
I "short-handed" the query:

SELECT v.VehID, p.Category
FROM parttable AS p
RIGHT JOIN vehiclepart AS v ON p.Partno = v.partno
WHERE v.VehID=6674 AND p.Category='Catalytic Converters';

If you go into Access, create a query, paste the above SQL into it and run it, do you get results?
0
 

Author Comment

by:emzi19
ID: 22724251
i got results from my initial query in access.
0
 

Author Comment

by:emzi19
ID: 22724301
i get nothing for the shortened query you suggested
0
 
LVL 29

Expert Comment

by:Badotz
ID: 22724538
It amounts to the same thing.

Try this in Access:

SELECT v.VehID, p.Category
FROM  vehiclepart AS v
RIGHT JOIN parttable AS p ON p.Partno = v.partno
WHERE v.VehID=6674;

and see if you get results.
0
 

Author Comment

by:emzi19
ID: 22724575
No, no results for that either
0
 

Author Comment

by:emzi19
ID: 22729086
I've tested the second recordset (recordset3) on its own for a particular vehicle and it works fine, put it back into the original page within the first recordset and it says no matches. Can you not nest and loop recordsets.
0
 

Accepted Solution

by:
emzi19 earned 0 total points
ID: 22729277
I've managed to fix this, it required a while and a wend instead of a Do while and a loop.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
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/…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

911 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

20 Experts available now in Live!

Get 1:1 Help Now