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

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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 …
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 is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

12 Experts available now in Live!

Get 1:1 Help Now