asked on

ASP WhereClause question

hi,
I have a search form. But It doesn't returns any record. record is there!

Could anyone tell me what's wrong with my code?
SearchResults.asp
<%@ language = "VBScript" %>
<%
If len(Request.QueryString) >0 then
tableColumns = 5
%>
<html>
<head>
<title>Search Results</title>
<Object classID="clsid:333c7bc4-46of-11do-bco4-oo8oc7o55a83" ID="tdcPpl" height="0" width="0">
<param name="DataURL" Value="searchresultsdata.asp?<%=Request.QueryString%>">
<param name="UseHeader" Value="True">
</Object>
<script language ="JavaScript">

function startPage() {

document.all.item("boundTable").dataSrc ="#tdcPpl";
}
</script>
</head>
<body topmargin="0" leftmargin="0" onload = "startPage()">
<table id = "holderTable" width= 100%>
<tr>
<td align = "Center" id = "waitMarquee"> 
      <marquee style = "font:bold 50 'arial'">Searching</marquee> </td>
</tr>
</table>
<table cols="<%=tableColumns%>" Border = "1" Cellspacing = "0" width = "100%" id = "boundTable">
</table>
</body>
</html>
<%End if%>

Search.asp
<%@ LANGUAGE = "VBSCRIPT" %>
<html>

<head>
<title>CONTENT</title>

<SCRIPT LANGUAGE = "JAVASCRIPT" >
function gosearch() {
      document.all.item("Searchform").submit();
}
</script>

</head>

<body>
<div id = "formHolder">
<form action = "searchresults.asp" method = "Get" ID= "Searchform" onsubmit="gosearch()">

<%
dim con
set con = server.createobject("adodb.connection")
con.open "dbaccess_1", "", ""
%>

<p>Last: <input name="Last" size="20"></p>

<p>First: <input name="First" size="20"></p>
<%
dim sql
dim rst
sql = "select * from hcas_afc_loc"
set rst = server.createobject("adodb.recordset")
rst.open sql, con
%>

<p>Office: <select name="Location" size="1">
      <option value = "">None</option>
<% while not(rst.eof) %>
      <option value="<% = rst("afc_code") %>"><% = rst("Loc_name") %></option>
      <% rst.movenext %>
<% wend %>
<% set rst = nothing %>
</select></p>

Title: <select name = "title" size = "1" >
<%
dim rst3
dim sql3
sql3 = "select * from hcas_job_code"
set rst3 = server.createobject("adodb.recordset")
rst3.open sql3, con
%>
<option value="">None</option>
<% while not(rst3.eof) %>
      <option value = "<% =rst3("job_code") %>"><% = rst3("description") %>
      <% rst3.movenext %>
<% wend %>
<% set rst3 = nothing %>
</select>

<p>RC: <select name="rc" size="1" >
<%
dim rst2
dim sql2
sql2 = "select rc_id from hcas_rc"
set rst2 = server.createobject("adodb.recordset")
rst2.open sql2, con
%>
<option value="">None</option>
<% while not(rst2.eof) %>
      <option value="<% = rst2("rc_id") %>"> <% =rst2("rc_id") %> </option>
      <% rst2.movenext %>
<% wend %>
<% set rst2 = nothing %>
</select></p>

<input type = "Submit" value = "Search"></input>
</form>
</div>
</body>
</html>

SearchResultsData.asp

<%

If Len(Request.queryString) >0 then
If len(trim(Request.QueryString("Last"))) > 0 then
whereClause = whereClause & "User_info_basic.l_name = "" &trim(Request.QueryString("Last")) & "%" and "
end if
if len(trim(Request.queryString("first"))) >0 then
whereClause = whereClause & "User_info_basic.f_name = '" &trim(Request.QueryString("first")) & "%' and "
end if
whereClause = trim(whereClause)
If Right(whereClause, 3) = "and" Then
whereClause = Left(whereClause.len(whereClause)-3)
       whereClause = Trim(whereClause)
end if

If Len(whereClause) > 0 Then
sqlStatement = "Select User_Info_basic.l_name AS Last_Name, User_Info_basic.f_name AS First_Name, User_Info_work.rc_id AS RC, User_Info_work.job_code AS Title, User_Info_work.office AS Office From User_Info_basic, User_info_work WHERE " & whereClause
else
      sqlStatement = "Select User_Info_basic.l_name AS Last_Name, User_Info_basic.f_name AS First_Name, User_Info_work.rc_id AS RC, User_Info_work.job_code AS Title, User_Info_work.office AS Office      From User_Info_basic, User_info_work"
end if

Set RC = Server.createobject("adodb.connection")
RC.open "dbaccess_1", "", ""
set cmdTemp = Server.Createobject("adodb.command")
set ppl = server.createobject("adodb.recordset")
cmdTemp.commandtext = sqlstatement
cmdtemp.commandtype = 1
set cmdtemp.activeconnection = ppl
ppl.open cmdtemp, , 3, 1

Response.contenttype = "text/plain"

Response.Write "Last_Name:String, First_Name:String, RC:String, Title:String, Office:String"&vbcrlf

