Solved

i use simple database contain table with field (first name,last name , job , country) and i use standerd SQL to search in the database how i can search for more one field

Posted on 2006-06-17
10
218 Views
Last Modified: 2010-04-25
hi all

i use dreamwaver for building web site and  i use simple database contain table with field (first name,last name , job , country) and i use standerd SQL to search in the database how i can search for more one field

i mean for example  i want to search for a person with name (jon) in country (USA) and have the job (doctor)
i want to search for more one field

thanks
0
Comment
Question by:nasemabdullaa
  • 5
  • 5
10 Comments
 
LVL 25

Expert Comment

by:Rouchie
ID: 16929040
You need to change your SQL statement to include these fields.  Something like this...

SELECT * from myTable WHERE (firstname = 'jon' AND country = 'USA' AND job = 'doctor')

You will need to swap out the names of the search fields with the form fields on your web page.  If you can't do this, post what you have so far and I'll modify it for you.
0
 

Author Comment

by:nasemabdullaa
ID: 16931835
hi
i use the code for search page

<%@LANGUAGE="VBSCRIPT"%>
<html>
<head>
<title>Simple Search Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF" text="#000000">
<h2>Simple Search Form </h2>
<form action="NameSearch_Results.asp" method="post" name="SimpleSearch" id="SimpleSearch">
  <p>Search by Name:
    <input type="text" name="firstname">
    <input type="submit" name="Submit" value="Search">
  </p>
  </form>
<p>&nbsp; </p>
</body>
</html>

but i have problem how i can search for three variable i put one text field and submit bottom how i can put three text field

i use the code for result page


<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/employee.asp" -->
<%
Dim RS_EmployeeSearch__varName
RS_EmployeeSearch__varName = "Bakharia"
If (Request.Form("Firstname")  <> "") Then
  RS_EmployeeSearch__varName = Request.Form("Firstname")
End If
%>
<%
Dim RS_EmployeeSearch__varName1
RS_EmployeeSearch__varName1 = "Bakharia"
If (Request.Form("Surname")   <> "") Then
  RS_EmployeeSearch__varName1 = Request.Form("Surname")  
End If
%>
<%
Dim RS_EmployeeSearch__varName2
RS_EmployeeSearch__varName2 = "Web Developer"
If (Request.Form("Job_Title")   <> "") Then
  RS_EmployeeSearch__varName2 = Request.Form("Job_Title")  
End If
%>
<%
Dim RS_EmployeeSearch
Dim RS_EmployeeSearch_numRows

Set RS_EmployeeSearch = Server.CreateObject("ADODB.Recordset")
RS_EmployeeSearch.ActiveConnection = MM_employee_STRING
RS_EmployeeSearch.Source = "SELECT Firstname, Surname, Phone_No  FROM Personal_Details  WHERE Firstname LIKE '%" + Replace(RS_EmployeeSearch__varName, "'", "''") + "%' AND Surname LIKE '%" + Replace(RS_EmployeeSearch__varName1, "'", "''") + "%' AND Job_Title LIKE '%" + Replace(RS_EmployeeSearch__varName2, "'", "''") + "%'"
RS_EmployeeSearch.CursorType = 0
RS_EmployeeSearch.CursorLocation = 2
RS_EmployeeSearch.LockType = 1
RS_EmployeeSearch.Open()

RS_EmployeeSearch_numRows = 0
%>


<html>
<head>
<title>Name Search Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF" text="#000000">
<h2>Name Search Results </h2>
<ul>
  <li><%=(RS_EmployeeSearch.Fields.Item("Firstname").Value)%>, <%=(RS_EmployeeSearch.Fields.Item("Surname").Value)%> (<%=(RS_EmployeeSearch.Fields.Item("Phone_No").Value)%>) </li>
</ul>
</body>
</html>
<%
RS_EmployeeSearch.Close()
Set RS_EmployeeSearch = Nothing
%>

when i test the code in test it work but when i seein browser i get the error (line 48 the statmant contain true vale)

the line contain error is (<li><%=(RS_EmployeeSearch.Fields.Item("Firstname").Value)%>, <%=(RS_EmployeeSearch.Fields.Item("Surname").Value)%> (<%=(RS_EmployeeSearch.Fields.Item("Phone_No").Value)%>) </li>)


thanks
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 16932196
The results page looks correct, so add the text boxes to your search page:

