?
Solved

Linking pages problem EASY 500 point

Posted on 2005-03-20
18
Medium Priority
?
353 Views
Last Modified: 2009-07-29
hi all , ive got a link on textsearch.asp which sends specific search , that link goes on to a detail1.asp which has more information, the page worked before  i put a img tag, the SQL string had to change and hence the problem, i get the following error msg:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Oracle][ODBC][Ora]ORA-00920: invalid relational operator

/student/00411343/detail1.asp, line 13


the LINK FROM TEXTSEARCH.ASP

<a href="detail1.asp?artist_name=<%=request.form("searchFor")%>" class="style24"><strong>Click this link to see more information on this artist...</strong></a>

  <p>&nbsp;</p>

THE DETAIL1.ASP PAGE

<%@LANGUAGE="VBSCRIPT"%><%

Dim strartist_name, objConn, objRs



strSQL = "SELECT ec_artists.*, ec_artists.artist_pic, ec_tunes.*, ec_tunes.style_id, ec_tunes.format_name " &_
              "FROM ec_artists INNER JOIN ec_tunes ON ec_artists.artist_name = ec_tunes.artist_name " &_
              "WHERE (ec_tunes.style_id) = " & strgenre & " AND (ec_tunes.format_name)='" & strformat & "';"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "dsn=Oracle;uid=00411343;pwd=090180"
Set objRs = objConn.Execute(strSQL)



