navigation
a webmaster learning community
     Home    Register     Search      Help      Login    
Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums
 

Advanced search
Recent Posts

 Todays Posts
 Most Active posts
 Posts since last visit
 My Recent Posts
 Mark posts read

 

Requesting more than one search

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP and Database >> Requesting more than one search 		Page: [1]
 
Joe Kauffman

 

Posts: 204
Joined: 6/9/2004
Status: offline

  		Requesting more than one search - 11/19/2004 15:45:44   
I have a page where the user can enter in a buyers last name and click search to find any in the database matching this last name. The next page does the searching and displays the last name and some other info. I now would like to make it possible for them to enter in either the buyers name or address, or lot number. Here is the current requesting code.
  <% Set objRS = Server.CreateObject("ADODB.Recordset")
  	 Set objConn=Server.CreateObject("ADODB.Connection")
	 
	 objConn.ConnectionString = MM_DS_STRING
	 objConn.Open
			
	If Request.QueryString("lastname") <> "" Then
		lastname = Request.QueryString("lastname")
	Else
		lastname = Request.Form("lastname")
	End If  
	strSQL = "SELECT tblBuyerLN, tblSub, tblLotNo, ID FROM tblWorkOrder WHERE tblBuyerLN LIKE '" & ucase(lastname) & "'"
		  
	Set objRS = objConn.Execute(strSQL)
	
	If NOT objRS.EOF Then
	  
	End IF
%>


Do I mearly replicate this for the other two fields I want searchable? Or can I add the others into this code?

Thanks in advance for your help.

Joe

_____________________________

Regards,

Joe Kauffman
rdouglass

 

Posts: 9280
From: Biddeford, ME USA
Status: offline

  		RE: Requesting more than one search - 11/19/2004 16:13:35   
quote:

<% Set objRS = Server.CreateObject("ADODB.Recordset")
Set objConn=Server.CreateObject("ADODB.Connection")

objConn.ConnectionString = MM_DS_STRING
objConn.Open

If Request.QueryString("lastname") <> "" Then
lastname = Request.QueryString("lastname")
Else
lastname = Request.Form("lastname")
End If
strSQL = "SELECT tblBuyerLN, tblSub, tblLotNo, ID FROM tblWorkOrder WHERE tblBuyerLN LIKE '" & ucase(lastname) & "'"

Set objRS = objConn.Execute(strSQL)

If NOT objRS.EOF Then

End IF
%>


How 'bout this:

<% Set objRS = Server.CreateObject("ADODB.Recordset")
Set objConn=Server.CreateObject("ADODB.Connection")

objConn.ConnectionString = MM_DS_STRING
objConn.Open

If Request.QueryString("lastname") <> "" Then
lastname = Request.QueryString("lastname")
Else
lastname = Request.Form("lastname")
End If

If Request.QueryString("address") <> "" Then
address = Request.QueryString("address")
Else
address = Request.Form("address")
End If
'continue with as many fields as you need

myCriteria = ""
IF trim(lastname)&"">"" THEN
myCriteria = " WHERE (lastname LIKE '%" & lastname & "%')"
END IF

IF trim(address)&"">"" THEN
IF myCriteria > "" THEN
myCriteria = myCriteria & " AND"
ELSE
myCriteria = myCriteria & " WHERE"
END IF
myCriteria = myCriteria & " (address LIKE '%" & address & "%')"
END IF
'continue with as many fields as you need


strSQL = "SELECT tblBuyerLN, tblSub, tblLotNo, ID FROM tblWorkOrder" & myCriteria

Set objRS = objConn.Execute(strSQL)

If NOT objRS.EOF Then

End IF
%>

See, what I'm doing is checking each of the fields I want to include in my search. If the field has data, add it to the search criteria; if not, don't add it. Pass all that to strSQL as the WHERE criteria and query the db.

Does that make any sense at all?

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to Joe Kauffman)
Joe Kauffman

 

Posts: 204
Joined: 6/9/2004
Status: offline

  		RE: Requesting more than one search - 11/19/2004 16:16:18   
Absolutely! I'll give it a shot and see how it goes. It's just about quitting time for this week so I'll probably hold off until Monday. Don't need any major dramas right before I leave on a Friday! Thanks for the help. I'll let you know how it goes.

Joe

_____________________________

Regards,

Joe Kauffman

(in reply to Joe Kauffman)
Joe Kauffman

 

Posts: 204
Joined: 6/9/2004
Status: offline

  		RE: Requesting more than one search - 11/22/2004 13:03:54   
rdouglass,

I inserted the code you suggested and I'm now pulling every record in the database. Here is the code. Could it maybe be in the response.write portion? I'm wanting to display the records with a hyperlink for them to click on. 

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<!--#include file="Connections/ds.asp" -->
<% If Session("dsuser") = TRUE Then %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Hacienda Builders - Design Studio Search Results</title>
<style type="text/css">
<!--
body {
	background-image: url(images/back.jpg);
}
.style7 {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-weight: bold;
	font-size: 14px;
}
body,td,th {
	font-family: Verdana, Arial, Helvetica, sans-serif;
}
-->
</style>
</head>
<body>
<table width="706" border="0" align="center">
  <tr>
    <td width="700"><div align="center"><img src="images/DSbanner2.gif" width="700" height="80"></div></td>
  </tr>
</table>
<p>


 <% Set objRS = Server.CreateObject("ADODB.Recordset") 
