Solved

ASP JOIN displaying data

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

825 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