[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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

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
nasemabdullaa
Asked:
nasemabdullaa
  • 5
  • 5
1 Solution
 
RouchieCommented:
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
 
nasemabdullaaAuthor Commented:
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
 
RouchieCommented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
nasemabdullaaAuthor Commented:
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
 
RouchieCommented:
Can you paste the error message please?
0
 
nasemabdullaaAuthor Commented:
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
 
RouchieCommented:
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
 
nasemabdullaaAuthor Commented:
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
 
RouchieCommented:
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
 
nasemabdullaaAuthor Commented:
hi Rouchie
thanks it  work

thanks
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now