PDA

View Full Version : can anyone help me with my search engine?

sweetmess
14-07-2004, 04:52/04:52AM
Hi all, I've got problems with my search engine here. When i press the 'Search' button, nothing happens. It just remains on the same page, no change, nothing. Is there something wrong with my post method or is it my asp coding?? Could someone help me check out the problem and fix it? Cause i'm really lost now...

Here's the coding>>

I've also attached the database...

Cert.asp
-----------
<%@ Language="VBScript" %>
<% Option Explicit %>

<%
Dim conn, connStr
Set conn = Server.Createobject("ADODB.Connection")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("database.mdb")
conn.open connStr
%>

<html>
<head>
<title>Search by Certificate No.</title>
</head>

<body>
<center>
<font face="verdana">
<table width="100%" action="Cert.asp" method="GET">
<tr>
<th colspan=2 bgcolor="CCCCCC">
<h3>Search by Cert No.</h3>
</th>
</tr>

<tr>
<td colspan=2 align="center"><br><br>
Enter Certificate No.: <input type="text" size="30" name="CertNo">
</td>
</tr>

<tr>
<td align="center"><br><br>
<input type="submit" value="Search">
</td>
</tr>

<%
If Request.QueryString("CertNo") <> "" then
Dim SearchRs, sql, cert

cert = Request.QueryString("CertNo")
sql = "SELECT * FROM Taiwan WHERE CertNumber=cert"
Set SearchRs = conn.Execute(sql)

if SearchRs.EOF then
response.write("No result is being found...")
else
%>
<tr>
<td>Domain<\td>
<td>Certificate No.<\td>
<td>RMN<\td>
<td>Part No.<\td>
<td>Product No.<\td>
<td>Product Type<\td>
<\tr>

<tr>
<td><%=SearchRs("Domain")%><\td>
<td><%=SearchRs("CertNumber")%><\td>
<td><%=SearchRs("RMN")%><\td>
<td><%=SearchRs("PartNo")%><\td>
<td><%=SearchRs("ProductNo")%><\td>
<td><%=SearchRs("ProductType")%><\td>
<\tr>
<%
end if
End if
%>
</table>
</font>
</center>
</body>
</html>

<%
conn.Close
%>
carlgm
14-07-2004, 05:02/05:02AM
Just wondering where your <form> and </form> fields are? :)
chrishirst
14-07-2004, 05:14/05:14AM
also you need to close your RS, then destroy both the RS and Conn objects.

when you do put your <form> tags in depending whether you are using the "post" or "get" method, set your request correctly.

use request.querystring("name") for GET and request.form("name") for POST.

for reference ASP and DB questions should be here, ASP Forum (http://www.ihelpyouservices.com/forums/forumdisplay.php?s=&forumid=87) or here, Languages and Database (http://www.ihelpyouservices.com/forums/forumdisplay.php?s=&forumid=103)
sweetmess
14-07-2004, 05:23/05:23AM
ok yes, i've entered the <form> tags. now i get directed to the error page. it says that there's a problem at line 45>>

Error Type:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
/Adlina/Cert.asp, line 45

No value given? What does that mean? How to i fix this problem??
chrishirst
14-07-2004, 05:26/05:26AM
and line 45 would be ?
chrishirst
14-07-2004, 05:47/05:47AM
ok found it

sql = "SELECT * FROM Taiwan WHERE CertNumber=cert"
should be

sql = "SELECT * FROM Taiwan WHERE CertNumber= " & cert & " ;"


debug tip

response.write out the results of variable concantenation and compare them with what you would expect.

Oh and all your cell end tags "</TD>" have the wrong slash.
sweetmess
14-07-2004, 20:50/08:50PM
oh yeah... the slash. haha... silly me.. Thanks for pointing that out! Alright, i'll try it out and see how it goes..!
sweetmess
15-07-2004, 01:05/01:05AM
Alright, now it works! Thanks!! :D Ok, so let's say if i wanna change it to search by any keyword instead? My friend told me to change this line>>

sql = "SELECT * FROM Taiwan WHERE CertNumber=Request.QueryString("CertNo")"

He mentioned something like the use of wildcard? How does that go about?
chrishirst
15-07-2004, 03:16/03:16AM
Something you should NEVER do is use the querystring parameters in a SQL query. You are leaving yourself wide open to a SQL injection attack, ALWAYS ALWAYS convert to a variable and at the very least run a typecast operation.

a wildcard in SQL is '%' and you would use the LIKE operator

your query would be constructed as;

"SELECT fieldlist FROM table WHERE fieldname LIKE '%" & srchPattern & "%' ;"


this means a srchPattern of "log"

would match with

"blog" "log" "logging" or "blogging"

one thing to note, the use of * for a field list does cause extra server load, mostly uneccessary. it is much better to use a list of fields that you need in the recordset.
sweetmess
15-07-2004, 22:20/10:20PM
Alright, i've tried it. It does the searching but i think there's some stuff that's still not right... This was what i did:

<%
If Request.QueryString("keyword") <> "" then
Dim SearchRs, sql, word

word=Request.QueryString("keyword")
sql = "SELECT * FROM Taiwan WHERE Producttype LIKE '%" & word & "%' ;"
Set SearchRs = conn.Execute(sql)

if SearchRs.EOF then
response.write("No result is being found...")
else
%>

And when i type 'desktops' to search, only the 1st result is shown. Do i like have to include some looping so that the other results can also be shown? If i do, then where do i put it?
JuniorHarris
16-07-2004, 01:22/01:22AM
If you are serious about designing a quality search that is going to be scalable and perform well, then *stop* what you are doing right now!

IMO, I recommend that you spend a bit more time researching and understanding the technologies in developing a search engine. Designing it as you go, is probably not the best design method. Instead do a bit of research, formulate an approach, and I'm sure you'll have the project done in half the time. I think you'll also find several different methods as well as coding examples on the various message boards, which can help add insight and direction.

I would suggest not using embedded SQL and use stored procedures. This will provide a bit of data independence from the ASP application and the database. So if you ever change a table layout, or even normalize it into two tables, all one has to do is modify the stored proc on the server and the ASP code never has to be modified! ;) In addition the query plan (on MSSQL) for the stored proc is cached, and improves performance.

And to answer your last question, yes you probably need some method of looping through the records. But then it again it really depends on your end goals....will all the search results be displayed on one page, or will it be multiple pages...this will determine the type(s) of ADO recordset calls necessary, rs.getvars, rs.movenext, etc...
sweetmess
16-07-2004, 02:27/02:27AM
u know what. i have no idea what you're explaining except for the 1st paragraph. haha.. sorry, bu i'm really bad at these coding stuff. I just want one that can search. it doesnt have to be really good. as long as it works enough, then it's good enough... : /
chrishirst
16-07-2004, 07:22/07:22AM
Ok valid points in JH's post, however scalability is probably not a criteria right now but good design should be. what you have right now is ok for a "playing" setup but is unsuitable for any "live" operation.

ASP101 have database sample (http://www.asp101.com/samples/) code for running various operations which should help you get the basics.
Tek-Tips (http://www.tek-tips.com) has many FAQs and threads in the asp forum covering all aspects of database queries and ASP.

for a simple loop of your retrieved records you could use


with response
if (RS.EOF or RS.BOF) then
.write "No matching records"
.write "<br>" & vbCrLf
else
do while not RS.EOF
.write RS.fields("fieldname")
.write "<br>" & vbCRLf
RS.MoveNext
loop
end if
end with



obviously I've not put in any formatting code to make the output pretty.
sweetmess
19-07-2004, 20:58/08:58PM
Which part of my coding do i put this loop?? and what is 'fieldname' referring to?
polarmate
19-07-2004, 21:11/09:11PM
I would suggest not using embedded SQL and use stored procedures. :thumb: Words of wisdom!!
sweetmess
19-07-2004, 22:50/10:50PM
So how in the world do i use stored procedures? I seriously tried reading up and looking for some asp samples, but i still don't get it. I'm sorry, but i'm just really bad at this stuff and i'm left with a week to finish my search engine... :(
polarmate
19-07-2004, 23:51/11:51PM
Is this for a school project or something?
sweetmess
20-07-2004, 03:17/03:17AM
It's for work... It's my attachment. So i'm told to do this... Now i'm stuck and i gotta show it to my supervisor next wk. : / As long as i can show the way it works, then it's fine. Cause i doubt they'd be using it for real...
chrishirst
20-07-2004, 03:36/03:36AM
stored procedures are precompiled queries or SQL statements saved in the database (hence the name) ASP Emporium on SPs (http://www.aspemporium.com/aspEmporium/tutorials/StoredProcedures.asp).
Provided they are written correctly and your database supports SPs it is the most efficient way to access the data. Think of them as similar to SQL Functions.
Most mainstream SQL database servers do support SPs (I hesitate to include Access as mainstream but it does support SPs) and MySQL has added SP support in the latest version (MySQL 5.0)

in my code example "fieldname" is simply a place holder (pseudo-code), to be replaced with the actual name of the field you are displaying. Should you need to display more fields, simply add more ' .write RS.fields("fieldname")' lines in the loop and include appropriate formatting between them.


HTH.
JuniorHarris
20-07-2004, 09:06/09:06AM
Having only a week hardly seems enough time for any project...

Maybe I'm confused here, are you creating a search engine or simply adding a search to an existing site?
sweetmess
22-07-2004, 03:02/03:02AM
I'm actually adding this whole new search function into a site. Guess what...? My search by keyword works now. But the thing is, i want it to be able to search different fields from the database and not just the 'Producttype'...

<%
If Request.QueryString("keyword") <> "" then
Dim SearchRs, sql, word

word=Request.QueryString("keyword")
sql = "SELECT * FROM Taiwan WHERE Producttype LIKE '%" & word & "%' ;"
Set SearchRs = conn.Execute(sql)

if SearchRs.EOF then
response.write("'<center>No result is being found...</center>'")
else
do until SearchRs.EOF
%>

where can i modify it so that i can search under more fieldnames rather than just the 'Producttype'...?
chrishirst
22-07-2004, 05:48/05:48AM
sql = "SELECT * FROM Taiwan WHERE ( Producttype LIKE '%" & word & "%' OR fieldname LIKE '%" & anotherword & "%') ;"



I do suggest you start some serious reading up on SQL before going much further maybe start here (http://www.w3schools.com/sql/sql_and_or.asp).
sweetmess
26-07-2004, 23:12/11:12PM
i would have to declare the 'anotherword' right? then do i have to include this statement first? >>
anotherword=Request.QueryString("keyword")
chrishirst
27-07-2004, 05:09/05:09AM
"anotherword" is there simply as a place holder.

It may be a different variable or search word or equally it could be the same one.

It's called pseudo-code.
Not writing the exact piece of code but placing indicators where you, as the programmer, put your own variables, constants or data.