asked on

SQL Code problem with advanced search

I am a beginning web developer working on a site using Dreamweaver and working with MS SQL and ASP Classic. I originally posted this question to web development and asp forums when I couldn't sort the results of an advanced search.

After I posted it, I was told to add the SQL order command "ORDER BY Pub_Year DESC, Pub_Publication ASC" to the end of theT statement, but that results in a 500 error.

When I do not add the ordering clause, the results appear but not in order. When I add it, I receive an error. The consensus from the previous post was that it was a problem with the SQL code. The code was originally created by modifying a helpful tutorial on creating advanced search and results pages I found onliny.

I am including the code for each page. I'd appreciate any help.

Shari

SEARCH PAGE:

<!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"><!-- InstanceBegin template="/Templates/subpage.dwt.asp" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<!-- InstanceBeginEditable name="doctitle" -->
<title>Publication Search | Learning Research and Development Center | University of Pittsburgh</title>
<!-- InstanceEndEditable -->
<style type="text/css">
<!--
body {
	font: 100% Verdana, Arial, Helvetica, sans-serif;
	background: #666666;
	margin: 0; /* it's good practice to zero the margin and padding of the body element to account for differing browser defaults */
	padding: 0; /* this centers the container in IE 5* browsers. The text is then set to the left aligned default in the #container selector */
	color: #000000;
	background-color: #8D8D8D;
}

.container {
	width: 980px; /* the auto margins (in conjunction with a width) center the page */
	border: 1px solid #000000;
	text-align: left; /* this overrides the text-align: center on the body element. */
	margin-top: 0;
	margin-right: auto;
	margin-bottom: 0;
	margin-left: auto;
	background-color: #F7f5e9;
}
.sidebar_text {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 11px;
	color: #6c6c6c;
}
.clearfloat {
	clear: both;
}
.sidebar_headings {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 11px;
	font-weight: bold;
	color: #2c61a9;
}
-->
</style>
<link href="../css/header.css" rel="stylesheet" type="text/css" />

<link href="../css/subpage.css" rel="stylesheet" type="text/css" />
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->


<script type="text/javascript" src="../Templates/stmenu.js"></script>

<link href="../css/navbar_sothink_980.css" rel="stylesheet" type="text/css" />
<link href="../css/small_header.css" rel="stylesheet" type="text/css" />
<link href="../css/lrdc_footer980.css" rel="stylesheet" type="text/css" />

