By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,334 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Please Help Advanse Search, two parts are working, the 3rd to connect.

P: n/a
Hi Everyone,

I got two parts of my advance search to work, I am running out of Logic to
connect the third. My mind is in swing! Pleaseeeeeeeee Help me.

I have 3 Fiels to search, the First two works, thats:

1. Category to search from the (Drop down name: ASPDBCatSearch) is working
with the Input Text Box Field (name: keyword), based on Whatever selection on
the Drop down value selection:
The "Option Value" FirstName,Title,Email etc... is connected to the Input
Text Box Field (name: keyword) with the asp code thus:

' HTML Drop down Name | FirstName is the value name

If Request.Form("ASPDBCatSearch") = "FirstName" Then

' HTML Input Text Box name is keyword

SQL = SQL & " WHERE FirstName LIKE '%" & Request.Form("keyword") & "%'
"
End If

The rest values based on selection works with the Keyword typing.
>>>>My Question is: I have added another drop Down Call salary (name: Salarycom), I need to add that to the Search Logic, and I am lost, How can I do this please?
In Breiaf How can I connect the "Salary Drop down Values" to the Category
Search drop down + and the Input txt box Search to match the Exact Search?

Can Some one Give me two sentences of example with my above Added code as to
how the salary values should be added to the Category and Input txt box
fields in the asp code?

------------------------------------------------------------------------------
The html Code:
<table border="0" width="525" bgcolor="#FFFFFF" cellspacing="3"
cellpadding="3"
style="border: 0px outset #EEEEEE; ">
<tr>
<td width="515" bgcolor="#FFFFFF"><form action="Searchengineresults.asp"
method="post" align="center">

<div align="center"><center>
<table border="1" width="486" cellpadding="3"
bgcolor="#F5F5F5" bordercolorlight="#EEEEEE" style="border-collapse:
collapse">
<tr>
<td align="left" width="169"><strong><font face="Tahoma"><big><big>
<font size="1">1.</font></big></big><font size="1"Select a category to
search</font></font></strong><p align="center"><select
name="ASPDBCatSearch" size="1">
<option selected value="All">All</option>
<option value="FirstName">First Name</option>
<option value="LastName">Last Name </option>
<option value="Title">Title </option>
<option value="Division">Dept/Div </option>
<option value="Phone">Phone </option>
<option value="Email">E-Mail </option>
</select></td>
<td align="left" width="106" bgcolor="#FF870F"><strong><font
face="Tahoma"><big><big>
<font size="1">2.</font></big></big><font size="1">
Salary</font></font></strong><p>
<select name="Salarycom" size="1">
<option selected value="Allsal">All</option>
<option value="10000">10000</option>
<option value="20000">20000</option>
<option value="30000">30000</option>
<option value="40000">30000</option>
<option value="50000">50000</option>
<option value="60000">60000</option>
<option value="70000">70000</option>
<option value="80000">80000</option>
<option value="90000">90000</option>

<option value="100000 or Abv">100000 or Abv</option>

</select></td>
<td width="172" align="right">
<p align="center"><strong><font face="Tahoma"><big><big>
<font size="1">3.</font></big></big><font size="1"Type search
criteria</font></font></strong><p>
<input type="text" size="30" name="keyword"></td>
</tr>
<tr>
<td colspan="3" align="center" width="478">
<p><input type="reset" name="B2" value="Clear">
<input type="submit" name="B1" value="Search employees"></td>
</tr>
</table>
</center></div>
</form>
</td>
</tr>
</table>
</center></div>

</body>
</html>

------------------------------------------------------------------------------
---------------------

ASP Code Searchengineresults.asp
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Search Engine Results!</title>
</head>

<body>
<%

Dim SQL

Set aspdbweb = Server.CreateObject("ADODB.Connection")
aspdbweb = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath
("employees.mdb") & ";"

SQL = "SELECT * FROM Employees"

' An Example on Salary Drop down Values to connect to the rest two (Category
and Input txt Box) please here will be appreciated
If Request.Form("ASPDBCatSearch") = "FirstName" Then
SQL = SQL & " WHERE FirstName LIKE '%" & Request.Form("keyword") & "%' "
End If

If Request.Form("ASPDBCatSearch") = "LastName" Then
SQL = SQL & " WHERE LastName LIKE '%" & Request.Form("keyword") & "%'"
End If

If Request.Form("ASPDBCatSearch") = "Title" Then
SQL = SQL & " WHERE Title LIKE '%" & Request.Form("keyword") & "%'"
End If

If Request.Form("ASPDBCatSearch") = "Division" Then
SQL = SQL & " WHERE Division LIKE '%" & Request.Form("keyword") & "%'"
End If

If Request.Form("ASPDBCatSearch") = "Phone" Then
SQL = SQL & " WHERE Phone LIKE '%" & Request.Form("keyword") & "%'"
End If

If Request.Form("ASPDBCatSearch") = "Email" Then
SQL = SQL & " WHERE EMail LIKE '%" & Request.Form("keyword") & "%'"
End If

Set rsglobal = Server.CreateObject("ADODB.Recordset")
rsglobal.Open SQL, aspdbweb, 3
%>
<%
If rsglobal.BOF and rsglobal.EOF Then%>

<h2 align="center">We did not find a match!</h2>
<%Else%>
<%If Not rsglobal.BOF Then%>

<h2>Here are the results of your search:</h2>

<table BORDER="0" width="100%" cellpadding="3">
<tr>
<th bgcolor="#FF870F"><span style="font-weight: 400">
<font face="Arial" color="#FFFFFF" size="1">First name </font></span></th>
<th bgcolor="#FF870F"><span style="font-weight: 400">
<font face="Arial" color="#FFFFFF" size="1">Last Name </font></span></th>
<th bgcolor="#FF870F"><span style="font-weight: 400">
<font face="Arial" color="#FFFFFF" size="1">Salary </font></span></th>
<th bgcolor="#FF870F"><span style="font-weight: 400">
<font face="Arial" color="#FFFFFF" size="1">Title </font></span></th>
<th bgcolor="#FF870F"><span style="font-weight: 400">
<font face="Arial" color="#FFFFFF" size="1">Division </font></span></th>
<th bgcolor="#FF870F"><span style="font-weight: 400">
<font face="Arial" color="#FFFFFF" size="1">Phone </font></span></th>
<th bgcolor="#FF870F"><span style="font-weight: 400">
<font face="Arial" color="#FFFFFF" size="1">E-Mail </font></span></th>
</tr>
<%
Do While Not rsglobal.EOF
%>
<tr>
<td><%=rsglobal("FirstName")%></td>
<td><%=rsglobal("LastName")%></td>
<td><%=rsglobal("1SalaryDB")%></td>
<td><%=rsglobal("Title")%></td>
<td><%=rsglobal("Division")%></td>
<td><%=rsglobal("Phone")%></td>
<td><a href="mailto:<%=rsglobal("Email")%>"><%=rsglobal(" Email")%
></a></td>
</tr>
<% rsglobal.MoveNext
Loop
%>
</table>
<%End If%>
<%End If%>
<%
rsglobal.Close
%>
</body>
</html>

--------------------------------------------------------

Awaiting your favorable reply.

Thank you.
Aug 21 '08 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Old Pedant wrote:
*******************
>
*NEVER* bother checking BOF. Pointless.
.... unless navigation in the recordset has occurred, or records have
been deleted from it. :-)

If you had added " ... immediately after opening the recordset." I would
have had no comment.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Aug 21 '08 #2

P: n/a
"Bob Barrows [MVP]" wrote:
If you had added " ... immediately after opening the recordset." I would
have had no comment.
Yah, I know. But in 8 years of writing ASP code (which I no longer do
except for a couple of pro bono non-profit org sites) I never once had a
reason to use or need a BOF test. I think the exceptions are so rare that
saying "never" is better than bothering to describe the exceptions that will
(in practice) not be encountered.

Oh...and in over 86,000 posts I made on the ASPMessageBoard site, answering
all sorts of questions, never once did anybody have any code that needed the
BOF test, either. ( http://www.aspmessageboard.com/scripts/topPosters.asp )

Sometimes, even for an old pedant, it's not worth expounding the theory when
the practice is so overwhelmingly one-sided.

It's nice to meet somebody even more pedantic than I am. <grin/>

p.s.: Actually, I gave myself that name because of my penchant for
correcting English syntax, not code syntax. <grin style="monstrous" />

Aug 21 '08 #3

P: n/a
Hello Bob Barrows, and Old Pedant,

I thank you so much for the response I am sorry for the late reply, and thank
you for the speedy response to help the needy. I am testing my code.

Thank you so much Old Pedant.

Old Pedant wrote:
>If you had added " ... immediately after opening the recordset." I would
have had no comment.

Yah, I know. But in 8 years of writing ASP code (which I no longer do
except for a couple of pro bono non-profit org sites) I never once had a
reason to use or need a BOF test. I think the exceptions are so rare that
saying "never" is better than bothering to describe the exceptions that will
(in practice) not be encountered.

Oh...and in over 86,000 posts I made on the ASPMessageBoard site, answering
all sorts of questions, never once did anybody have any code that needed the
BOF test, either. ( http://www.aspmessageboard.com/scripts/topPosters.asp )

Sometimes, even for an old pedant, it's not worth expounding the theory when
the practice is so overwhelmingly one-sided.

It's nice to meet somebody even more pedantic than I am. <grin/>

p.s.: Actually, I gave myself that name because of my penchant for
correcting English syntax, not code syntax. <grin style="monstrous" />
--
Message posted via http://www.webmasterkb.com

Aug 23 '08 #4

P: n/a


"iahamed via WebmasterKB.com" wrote:
My Database Table Name is "1SalaryDB"
TABLE name??? Or FIELD name???

In either case, you need to put the name between [...] because a name that
starts with a digit will be seen as a number and then an error, otherwise.

If it's the TABLE name, then you need to code
SQL = "SELECT * FROM [1SalaryDB] WHERE 1=1 "

If it's the FIELD name, then leave the table name alone and change
salary = Replace( Trim( Request("SalaryCom") ), "'", "''" )
If salary <"" Then
SQL = SQL & " AND salary " & salary
End If
To

salary = Replace( Trim( Request("SalaryCom") ), "'", "''" )
If salary <"" Then
SQL = SQL & " AND [1SalaryDB] " & salary
End If

************

No matter what, your code doing
SQL = SQL & " AND " & 1SalaryDB & " LIKE '%" & keyword & "%' "
makes no sense at all.

You can't have a VBScript variable that starts with a digit.

Aug 23 '08 #5

P: n/a
salary = Replace( Trim( Request("SalaryCom") ), "'", "''" )
If salary <"" Then
SQL = SQL & " AND [1SalaryDB] " & salary
End If

I am confused with the "whatToSearch", and the above "salary" in the detailed
given. However adding SQL = "SELECT * FROM Employees WHERE 1=1 " makes my
rest of the Search options codes collapse that is:

If Request.Form("ASPDBCatSearch") = "LastName" Then
SQL = SQL & " WHERE LastName LIKE '%" & Request.Form("keyword") & "%'"
End If

If Request.Form("ASPDBCatSearch") = "Title" Then
SQL = SQL & " WHERE Title LIKE '%" & Request.Form("keyword") & "%'"
End If

If Request.Form("ASPDBCatSearch") = "Division" Then
SQL = SQL & " WHERE Division LIKE '%" & Request.Form("keyword") & "%'"
End If
Thank for the Help Anyway I will find someone to Code the third part for me.

Good luck Old Pedant.

--
Message posted via WebmasterKB.com
http://www.webmasterkb.com/Uwe/Forums.aspx/asp/200808/1

Aug 24 '08 #6

P: n/a

"iahamed via WebmasterKB.com" wrote:
Set aspdbweb = Server.CreateObject("ADODB.Connection")
aspdbweb = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath
("employees.mdb") & ";"
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'aspdbweb'
/GoodASPDBsearch/Searchengineresults.asp, line 37

Line no 37 is: Set rsglobal = aspdbweb.Execute( SQL )
************

Sorry! Minor typo on my part.

The problem is actually on this line:
aspdbweb = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath ("employees.mdb") & ";"

That SHOULD be

aspdbweb.OPEN "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & Server.MapPath("employees.mdb") & ";"

I don't know how/why I put an equals sign there instead of the call to .OPEN

See, though, it DOES help to tell us what errors you get! This one was an
easy fix to find, once I saw the error message and you told me what line had
the error! Good posting.

Aug 26 '08 #7

P: n/a
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/GoodASPDBsearch/Searchengineresults.asp, line 13

Thats the error I get Sir, and I did try with other various connection
methods its just asking the line no 13.

aspdbweb.OPEN "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" & Server.
MapPath("employees.mdb") & ";"
Other connection methods I tried:
'aspdbweb.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & "" &
Request.ServerVariables("APPL_PHYSICAL_PATH") & "employees.mdb"

Thank you.

Rgds.
Old Pedant wrote:
>Set aspdbweb = Server.CreateObject("ADODB.Connection")
aspdbweb = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath
[quoted text clipped - 6 lines]
>>
Line no 37 is: Set rsglobal = aspdbweb.Execute( SQL )

************

Sorry! Minor typo on my part.

The problem is actually on this line:
aspdbweb = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath ("employees.mdb") & ";"

That SHOULD be

aspdbweb.OPEN "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & Server.MapPath("employees.mdb") & ";"

I don't know how/why I put an equals sign there instead of the call to .OPEN

See, though, it DOES help to tell us what errors you get! This one was an
easy fix to find, once I saw the error message and you told me what line had
the error! Good posting.
--
Message posted via WebmasterKB.com
http://www.webmasterkb.com/Uwe/Forums.aspx/asp/200808/1

Aug 27 '08 #8

P: n/a


"iahamed via WebmasterKB.com" wrote:
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/GoodASPDBsearch/Searchengineresults.asp, line 13

Thats the error I get Sir, and I did try with other various connection
methods its just asking the line no 13.

aspdbweb.OPEN "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" & Server.
MapPath("employees.mdb") & ";"
I can't see how the actual connection string used would have any impact on
that particular error message.

When it complains about "object required" inevitably that means that you are
using an expression of the form
objectName.methodOrPropertyName
and the "objectName" is not really an object.

If you have those two lines in a row *EXACTLY* as

Set aspdbweb = Server.CreateObject("ADODB.Connection")
aspdbweb.Open "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & Server.MapPath("employees.mdb") & ";"

Then I honestly have no idea what the cause of that error is.

If you want to, post the full code (at least, say, the first 50 lines?)
here. Again.

Aug 27 '08 #9

P: n/a


"iahamed via WebmasterKB.com" wrote:
ASP Code as requested to paste:
------------------------------------------------------
<%

Dim SQL

'aspdbweb.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & "" &
Request.ServerVariables("APPL_PHYSICAL_PATH") & "employees.mdb"
aspdbweb.OPEN "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" & Server.
MapPath("employees.mdb") & ";"

'Set aspdbweb = Server.CreateObject("ADODB.Connection")
'aspdbweb = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath
("employees.mdb") & ";"

'My Old SQL = "SELECT * FROM Employees"
'WHERE 1=1 is sneaky trick so we can use AND for other conditions
SQL = "SELECT * FROM Employees WHERE 1=1"

' the REPLACE calls are to protect against SQL injection
whatToSearch = Replace( Trim( Request("ASPDBCatSearch") ), "'", "''" )
keyword = Replace( Trim( Request("Keyword") ), "'", "''" )

.. et cetera ...
If you would GET RID of all the commented-out lines, you would easily see
your problem. And, indeed, it is EXACTLY what I said it was. Thus:

<%
Dim SQL
aspdbweb.OPEN "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" &
Server.MapPath("employees.mdb") & ";"

SQL = "SELECT * FROM Employees WHERE 1=1"

' the REPLACE calls are to protect against SQL injection
whatToSearch = Replace( Trim( Request("ASPDBCatSearch") ), "'", "''" )
keyword = Replace( Trim( Request("Keyword") ), "'", "''" )

.... et cetera ...

See? After we get rid of your commented-out code, it is easy to see that
you are doing
aspdbweb.OPEN
but you are *NOT* doing
Set aspdbweb = Server.CreateObject("ADODB.Connection")
*before* the OPEN line!!!

You MUST MUST MUST create objects *BEFORE* you try to use them!

I know that lots of beginners leave in old code just so they don't have to
retype it if they change their minds. I think that's a mistake. Instead,
keep copies of your files (I make copies with the copy dates in their names.
Example: I'll copy "test.asp" to "test.20080828.asp") and then really and
truly get rid of the deleted code. Now it's easier to see what code is
*ACTUALLY* in there!
Aug 28 '08 #10

P: n/a
As you said the changes were made but I cannot Understand the querry Sir,
which was disseminated by you. This style I am not Sure: please bear with me
for being ignorant.

If salary <"" Then
SQL = SQL & " AND [1SalaryDB] " & salary
End If

The error is on that now:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression '1SalaryDB'.
/GoodASPDBsearch/Searchengineresults.asp, line 32

1SalaryDB is the Field name Sir, and the Table name is: Employees.

Thank you for your patience.

--
Message posted via WebmasterKB.com
http://www.webmasterkb.com/Uwe/Forums.aspx/asp/200808/1

Aug 30 '08 #11

P: n/a


"iahamed via WebmasterKB.com" wrote:
As you said the changes were made but I cannot Understand the querry Sir,
which was disseminated by you. This style I am not Sure: please bear with me
for being ignorant.

If salary <"" Then
SQL = SQL & " AND [1SalaryDB] " & salary
End If

The error is on that now:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression '1SalaryDB'.
/GoodASPDBsearch/Searchengineresults.asp, line 32

1SalaryDB is the Field name Sir, and the Table name is: Employees.
It's time to debug.

*IN PLACE OF* the line
Set rsglobal = aspdbweb.Execute( SQL )
in your code, put in these two lines (just temporarily!)
Response.Write "DEBUG SQL: " & SQL
Response.End
and then show me what that DEBUG output shows you.

But going back and forth like this in the forum is way too slow. Why don't
you just email me the results???

Also, email me both the FORM page and this form processing page, just so I
can make sure I didn't mislead you.

You can send them to me at either the address this group gives for me or at
asp ATSIGN juncojunction PERIOD com
(that is, where ATSIGN is @ and PERIOD is . )

Look for a response from "bill wilkinson"
Aug 30 '08 #12

P: n/a
Sir thank you so much when add that code it did propound the values as pure
write and no error! O!
However When run the app it encounters the issue.

Sir I did mail to asp ATSIGN juncojunction PERIOD com thank you so much for
the help. And may your generosity full fill your needs in all ways.

Thank you So much Old Pedant.

Thank you.

--
Message posted via WebmasterKB.com
http://www.webmasterkb.com/Uwe/Forums.aspx/asp/200808/1

Aug 31 '08 #13

This discussion thread is closed

Replies have been disabled for this discussion.