Do until ppl.eof
      If len(trim(ppl("First_Name"))) > 0 Then
       First_Name = Replace(ppl("First_Name"), ",","")
      else
       First_Name = ""
      end if
      rowItem = Replace(ppl("Last_Name"),",","")&","&Last_Name&"," &Replace(ppl("RC"),",","")&"," &Replace(ppl("Title"),",","")&","&Replace(ppl("Office"),",","")
      rowItem = Replace(rowPpl,vbcrlf,"")

      Response.write rowItem&vbcrlf

      ppl.movenext
loop

End if
%>

Or anyone could send me a sample sourccode to chrislee8@hotmail.com
I will grant it with 150 points

Thank you very much and have a nice day!

ny971181

rajgn

Are you getting any errors?

ny971181

ASKER

No. I didn't get any error

PBall

Just curious, is this your first attempt at using data binding?
You know you could do this in a more direct matter no?
Instead of reading things into a text file and then binding it, you can bind the data directly from the database itself. I have done this with a SQL database and all client side scripting. It's damn fast too :), at least faster than doing it the ASP way. Was pulling something like 2000+ records.

The only error in the code above that I can see is there is one period that supposed to be a comma.

If Right(whereClause, 3) = "and" Then
whereClause = Left(whereClause.len(whereClause)-3)
whereClause = Trim(whereClause)

Here is an example on how to do a dynamic search (SQL Building from the client side)

      function doSearch() {
            var WhereClause = false;
            var f = document.frmSearch;
            var strSQL = "SELECT Directory.Name, DirectoryTitles.TitleName, DirectoryLocations.LocationShort, Directory.Phone, DirectoryFlags.FlagName FROM Directory INNER JOIN DirectoryFlags ON Directory.FlagID = DirectoryFlags.FlagID INNER JOIN DirectoryLocations ON Directory.LocationID = DirectoryLocations.LocationID INNER JOIN DirectoryTitles ON Directory.TitleID = DirectoryTitles.TitleID "

            var lsort = sortArray[sortIdx].value();

            if (f.txtKeyword.value.length > 0) {

                  var SearchPhrase = '%'+f.txtKeyword.value+'%';

                  strSQL += "WHERE (Directory.Name LIKE '" + SearchPhrase + "' "
                  strSQL += "OR Directory.Phone LIKE '" + SearchPhrase + "' "
                  strSQL += "OR DirectoryLocations.LocationName LIKE '" + SearchPhrase + "' "
                  strSQL += "OR DirectoryLocations.LocationShort LIKE '" + SearchPhrase + "' "
                  strSQL += "OR DirectoryTitles.TitleName LIKE '" + SearchPhrase + "' "
                  strSQL += "OR DirectoryFlags.FlagName LIKE '" + SearchPhrase + "') "

                  if (f.radType[1].checked) strSQL += "AND Directory.FlagID = 3 ";
                  if (f.radType[2].checked) strSQL += "AND Directory.FlagID = 2 ";

                  if (f.selLocation.options.selectedIndex > 0) {
                        strSQL = strSQL + 'AND Directory.LocationID=' + f.selLocation.options[f.selLocation.options.selectedIndex].value + ' '
                  }

                  strSQL += "ORDER BY " + lsort;

                  dsoDirectory.SQL = strSQL;
                  dsoDirectory.refresh();
            } else {
                  if (f.radType[1].checked) {
                        strSQL += "WHERE Directory.FlagID = 3 ";
                        WhereClause = true;
                  }
                  if (f.radType[2].checked) {
                        strSQL += "WHERE Directory.FlagID = 2 ";
                        WhereClause = true;
                  }

                  if (f.selLocation.options.selectedIndex > 0) {

                        if (WhereClause)
                              strSQL += 'AND ';
                        else
                              strSQL += 'WHERE ';

                        strSQL = strSQL + 'Directory.LocationID=' + f.selLocation.options[f.selLocation.options.selectedIndex].value + ' '
                  }

                  strSQL += "ORDER BY " + lsort;
                  dsoDirectory.SQL = strSQL;
                  dsoDirectory.refresh();
            }
                  return false;
      }

<OBJECT classid=clsid:BD96C556-65A3-11D0-983A-00C04FC29E33 height=1 id=dsoDirectory width=0 height=0>
      <PARAM NAME="SQL" VALUE="SELECT Directory.Name, DirectoryTitles.TitleName, DirectoryLocations.LocationShort, Directory.Phone, DirectoryFlags.FlagName FROM Directory INNER JOIN DirectoryFlags ON Directory.FlagID = DirectoryFlags.FlagID INNER JOIN DirectoryLocations ON Directory.LocationID = DirectoryLocations.LocationID INNER JOIN DirectoryTitles ON Directory.TitleID = DirectoryTitles.TitleID ORDER BY Directory.Name">
      <PARAM NAME="Connect" VALUE="DSN=intranetDB;Description=New Intranet SQL Database;SERVER=www;UID=sa;PWD=;APP=SS Employee Directory;WSID=SSTEC166182;DATABASE=intranetDB;LANGUAGE=us_english">
      <PARAM NAME="Server" VALUE="http://www.sunsent.trb">
</OBJECT>

ASKER CERTIFIED SOLUTION

BlackMan

membership

This solution is only available to members.

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

Start Free Trial

ny971181

ASKER

Thanks