AJAX搜索2个元素

Question

I am working on a project using classic ASP (not my idea) and AJAX which uses an access database to pull data from. I have the following form:

<form action="" id="autocomplete-search"> 
<div class="simplesearch">
<label for="intesearch">Start typing a name</label>
<input type="text" onkeyup="showCustomer(this.value)" id="intesearch" onfocus="disable_submit()">
</div>

The javascript I have at the top of my page is the following:

function showCustomer(str)
{
// remove white space from the name entered on the search screen
str = str.replace(" ", "");
var xmlhttp;    
if (str=="")
{
document.getElementById("txtHint").innerHTML="No records found";
return; 
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
}
    xmlhttp.open("GET","get_customer.asp?q="+str,true);
    xmlhttp.send();
}

This links to the an ASP file:

<%
response.expires=-1
dim name
name = request.querystring("q")
sql="SELECT person.id, person.forename, person.surname, person.postid, person.extension, person.internal_extension, person.mobile,  person.room_number, job.id, job.name FROM staff INNER JOIN posts ON job.id = person.jobid WHERE (person.forename & person.surname like '%" & name & "%') or job.name like '%" & name & "%'
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open("E:\websites\directory\database.mdb")
set rs=Server.CreateObject("ADODB.recordset")
rs.Open sql,conn
response.write("<table>")
response.write("<thead>")
response.write("<tr>")
 response.write("<th>Forename</th>")
 response.write("<th>Surname</th>")
 response.write("<th>Extension</th>")
 response.write("<th>Internal Ext.</th>")
 response.write("<th>Mobile</th>")
 response.write("<th>Room No.</th>")
 response.write("<th></th>")
 response.write("</tr>")
 response.write("</thead>")
 response.write("<tbody>")
 Do While Not rs.EOF
     response.write("<tr>")
     response.write("<td>" & rs("forename") & "</td>")
     response.write("<td>" & rs("surname") & "</td>")
     response.write("<td>" & rs("extension") & "</td>")
     response.write("<td>" & rs("internal_extension") & "</td>")
     response.write("<td>" & rs("mobile") & "</td>")
     response.write("<td>" & rs("room_number") & "</td>")
      response.write("<td>" & rs("name") & "</td>")
      response.write("<td><a href='index.asp?View=StaffRecord&StaffID=" & rs("id") & "'>Record</a></td>")
     response.write("</tr>")
rs.MoveNext
loop
response.write("</tbody>")
response.write("</table>")
%>

The problem is that it works when I am pulling data from the one table. AJAX just seems to hate the join for some reason.

Any ideas?

Answer 1

Your query needs to be changed like this to refer tables as job and person

sql="SELECT person.id, person.forename, person.surname, person.postid, person.extension, person.internal_extension, person.mobile,  person.room_number, job.id, job.name FROM staff as person INNER JOIN posts as job ON job.id = person.jobid WHERE ((person.forename like '%' and person.surname like '%' and name like '%') or (job.name like '%' and name like '%'))";