How to pull-out data from 2 tables in Access & show it in 1 ASP page?

Dear EE experts,

We would like to ask for tech support on how to pull-out data from 2 table in 1 access db & show it in 1 ASP page...

Kindly check the attached code & access db that we created. Could you please advise if the SQL statement that I created is correct or not?

Before this page, there's an Upload page that if a user uploads files, the data of it will be keep into that db, and the attached code is a page that needs to show the uploaded files.

Maximum number of files (photos) that a user can upload is 5, so if a user uploads 5 photos, there should be 5 items to be shown, if 3, then it should be 3...

From the code, you'll see that there are some items that I'm pulling out some columns from other table, in regards to that, I'm not sure if what I did is correct...

Please advise...
Hope to hear soon...
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../photo/products.mdb")
	Set myConnection = Server.CreateObject("ADODB.Connection")
	Set rsProducts = Server.CreateObject("ADODB.Recordset")
SeriesID = request.QueryString("SeriesID")
myConnection.Open connectString
sqlContent = "SELECT ID, SeriesID, product_name, image1, image2, image3, image4, image5, download1, download2, download3, download4, download5, product_file, createby, createdate,series_name FROM Series,Products WHERE Products.seriesid=Series.seriesid  AND SeriesID ="&Seriesid

Set rsProducts = myConnection.Execute(sqlContent)
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<meta name="Author" content="IT">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta http-equiv="Content-Type" content="text/html; charset=windows-874">

<meta name="GENERATOR" content="Mozilla/4.7 [en] (Win98; I) [Netscape]">

<title>..:: Stiebel Eltron's iPhoto Stock Gallery - Powered by STE IT Dept. ::..</title>
<style TYPE="text/css">
<!--
	a
 	{
  		color:#000080;
		font-family:verdana;
		text-decoration:none;
		font-size:12px
	}
	
	
	a:active
 	{
  		color:red;

	}

	a:hover
 	{
  		color:#FF0000;
		text-decoration:none;
		cursor:crosshair;
	}
		
	table
	{
		font-family:verdana;
	}
	
	td
	{
		font-family:verdana;
		font-size:10e;
	}

	.b
	{
		font-color:#0A0EB7;
	}		

-->
</STYLE>

<!--date-->
<SCRIPT LANGUAGE="JavaScript">

<!-- Begin
dayName = new Array("", "¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿", "¿¿¿¿¿¿¿¿", "¿¿¿¿¿", "¿¿¿¿¿", "¿¿¿¿¿¿¿")
monName = new Array("¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿")
now = new Date
//  End -->
</script>

<script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script>

</head>

<body bgproperties="fixed" bgcolor="#000000" onLoad="javascript:startmenu()">
<table width="760" border="0" cellspacing="3" cellpadding="1">
  <tr>
    <td width="134"><img src="IMG/logo_res.jpg" alt="STIEBEL ELTRON" /></td>
    <td width="620" valign="top" bgcolor="#666666"><object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0" width="548" height="197">
      <param name="movie" value="homeintro20.swf" />
      <param name="quality" value="high" />
      <param name="wmode" value="opaque" />
      <embed src="homeintro20.swf" quality="high" wmode="opaque" pluginspage="http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash" type="application/x-shockwave-flash" width="548" height="197"></embed>
    </object></td>
    <td width="134"><img src="IMG/white_square.png" alt="STIEBEL ELTRON" /></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td align="right" bgcolor="#999999"><font face="Verdana, Geneva, sans-serif" color="#ffffff">
    <%
   '// If user is logged in show name and logout link
   If Session("loggedin") = true Then
      Response.Write "Welcome: " & Session("name")
      Response.Write "<br /><a href=""../iphoto/login2/logout.asp"">Logout</a>"
   Else
      Response.Write "<a href=""ste-iphoto-login.asp"" title=""CLICK TO LOGIN"">REGISTERED USERS</a>"
   End If
	%>&nbsp;
	</td>
    <td bgcolor="#666666" align="center">&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    
    <td align="left" bordercolor="#999999" bgcolor="#FFFFFF">
    » <img src="IMG/imgfolder.gif" />&nbsp;<a href="javaScript:menu0func()">STE Shower Units - <b><font face="verdana" size="+1"> - <%=rsProducts("series_name")%></font></b></a>
	</td>
	<td bgcolor="#999999" align="center">&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    
