Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Linking pages problem EASY 500 point

Posted on 2005-03-20
18
Medium Priority
?
359 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2

572 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