Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ASP JOIN displaying data

Posted on 2008-10-14
3
Medium Priority
?
371 Views
Last Modified: 2008-10-20
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.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"[/CODE]

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") %>
        &nbsp;
        <%Response.write recordset2.fields("SubModel")%>
      </span></td>
    </tr>
	<%End If%>
    <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%"><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")%>
        &nbsp;</span></td>
      <td width="2%">&nbsp;</td>
      <td width="3%">&nbsp;</td>
    </tr>
    <% 'End If %>
    <% 'oddsevens = oddsevens + 1 %>
    <%
    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
%>
  </p>
  <p>&nbsp;</p></td>
  </tr>
</table>

Open in new window

0
Comment
Question by:emzi19
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 7

Expert Comment

by:Norush
ID: 22709787
What happens if you change the word RIGHT to LEFT ?
0
 

Author Comment

by:emzi19
ID: 22709809
Microsoft JET Database Engine error '80040e14'
Join expression not supported.
0
 

Accepted Solution

by:
emzi19 earned 0 total points
ID: 22710175
Anyone ?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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 informatio…
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/…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

636 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