Solved

ASP WhereClause question

Posted on 1998-12-16
5
179 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
rearranging, beautifying pure CSS read more script 5 54
Reinstall Putty Windows 7 5 64
Remove lines by logo 2 29
Divi Them Help with Full Width header 20 61
Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

786 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