Solved

How to add table name in SQLContent in SQL Query?

Posted on 2011-09-20
15
262 Views
Last Modified: 2012-05-12
... 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
Comment
Question by:Stiebel Eltron
15 Comments
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 36571407
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
 
LVL 15

Expert Comment

by:Eyal
ID: 36571437
image1 and  product_name are missing in the database query
0
 
LVL 30

Expert Comment

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

Author Comment

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

Expert Comment

by:Gene-Math
ID: 36571552
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
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 36571564
Show me all of your code, and I will show you where it SHOULD show at
0
 

Author Comment

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

Accepted Solution

by:
Wayne Barron earned 500 total points
ID: 36571643
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
 

Author Closing Comment

by:Stiebel Eltron
ID: 36571893
Great! Thanks for the video as well!
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 36571917
Glad it worked for you.
Good Luck!
Carrzkiss
0
 

Author Comment

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

Author Comment

by:Stiebel Eltron
ID: 36571945
BTW, I got it! I found the problem, from the db itself, it has no series name! Super LOL!
0
 
LVL 30

Expert Comment

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

Author Comment

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

Expert Comment

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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

24 Experts available now in Live!

Get 1:1 Help Now