Set objConn=Server.CreateObject("ADODB.Connection") 

objConn.ConnectionString = MM_DS_STRING 
objConn.Open 

If Request.QueryString("lastname") <> "" Then 
lastname = Request.QueryString("lastname") 
Else 
lastname = Request.Form("lastname") 
End If 

If Request.QueryString("community") <> "" Then 
community = Request.QueryString("community") 
Else 
community = Request.Form("address") 
End If 

If Request.QueryString("lotnumber") <> "" Then 
lotnumber = Request.QueryString("lotnumber") 
Else 
lotnumber = Request.Form("lotnumber") 
End If 

myCriteria = "" 
IF trim(lastname)&"">"" THEN 
myCriteria = " WHERE (lastname LIKE '%" & lastname & "%')" 
END IF 

IF trim(community)&"">"" THEN 
IF myCriteria > "" THEN 
myCriteria = myCriteria & " AND" 
ELSE 
myCriteria = myCriteria & " WHERE" 
END IF 
myCriteria = myCriteria & " (address LIKE '%" & address & "%')" 
END IF 
'continue with as many fields as you need 
IF trim(lotnumber)&"">"" THEN 
IF myCriteria > "" THEN 
myCriteria = myCriteria & " AND" 
ELSE 
myCriteria = myCriteria & " WHERE" 
END IF 
myCriteria = myCriteria & " (address LIKE '%" & address & "%')" 
END IF 



strSQL = "SELECT tblBuyerLN, tblSub, tblLotNo, ID FROM tblWorkOrder" & myCriteria 

Set objRS = objConn.Execute(strSQL) 

If NOT objRS.EOF Then 

End IF 
%> 


<table width="100%"  border="0">
  <tr>
    <td><div align="center">Click on the desired buyer below to edit their Work Order. </div></td>
  </tr>
</table>
<table border="0" width="100%" id="table1">
	<tr>
		<td>
<table width="53%"  border="2" align="center" bordercolorlight="#672C74" bordercolordark="#672C74" id="table2">
  <tr>
    <td width="33%" align="center" style="font-family: Verdana, Arial, Helvetica, sans-serif">Choose One </td>
    <td width="22%" align="left" style="font-family: Verdana, Arial, Helvetica, sans-serif">Name </td>
    <td width="22%" align="center" style="font-family: Verdana, Arial, Helvetica, sans-serif">Subdivision</td>
    <td width="11%" align="center" style="font-family: Verdana, Arial, Helvetica, sans-serif">Lot</td>
    <td width="12%" align="center" style="font-family: Verdana, Arial, Helvetica, sans-serif">ID</td>
  </tr>
</table>
		</td>
	</tr>
	<tr>
		<td>
<form action="dsupdaterecord.asp" method="post" name="lastname">
</table>
<table width="53%"  border="2" align="center" bordercolorlight="#672C74" bordercolordark="#672C74" id="table3">
<%
Dim i
FOR i = 0 to objRS.Fields.Count - 1
	%>
          <% next %>
<% 
y=1
While Not objRS.EOF 
if y MOD 2 = 0 Then
response.write "<tr style='background-color:cornsilk;'>"
Else
response.write "<tr style='background-color:white;'>"
End If
y=y+1
%>
    <td align="center" style="font-family: Verdana, Arial, Helvetica, sans-serif"><div align="center">
          <% For i = 0 to objRS.Fields.Count - 1 %>
          <% 
		If i=0 Then
		response.write "<a href=""dsupdateworkorder.asp?ID="&objRS("ID")&""">Click to Edit</a><td>" 
		response.write objRS("tblBuyerLN")
		
	ElseIf IsNull(objRS(i)) Then
		Response.Write " "
	Else
		Response.Write objRS(i).Value
	End If%>
    </div></td>
    <td align="center" style="font-family: Verdana, Arial, Helvetica, sans-serif">
      <div align="center">
          <% Next %>
          <% objRS.MoveNext
Wend


%>

</table>
<p align="center">   </p>
</form>
</td>
</tr>
		<p> </td>
	</tr>
</table>
<%
Else
	Response.Write "You are not authorized to view this page."
	response.write "<a href='dsloginpage.asp'>Click here to Login.</A>"
End IF
%>


Thanks again for your help.

Joe

_____________________________

Regards,

Joe Kauffman

(in reply to Joe Kauffman)
Spooky

 

Posts: 26606
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Requesting more than one search - 11/22/2004 13:14:13   
Whats the SQL you are using?

eg :
strSQL = "SELECT tblBuyerLN, tblSub, tblLotNo, ID FROM tblWorkOrder" & myCriteria

response.write strSQL
response.end

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to Joe Kauffman)
Joe Kauffman

 

Posts: 204
Joined: 6/9/2004
Status: offline

  		RE: Requesting more than one search - 11/23/2004 14:29:17   
Got it figured out and it's working great! Thanks for the help.

_____________________________

Regards,

Joe Kauffman

(in reply to Spooky)
Page:   [1]

All Forums >> Web Development >> ASP and Database >> Requesting more than one search 		Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Back to top

  Microsoft Corporation in no way endorses or is affiliated with "OutFront."
  OutFront is published and edited by Thomas Brunt.
  440 Great Circle Dr., St. Matthews, SC 29135.
  © 2008 All Rights Reserved
  Click here to view our privacy statement.