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
179 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:nasemabdullaa
Comment Utility
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
Comment Utility
Can you paste the error message please?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:nasemabdullaa
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
hi Rouchie
thanks it  work

thanks
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

17 Experts available now in Live!

Get 1:1 Help Now