Solved

ASP JOIN displaying data

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
if form parameter is null redirect 2 53
How do i Properly Convert Date time String in C# 29 66
JSON error 4 51
Classic ASP application Will support SQL 2014 5 74
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…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now