?
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
Medium Priority
?
242 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
[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
  • 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
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 

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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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…
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

762 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