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.

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.


--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/


Melissa <(E-Mail Removed)> wrote:
> 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.

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.

"Melissa" <(E-Mail Removed)> wrote in message
news:2BFA6A17-F853-42B2-A9D9-(E-Mail Removed)...
> 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.

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.


>-----Original Message-----
>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.
>
>"Melissa" <(E-Mail Removed)> wrote in
message
>news:2BFA6A17-F853-42B2-A9D9-
(E-Mail Removed)...
>> 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.
>
>
>.
>

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.

<(E-Mail Removed)> wrote in message
news:2e4a01c4289d$908888a0$(E-Mail Removed)...
> 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.
>
>
> >-----Original Message-----
> >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.
> >
> >"Melissa" <(E-Mail Removed)> wrote in
> message
> >news:2BFA6A17-F853-42B2-A9D9-
> (E-Mail Removed)...
> >> 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.
> >
> >
> >.
> >

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.


>-----Original Message-----
>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.
>
><(E-Mail Removed)> wrote in message
>news:2e4a01c4289d$908888a0$(E-Mail Removed)...
>> 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.
>>
>>
>> >-----Original Message-----
>> >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.
>> >
>> >"Melissa" <(E-Mail Removed)> wrote
in
>> message
>> >news:2BFA6A17-F853-42B2-A9D9-
>> (E-Mail Removed)...
>> >> 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.
>> >
>> >
>> >.
>> >
>
>
>.
>

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.

<(E-Mail Removed)> wrote in message
news:2f0601c428ab$420cf2c0$(E-Mail Removed)...
> 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.
>
>
> >-----Original Message-----
> >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.
> >
> ><(E-Mail Removed)> wrote in message
> >news:2e4a01c4289d$908888a0$(E-Mail Removed)...
> >> 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.
> >>
> >>
> >> >-----Original Message-----
> >> >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.
> >> >
> >> >"Melissa" <(E-Mail Removed)> wrote
> in
> >> message
> >> >news:2BFA6A17-F853-42B2-A9D9-
> >> (E-Mail Removed)...
> >> >> 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.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >