Re: Please Help Advanse Search, two parts are working, the 3rd to
Old Pedant 31 August 2008 07:11:50
"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" />
iahamed via WebmasterKB.com 23 August 2008 09:37:20 [ permanent link ]
Hello Old Pedant,
Thank you Once again for the effort taken to Help. It seems to be a Error that generates then I try to test it.
My Database Table Name is "1SalaryDB"
Can u please tell me where to add the Salarycom (Drop down name) and the Value with One example BETWEEN 0 AND 10000 please. The error with the Below, your code is
Microsoft VBScript compilation (0x800A0401) Expected end of statement /GoodASPDBsearch/Searchengineresults.asp, line 24, column 23 SQL = SQL & " AND " & 1SalaryDB & " LIKE '%" & keyword & "%' "
iahamed via WebmasterKB.com 24 August 2008 08:46:59 [ permanent link ]
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.
Set aspdbweb = Server.CreateObject("ADODB.Connection") aspdbweb = "Provider=Microsoft.Jet.OLEDB.4.0; " _ & Data Source=" & Server.MapPath("employees.mdb") & ";"
' WHERE 1=1 is sneaky trick so we can use AND for other conditions SQL = "SELECT * FROM Employees WHERE 1=1 "
' whatToSearch is the name of the field the user asked to search on ' instead of using IF on that name, we just pick it up from the ' request and then use it to build the query: whatToSearch = Replace( Trim( Request("ASPDBCatSearch") ), "'", "''" ) keyword = Replace( Trim( Request("Keyword") ), "'", "''" )
' so if the user is *NOT* searching ALL, then we ' search the field he asked for looking for keyword given: If whatToSearch <> "All" Then SQL = SQL & " AND " & whatToSearch & " LIKE '%" & keyword & "%' " End If
' then salary is tricky, because the actual SQL where ' condition is the VALUE= of each <OPTION>. salary = Replace( Trim( Request("SalaryCom") ), "'", "''" ) ' so if any salary choice was made, we use it here: If salary <> "" Then SQL = SQL & " AND [1SalaryDB] " & salary End If
' if you don't use an ORDER BY, results will be ' in some random order. Don't HAVE to use ' ORDER BY, but why not?? SQL = SQL & " ORDER BY LastName, FirstName " ' or whatever order you want
' don't use a static cursor when you don't need it...simpler & faster: Set rsglobal = aspdbweb.Execute( SQL ) If rsglobal.EOF Then %> <h2 align="center">We did not find a match!</h2> <% Else %> <h2>Here are the results of your search:</h2>
Set aspdbweb = Server.CreateObject("ADODB.Connection") aspdbweb = "Provider=Microsoft.Jet.OLEDB.4.0; " _ & Data Source=" & Server.MapPath("employees.mdb") & ";"
' WHERE 1=1 is sneaky trick so we can use AND for other conditions SQL = "SELECT * FROM Employees WHERE 1=1 "
' whatToSearch is the name of the field the user asked to search on ' instead of using IF on that name, we just pick it up from the ' request and then use it to build the query: whatToSearch = Replace( Trim( Request("ASPDBCatSearch") ), "'", "''" ) keyword = Replace( Trim( Request("Keyword") ), "'", "''" )
' so if the user is *NOT* searching ALL, then we ' search the field he asked for looking for keyword given: If whatToSearch <> "All" Then SQL = SQL & " AND " & whatToSearch & " LIKE '%" & keyword & "%' " End If
' then salary is tricky, because the actual SQL where ' condition is the VALUE= of each <OPTION>. salary = Replace( Trim( Request("SalaryCom") ), "'", "''" ) ' so if any salary choice was made, we use it here: If salary <> "" Then SQL = SQL & " AND [1SalaryDB] " & salary End If
' if you don't use an ORDER BY, results will be ' in some random order. Don't HAVE to use ' ORDER BY, but why not?? SQL = SQL & " ORDER BY LastName, FirstName " ' or whatever order you want
' don't use a static cursor when you don't need it...simpler & faster: Set rsglobal = aspdbweb.Execute( SQL ) If rsglobal.EOF Then %> <h2 align="center">We did not find a match!</h2> <% Else %> <h2>Here are the results of your search:</h2>
If salary <> "" Then SQL = SQL & " AND [1SalaryDB] " & salary End If
SQL = SQL & " ORDER BY FirstName, LastName, 1SalaryDB, Title, Division, Phone, Email " ' or whatever order you want
'----
Set rsglobal = aspdbweb.Execute( SQL ) If rsglobal.EOF Then
'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%>
... rest same ...
Sir thank you so so much for the generosity in posting again and replying, I did the changes as you said, I do not know whether the Search will do a Value search cause its producing a error I cannot see the message.
Error Type: Microsoft VBScript runtime (0x800A01A8) Object required: 'aspdbweb' /GoodASPDBsearch/Searchengineresults.asp, line 37
Line no 37 is: Set rsglobal = aspdbweb.Execute( SQL )
However my requirement is to search all values "ASPDBCatSearch" (Drop down with its values to be searched), "Salarycom" (Drop down with its values to be searched), and the Input txt box named "keyword". Thats why Sir I used a If. I didnt know to connect to the 3rd Logic.
Please be kind enough to Response when you are free Sir.
Thank you so much for the Help and time taken to go through my troubling you Code.
If salary <> "" Then SQL = SQL & " AND [1SalaryDB] " & salary End If
SQL = SQL & " ORDER BY FirstName, LastName, 1SalaryDB, Title, Division, Phone,
Email " ' or whatever order you want
'----
Set rsglobal = aspdbweb.Execute( SQL ) If rsglobal.EOF Then
'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%>
... rest same ...
Sir thank you so so much for the generosity in posting again and replying, I did the changes as you said, I do not know whether the Search will do a Value search cause its producing a error I cannot see the message.
Error Type: Microsoft VBScript runtime (0x800A01A8) Object required: 'aspdbweb' /GoodASPDBsearch/Searchengineresults.asp, line 37
Line no 37 is: Set rsglobal = aspdbweb.Execute( SQL )
However my requirement is to search all values "ASPDBCatSearch" (Drop down with its values to be searched), "Salarycom" (Drop down with its values to be searched), and the Input txt box named "keyword". Thats why Sir I used a If. I didnt know to connect to the 3rd Logic.
Please be kind enough to Response when you are free Sir.
Thank you so much for the Help and time taken to go through my troubling you Code.
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=" &
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 salary <> "" Then SQL = SQL & " AND [1SalaryDB] " & salary End If
SQL = SQL & " ORDER BY FirstName, LastName, 1SalaryDB, Title, Division, Phone, Email " ' or whatever order you want
'----
Set rsglobal = aspdbweb.Execute( SQL ) If rsglobal.EOF Then
'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%>
' 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!
