Solved

ASP WhereClause question

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Uploading files to the web server has become common part of almost any kind of web application. People use different technologies to solve this, but regardless of the technology used, it is always useful to have some kind of progress indicator shown…
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.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn how to dynamically set the form action using jQuery.

708 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

13 Experts available now in Live!

Get 1:1 Help Now