%>
<title>details</title>
<link rel="stylesheet" href="3col_leftNav.css" type="text/css">
<style type="text/css">
<!--
.style2 {
      font-size: 60px;
      font-weight: bold;
      color: #000000;
      font-family: Geneva, Arial, "Arial Black";
}
.style4 {
      font-weight: bold;
      background-color: #CCCCCC;
}
.style6 {color: #FF0000}
.style8 {
      font-size: small;
      background-color: #CCCCCC;
}
.style12 {font-size: 60px}
.style13 {
      font-size: small;
      color: #FF0000;
      font-weight: bold;
      font-style: italic;
}
.style19 {color: #0000FF; font-weight: bold; }
.style23 {font-weight: bold; background-color: #CCCCCC; font-size: 120%; }
.style24 {font-size: small}
.style25 {font-weight: bold}
.style26 {font-size: medium}
-->
</style>
</head>
<body>
<div id="masthead">
  <h1 align="justify" class="style2 style12" id="siteName">AllWeNeedIsMusic.com</h1>
 <div id="globalNav">
 
<p class="style23">  <a href="index.asp">Home</a> <a href="PageCon.htm"> Singles</a> <a href="Albums.htm"> Album</a> <a href="textsearch.asp"> Search </a> <a href="Artist.htm"> Artist</a>  <a href="PageCon.htm"> Coming Soon</a>  <a href="PageCon.htm"> Chect Out Cart</a></p>
  </div>
   <p align="centre"><span class="style13"> Welcome to AllWeNeedIsMusic.com: Get The Best Music On The Web. We're offering 4 CDs For Only &pound;20</span><span class="style8"><br class="style13">
  </span></p>
</div>
<div id="navBar">
  <div id="search">
    <form action="#">
      <p>
        <label><p>
 <form>
Username:<input type="text" name="user">
<br>
Password :<input type="password" name="password">
<br>
<input type="submit" value="Submit">
<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=5,0,0,0" width="129" height="22">
  <param name="BGCOLOR" value="#EEEEEE">
  <param name="movie" value="button22.swf">
  <param name="quality" value="high">
  <embed src="button22.swf" width="129" height="22" quality="high" pluginspage="http://www.macromedia.com/shockwave/download/index.cgi?P1_Prod_Version=ShockwaveFlash" type="application/x-shockwave-flash" bgcolor="#EEEEEE" ></embed>
</object>
  </form>
 
<p>

     
  </div>
  <div id="sectionLinks">
    <h3 class="style23">Charts</h3>
    <ul>
      <a href="Albums.htm">Artist Albums</a>      <li>        <a href="PageCon.htm">Singles</a>      </li>
      <li>      <a href="PageCon.htm">Special Offers</a>      </li>
    </ul>
  </div>
  <div id="sectionLinks">
    <h3 class="style23">Reviews</h3>
    <ul>
      <li>      <a href="Albums.htm">Artist</a>      </li>
      <li>      <a href="Albums.htm">Albums</a>      </li>
      <li>      <a href="PageCon.htm">Singles</a>      </li>
    </ul>
  </div>
  <div id="sectionLinks">
    <h3 class="style23">New Relases </h3>
    <ul>
      <li>      <a href="PageCon.htm">Artist</a>      </li>
      <li>      <a href="Albums.htm">Album</a>      </li>
      <li>        <a href="PageCon.htm">Singles</a>            
        <h3 class="style23">News </h3>
      </li>
      <li>      <a href="PageCon.htm">Music News</a></li>
      <li></li>
    </ul>
  </div>
</div>
<div id="content">
  <div class="feature">

<BODY>
<p><style type="text/css">
<!--
.style1 {font-weight: bold}
-->
</style>
<%
if not (objRs.EOF and objRs.BOF) then

Do While Not (objRs.EOF)
%>
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
//-->
</script>
<table width="546" height="172"  border="2" cellpadding=6 cellspacing=2" bgcolor="#F8F8F6" frame="border">
  <!--DWLayoutTable-->
  <tr>
    <td width="78" height="33"><div align="right" class="style24"></div>
        <strong>Tune ID:</strong></td>
    <td width="60"><div align="center" class="style24">
        <div align="left"><font font><%=objRs("tune_id")%></font></div>
    </div></td>
    <td width="64" colspan="1"><div align="center" class="style24">
        <div align="left"><strong>Label:</strong></div>
    </div></td>
    <td width="41"><div align="center" class="style24">
        <div align="left"><%=objRs("label_name")%></div>
    </div></td>
    <td colspan="2"><span class="style24"><strong>Description:</strong></span></td>
    <td width="71" rowspan="2" valign="middle"><div align="center">
        <div align="center"></div>
        <%=objRs("tune_description")%></div>
        <div align="center">
          <div align="center"></div>
      </div></td>
    </tr>
  <tr>
    <td height="50"><div align="left" class="style24">
        <div align="left">
          <div align="left"><strong>Artist:</strong></div>
        </div>
    </div></td>
    <td><div align="center" class="style24">
        <div align="left"><font font><%=objRs("artist_name")%></font></div>
    </div></td>
    <td><div align="center" class="style24">
        <div align="right">
          <div align="left"><strong>Price: (&pound;) </strong></div>
        </div>
    </div></td>
    <td><span class="style24"><font font><%=objRs("tune_price")%></font></span></td>
    <td colspan="2" rowspan="2" valign="top"><div align="center">
            <div align="left" class="style24 style1"><img src="allbumpics/<%=objRs("artist_pic")%>" alt="<%=objRs("artist_name")%>" width="120" height="97" border="0" align="middle" /></div>
    </div></td>
    <div align="center"></div>
 
  <tr>
    <td colspan="1" rowspan="3"><div align="center" class="style24">
        <div align="left"><strong>Tune Name:</strong></div>
    </div></td>
    <td rowspan="3"><div align="center" class="style24">
        <div align="left"><%=objRs("tune_name")%></div>
    </div></td>
    <td rowspan="2" valign="top"><div align="center">
        <div align="center">
          <div align="left" class="style1 style24"><strong>Format: </strong></div>
        </div>
    </div></td>
    <td rowspan="2"><div align="center" class="style24">
        <div align="left"><span class="style24 style1"><%=objRs("format_name")%></span></div>
    </div></td>
    <td height="15" rowspan="2" class="relatedLinks"><a href="customer.htm"><strong>Add to Cart</strong></a>
    <tr>
    <td width="87" rowspan="2" valign="top"><p><strong>Quantity </strong></p></td>
    <td width="27" rowspan="2" valign="top"><div align="center">
            <div align="left" class="style24 style1"><%=objRs("tune_copies")%></div>
    </div></td>
<tr>
    <td height="33" valign="top"><span class="style25"><span class="style24"><strong>Year:</strong></span>
          <div align="center" class="style1 style25  style24">
            <div align="left"></div>
          </div>
    </span></td>
    <td valign="top"><div align="center">
        <div align="left" class="style24 style1"><%=objRs("tune_year")%></div>
    </div></td>
    <td>&nbsp;</td>
    </table>
<p><%
objRs.MoveNext
Loop
else
response.write "No data found"


end if
%>
  <P>

<%

objRs.Close
Set objRs=Nothing
objConn.Close
Set objConn=Nothing
%>
<a href="textsearch.asp" class="style24"><strong>Back to Search</strong></a>
<p>&nbsp;</p>

<p>&nbsp;</p>

<p align="justify" class="style4">
 
  <div class="feature">
    <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
  <p><br>
</div>
  </div>
</div>

<div id="siteInfo">
  <img src="nfym_sq.gif" width="65" height="46" class="style6"> <a href="#">About Us</a> | <a href="login.htm" target="_blank">Administration</a> | <a href="#">Privacy Policy</a> | <a href="#">Contact Us</a> |<span class="style19"> &copy;2005 AllWeNeedIsMusic.com</span></div>
<br>
</body>
</html>
0
Comment
Question by:sid1980
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 2

Expert Comment

by:pheine
ID: 13587218
dont say simple question if you plan to submit such a code ;)

ok, i might guess wrong, but in my opinion the error msg appeared during execution of the sql command:
strSQL = "SELECT ec_artists.*, ec_artists.artist_pic, ec_tunes.*, ec_tunes.style_id, ec_tunes.format_name " &_
              "FROM ec_artists INNER JOIN ec_tunes ON ec_artists.artist_name = ec_tunes.artist_name " &_
              "WHERE (ec_tunes.style_id) = " & strgenre & " AND (ec_tunes.format_name)='" & strformat & "';"

so there are 2 errors i can see:
1. strgenre and strformat are variables i dont see initialized on the page - in that case they will be null or empty strings which are never good in sql WHEREs.
2. the WHERE clauses are syntactically wrong. The last line should be something like this:

"WHERE (ec_tunes.style_id = " & strgenre & ") AND (ec_tunes.format_name = '" & strformat & "')"

im not quite in oracle so i dont know if you need the ; at the end of the sql statement - for all i know is that you dont need them in mysql, mssql, etc. so i guess its not important.
0
 

Author Comment

by:sid1980
ID: 13587289
that did not work when the page worked i had this SQL statement:

strSQL = "select * from ec_tunes where artist_name= '" & request.querystring("artist_name") & "'"

now i have addedd pictures i have the SQL statement:
strSQL = "SELECT ec_artists.*, ec_artists.artist_pic, ec_tunes.*, ec_tunes.style_id, ec_tunes.format_name " &_
              "FROM ec_artists INNER JOIN ec_tunes ON ec_artists.artist_name = ec_tunes.artist_name " &_
              "WHERE (ec_tunes.style_id) = " & strgenre & " AND (ec_tunes.format_name)='" & strformat & "';"


i need to incorparate the working SQL statement into the above statement
0
 
LVL 2

Expert Comment

by:pheine
ID: 13587404
hope i didnt miss your point but if you want to search for an artist, then the artist needs to be in the WHERE clause simply by:
AND ec_tunes.artist_name = '"& request.querystring("artist_name") &"'

or the whole WHERE clause:
'WHERE (ec_tunes.artist_name = '"& request.querystring("artist_name") &"' AND ec_tunes.style_id = "& strgenre &" AND ec_tunes.format_name = '"& strformat &"'"
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:pheine
ID: 13587408
god, i should not write here and elsewhere at the same time... the statement should be:

"WHERE (ec_tunes.artist_name = '"& request.querystring("artist_name") &"') AND (ec_tunes.style_id = "& strgenre &") AND (ec_tunes.format_name = '"& strformat &"')"
0
 
LVL 7

Expert Comment

by:Hecatonchires
ID: 13587546
does your sql work in query analyser?
0
 

Author Comment

by:sid1980
ID: 13587576
yes the do work now im getting a error msg on line 14 the erorr msg is:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Oracle][ODBC][Ora]ORA-00936: missing expression

/student/00411343/detail1.asp, line 14


THE CODE

<%@LANGUAGE="VBSCRIPT"%><%

Dim strartist_name, objConn, objRs




strSQL = "SELECT ec_artists.*, ec_artists.artist_pic, ec_tunes.*, ec_tunes.style_id, ec_tunes.format_name " &_
              "FROM ec_artists INNER JOIN ec_tunes ON ec_artists.artist_name = ec_tunes.artist_name " &_
              "WHERE (ec_tunes.artist_name = '"& request.querystring("artist_name") &"') AND (ec_tunes.style_id = "& strgenre &") AND (ec_tunes.format_name = '"& strformat &"')"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "dsn=Oracle;uid=00411343;pwd=090180"
Set objRs = objConn.Execute(strSQL)

0
 
LVL 7

Expert Comment

by:Hecatonchires
ID: 13587625
its hard to tell, is line 14 objConn.open or set objRS = ???

write out the values of:
- request.querystring("artist_name")
- strgenre
- strformat
and confirm they are not null before executing the sql.  also, bad style to put request.etc() straight into the sql, but that can be tidied up later

i know you can return a recordset, but humour me

set rs = server.createobject("ADODB.recordset")
rs.open strSQL, objConn, adopenforwardonly, adlockreadonly, adcmdtext
0
 

Author Comment

by:sid1980
ID: 13587634
line 14 is Set objRs = objConn.Execute(strSQL)
0
 
LVL 15

Expert Comment

by:gladxml
ID: 13588710
sid1980,

originnaly you have two and now you are introducing a new criteria which is the artistname right so in the sql statement where clause you will have three condition that need to be filled up...

strSQL = "SELECT ec_artists.*, ec_artists.artist_pic, ec_tunes.*, ec_tunes.style_id, ec_tunes.format_name " &_
              "FROM ec_artists INNER JOIN ec_tunes ON ec_artists.artist_name = ec_tunes.artist_name " &_
              "WHERE (ec_tunes.style_id) = " & strgenre & " AND (ec_tunes.format_name)='" & strformat & "' AND ec_tunes.artist_name = '" & strname &"';"

this are

strgenre = Request.QueryString ("Genre")  
strformat = Request.QueryString ("format")
strname = Request.QueryString ("name")

Now to check if the query works.... just hard code the values of the variable like this

strgenre = genrehardcode value  
strformat = fromat hardcode value
strname = name hardcode vale

your sql statement... and run the code using the hardcoded values

Now if you only want to get the regardless of the genre and format the delete the other condition then sql statement will be

strSQL = "SELECT ec_artists.*, ec_artists.artist_pic, ec_tunes.*, ec_tunes.style_id, ec_tunes.format_name " &_
              "FROM ec_artists INNER JOIN ec_tunes ON ec_artists.artist_name = ec_tunes.artist_name " &_
              "WHERE ec_tunes.artist_name = '" & strname &"';"

Tyr to check the above sql also and check the result...

HTH...
Happy programming...








0
 
LVL 15

Expert Comment

by:gladxml
ID: 13588718

OOPS ommitted some words in my previous comment...

Now if you only want to get the details of a specific artist name regardless of the genre and format then delete the other condition then sql statement will be

strSQL = "SELECT ec_artists.*, ec_artists.artist_pic, ec_tunes.*, ec_tunes.style_id, ec_tunes.format_name " &_
              "FROM ec_artists INNER JOIN ec_tunes ON ec_artists.artist_name = ec_tunes.artist_name " &_
              "WHERE ec_tunes.artist_name = '" & strname &"';"
0
 

Author Comment

by:sid1980
ID: 13589798
using the above statement it now just runs thru, to "no data found"
0
 
LVL 15

Assisted Solution

by:gladxml
gladxml earned 800 total points
ID: 13589878
have you add this line

strname = Request.QueryString ("name")

so that strname variable will have a value on the below sql statement...

strSQL = "SELECT ec_artists.*, ec_artists.artist_pic, ec_tunes.*, ec_tunes.style_id, ec_tunes.format_name " &_
              "FROM ec_artists INNER JOIN ec_tunes ON ec_artists.artist_name = ec_tunes.artist_name " &_
              "WHERE ec_tunes.artist_name = '" & strname &"';"

HTH...

Happy programming...
0
 

Author Comment

by:sid1980
ID: 13589892
i have got this but it still dont work


strname = Request.QueryString ("name")


strSQL = "SELECT ec_artists.*, ec_artists.artist_pic, ec_tunes.*, ec_tunes.style_id, ec_tunes.format_name " &_
              "FROM ec_artists INNER JOIN ec_tunes ON ec_artists.artist_name = ec_tunes.artist_name " &_
              "WHERE ec_tunes.artist_name = '" & strname &"';"
0
 
LVL 2

Accepted Solution

by:
pheine earned 1200 total points
ID: 13589906
it should be

strname = Request.QueryString("artist_name")

shouldnt it?
0
 

Author Comment

by:sid1980
ID: 13589952
@gladxml......you sorted out my detail.asp page in a question i asked before.....that page works fine, what i have done is added a detail1.asp where the user can have a more specific search for a artist, when they type there name in the search input box the will get a short summary of the artist....that all works fine , but to get more details on that artist the click a link at the bottom which is......

<a href="detail1.asp?artist_name=<%=request.form("searchFor")%>" class="style24"><strong>Click this link to see more information on this artist...</strong></a>

but this page does not show up
0
 

Author Comment

by:sid1980
ID: 13589993
thanks for the answers
0
 
LVL 15

Expert Comment

by:gladxml
ID: 13590072
I think to get more specific for that artist is you need to reference it to a unique key in your table

Now try to use this so that we can check if your query works... that is the details of artist is in both tables... try to change the hardcodedvalue with any artist name that is in your db... and check the result... if the statement returns a record or data

strname = "hardcodedvalue"
strSQL = "SELECT ec_artists.*, ec_artists.artist_pic, ec_tunes.*, ec_tunes.style_id, ec_tunes.format_name " &_
              "FROM ec_artists INNER JOIN ec_tunes ON ec_artists.artist_name = ec_tunes.artist_name " &_
              "WHERE ec_tunes.artist_name = '" & strname &"';"

0
 
LVL 15

Expert Comment

by:gladxml
ID: 13590104
@ pheine
yes your right I copy the wrong variable... a typo... I did not see it though...




0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

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