<td width="568" align="left" bordercolor="#999999" bgcolor="#FFFFFF">
    	<table width="78%" border="0" cellspacing="3" cellpadding="1">
  			<tr> 
				<% 
				dim i
			    i=0
				Do Until rsProducts.EOF 
			   	%>
		    	<td align="center" bordercolor="#999999" bgcolor="#FFFFFF">
					<img src="<%=rsProducts("image1")%>" alt="<%=rsProducts("series_name")%>"/>
					<br/>
					<center><font face="Tahoma, Geneva, sans-serif" size="1"><%=rsProducts("product_name")%>&nbsp;&nbsp;
   				    <%
        				If Session("loggedIn") = true Then
            			Response.Write "<a href=""" & rsProducts("download1") & """>DOWNLOAD</a>"
						
						End If
    					%>
        				
        			</center>
			  </td>
			<%
			if i>2 then
			%>
		</tr>
  		<tr>
	
    		<% 
			i=0
				else
			i=i+1
			end if
        	rsProducts.MoveNext 
    		Loop 
			if i<>0 and (4-i)<>0 then
	   		%>
	   			<td colspan="<%=(4-i)%>" bgcolor="#FFFFFF">&nbsp;</td>
	   		<%
			end if
			%>
  		</tr>
  
	</table>
    </td>
	<td bgcolor="#999999" align="center">&nbsp;</td>
  </tr>
  <tr>
  	<td bgcolor="#000000">&nbsp;
    	
    </td>
  	<td bgcolor="#666666" colspan="2" align="left">
    	<a href = "javascript:history.back()"><font size="0.5e">&nbsp;BACK TO TOP</font></a>
    </td>
  </tr>
  <tr>
  	<td bgcolor="#000000">&nbsp;
    	
    </td>
  	<td align="center" bgcolor="#999999" colspan="2">
    	<a href = "http://www.cssasia.info/iphoto/"><font size="0.5e">BACK TO MAIN&nbsp;<img src="IMG/base.gif" alt="BACK TO MAIN PAGE" border="0" /></font></a>
    </td>
  </tr>  
  
</table>
</body>
</html>

Open in new window

products.mdb
Stiebel EltronAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Wayne BarronConnect With a Mentor Author, Web DeveloperCommented:
Here you go, run it and see what you think.
Also, read the notes I have in it along line 142, not all columns exist in your database.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Set connectString = Server.CreateObject("ADODB.Connection")
connectString.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("products.mdb") & ";"
connectString.Open


SeriesID = request.QueryString("SeriesID")

Set sqlContent = CreateObject("ADODB.Command")
sqlContent.ActiveConnection=connectString
sqlContent.Prepared = true
sqlContent.commandtext = "SELECT category.categoryid, category.categoryname, category.pathcate, category.groupid, series.seriesid, series.typeid, series.brandid, series.series_name FROM category INNER JOIN series ON category.categoryid = series.categoryid WHERE series.seriesid=?"
sqlContent.Parameters.Append sqlContent.CreateParameter("@seriesid", 3, 1, , SeriesID)
Set rsProducts = sqlContent.Execute
if not rsProducts.eof then
series_name = rsProducts("series_name")
end if
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<meta name="Author" content="IT">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta http-equiv="Content-Type" content="text/html; charset=windows-874">

<meta name="GENERATOR" content="Mozilla/4.7 [en] (Win98; I) [Netscape]">

<title>..:: Stiebel Eltron's iPhoto Stock Gallery - Powered by STE IT Dept. ::..</title>
<style TYPE="text/css">
<!--
	a
 	{
  		color:#000080;
		font-family:verdana;
		text-decoration:none;
		font-size:12px
	}
	
	
	a:active
 	{
  		color:red;

	}

	a:hover
 	{
  		color:#FF0000;
		text-decoration:none;
		cursor:crosshair;
	}
		
	table
	{
		font-family:verdana;
	}
	
	td
	{
		font-family:verdana;
		font-size:10e;
	}

	.b
	{
		font-color:#0A0EB7;
	}		

-->
</STYLE>

<!--date-->
<SCRIPT LANGUAGE="JavaScript">

<!-- Begin
dayName = new Array("", "¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿", "¿¿¿¿¿¿¿¿", "¿¿¿¿¿", "¿¿¿¿¿", "¿¿¿¿¿¿¿")
monName = new Array("¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿")
now = new Date
//  End -->
</script>

<script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script>

</head>

<body bgproperties="fixed" bgcolor="#000000" onLoad="javascript:startmenu()">
<table width="760" border="0" cellspacing="3" cellpadding="1">
  <tr>
    <td width="134"><img src="IMG/logo_res.jpg" alt="STIEBEL ELTRON" /></td>
    <td width="620" valign="top" bgcolor="#666666"><object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0" width="548" height="197">
      <param name="movie" value="homeintro20.swf" />
      <param name="quality" value="high" />
      <param name="wmode" value="opaque" />
      <embed src="homeintro20.swf" quality="high" wmode="opaque" pluginspage="http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash" type="application/x-shockwave-flash" width="548" height="197"></embed>
    </object></td>
    <td width="134"><img src="IMG/white_square.png" alt="STIEBEL ELTRON" /></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td align="right" bgcolor="#999999"><font face="Verdana, Geneva, sans-serif" color="#ffffff">
    <%
   '// If user is logged in show name and logout link
   If Session("loggedin") = true Then
      Response.Write "Welcome: " & Session("name")
      Response.Write "<br /><a href=""../iphoto/login2/logout.asp"">Logout</a>"
   Else
      Response.Write "<a href=""ste-iphoto-login.asp"" title=""CLICK TO LOGIN"">REGISTERED USERS</a>"
   End If
	%>&nbsp;
	</td>
    <td bgcolor="#666666" align="center">&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    
    <td align="left" bordercolor="#999999" bgcolor="#FFFFFF">
    » <img src="IMG/imgfolder.gif" />&nbsp;
    <%if rsProducts.eof then
	response.Write"STE Shower Units - Sorry, there is no products for that number"
	else%>
    <a href="javaScript:menu0func()">STE Shower Units - <b><font face="verdana" size="+1"> - 
	<%=series_name%></font></b></a><%end if%>
	</td>
	<td bgcolor="#999999" align="center">&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    
<td width="568" align="left" bordercolor="#999999" bgcolor="#FFFFFF">
    	<table width="78%" border="0" cellspacing="3" cellpadding="1">
  			<tr> 
				<% 
				dim i
			    i=0
				Do Until rsProducts.EOF 
				if not rsProducts.eof then
				' The below do not exist in your database. This needs to be fixed.
				'image1 = rsProducts("image1")
				' product_name = rsProducts("product_name")
				' download1 = rsProducts("download1")
				end if
			   	%>
		    	<td align="center" bordercolor="#999999" bgcolor="#FFFFFF">
					<img src="<%=image1%>" alt="<%=series_name%>"/>
					<br/>
					<center><font face="Tahoma, Geneva, sans-serif" size="1"><%=product_name%>&nbsp;&nbsp;
   				    <%
        				If Session("loggedIn") = true Then
            			Response.Write "<a href=""" & download1 & """>DOWNLOAD</a>"
						
						End If
    					%>
        				
        			</center>
			  </td>
			<%
			if i>2 then
			%>
		</tr>
  		<tr>
	
    		<% 
			i=0
				else
			i=i+1
			end if
        	rsProducts.MoveNext 
    		Loop 
			if i<>0 and (4-i)<>0 then
	   		%>
	   			<td colspan="<%=(4-i)%>" bgcolor="#FFFFFF">&nbsp;</td>
	   		<%
			end if
			%>
  		</tr>
  
	</table>
    </td>
	<td bgcolor="#999999" align="center">&nbsp;</td>
  </tr>
  <tr>
  	<td bgcolor="#000000">&nbsp;
    	
    </td>
  	<td bgcolor="#666666" colspan="2" align="left">
    	<a href = "javascript:history.back()"><font size="0.5e">&nbsp;BACK TO TOP</font></a>
    </td>
  </tr>
  <tr>
  	<td bgcolor="#000000">&nbsp;
    	
    </td>
  	<td align="center" bgcolor="#999999" colspan="2">
    	<a href = "http://www.cssasia.info/iphoto/"><font size="0.5e">BACK TO MAIN&nbsp;<img src="IMG/base.gif" alt="BACK TO MAIN PAGE" border="0" /></font></a>
    </td>
  </tr>  
  
</table>
</body>
</html>

Open in new window


Carrzkiss
0
 
plummetCommented:
Hi Stiebel

There are some changes I would recommend from a quick look at your code:

1) Make sure you have spaces when joining strings as it looks like you don't at the moment (see the end of the string):

sqlContent = "SELECT ID, SeriesID, product_name, image1, image2, image3, image4, image5, download1, download2, download3, download4, download5, product_file, createby, createdate,series_name FROM Series,Products WHERE Products.seriesid=Series.seriesid  AND SeriesID =" & Seriesid

Open in new window


2) Open an ADO recordset like this:

rsProducts.open sqlContent, myConnection, (options here)

Open in new window


The options are to specify how you want to open the recordset but to start with you are probably OK with the defaults.

Hope that helps!
0
 
Stiebel EltronAuthor Commented:
Hi plummet!

Thank you for your prompt response!
Here's the error result that we received:

Microsoft VBScript compilation error '800a03ee'

Expected ')'

/iphoto/asia/ste-shower-units.asp, line 15

rsProducts.open sqlContent, myConnection, (options here)

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Stiebel EltronAuthor Commented:
But when I change and used this command: Set rsProducts = myConnection.Execute(sqlContent)
I got this error:
Microsoft JET Database Engine error '80004005'

The specified field 'SeriesID' could refer to more than one table listed in the FROM clause of your SQL statement.

/iphoto/asia/ste-shower-units.asp, line 13
0
 
Stiebel EltronAuthor Commented:
I removed the "," after the myConnection: rsProducts.open sqlContent, myConnection

Then still the same error result:
Microsoft JET Database Engine error '80004005'

The specified field 'SeriesID' could refer to more than one table listed in the FROM clause of your SQL statement.

/iphoto/asia/ste-shower-units.asp, line 15


Please help to resolve...
0
 
mansooralia_yahooCommented:
Hi stiebel, you need to add alias to the table names in the from clause and use the same into the field list, e.g.

sqlContent = "SELECT ID, SeriesID, product_name, image1, image2, image3, image4, image5, download1, download2, download3, download4, download5, product_file, createby, createdate,series_name

FROM Series s,Products p

PUT S WHERE THE FIELDS ARE TO BE EXTRACTED FROM SERIES AND P FOR THE FIELDS THAT ARE TO BE EXTRACTED FROM PRODUCTS

WHERE Products.seriesid=Series.seriesid  AND SeriesID ="&Seriesid
0
 
Wayne BarronAuthor, Web DeveloperCommented:
Here is a video to demonstrate how to do this on your own.
http://www.youtube.com/watch?v=OpePhW8z0mM
Good Luck

@ mansooralia
If he follows the code that I provided, he will be able to accomplish what he needs to do.
Carrzkiss
0
 
plummetCommented:
Hi Stiebel,

I had to go out and I can see that others have helped you in the meantime so I will leave this to them, unless you want me to help.

Good luck!
0
 
djpazzaCommented:
The specified field 'SeriesID' could refer to more than one table listed in the FROM clause of your SQL statement.

This is because the same field exists in the query.  You would need to refer to them by tablename.fieldname

I can not open the attachment at the moment but one way of doing this would be an inner join.  

"SELECT ID, SeriesID, product_name, image1, image2, image3, image4, image5, download1, download2, download3, download4, download5, product_file, createby, createdate,series_name FROM Series
INNER JOIN Products WHERE Products.seriesid=Series.seriesid  AND SeriesID = "& Seriesid
0
 
plummetCommented:
Actually, the correct way to build the SQL string using an inner join would be:

sqlContent = "SELECT p.ID, p.SeriesID, p.product_name, p.image1, p.image2, p.image3, p.image4, p.image5, p.download1, p.download2, p.download3, p.download4, p.download5, "
sqlContent = sqlContent & "p.product_file, p.createby, p.createdate,s.series_name FROM Series s inner join Products p on p.seriesid=s.seriesid where SeriesID =" & Seriesid

Open in new window


Here is the string in your code, which as far as I can see should work OK now. However I can't test it so do let me know if you have further problems.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../photo/products.mdb")
	Set myConnection = Server.CreateObject("ADODB.Connection")
	Set rsProducts = Server.CreateObject("ADODB.Recordset")

SeriesID = request.QueryString("SeriesID")
myConnection.Open connectString

sqlContent = "SELECT p.ID, p.SeriesID, p.product_name, p.image1, p.image2, p.image3, p.image4, p.image5, p.download1, p.download2, p.download3, p.download4, p.download5, "
sqlContent = sqlContent & "p.product_file, p.createby, p.createdate,s.series_name FROM Series s inner join Products p on p.seriesid=s.seriesid where SeriesID =" & Seriesid

rsProducts.open sqlContent, myConnection
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<meta name="Author" content="IT">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta http-equiv="Content-Type" content="text/html; charset=windows-874">

<meta name="GENERATOR" content="Mozilla/4.7 [en] (Win98; I) [Netscape]">

<title>..:: Stiebel Eltron's iPhoto Stock Gallery - Powered by STE IT Dept. ::..</title>
<style TYPE="text/css">
<!--
	a
 	{
  		color:#000080;
		font-family:verdana;
		text-decoration:none;
		font-size:12px
	}
	
	
	a:active
 	{
  		color:red;

	}

	a:hover
 	{
  		color:#FF0000;
		text-decoration:none;
		cursor:crosshair;
	}
		
	table
	{
		font-family:verdana;
	}
	
	td
	{
		font-family:verdana;
		font-size:10e;
	}

	.b
	{
		font-color:#0A0EB7;
	}		

-->
</STYLE>

<!--date-->
<SCRIPT LANGUAGE="JavaScript">

<!-- Begin
dayName = new Array("", "¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿", "¿¿¿¿¿¿¿¿", "¿¿¿¿¿", "¿¿¿¿¿", "¿¿¿¿¿¿¿")
monName = new Array("¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿")
now = new Date
//  End -->
</script>

<script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script>

</head>

<body bgproperties="fixed" bgcolor="#000000" onLoad="javascript:startmenu()">
<table width="760" border="0" cellspacing="3" cellpadding="1">
  <tr>
    <td width="134"><img src="IMG/logo_res.jpg" alt="STIEBEL ELTRON" /></td>
    <td width="620" valign="top" bgcolor="#666666"><object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0" width="548" height="197">
      <param name="movie" value="homeintro20.swf" />
      <param name="quality" value="high" />
      <param name="wmode" value="opaque" />
      <embed src="homeintro20.swf" quality="high" wmode="opaque" pluginspage="http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash" type="application/x-shockwave-flash" width="548" height="197"></embed>
    </object></td>
    <td width="134"><img src="IMG/white_square.png" alt="STIEBEL ELTRON" /></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td align="right" bgcolor="#999999"><font face="Verdana, Geneva, sans-serif" color="#ffffff">
    <%
   '// If user is logged in show name and logout link
   If Session("loggedin") = true Then
      Response.Write "Welcome: " & Session("name")
      Response.Write "<br /><a href=""../iphoto/login2/logout.asp"">Logout</a>"
   Else
      Response.Write "<a href=""ste-iphoto-login.asp"" title=""CLICK TO LOGIN"">REGISTERED USERS</a>"
   End If
	%>&nbsp;
	</td>
    <td bgcolor="#666666" align="center">&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    
    <td align="left" bordercolor="#999999" bgcolor="#FFFFFF">
    » <img src="IMG/imgfolder.gif" />&nbsp;<a href="javaScript:menu0func()">STE Shower Units - <b><font face="verdana" size="+1"> - <%=rsProducts("series_name")%></font></b></a>
	</td>
	<td bgcolor="#999999" align="center">&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    
<td width="568" align="left" bordercolor="#999999" bgcolor="#FFFFFF">
    	<table width="78%" border="0" cellspacing="3" cellpadding="1">
  			<tr> 
				<% 
				dim i
			    i=0
				Do Until rsProducts.EOF 
			   	%>
		    			<td align="center" bordercolor="#999999" bgcolor="#FFFFFF">
					<img src="<%=rsProducts("image1")%>" alt="<%=rsProducts("series_name")%>"/>
					<br/>
					<center><font face="Tahoma, Geneva, sans-serif" size="1"><%=rsProducts("product_name")%>&nbsp;&nbsp;
   					<%
        				If Session("loggedIn") = true Then
            					Response.Write "<a href=""" & rsProducts("download1") & """>DOWNLOAD</a>"
						
					End If
    					%>
        				
        				</center>
			  		</td>
				<%
				if i>2 then
				%>
					</tr>
  					<tr>
	
    				<% 
					i=0
				else
					i=i+1
				end if
        		rsProducts.MoveNext 
    		Loop 
			if i<>0 and (4-i)<>0 then
	   		%>
	   			<td colspan="<%=(4-i)%>" bgcolor="#FFFFFF">&nbsp;</td>
	   		<%
			end if
			%>
  		</tr>
  
	</table>
    </td>
	<td bgcolor="#999999" align="center">&nbsp;</td>
  </tr>
  <tr>
  	<td bgcolor="#000000">&nbsp;
    	
    </td>
  	<td bgcolor="#666666" colspan="2" align="left">
    	<a href = "javascript:history.back()"><font size="0.5e">&nbsp;BACK TO TOP</font></a>
    </td>
  </tr>
  <tr>
  	<td bgcolor="#000000">&nbsp;
    	
    </td>
  	<td align="center" bgcolor="#999999" colspan="2">
    	<a href = "http://www.cssasia.info/iphoto/"><font size="0.5e">BACK TO MAIN&nbsp;<img src="IMG/base.gif" alt="BACK TO MAIN PAGE" border="0" /></font></a>
    </td>
  </tr>  
  
</table>
</body>
</html>

Open in new window


I have a question for you though, do you want to see the 5 images that are in the product table? Because your code will only show image1 and download1. If you want to show all 5 (if they exist) then you need to change the code which is not difficult but you need to let me know if each image has its own <tr> or should be in its own <td>, for example.

0
 
Wayne BarronAuthor, Web DeveloperCommented:
@ plummet
You state that the way you have your SQL statement written is the proper way to write it.

I am always up to learn better ways of doing my code, would you mind explaining how your code is better than the way that I have provided above?

Also, to stiebel of this question.
The code that I provided will help in the prevention of SQL Injection, as inserting the following
SeriesID =" & Seriesid
Will make your code vulnerable to sql injections

There are also other steps to take in the prevention, but using Parameterized queries, is a start in the prevention.

If you want to learn, you can check out my article here
http://www.experts-exchange.com/A_3626.html

Carrzkiss
0
 
plummetCommented:
Hi Carrzkiss

Your code is extracting data from the Category table joining to the Series table, and from what I can see the requirement is to read from Products and Series. Otherwise your comments about SQL injection are sensible and using a parameterized query would be a good solution to overcome this potential security risk. I was actually looking at the code in the previous comment that hadn't included a table alias and was therefore going to fail.

I'm sure you're a much better coder than I am - so don't look to learn anything from me.

0
 
Wayne BarronAuthor, Web DeveloperCommented:
OK, yea, I did mess up on the table of which I created the SQL for.
But that does not explain why your code would be better than the code that I provided.
0
 
Stiebel EltronAuthor Commented:
@carrzkiss: Thank you very much for the nice video that you shared. I followed what you explained in the video, before I go and check your codes... If I select SeriesID=1, here's the error result that I'm receiving:
"ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/iphoto/asia/ste-shower-units.asp, line 0"

Then if I select SeriesID=17 (in whereas, it has a record already), here's the error result that I'm receiving:
"ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/iphoto/asia/ste-shower-units.asp, line 148"

Line 148 is:       <img src="<%=rsProducts("image1")%>" alt="<%=rsProducts("series_name")%>"/>

*** The page is showing, but that errors shows in the middle of the page, in whereas the picture of the products shoould show...
0
 
Stiebel EltronAuthor Commented:
@plummet: I tried your advise & here's the error result that I received:
Microsoft JET Database Engine error '80004005'

The specified field 'SeriesID' could refer to more than one table listed in the FROM clause of your SQL statement.

/iphoto/asia/ste-shower-units.asp, line 13

In whereas Line 13 is: rsProducts.open sqlContent, myConnection
0
 
Stiebel EltronAuthor Commented:
@dipazza: I also tried your advise, but also the same, got error result from line 10, it's the sqlContent
0
 
plummetConnect With a Mentor Commented:
Hi Stiebel,

I can see the problem, I forgot one small thing! Here is the amended full script, the change is at the end of the sqlContent line:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../photo/products.mdb")
	Set myConnection = Server.CreateObject("ADODB.Connection")
	Set rsProducts = Server.CreateObject("ADODB.Recordset")

SeriesID = request.QueryString("SeriesID")
myConnection.Open connectString

sqlContent = "SELECT p.ID, p.SeriesID, p.product_name, p.image1, p.image2, p.image3, p.image4, p.image5, p.download1, p.download2, p.download3, p.download4, p.download5, "
sqlContent = sqlContent & "p.product_file, p.createby, p.createdate,s.series_name FROM Series s inner join Products p on p.seriesid=s.seriesid where p.SeriesID =" & Seriesid

rsProducts.open sqlContent, myConnection
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<meta name="Author" content="IT">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta http-equiv="Content-Type" content="text/html; charset=windows-874">

<meta name="GENERATOR" content="Mozilla/4.7 [en] (Win98; I) [Netscape]">

<title>..:: Stiebel Eltron's iPhoto Stock Gallery - Powered by STE IT Dept. ::..</title>
<style TYPE="text/css">
<!--
	a
 	{
  		color:#000080;
		font-family:verdana;
		text-decoration:none;
		font-size:12px
	}
	
	
	a:active
 	{
  		color:red;

	}

	a:hover
 	{
  		color:#FF0000;
		text-decoration:none;
		cursor:crosshair;
	}
		
	table
	{
		font-family:verdana;
	}
	
	td
	{
		font-family:verdana;
		font-size:10e;
	}

	.b
	{
		font-color:#0A0EB7;
	}		

-->
</STYLE>

<!--date-->
<SCRIPT LANGUAGE="JavaScript">

<!-- Begin
dayName = new Array("", "¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿", "¿¿¿¿¿¿¿¿", "¿¿¿¿¿", "¿¿¿¿¿", "¿¿¿¿¿¿¿")
monName = new Array("¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿", "¿¿¿¿¿¿", "¿¿¿¿¿¿¿¿¿", "¿¿¿¿¿¿¿")
now = new Date
//  End -->
</script>

<script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script>

</head>

<body bgproperties="fixed" bgcolor="#000000" onLoad="javascript:startmenu()">
<table width="760" border="0" cellspacing="3" cellpadding="1">
  <tr>
    <td width="134"><img src="IMG/logo_res.jpg" alt="STIEBEL ELTRON" /></td>
    <td width="620" valign="top" bgcolor="#666666"><object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0" width="548" height="197">
      <param name="movie" value="homeintro20.swf" />
      <param name="quality" value="high" />
      <param name="wmode" value="opaque" />
      <embed src="homeintro20.swf" quality="high" wmode="opaque" pluginspage="http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash" type="application/x-shockwave-flash" width="548" height="197"></embed>
    </object></td>
    <td width="134"><img src="IMG/white_square.png" alt="STIEBEL ELTRON" /></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td align="right" bgcolor="#999999"><font face="Verdana, Geneva, sans-serif" color="#ffffff">
    <%
   '// If user is logged in show name and logout link
   If Session("loggedin") = true Then
      Response.Write "Welcome: " & Session("name")
      Response.Write "<br /><a href=""../iphoto/login2/logout.asp"">Logout</a>"
   Else
      Response.Write "<a href=""ste-iphoto-login.asp"" title=""CLICK TO LOGIN"">REGISTERED USERS</a>"
   End If
	%>&nbsp;
	</td>
    <td bgcolor="#666666" align="center">&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    
    <td align="left" bordercolor="#999999" bgcolor="#FFFFFF">
    » <img src="IMG/imgfolder.gif" />&nbsp;<a href="javaScript:menu0func()">STE Shower Units - <b><font face="verdana" size="+1"> - <%=rsProducts("series_name")%></font></b></a>
	</td>
	<td bgcolor="#999999" align="center">&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    
<td width="568" align="left" bordercolor="#999999" bgcolor="#FFFFFF">
    	<table width="78%" border="0" cellspacing="3" cellpadding="1">
  			<tr> 
				<% 
				dim i
			    i=0
				Do Until rsProducts.EOF 
			   	%>
		    			<td align="center" bordercolor="#999999" bgcolor="#FFFFFF">
					<img src="<%=rsProducts("image1")%>" alt="<%=rsProducts("series_name")%>"/>
					<br/>
					<center><font face="Tahoma, Geneva, sans-serif" size="1"><%=rsProducts("product_name")%>&nbsp;&nbsp;
   					<%
        				If Session("loggedIn") = true Then
            					Response.Write "<a href=""" & rsProducts("download1") & """>DOWNLOAD</a>"
						
					End If
    					%>
        				
        				</center>
			  		</td>
				<%
				if i>2 then
				%>
					</tr>
  					<tr>
	
    				<% 
					i=0
				else
					i=i+1
				end if
        		rsProducts.MoveNext 
    		Loop 
			if i<>0 and (4-i)<>0 then
	   		%>
	   			<td colspan="<%=(4-i)%>" bgcolor="#FFFFFF">&nbsp;</td>
	   		<%
			end if
			%>
  		</tr>
  
	</table>
    </td>
	<td bgcolor="#999999" align="center">&nbsp;</td>
  </tr>
  <tr>
  	<td bgcolor="#000000">&nbsp;
    	
    </td>
  	<td bgcolor="#666666" colspan="2" align="left">
    	<a href = "javascript:history.back()"><font size="0.5e">&nbsp;BACK TO TOP</font></a>
    </td>
  </tr>
  <tr>
  	<td bgcolor="#000000">&nbsp;
    	
    </td>
  	<td align="center" bgcolor="#999999" colspan="2">
    	<a href = "http://www.cssasia.info/iphoto/"><font size="0.5e">BACK TO MAIN&nbsp;<img src="IMG/base.gif" alt="BACK TO MAIN PAGE" border="0" /></font></a>
    </td>
  </tr>  
  
</table>
</body>
</html>

Open in new window


That should be better, I hope.

Best regards
0
 
Stiebel EltronAuthor Commented:
@plummet: I received the same error as carrzkiss:
»  STE Shower Units - -
ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/iphoto/asia/ste-shower-units.asp, line 0
0
 
plummetCommented:
Hi Stiebel

That might mean that there is no seriesID for the number given; which number did you test with?
0
 
Stiebel EltronAuthor Commented:
I test both, SeriesID that aren't on the db & SeriesID that are on the db. Like for example SeriesID 17 up, it has, but the result is error ... :-(
0
 
plummetCommented:
I think the problem might be that you are looking in the Series table, which has many seriesIDs, but the query is extracting from Series and Products. On the database you uploaded there are only 4 records - with series ID 116, 183 and 185. Can you try one of these numbers?
0
 
Stiebel EltronAuthor Commented:
Here's the error result:
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/iphoto/asia/ste-shower-units.asp, line 148
 
Line 148: <img src="<%=rsProducts("image1")%>" alt="<%=rsProducts("series_name")%>"/>
Even I change the series_name into product_name, the error result is still the same...
0
 
Stiebel EltronAuthor Commented:
And for your question previously,
>>"do you want to see the 5 images that are in the product table? Because your code will only show image1 and download1. If you want to show all 5 (if they exist) then you need to change the code which is not difficult but you need to let me know if each image has its own <tr> or should be in its own <td>, for example."<<

-- I only want to show the number of pictures that the user uploads. For example, if the user uploads 1 photo, so it will only show 1 photo. Max is 5 photos...
0
 
Stiebel EltronAuthor Commented:
... to be continued... Just want to award you points...
Hope to hear from you again for the continuation of this thread...

Thank you!
0
 
Wayne BarronAuthor, Web DeveloperCommented:
My code explained how to get around the error that you are receiving.
Other than the fact that the
<%=rsProducts("image1")%>" alt="<%=rsProducts("series_name")%>"/>
Does not exist in your database.
You need to get this columns added to your database, in order to use them in your project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.