Solved

ASP WhereClause question

Posted on 1998-12-16
5
176 Views
Last Modified: 2013-12-25
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">&#160;
      <marquee style = "font:bold 50 'arial'">Searching</marquee>&#160;</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
0
Comment
Question by:ny971181
5 Comments
 
LVL 1

Expert Comment

by:rajgn
ID: 1857306
Are you getting any errors?
0
 
LVL 1

Author Comment

by:ny971181
ID: 1857307
No. I didn't get any error
0
 
LVL 6

Expert Comment

by:PBall
ID: 1857308
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>



0
 
LVL 7

Accepted Solution

by:
BlackMan earned 80 total points
ID: 1857309
The problem is that in your Where clause, you are using the = operator with a wildcard equation. Wildcards works with the Like operator, so change your code to this:

whereClause = whereClause & "User_info_basic.l_name like "" &trim(Request.QueryString("Last")) & "%" and " 
0
 
LVL 1

Author Comment

by:ny971181
ID: 1857310
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Accessibility and Usability are two concepts that seem to be closely related.  But, too many people seem to have a distorted perception of them. During last five years, those two words have come to the day-to-day work of almost every web develope…
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to dynamically set the form action using jQuery.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now