Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ASP WhereClause question

Posted on 1998-12-16
5
Medium Priority
?
190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 240 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 to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Because your company can’t afford for you to make SEO mistakes, you’ll want to ensure you’re taking the right steps each and every time you post a new piece of content. This list of optimization do’s and don’ts can help you become an SEO wizard.
Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Suggested Courses

610 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