emzi19
asked on
Using 2 recordsets If .EOF Else not working
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.
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") %>
<%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"> </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%"> </td>
<td width="4%"><span class="style14"> </span></td>
<td width="2%"> </td>
<td width="3%"> </td>
</tr>
<% Else
do while not recordset3.eof
response.write("parts matching")%>
<tr bgcolor="<%=theColor%>">
<td height="12"> </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")%>
</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"> </td>
</tr>
</table>
<p> </p>
<p> </p>
<p>
<%
rstSimple.Close
Set rstSimple = Nothing
cnnSimple.Close
Set cnnSimple = Nothing
%>
ASKER
My code does not use With, sorry, i cannot see why this would cause the issue.
hmmm...must've posted to the wrong question, sorry. Not enough coffee, I guess...let me try it again...
ASKER
no problem mate, i'm frustrated and tired too :)
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("Ve hID")) Then
should be this:
If Cstr(lastValue) <> Cstr(recordset2.fields("Ve hID").Valu e) 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.
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("Ve
should be this:
If Cstr(lastValue) <> Cstr(recordset2.fields("Ve
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.
ASKER
nah, recorsdet3 still saying no matches when there is !
What did
response.write(SQL2)
display? Show me the SQL, please.
response.write(SQL2)
display? Show me the SQL, please.
ASKER
SELECT vehiclepart.*, parttable.*, vehiclepart.VehID, parttable.Category FROM parttable RIGHT JOIN vehiclepart ON parttable.Partno = vehiclepart.partno WHERE (((vehiclepart.VehID)=6674 ) AND ((parttable.Category)='Cat alytic 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)='Cat alytic Converters'));no parts matching
80 RS2
SELECT vehiclepart.*, parttable.*, vehiclepart.VehID, parttable.Category FROM parttable RIGHT JOIN vehiclepart ON parttable.Partno = vehiclepart.partno WHERE (((vehiclepart.VehID)=6675
ASKER
yet there are parts matching for the RS2 vehicle (tested in access)
Change the RIGHT JOIN to INNER JOIN and try it again.
ASKER
Still the same
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?
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?
ASKER
i got results from my initial query in access.
ASKER
i get nothing for the shortened query you suggested
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.
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.
ASKER
No, no results for that either
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Change your code, try it again and if you still have problems, post another response here.