Database Search Results Are Incorrect

[Melissa]

I have been trying for quite some time to put together .asp database search and results pages (using the front page wizard) and have been unsuccessful. I am currently using front page 2003 (though I've tried this with both 98 and 00) and the database is connected properly and is in excel format. (i've also tried this using an access database) I have tried many different variations of search criteria (starts with, equals, contains, ect...) but my results do not return correctly in any instance. If I set the criteria to "contains" then I get the entire database as my result. I am not very saavy with code, but am willing to try anything to make this work. I'm very frustrated and can't figure out what I'm doing wrong. Here's a link to my attempt at this search feature
http://www.caassistedliving.org/Templates/Assoc.asp. Please excuse the look of this site. I am working on a complete re-vamp
please please please help
Thanks.

 

[Kathleen Anderson [MVP - FP]]

Hi Melissa:
This KB article should help you with the queries: SQL Syntax Generated by
the FrontPage Database Results Wizard
http://support.microsoft.com/default.aspx?scid=kb;[LN];306430

Also, I would recommend sticking with Access instead of Excel. It's not so
problematic.

 

[Kevin Spencer]

If you are using a wildcard query (e.g. company LIKE '::company::*'), which
has multiple conditions which you OR together, you may be getting all
records because of the combination of wildcard conditions, blank form
fields, and the ORs which you use to string them together. For example, in
the WHERE clause I posted above, if the form field was empty, the
translation would be "company like '*'" which returns all records. Now
combine that with another field that DOES have a value, but the conditions
are ORed together:

SELECT blah from blah WHERE
company LIKE '*' OR FullName LIKE 'Mary*'

Notice that the first condition allows ALL records, while the second
condition filters the result set. However, the OR means that any record
satisfying EITHER condition is a match. The above query would return ALL
records.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

I have been trying for quite some time to put together .asp database

search and results pages (using the front page wizard) and have been
unsuccessful. I am currently using front page 2003 (though I've tried this
with both 98 and 00) and the database is connected properly and is in excel
format. (i've also tried this using an access database) I have tried many
different variations of search criteria (starts with, equals, contains,
ect...) but my results do not return correctly in any instance. If I set the
criteria to "contains" then I get the entire database as my result. I am not
very saavy with code, but am willing to try anything to make this work. I'm
very frustrated and can't figure out what I'm doing wrong. Here's a link to
my attempt at this search feature:

http://www.caassistedliving.org/Templates/Assoc.asp. Please excuse the

look of this site. I am working on a complete re-vamp.

 

[Guest]

Thanks for all of your feedback! The site link with query
explinations was helpful. However, I'm not getting the
right results even if my query is exact. If I
say "equals" and put in exact criteria that I know
matches a record in my data I get no results returned.
Any other ideas as to what I could be doing wrong?

Karen - I am pulling data from a database management
program that is not part of the microsoft word suite. I
am able to export to excel but not access. This morning I
attempted to export my excel data into access but when I
upload it to my site the data is not recognized in the
database wizard. Any ideas?

Thanks so much for your help! I'm desperate.

 

[Kevin Spencer]

It sounds like you're using a wizard to build your query. You will need a
custom query to get all the logic right. Again, the issue here is the logic
in your WHERE clause. You have 4 or 5 form fields, any of which could be
blank. If any of them has a value in it, you don't want to use any of the
blank ones, as they will return all records, and, as I mentioned before,
when using multiple conditions connected by OR, ANY of the conditions will
satisfy the criteria. So, you have to include logic in your query, so that
blank fields are ignored when there are form fields with values in them.

To explain it in pseudo-code:

SELECT All columns in All records WHERE
(Your Company form field is blank
OR The Company Column in your table contains the value in your Company form
field)
AND
(Your ID form field is blank
OR The ID Column in your table contains the value in your IDform field)
AND
(Your FullName form field is blank
OR The FullName Column in your table contains the value in your FullName
form field)
AND
(Your mbrcategor form field is blank
OR The mbrcategor Column in your table contains the value in your mbrcategor
form field)

Explanation: This combines multiple ORed conditions that are ANDed together.
AND means that BOTH conditions (or ALL in your case) meet the criteria. OR
means that EITHER (or ANY in your case) meet the criteria. There are 4 ORed
Conditions that are ANDed together. In each of the ORed conditions, EITHER
of the 2 conditions makes a mtach, but ALL of the four ORed conditions must
be true for any given record.

The end result is that any empty form field returns TRUE for any given
record, but if more than one form field has a value in it, BOTH (or ALL) of
the form fields that have a value must satisfy their respective criteria in
a given record in order to get a match.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Guest]

Thank you so much Kevin. That makes perfect sense. My
only problem now is creating a custom query that works.
Any idea where I can get help with this? I am somewhat
code-challenged.

 

[Kevin Spencer]

Well, Melissa, I wish I could, but I have NEVER used Excel as a data source,
and I'm not at all familiar with Excel's "flavor" of SQL. In addition, this
is a FP DRW query, and I'm not a DRW user, so I wouldn't know the syntax
offhand for constructing the query. Hopefully, someone else here knows Excel
syntax and DRW syntax, and can take it from here!

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.