Solved

ASP JOIN displaying data

Posted on 2008-10-14
3
366 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

689 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