<style type="text/css">.highlight { background: #FFFF40; }</style>
<script type="text/javascript" src="../javascript/highlight.js"></script>
</head>

<body onload="highlight();">

<div class="container" id="container">

 <div id="small_header"></div>
 
 <!--#include file="../include/navbar.asp"-->
 


<div id="subpage_heading">
  <h3><!-- InstanceBeginEditable name="heading_title" --><img src="../images/970162_70X70.png" width="70" height="70" align="middle" /> Publication Search<!-- InstanceEndEditable --></h3>
</div>

 <!-- InstanceBeginEditable name="breadcrumbs" -->
<div id="breadcrumbs"><a href="../index.asp">&gt; Home</a> : <a href="dbpubsearch2.asp">Publication Search</a></div>
<!-- InstanceEndEditable -->

 <div id="subpage_content">
   
  <!-- InstanceBeginEditable name="content" --><form name="form1" method="post" action="pubsearchresults-sk.asp">
  <p> Search for: 
    <input type="text" name="SearchParam" size="50">
    <br><br />
    Using: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  
    <select name="CompType">
      <option value="AND">All Words</option>
      <option value="OR">Any Word</option>
      <option value="EXACT">Exact Phrase</option>
    </select>
    </p>
    <br />
  <p> 
    <input type="submit" name="Submit" value="Submit">
  </p>
  
  </form>
<p><b>Notes:</b></p>
<p>Search criteria can be any number of words, just separate with spaces.</p>
<p>As the results are citations, if searching for the works of a particular author, please avoid searching for an author's full name; we suggest using only the author's last name.</p>
<p>Use the drop-down menu to specify All Words (AND), Any Word 
  (OR), Exact Phrase (EXACT)</p>
<p>&nbsp;</p><!-- InstanceEndEditable --></div>

  <!-- This clearing element should immediately follow the #mainContent div in order to force the #container div to contain all child floats -->
<br class="clearfloat" />

<!--#include file="../include/footer.asp"-->


<!--end container div--></div>

<!--#include file="../include/copyright.asp"-->

<script type="text/javascript">
tabberAutomatic(tabberOptions);
</script>

</body><!-- InstanceEnd --></html>

RESULTS PAGE:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!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">
<!--#include file="../Connections/EWI.asp" -->
<%
Dim CompType, adv_advSearch_String
'Store type of query in CompType ie All Words/OR, Any Word/AND or Exact Phrase/EXACT
CompType = Request("CompType")
SearchColumn = "pub_publication"

SearchField = "zzz"
if(Request("SearchParam") <> "") then SearchField = Request("SearchParam")
'Remove any single quotes from search field to eliminate potential errors.
SearchField = Replace(SearchField, "'", "")

'Checks the CompType, Executes this code if the option All words or Any Word is chosen
if(CompType <> "EXACT") then
	adv_advSearch_String = "WHERE " & SearchColumn & " LIKE '%"
	'Splits the search criteria into seperate words and stores them in an Array
	SearchArray=Split(SearchField," ")
	for i = 0 to Ubound(SearchArray)
		if i > 0 then
			'Builds the sql statement using the CompType to substitute AND/OR
			adv_advSearch_String = adv_advSearch_String & " " & CompType & " " & SearchColumn & " LIKE '%"& SearchArray(i) & "%'"
		else
			'Ends the sql statement if there is only one word
			adv_advSearch_String = adv_advSearch_String & SearchArray(i) & "%'"
		end if
	next

else
	adv_advSearch_String = "WHERE " & SearchColumn & " LIKE '%" & SearchField & "%'"
end if
%>
<%
Dim rsSearch__adv_String
'rsSearch__adv_String = "WHERE ID=1"
if (adv_advSearch_String  <> "") then rsSearch__adv_String = adv_advSearch_String 
%>
<%
set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.ActiveConnection = MM_EWI_STRING
rsSearch.Source = "SELECT *  FROM dbo.PUBLICATIONS " + Replace(rsSearch__adv_String, "'", "'") + ""
rsSearch.CursorType = 0
rsSearch.CursorLocation = 2
rsSearch.LockType = 3
rsSearch.Open()
rsSearch_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsSearch_numRows = rsSearch_numRows + Repeat1__numRows
%>

<!-- InstanceBegin template="/Templates/subpage_vbs.dwt.asp" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<!-- InstanceBeginEditable name="doctitle" -->
<title>Learning Research and Development Center | University of Pittsburgh</title>
<!-- InstanceEndEditable -->
<style type="text/css">
<!--
body {
	font: 100% Verdana, Arial, Helvetica, sans-serif;
	background: #666666;
	margin: 0; /* it's good practice to zero the margin and padding of the body element to account for differing browser defaults */
	padding: 0; /* this centers the container in IE 5* browsers. The text is then set to the left aligned default in the #container selector */
	color: #000000;
	background-color: #8D8D8D;
}

.container {
	width: 980px; /* the auto margins (in conjunction with a width) center the page */
	border: 1px solid #000000;
	text-align: left; /* this overrides the text-align: center on the body element. */
	margin-top: 0;
	margin-right: auto;
	margin-bottom: 0;
	margin-left: auto;
	background-color: #F7f5e9;
}
.sidebar_text {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 11px;
	color: #6c6c6c;
}
.clearfloat {
	clear: both;
}
.sidebar_headings {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 11px;
	font-weight: bold;
	color: #2c61a9;
}
-->
</style>
<link href="../css/header.css" rel="stylesheet" type="text/css" />

<link href="../css/subpage.css" rel="stylesheet" type="text/css" />
<!-- InstanceBeginEditable name="head" -->
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">


<!-- InstanceEndEditable -->


<script type="text/javascript" src="../Templates/stmenu.js"></script>

<link href="../css/navbar_sothink_980.css" rel="stylesheet" type="text/css" />
<link href="../css/small_header.css" rel="stylesheet" type="text/css" />
<link href="../css/lrdc_footer980.css" rel="stylesheet" type="text/css" />

<style type="text/css">.highlight { background: #FFFF40; }</style>
<script type="text/javascript" src="../javascript/highlight.js"></script>
</head>

<body onload="highlight()">

<div class="container" id="container">

 <div id="small_header"></div>
 
 <!--#include file="../include/navbar.asp"-->
 


<div id="subpage_heading">
  <h3><!-- InstanceBeginEditable name="heading_title" -->Publication Search Results<!-- InstanceEndEditable --></h3>
</div>

 <!-- InstanceBeginEditable name="breadcrumbs" -->
<div id="breadcrumbs"><a href="http://www.lrdc.pitt.edu/index.asp">&gt; Home</a> : <a href="dbpubsearch.asp">Publication Search</a> : Publication Search Results</div>
<!-- InstanceEndEditable -->

 <div id="subpage_content">
   
  <!-- InstanceBeginEditable name="content" -->
  
<table width="800" border="0" cellspacing="8" cellpadding="8">
  <tr>
    <td align="right"><a href="dbpubsearch.asp">Back to Pubs Search</a></td></tr>
  <tr> 
    <td width="780"><b>Citation(s)</b></td>
   </tr>
  <% 
While ((Repeat1__numRows <> 0) AND (NOT rsSearch.EOF)) 
%>
  <tr> 
    <td><%=(rsSearch.Fields.Item("pub_publication").Value)%></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsSearch.MoveNext()
Wend
%>
</table>

<!-- InstanceEndEditable --></div>

  <!-- This clearing element should immediately follow the #mainContent div in order to force the #container div to contain all child floats -->
<br class="clearfloat" />

<!--#include file="../include/footer.asp"-->

<!--end container div--></div>
<!--#include file="../include/copyright.asp"-->

<script type="text/javascript">
tabberAutomatic(tabberOptions);
</script>

</body><!-- InstanceEnd -->
</html>
<%
rsSearch.Close()
%>

Open in new window

rajvja

Hi,

try to display the final query(adv_advSearch_String) and check any errors in the script.

Carl Tawn

It looks like you may simply be neglecting to put a space between the end of your query and the ORDER BY clause. Try tagging the following onto your query:

" ORDER BY Pub_Year DESC, Pub_Publication ASC"    '// Note the space before the O

Open in new window

lrdchelp

ASKER

Thanks for letting me kn0ow about the space, but unfortunately it did not solve the problem. My query is as follows. Do you see anything else?

Is there any way to put the order command at another point in the code?

<%
set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.ActiveConnection = MM_EWI_STRING
rsSearch.Source = "SELECT * FROM dbo.PUBLICATIONS" + Replace(rsSearch__t3_String, "'", "'") + " ORDER BY Pub_Year DESC, Pub_Publication ASC"
rsSearch.CursorType = 0
rsSearch.CursorLocation = 2
rsSearch.LockType = 3
rsSearch.Open()
rsSearch_numRows = 0
%>

Carl Tawn

Dump the WHERE clause to the browser using:

Response.Write rsSearch__t3_String : Response.End

Open in new window

And post the results here.

lrdchelp

ASKER

Rajva,

Are you talking about checking it in the bindings. If so, I receive the following error:

Parameter grid row:1
t3_String is an invalid variable name; it does n9ot appear in the SQL

The odd thing is that I get the same error with or without the ordering and the instructions for the code warned that you may get a recordset error but that it wouldn't affect the results.

Shari

lrdchelp

ASKER

Carl,

Can you be more specific. I apologize. I'm used to simple queries at this point.

Shari

Carl Tawn

Before you try to run the query can you output the value of:

"SELECT * FROM dbo.PUBLICATIONS" + Replace(rsSearch__t3_String, "'", "'") + " ORDER BY Pub_Year DESC, Pub_Publication ASC"

Open in new window

To the browser and post the result? Your problem is likely to be a simply syntax error, but it is difficult to tell what from fragments. If you output the value above it will give us the entire query.

lrdchelp

ASKER

When I output the value without a parameter, I revceive the following error:

Carl Tawn

Before you try and execute the query add the following:

Response.Write "SELECT * FROM dbo.PUBLICATIONS" + Replace(rsSearch__t3_String, "'", "'") + " ORDER BY Pub_Year DESC, Pub_Publication ASC" : Response.End

Open in new window

We know there is a syntax error, but we need to see the whole SQL statement to be able to determine what it is.

lrdchelp

ASKER

I appreciate your help.
I am including my code again as a reference. I placed the code before the previous select statement.

The following displayed on my browser (perfetti and erp were the search terms I entered)
:
SELECT * FROM dbo.PUBLICATIONSWHERE pub_publication LIKE '%perfetti%' AND pub_publication LIKE '%erp%' ORDER BY Pub_Year DESC, Pub_Publication ASC

Does that help or did I put it in the wrong place?

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!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">
<!--#include file="../Connections/EWI.asp" -->
<%
Dim CompType, t3_advSearch_String
'Store type of query in CompType ie All Words/OR, Any Word/AND or Exact Phrase/EXACT
CompType = Request("CompType")
SearchColumn = "pub_publication"

SearchField = "zzz"
if(Request("SearchParam") <> "") then SearchField = Request("SearchParam")
'Remove any single quotes from search field to eliminate potential errors.
SearchField = Replace(SearchField, "'", "")

'Checks the CompType, Executes this code if the option All words or Any Word is chosen
if(CompType <> "EXACT") then
	t3_advSearch_String = "WHERE " & SearchColumn & " LIKE '%"
	'Splits the search criteria into seperate words and stores them in an Array
	SearchArray=Split(SearchField," ")
	for i = 0 to Ubound(SearchArray)
		if i > 0 then
			'Builds the sql statement using the CompType to substitute AND/OR
			t3_advSearch_String = t3_advSearch_String & " " & CompType & " " & SearchColumn & " LIKE '%"& SearchArray(i) & "%'"
		else
			'Ends the sql statement if there is only one word
			t3_advSearch_String = t3_advSearch_String & SearchArray(i) & "%'"
		end if
	next

else
	t3_advSearch_String = "WHERE " & SearchColumn & " LIKE '%" & SearchField & "%'"
end if
%>
<%
Dim rsSearch__t3_String
rsSearch__t3_String = "WHERE ID=1"
if (t3_advSearch_String  <> "") then rsSearch__t3_String = t3_advSearch_String 
%>
<%
set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.ActiveConnection = MM_EWI_STRING

	

Response.Write "SELECT * FROM dbo.PUBLICATIONS" + Replace(rsSearch__t3_String, "'", "'") + " ORDER BY Pub_Year DESC, Pub_Publication ASC" : Response.End

rsSearch.Source = "SELECT * FROM dbo.PUBLICATIONS" + Replace(rsSearch__t3_String, "'", "'") + " ORDER BY Pub_Year DESC, Pub_Publication ASC" 
rsSearch.CursorType = 0
rsSearch.CursorLocation = 2
rsSearch.LockType = 3
rsSearch.Open()
rsSearch_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsSearch_numRows = rsSearch_numRows + Repeat1__numRows
%>

<!-- InstanceBegin template="/Templates/subpage_vbs.dwt.asp" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<!-- InstanceBeginEditable name="doctitle" -->
<title>Learning Research and Development Center | University of Pittsburgh</title>
<!-- InstanceEndEditable -->
<style type="text/css">
<!--
body {
	font: 100% Verdana, Arial, Helvetica, sans-serif;
	background: #666666;
	margin: 0; /* it's good practice to zero the margin and padding of the body element to account for differing browser defaults */
	padding: 0; /* this centers the container in IE 5* browsers. The text is then set to the left aligned default in the #container selector */
	color: #000000;
	background-color: #8D8D8D;
}

.container {
	width: 980px; /* the auto margins (in conjunction with a width) center the page */
	border: 1px solid #000000;
	text-align: left; /* this overrides the text-align: center on the body element. */
	margin-top: 0;
	margin-right: auto;
	margin-bottom: 0;
	margin-left: auto;
	background-color: #F7f5e9;
}
.sidebar_text {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 11px;
	color: #6c6c6c;
}
.clearfloat {
	clear: both;
}
.sidebar_headings {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 11px;
	font-weight: bold;
	color: #2c61a9;
}
-->
</style>
<link href="../css/header.css" rel="stylesheet" type="text/css" />

<link href="../css/subpage.css" rel="stylesheet" type="text/css" />
<!-- InstanceBeginEditable name="head" -->
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">


<!-- InstanceEndEditable -->


<script type="text/javascript" src="../Templates/stmenu.js"></script>

<link href="../css/navbar_sothink_980.css" rel="stylesheet" type="text/css" />
<link href="../css/small_header.css" rel="stylesheet" type="text/css" />
<link href="../css/lrdc_footer980.css" rel="stylesheet" type="text/css" />

<style type="text/css">.highlight { background: #FFFF40; }</style>
<script type="text/javascript" src="../javascript/highlight.js"></script>
</head>

<body onload="highlight()">

<div class="container" id="container">

 <div id="small_header"></div>
 
 <!--#include file="../include/navbar.asp"-->
 


<div id="subpage_heading">
  <h3><!-- InstanceBeginEditable name="heading_title" -->Publication Search Results<!-- InstanceEndEditable --></h3>
</div>

 <!-- InstanceBeginEditable name="breadcrumbs" -->
<div id="breadcrumbs"><a href="http://www.lrdc.pitt.edu/index.asp">&gt; Home</a> : <a href="dbpubsearch.asp">Publication Search</a> : Publication Search Results</div>
<!-- InstanceEndEditable -->

 <div id="subpage_content">
   
  <!-- InstanceBeginEditable name="content" -->
  
<table width="800" border="0" cellspacing="8" cellpadding="8">
  <tr>
    <td align="right"><a href="dbpubsearch.asp">Back to Pubs Search</a></td></tr>
  <tr> 
    <td width="780"><b>Citation(s)</b></td>
   </tr>
  <% 
While ((Repeat1__numRows <> 0) AND (NOT rsSearch.EOF)) 
%>
  <tr> 
    <td><%=(rsSearch.Fields.Item("pub_publication").Value)%></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsSearch.MoveNext()
Wend
%>
</table>

<!-- InstanceEndEditable --></div>

  <!-- This clearing element should immediately follow the #mainContent div in order to force the #container div to contain all child floats -->
<br class="clearfloat" />

<!--#include file="../include/footer.asp"-->

<!--end container div--></div>
<!--#include file="../include/copyright.asp"-->

<script type="text/javascript">
tabberAutomatic(tabberOptions);
</script>

</body><!-- InstanceEnd -->
</html>
<%
rsSearch.Close()
%>

Open in new window

ASKER CERTIFIED SOLUTION

Carl Tawn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lrdchelp

ASKER

Fantastic, but now how do I see where the space goes since it does not show up like that in the code.

I really do appreciate your help.

Shari

Carl Tawn

You seem to have about three places that start the "WHERE" section, so it would be easier to change this line:

rsSearch.Source = "SELECT * FROM dbo.PUBLICATIONS" + Replace(rsSearch__t3_String, "'", "'") + " ORDER BY Pub_Year DESC, Pub_Publication ASC

Open in new window

To:

rsSearch.Source = "SELECT * FROM dbo.PUBLICATIONS " + Replace(rsSearch__t3_String, "'", "'") + " ORDER BY Pub_Year DESC, Pub_Publication ASC

Open in new window

lrdchelp

ASKER

THANK YOU SO MUCH!!! It worked and I'm slowly learning how to troubleshoot with your help and the help of others on these sites.

Just an FYI if anyone uses this for a reference, I think you missed an end " at the very end of your statement.

Shari