<p>Name: <input type="text" name="firstname"></p>
<p>Surname: <input type="text" name="surname"></p>
<p>Job Title: <input type="text" name="job_title"></p>
<p><input type="submit" name="Submit" value="Search"></p>
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:nasemabdullaa
ID: 16940518
hi
i try but it not work i get error in line 48  ( the same error)
(<li><%=(RS_EmployeeSearch.Fields.Item("Firstname").Value)%>, <%=(RS_EmployeeSearch.Fields.Item("Surname").Value)%> (<%=(RS_EmployeeSearch.Fields.Item("Phone_No").Value)%>) </li>

thanks

0
 
LVL 25

Expert Comment

by:Rouchie
ID: 16940539
Can you paste the error message please?
0
 

Author Comment

by:nasemabdullaa
ID: 16948434
hi
BOF or EOF contain true value
the error in the line
<li><%=(RS_EmployeeSearch.Fields.Item("Firstname").Value)%>, <%=(RS_EmployeeSearch.Fields.Item("Surname").Value)%> (<%=(RS_EmployeeSearch.Fields.Item("Phone_No").Value)%>) </li>

thanks
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 16949351
EOF means End Of File, which usually means that no records have been found in the database that match the search conditions you entered.

You need to make sure your code checks for this before outputting any data.  Something like this:

<%
If Not RS_EmployeeSearch.EOF then ' check to make sure some records are found

      While not RS_EmployeeSearch.EOF
%>

      <li><%=(RS_EmployeeSearch.Fields.Item("Firstname").Value)%>, <%=(RS_EmployeeSearch.Fields.Item("Surname").Value)%>
      (<%=(RS_EmployeeSearch.Fields.Item("Phone_No").Value)%>) </li>

<%
      RS_EmployeeSearch.MoveNext
      Wend
Else ' no records found
%>

    No records have been found

<%
End If ' end check
0
 

Author Comment

by:nasemabdullaa
ID: 16957131
hi  Rouchie
i change code for search page to

<%@LANGUAGE="VBSCRIPT"%>
<html>
<head>
<title>Simple Search Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF" text="#000000">
<h2>Simple Search Form </h2>
<form action="NameSearch_Results.asp" method="post" name="SimpleSearch" id="SimpleSearch">
  <p>Search by Name:
    <input type="text" name="firstname">
    <input type="text" name="surname">
    <input type="text" name="job_title">  
  <p>
    <input type="submit" name="Submit" value="Search">
  </p>
  </form>
<p>&nbsp; </p>
</body>
</html>

and i change the resulat page to

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/employee.asp" -->
<%
Dim RS_EmployeeSearch__varName
RS_EmployeeSearch__varName = "bakharia"
If (Request.Form("Firstname") <> "") Then
  RS_EmployeeSearch__varName = Request.Form("Firstname")
End If
%>
<%
Dim RS_EmployeeSearch__varName1
RS_EmployeeSearch__varName1 = "bakharia"
If (Request.Form("Surname")  <> "") Then
  RS_EmployeeSearch__varName1 = Request.Form("Surname")
End If
%>
<%
Dim RS_EmployeeSearch__varName2
RS_EmployeeSearch__varName2 = "web developer"
If (Request.Form("Job_Title")   <> "") Then
  RS_EmployeeSearch__varName2 = Request.Form("Job_Title")  
End If
%>
<%
Dim RS_EmployeeSearch
Dim RS_EmployeeSearch_numRows

Set RS_EmployeeSearch = Server.CreateObject("ADODB.Recordset")
RS_EmployeeSearch.ActiveConnection = MM_employee_STRING
RS_EmployeeSearch.Source = "SELECT Firstname, Surname, Phone_No  FROM Personal_Details  WHERE Firstname LIKE '%" + Replace(RS_EmployeeSearch__varName, "'", "''") + "%' AND Surname LIKE '%" + Replace(RS_EmployeeSearch__varName1, "'", "''") + "%' AND Job_Title LIKE '%" + Replace(RS_EmployeeSearch__varName2, "'", "''") + "%'"
RS_EmployeeSearch.CursorType = 0
RS_EmployeeSearch.CursorLocation = 2
RS_EmployeeSearch.LockType = 1
RS_EmployeeSearch.Open()

RS_EmployeeSearch_numRows = 0
%>

<html>
<head>
<title>Name Search Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF" text="#000000">
<h2>Name Search Results </h2>
<ul>
<p>
  <%
If Not RS_EmployeeSearch.EOF then ' check to make sure some records are found

      While not RS_EmployeeSearch.EOF
%>
</p>
<li><%=(RS_EmployeeSearch.Fields.Item("Firstname").Value)%>, <%=(RS_EmployeeSearch.Fields.Item("Surname").Value)%>
      (<%=(RS_EmployeeSearch.Fields.Item("Phone_No").Value)%>) </li>

<%
      RS_EmployeeSearch.MoveNext
      Wend
Else ' no records found
%>

    No records have been found

<%
End If ' end check
 
</ul>
</body>
</html>
<%
RS_EmployeeSearch.Close()
Set RS_EmployeeSearch = Nothing
%>

but i still get the error

Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/NameSearch_Results.asp, line 42

when i put all three field with correct information i get the result page but if i enter one wrong field i get the error
i mean i one with first name (jon) and surname (mike) and i enter in the search page first name (jon) and surname (mike) i get the correct result but if i enter first name (jon) and surname (son) i must get the result no result but i get the error in the page

thanks


0
 
LVL 25

Accepted Solution

by:
Rouchie earned 500 total points
ID: 16957621
The END IF statement has not been terminated correctly, which I think is the problem because everything else looks okay.

Change this:

<%
End If ' end check
 

To this:

<%  End If  %>
0
 

Author Comment

by:nasemabdullaa
ID: 16978226
hi Rouchie
thanks it  work

thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Manipulate Access Database from Web Page 8 519
Update 2 tables at the same time 3 468
div id bookmark tag not working in Firefox 15 569
page loads much slower in IE than chrome 9 1,296
For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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