Searching a ASP database

[donald]

Hi all, I have a website running asp (about to move to asp.net soon though)
which has a list of DVD's I have the various pages I want, last 10, listing,
full listing ect, but the one page i can't figure out is a search page.

I have read various books, but none have given any tips on how to make a
search page for a access DB.

Could someone point me to a tutorial?

Thanks
Donald

 

[Thomas Gullen]

Hi Donald,

Searching a database is always done in SQL. To acheive a search on an
access database you would need to:

1) Create a search form
2) When the search is processed in the ASP code build up the SQL query
using operators such as LIKE, OR and AND.
3) Execute the query and return all the results.

So, you might for example have one textbox named 'searchtext'. Someone
might enter 'pulp fiction'. In the processing page you would do:

sqlvar = "SELECT * FROM tableFilms WHERE name LIKE '%" &
Request.form("searchtext") & "%'"

This would produce:

SELECT * FROM tableFilms WHERE name LIKE '%Pulp Fiction%'

Then, execute sqlvar on your database. This would return all records
where the name contains 'pulp fiction'. Check w3schools tutorials on
the LIKE comparison.

You can add more fields to your search form, like dates and one by one
process the search fields slowly building up the SQL query before execution.

Hope this helped,

Tom

 

[mark | r]

Hi Donald,

Searching a database is always done in SQL. To acheive a search on an
access database you would need to:

1) Create a search form
2) When the search is processed in the ASP code build up the SQL query
using operators such as LIKE, OR and AND.
3) Execute the query and return all the results.

So, you might for example have one textbox named 'searchtext'. Someone
might enter 'pulp fiction'. In the processing page you would do:

sqlvar = "SELECT * FROM tableFilms WHERE name LIKE '%" &
Request.form("searchtext") & "%'"

This would produce:

SELECT * FROM tableFilms WHERE name LIKE '%Pulp Fiction%'

Then, execute sqlvar on your database. This would return all records
where the name contains 'pulp fiction'. Check w3schools tutorials on
the LIKE comparison.

You can add more fields to your search form, like dates and one by one
process the search fields slowly building up the SQL query before execution.

Hope this helped,

Tom

Tom. do you have any tips for rating the results on the fly and showing the
most relevant results first?

mark

 

[Jeff Cochran]

[Answers Inline]

Tom. do you have any tips for rating the results on the fly and showing the
most relevant results first?

Rating systems are tough because it depends on what you (or your user)
rates as higher than something else. For example, in a search of a
name, you're searching for:

Tom Jones

Define which of these is closer:

Thomas Jones
Tom Jonas
Tony Jones

You may say "Thomas Jones" and it's obvious to you. But "Tom Jonas"
has more matching letters, has more in the right places, is closer
alphabetically and wins all around. Tony Jones is actually closer
since it has a "To" and not a "Th" to start.

Might look at third party search products.

Jeff