Results page returns no records

App: Dreamweaver MX 2004 and ASP VBScript

I have a search page sending results via a GET form, but the results page returns no records. I am using a very simple page for debugging purposes, and I have posted the code below. Here is the SQL statement I am using:

SELECT *
FROM tasks
WHERE date_updated = 'VARdate_updated' AND deadline = 'VARdeadline' AND delegated_to = 'VARdelegated_to' AND job_ep = 'VARjob_ep' AND notes LIKE 'VARnotes' AND others_involved = 'VARothers_involved' AND request_date = 'VARrequest_date' AND requested_by = 'VARrequested_by' AND status = 'VARstatus' AND task LIKE 'VARtask'

My variables have also been set:

VARdate_updated || % || Request.QueryString("date_updated")
etc...



THE PAGE CODE:

<<A href="mailto:%@LANGUAGE="VBSCRIPT">%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tasks.asp" -->
<%
Dim search_results__VARdate_updated
search_results__VARdate_updated = "%"
If (Request.QueryString("date_updated") <> "") Then
search_results__VARdate_updated = Request.QueryString("date_updated")
End If
%>
<%
Dim search_results__VARdeadline
search_results__VARdeadline = "%"
If (Request.QueryString("deadline") <> "") Then
search_results__VARdeadline = Request.QueryString("deadline")
End If
%>
<%
Dim search_results__VARdelegated_to
search_results__VARdelegated_to = "%"
If (Request.QueryString("delegated_to") <> "") Then
search_results__VARdelegated_to = Request.QueryString("delegated_to")
End If
%>
<%
Dim search_results__VARjob_ep
search_results__VARjob_ep = "%"
If (Request.QueryString("job_ep") <> "") Then
search_results__VARjob_ep = Request.QueryString("job_ep")
End If
%>
<%
Dim search_results__VARnotes
search_results__VARnotes = "%"
If (Request.QueryString("notes") <> "") Then
search_results__VARnotes = Request.QueryString("notes")
End If
%>
<%
Dim search_results__VARothers_involved
search_results__VARothers_involved = "%"
If (Request.QueryString("others_involved") <> "") Then
search_results__VARothers_involved = Request.QueryString("others_involved")
End If
%>
<%
Dim search_results__VARrequest_date
search_results__VARrequest_date = "%"
If (Request.QueryString("request_date") <> "") Then
search_results__VARrequest_date = Request.QueryString("request_date")
End If
%>
<%
Dim search_results__VARrequested_by
search_results__VARrequested_by = "%"
If (Request.QueryString("requested_by") <> "") Then
search_results__VARrequested_by = Request.QueryString("requested_by")
End If
%>
<%
Dim search_results__VARstatus
search_results__VARstatus = "%"
If (Request.QueryString("status") <> "") Then
search_results__VARstatus = Request.QueryString("status")
End If
%>
<%
Dim search_results__VARtask
search_results__VARtask = "%"
If (Request.QueryString("task") <> "") Then
search_results__VARtask = Request.QueryString("task")
End If
%>
<%
Dim search_results
Dim search_results_numRows
Set search_results = Server.CreateObject("ADODB.Recordset")
search_results.ActiveConnection = MM_tasks_STRING
search_results.Source = "SELECT * FROM tasks WHERE date_updated = '" + Replace(search_results__VARdate_updated, "'", "''") + "' AND deadline = '" + Replace(search_results__VARdeadline, "'", "''") + "' AND delegated_to = '" + Replace(search_results__VARdelegated_to, "'", "''") + "' AND job_ep = '" + Replace(search_results__VARjob_ep, "'", "''") + "' AND notes LIKE '" + Replace(search_results__VARnotes, "'", "''") + "' AND others_involved = '" + Replace(search_results__VARothers_involved, "'", "''") + "' AND request_date = '" + Replace(search_results__VARrequest_date, "'", "''") + "' AND requested_by = '" + Replace(search_results__VARrequested_by, "'", "''") + "' AND status = '" + Replace(search_results__VARstatus, "'", "''") + "' AND task LIKE '" + Replace(search_results__VARtask, "'", "''") + "'"
search_results.CursorType = 0
search_results.CursorLocation = 2
search_results.LockType = 1
search_results.Open()
search_results_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
search_results_numRows = search_results_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
<table border="1">
<tr>
<td>date_updated</td>
<td>deadline</td>
<td>delegated_to</td>
<td>ID</td>
<td>job_ep</td>
<td>notes</td>
<td>others_involved</td>
<td>request_date</td>
<td>requested_by</td>
<td>status</td>
<td>task</td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT search_results.EOF)) %>
<tr>
<td><%=(search_results.Fields.Item("date_updated").Value)%></td>
<td><%=(search_results.Fields.Item("deadline").Value)%></td>
<td><%=(search_results.Fields.Item("delegated_to").Value)%></td>
<td><%=(search_results.Fields.Item("ID").Value)%></td>
<td><%=(search_results.Fields.Item("job_ep").Value)%></td>
<td><%=(search_results.Fields.Item("notes").Value)%></td>
<td><%=(search_results.Fields.Item("others_involved").Value)%></td>
<td><%=(search_results.Fields.Item("request_date").Value)%></td>
<td><%=(search_results.Fields.Item("requested_by").Value)%></td>
<td><%=(search_results.Fields.Item("status").Value)%></td>
<td><%=(search_results.Fields.Item("task").Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
search_results.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
search_results.Close()
Set search_results = Nothing
%>



I appreciate any and all help,
John

it should look more like this

Of course this is completely dependent on the data types of the fields in your database.

The data types are text except for 'date_updated' which is Date/Time.
The code you provided returned:
Syntax error in string in query expression 'date_updated = ' + Replace(search_results__VARdate_updated,'

This is the line in the code:


after that, all the rest of the code is commented out:


It seems to be looking at the ' as a comment character? I'm sorry, I can't do SQL from code.

John

You'll have to modify the code to handle the date according to your database.
Dates are handled differently depending on the database.
Sql Server

MS Access

Ok, dates are good now, just needed the # instead of '. While testing, I've discovered:
- if using AND operator and filling some fields: no results returned
- if using AND operator and filling all fields: correct results returned