Solved

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

Posted on 2011-09-19
25
333 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Stiebel Eltron
  • 11
  • 7
  • 5
  • +2
25 Comments
 
LVL 10

Expert Comment

by:plummet
ID: 36559159
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
 

Author Comment

by:Stiebel Eltron
ID: 36559184
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
 

Author Comment

by:Stiebel Eltron
ID: 36559189
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
 

Author Comment

by:Stiebel Eltron
ID: 36559257
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
 
LVL 30

Accepted Solution

by:
Wayne Barron earned 250 total points
ID: 36559267
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
 
LVL 2

Expert Comment

by:mansooralia_yahoo
ID: 36559301
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
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 36559330
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
 
LVL 10

Expert Comment

by:plummet
ID: 36561155
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
 
LVL 9

Expert Comment

by:djpazza
ID: 36561156
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
 
LVL 10

Expert Comment

by:plummet
ID: 36561382
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
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 36562770
@ 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
 
LVL 10

Expert Comment

by:plummet
ID: 36563079
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 30

Expert Comment

by:Wayne Barron
ID: 36563324
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
 

Author Comment

by:Stiebel Eltron
ID: 36564726
@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
 

Author Comment

by:Stiebel Eltron
ID: 36564732
@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
 

Author Comment

by:Stiebel Eltron
ID: 36564751
@dipazza: I also tried your advise, but also the same, got error result from line 10, it's the sqlContent
0
 
LVL 10

Assisted Solution

by:plummet
plummet earned 250 total points
ID: 36565047
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
 

Author Comment

by:Stiebel Eltron
ID: 36565083
@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
 
LVL 10

Expert Comment

by:plummet
ID: 36565093
Hi Stiebel

That might mean that there is no seriesID for the number given; which number did you test with?
0
 

Author Comment

by:Stiebel Eltron
ID: 36565114
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
 
LVL 10

Expert Comment

by:plummet
ID: 36565141
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
 

Author Comment

by:Stiebel Eltron
ID: 36565195
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
 

Author Comment

by:Stiebel Eltron
ID: 36565309
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
 

Author Closing Comment

by:Stiebel Eltron
ID: 36565816
... to be continued... Just want to award you points...
Hope to hear from you again for the continuation of this thread...

Thank you!
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 36565921
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

757 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

21 Experts available now in Live!

Get 1:1 Help Now