emzi19
asked on
ASP JOIN displaying data
let me post the problem again to see if we can make more sense of it... right...
i am making a vehicle parts catalogue
3 tables - vindex - which lists all the parts in the catalogue.
parttable - which lists all the parts
and vehiclepart that lists all the parts that fit the vehicles (so this links the 2 tables together)
now each part is assigned a category e.g. abs123 is an abs sensor, as343 is a catalytic converter
i have created an sql statement and its fine when i lookup vehicles that have those parts assigned to them - but if i change the category then some vehicles do not display
SQL Statement
[CODE]SQL= "SELECT vindex.VehID, vindex.Make, vindex.Model, vindex.Mark, vindex.Litre, vindex.InModelSort, vindex.SubModel, vindex.Ident, vindex.Fuel, vindex.Body, vindex.Drive, vindex.EngNo, vindex.cc, vindex.Valves, vindex.bhp, vindex.PS, vindex.kW, vindex.Cam, vindex.Cyl, vindex.FrmM, vindex.FrmY, vindex.ToM, vindex.ToY, vindex.CarParc, vindex.Petrol, vindex.Internalnotes, vindex.CreateDate, vindex.CreatedBy, vindex.UpdatedDate, vindex.UpdatedBy, vehiclepart.partno, vehiclepart.PartComments, vehiclepart.VehicleComment s, vehiclepart.Internalnotes, vehiclepart.LinkDate, vehiclepart.LinkCreatedBy, vehiclepart.Altfrmm, vehiclepart.Altfrmy, vehiclepart.Alttom, vehiclepart.Alttoy, vehiclepart.Publish, vehiclepart.Partsort, parttable.shortdesc, parttable.partnotes, parttable.Category FROM parttable
RIGHT JOIN (vindex LEFT JOIN vehiclepart ON vindex.VehID = vehiclepart.VehID) ON parttable.Partno = vehiclepart.partno WHERE (((vindex.Make)='" & vmake & "') AND ((vindex.Model)='" & vmodel &"') AND ((vindex.Litre)='" & vlitre &"') AND ((parttable.Category)='" & vapplication & "')) OR (((vindex.Make)='" & vmake & "') AND ((vindex.Model)='" & vmodel &"') AND ((vindex.Litre)='" & vlitre & "') AND ((parttable.Category) Is Null))ORDER BY vindex.InModelSort, vehiclepart.Partsort"[/COD E]
This is what the results turn out like when i select catalytic converters
[IMG]http://www.r103.info/temp/screenshotB.jpg[/IMG]
But if i select another category then the RS2 model does not display, this is because, if we remove the where conditions of the SQL statment the data looks something like this.
e.g. (not all fields - just using this as an example)
id make model part category
6674 audi 80
6674 audi 80 rs2 TEST1 Catalytic Converters
6674 audi 80 rs2 TEST2 Catalytic Converters
6674 audi 80 rs2 TEST3 Catalytic Converters
But if the category doesnt match i.e. if we are looking up abs sensors, and we see here the list of converters, i still need it to display the rs2 model without any parts assigned.
and the code....
i am making a vehicle parts catalogue
3 tables - vindex - which lists all the parts in the catalogue.
parttable - which lists all the parts
and vehiclepart that lists all the parts that fit the vehicles (so this links the 2 tables together)
now each part is assigned a category e.g. abs123 is an abs sensor, as343 is a catalytic converter
i have created an sql statement and its fine when i lookup vehicles that have those parts assigned to them - but if i change the category then some vehicles do not display
SQL Statement
[CODE]SQL= "SELECT vindex.VehID, vindex.Make, vindex.Model, vindex.Mark, vindex.Litre, vindex.InModelSort, vindex.SubModel, vindex.Ident, vindex.Fuel, vindex.Body, vindex.Drive, vindex.EngNo, vindex.cc, vindex.Valves, vindex.bhp, vindex.PS, vindex.kW, vindex.Cam, vindex.Cyl, vindex.FrmM, vindex.FrmY, vindex.ToM, vindex.ToY, vindex.CarParc, vindex.Petrol, vindex.Internalnotes, vindex.CreateDate, vindex.CreatedBy, vindex.UpdatedDate, vindex.UpdatedBy, vehiclepart.partno, vehiclepart.PartComments, vehiclepart.VehicleComment
RIGHT JOIN (vindex LEFT JOIN vehiclepart ON vindex.VehID = vehiclepart.VehID) ON parttable.Partno = vehiclepart.partno WHERE (((vindex.Make)='" & vmake & "') AND ((vindex.Model)='" & vmodel &"') AND ((vindex.Litre)='" & vlitre &"') AND ((parttable.Category)='" & vapplication & "')) OR (((vindex.Make)='" & vmake & "') AND ((vindex.Model)='" & vmodel &"') AND ((vindex.Litre)='" & vlitre & "') AND ((parttable.Category) Is Null))ORDER BY vindex.InModelSort, vehiclepart.Partsort"[/COD
This is what the results turn out like when i select catalytic converters
[IMG]http://www.r103.info/temp/screenshotB.jpg[/IMG]
But if i select another category then the RS2 model does not display, this is because, if we remove the where conditions of the SQL statment the data looks something like this.
e.g. (not all fields - just using this as an example)
id make model part category
6674 audi 80
6674 audi 80 rs2 TEST1 Catalytic Converters
6674 audi 80 rs2 TEST2 Catalytic Converters
6674 audi 80 rs2 TEST3 Catalytic Converters
But if the category doesnt match i.e. if we are looking up abs sensors, and we see here the list of converters, i still need it to display the rs2 model without any parts assigned.
and the code....
<%
'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 vindex.VehID, vindex.Make, vindex.Model, vindex.Mark, vindex.Litre, vindex.InModelSort, vindex.SubModel, vindex.Ident, vindex.Fuel, vindex.Body, vindex.Drive, vindex.EngNo, vindex.cc, vindex.Valves, vindex.bhp, vindex.PS, vindex.kW, vindex.Cam, vindex.Cyl, vindex.FrmM, vindex.FrmY, vindex.ToM, vindex.ToY, vindex.CarParc, vindex.Petrol, vindex.Internalnotes, vindex.CreateDate, vindex.CreatedBy, vindex.UpdatedDate, vindex.UpdatedBy, vehiclepart.partno, vehiclepart.PartComments, vehiclepart.VehicleComments, vehiclepart.Internalnotes, vehiclepart.LinkDate, vehiclepart.LinkCreatedBy, vehiclepart.Altfrmm, vehiclepart.Altfrmy, vehiclepart.Alttom, vehiclepart.Alttoy, vehiclepart.Publish, vehiclepart.Partsort, parttable.shortdesc, parttable.partnotes, parttable.Category FROM parttable RIGHT JOIN (vindex LEFT JOIN vehiclepart ON vindex.VehID = vehiclepart.VehID) ON parttable.Partno = vehiclepart.partno WHERE (((vindex.Make)='" & vmake & "') AND ((vindex.Model)='" & vmodel &"') AND ((vindex.Litre)='" & vlitre &"') AND ((parttable.Category)='" & vapplication & "')) OR (((vindex.Make)='" & vmake & "') AND ((vindex.Model)='" & vmodel &"') AND ((vindex.Litre)='" & vlitre & "') AND ((parttable.Category) Is Null))ORDER BY vindex.InModelSort, vehiclepart.Partsort"
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="<%response.write recordset2.fields("VehID")%>" type="text" id="<%response.write recordset2.fields("VehID")%>" value="<%response.write recordset2.fields ("InModelSort")%>" size="3" maxlength="3" /></td>
<td colspan="22" bgcolor="<%=theColor%>"><span class="style2">
<%Response.write recordset2.fields("model") %>
<%Response.write recordset2.fields("SubModel")%>
</span></td>
</tr>
<%End If%>
<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%"><input name="pub" type="checkbox" id="pub" value="<%response.write recordset2.fields("VehID")%>" /></td>
<td width="4%"><span class="style14">
<% Response.write recordset2.fields("partno")%>
</span></td>
<td width="2%"> </td>
<td width="3%"> </td>
</tr>
<% 'End If %>
<% 'oddsevens = oddsevens + 1 %>
<%
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
%>
</p>
<p> </p></td>
</tr>
</table>
What happens if you change the word RIGHT to LEFT ?
ASKER
Microsoft JET Database Engine error '80040e14'
Join expression not supported.
Join expression not supported.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.