Solved

ASP JOIN displaying data

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
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/…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 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

17 Experts available now in Live!

Get 1:1 Help Now