Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

How to add table name in SQLContent in SQL Query?

... related to my ID: 27314902

Dear EE Experts,

We would like to ask for tech support on how to add table_name from FROM part in SQL Query.
From the SQL Query that we got from EE expert (carrzkiss), here's the query that was advised:
>>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=?"<<

The FROM have the Category table, and INNER JOIN series table, in whereas the column name "image1" & "product_name" can't be found in Category & Series table, but can be found in Products table.

Kindly see the attached images, specially the web error for the error result that we received, even we set an seriesID for it...

Thank you & hope to hear soon...
dbTable-Category.jpg
dbTable-Series.jpg
dbTable-Products.jpg
SQL-Content.jpg
web-error.jpg
0
Stiebel Eltron
Asked:
Stiebel Eltron
1 Solution
 
Wayne BarronAuthor, Web DeveloperCommented:
Did you not view the video that I provided the tutorial link for?
It shows you exactly how to do what you are wanting to do.

Please look at the video again, and the only thing you will need to do
It exchange the table that I placed in, with the one that you need.

Also, not unless you forgot to add in the columns, the image1 does not exist in the database that you provided.

Here is the code, but please view the video that I created for you.

sqlContent.commandtext = "SELECT products.ID, products.seriesid, products.product_name, products.image1, products.image2, products.image3, products.image4, products.image5, products.download1, products.download2, products.download3, products.download4, products.download5, products.product_file, products.createby, products.createdate, series.typeid, series.categoryid, series.brandid, series.series_name FROM products INNER JOIN series ON products.seriesid = series.seriesid WHERE products.seriesid=?"

Open in new window

0
 
EyalCommented:
image1 and  product_name are missing in the database query
0
 
Wayne BarronAuthor, Web DeveloperCommented:
@ Eyal

The Query that I just provided has them both in it.
The original one that I provided did not, as I made a slight mistake when I created that Query.

So, It is fixed now, waiting on stiebel now.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Stiebel EltronAuthor Commented:
@carrzkiss: Yes! It's working, but couldn't find the message that if the SeriesID isn't available, it will show a "Sorry....." message...

And one more thing, the Series name is gone, why it didn't show at all? lol :-)
Series-label.jpg
0
 
Gene-MathCommented:
I apologize, I was not able to test this myself.

Try changing CreateParameter to sqlContent.CreateParameter("@Series_ID", 3, 1, , adInteger).  I think the CreateParameter name should not match the field name.   The last item on the CreateParameter is the value of the data that is being passed.

In your SQL change "WHERE products.seriesid=?" to "WHERE products.seriesid=[Series_ID]".
0
 
Wayne BarronAuthor, Web DeveloperCommented:
Show me all of your code, and I will show you where it SHOULD show at
0
 
Stiebel EltronAuthor Commented:
Please see the attached code :-)
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Set connectString = Server.CreateObject("ADODB.Connection")
connectString.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("../photo/products.mdb") & ";"
connectString.Open

SeriesID = request.QueryString("SeriesID")

Set sqlContent = CreateObject("ADODB.Command")
sqlContent.ActiveConnection=connectString
sqlContent.Prepared = true

sqlContent.commandtext = "SELECT products.ID, products.seriesid, products.product_name, products.image1, products.image2, products.image3, products.image4, products.image5, products.download1, products.download2, products.download3, products.download4, products.download5, products.product_file, products.createby, products.createdate, series.typeid, series.categoryid, series.brandid, series.series_name FROM products INNER JOIN series ON products.seriesid = series.seriesid WHERE products.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>

<!--Lytebox-->
<script type="text/javascript" language="javascript" src="lytebox/lytebox.js"></script>
<link rel="stylesheet" href="lytebox/lytebox.css" type="text/css" media="screen" />
<!--END-->

</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 
				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="<%=rsProducts("image1")%>" alt="<%=rsProducts("product_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=""" & 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

0
 
Wayne BarronAuthor, Web DeveloperCommented:
Look at lines 129 - 133 (This is the Show Message is record does not exist)
147-151 - Creates the Variables if the record exist.
This helps to stop any additional errors from forming after the first test on line 129.

Good Luck
Carrzkiss

<%@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 products.ID, products.seriesid, products.product_name, products.image1, products.image2, products.image3, products.image4, products.image5, products.download1, products.download2, products.download3, products.download4, products.download5, products.product_file, products.createby, products.createdate, series.typeid, series.categoryid, series.brandid, series.series_name FROM products INNER JOIN series ON products.seriesid = series.seriesid WHERE products.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 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.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 type="text/javascript">

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

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

<!--Lytebox-->
<script type="text/javascript" src="lytebox/lytebox.js"></script>
<link rel="stylesheet" href="lytebox/lytebox.css" type="text/css" media="screen" />
<!--END-->

</head>

<body bgproperties="fixed" bgcolor="#000000" onload="javascript:startmenu()">
<table width="760" border="0" cellspacing="3" cellpadding="1">
  <tr>
    <td><img src="IMG/logo_res.jpg" alt="STIEBEL ELTRON" /></td>
    <td 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><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" alt="" />&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 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
				image1 = rsProducts("image1")
				 product_name = rsProducts("product_name")
				 download1 = rsProducts("download1")
				end if
			   	%>
		    	<td align="center" bordercolor="#999999" bgcolor="#FFFFFF">
					<img src="<%=image1%>" alt="<%=product_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

0
 
Stiebel EltronAuthor Commented:
Great! Thanks for the video as well!
0
 
Wayne BarronAuthor, Web DeveloperCommented:
Glad it worked for you.
Good Luck!
Carrzkiss
0
 
Stiebel EltronAuthor Commented:
Yes! I got your point, specially from your video... please don't delete it... :-)
But the Series name is still not working, anyway, I'll figure it out why...

So if the user uploads more than 1 photo with the same SeriesID, it will show all right?

Thanks again!
0
 
Stiebel EltronAuthor Commented:
BTW, I got it! I found the problem, from the db itself, it has no series name! Super LOL!
0
 
Wayne BarronAuthor, Web DeveloperCommented:
No worries, the Video was for you and for others to learn from as well.

Series Name is not working, because the Products and Series connection only displays 4 records, and them 4 records have nothing in them for the series_name
So.

Open your Series Table, and look at the series_name column
Scroll down to the bottom, and you will see that they are empty.
That is your answer on that one, you need a record in order to show something.

---
If you have the code designed to do so, then the uploaded images up to 5 will show like it is suppose too.
Other than that, I am unable to say, because I do not see the code, and do not really want to dive into something like that on EE.
0
 
Stiebel EltronAuthor Commented:
Thanks again! Now, that this program is running, I need to re-arrange everything, for each country...
And after that would be my upcoming thread in regards to Users sectioning... Wait for it... :-)
Hope you'll still be there to support for my upcoming thread...

:-)
0
 
Wayne BarronAuthor, Web DeveloperCommented:
Actually, the series_name that is in the Series Table, is the Column that you want and need. You add in information to them records, and you will have what you need.
For what I have seen, the database seems to have everything that it needs to do the job. You just need to add in the records to display them correctly.

Carrzkiss
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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now