What does your SQL statement look like?
--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web:
http://www.spiderwebwoman.com/resources/
blog:
http://msmvps.com/spiderwebwoman/category/321.aspx
Kathleen,
I found an interesting thing today when being a bit more fresh and
being very systematic about it. You know I had this other problem
with not being able to search with more than 4-5 fields, and when
using more I got no records returned.
So, I started with a few fields. Fine! Added a few more. Fine! Added
a few more. All had the wildcard "%" as default. This last time I
only got 3 records returned instead of the 4 test records I have
created. Hmm???
I took a look at all the records and the one that was not returned
had nothing written to that last column in the table. The next
column was blank in all records, and when I added that field to the
search I got no records returned at all!!
It is now pretty clear that if a field in the record is empty it
will prevent the record from being returned. I just assumed that the
wildcard "%" meant anything, even an emty field.
What would you have to enter to allow even empty fields?
Best,
Tomas
"Kathleen Anderson [MVP - FrontPage]" skrev:
Inline.
--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web:
http://www.spiderwebwoman.com/resources/
blog:
http://msmvps.com/spiderwebwoman/category/321.aspx
Kathleen,
I don't believe I did anything wrong. As I mentioned in my earlier
post, I have tried with and without a search form. The current
page was just left after the last trial. The search field for the
ColumnSort parameter is something tha FP adds automatically to the
search from if you add the parameter in the SQL statement (can the
field be removed without affecting the parameter?).
Step 15 of the tutorial says: In Step 5 of the wizard, click
Display all Records Together, and clear the Add search form check
box.
I have quickly made a page that only has this in the SQL
expression: "SELECT * FROM Resultat ORDER BY ::ColumnSort::
ASC". It passes the verification. You can find it here:
http://www.en.colourfidelity.com/search3.asp. I did not have time
to add the clickable link, but as you can see there is an error
message immediately when opening this page. I have set the
default value of "ColumnSort" to "printer_brand".
Do you think the problems I see can come from the server not
running Window`?
Yes. You need to be runing on Windows server that supports ASP and
Access.
I believe they use freeBSD.
A follow up question. If you can't add a search form on the
results page to use the column sort feature, how can you search
the database?
You would search the databse first, and then implement the dynamic
sort of the results page (sort.asp).
Or is it only the parameter that shouldn't have a search field
(automatically added by FP)?
I need to hit the sack. It is 1:50 AM here and I must start work
8:00 tomorrow.
I really appreciate your help so far. I am not an expert in web
design, nor databases, but if you have questions about algorithm
development, especially for audio, or anything photo related, I
might be able to do a return favor.
Thank you. Feel free to contact me by email.
Good night,
Tomas
"Kathleen Anderson [MVP - FrontPage]" skrev:
OK. The SortColumn, as described in the tutorial, is not meant to
be used as a search field. If you follow the tutorial through to
the end, the dynamic sorting is done by the user when they click
on each column heading in the table. It works because you've made
each column heading a hyperlink. In your case, ID would be
clickable, and it would sort the table in ID order, user_name
would be clickable and clicking on it would sort the table in
user_name order, and so on.
--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web:
http://www.spiderwebwoman.com/resources/
blog:
http://msmvps.com/spiderwebwoman/category/321.aspx
Kthleen,
I made a second search page without the parameter and then you
can see all entries yourself. Here is the link:
http://www.en.colourfidelity.com/search2.asp.
Best,
Tomas
"Kathleen Anderson [MVP - FrontPage]" skrev:
Tomas:
What are some vaid values to enter in each of the search form
fields?
--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web:
http://www.spiderwebwoman.com/resources/
blog:
http://msmvps.com/spiderwebwoman/category/321.aspx
Kathleen,
I am doing it on my machine and then publishing, but when you
mentioned this you gave med the idea to try directly on the
server. Same results though.
Here is the link:
http://www.en.colourfidelity.com/search.asp.
Currently there is a two-field search from plus the field that
is added when using the parameter, but I have tried with and
without the search form. I am saying this if there should be a
difference when using a search form or not. But, as I
mentioned, I have not seen any difference.
What I am trying to achieve is to let the user search with any
of the 40 fields and then sort by column according to his/her
choice.
The corresponing entry form for entering new data into the
database is here:
http://www.en.colourfidelity.com/ppi_submit_main.asp.
Everything is currently in a stage of development, so this is
not the final form.
Best,
Tomas
"Kathleen Anderson [MVP - FrontPage]" skrev:
Tomas:
Are you doing this on your machine and then publishing or are
you doing this live on your web site?
What's the URL?
--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web:
http://www.spiderwebwoman.com/resources/
blog:
http://msmvps.com/spiderwebwoman/category/321.aspx
Kathleen,
I gave it another try. As soon as I add the "ORDER BY
SortColumn:: ASC" I get the error message "Databse reults
error. If this continues contact your server
administrator." I did not forget to set a default value for
"SortColumn".
I do exactly as it says in the article you gave me the link
to. Order by a certain column, like: "ORDER BY printer_brand
ASC" is fine.
Sorry about the sloppy wording. I have one form with 40
fields which then all are shown in one single table. I try
to make the query contain all 40 fields but no records are
returned. I get the message "No records returned". One
could think that I had forgotten to set the default value
for a field to "%" in case of a text field and "0" when the
query is >=, but I have checked and double checked.
Removing all the numeric fields doesn't help. It is only
when I cut down the number of text fields to about 5 that I
get records returned. That's why I wondered if there was a
limit to the number of fields in a query.
I start to get the nagging feeling that there could be
something wrong on the server side. I rent space in a b
hotel.
As an answer to your other question, when verifying SELECT *
FROM Categories WHERE (CategoryID >= ::CategoryID:
it
passes the verification. All queries that I have had
problems with passed the verification. If I take this query
to Access I get an error messag, bur adding the single
quotes solves it.
Best,
Tomas
"Kathleen Anderson [MVP - FrontPage]" skrev:
Kathleen,
I would really appreciate if you did that. I use Frontpage
XP (2002).
I just tried it and it worked. The SQL looks like this:
"SELECT * FROM Employees ORDER BY ::SortColumn:: ASC"
The links for the column headings look like this:
<th ALIGN="LEFT"><b><a
href="sort.asp?SortColumn=EmployeeID">
EmployeeID</a></b></th>
<th ALIGN="LEFT"><b><a
href="sort.asp?SortColumn=DepartmentName">
DepartmentName</a></b></th>
Why don't you give it another try?
Since you seem to be an expert in the area, could you
please see if you have an answer for these related
problems I run into regarding this database:
1. My database has 40 tables coming from an input form
with 40 fields.
40 tables? or 40 fields in one record in one table? or?
When I try to send a query with all 40, I get no answer.
There is no error message, just no result. I have used the
same query as in my last post, but with 40 entries in it.
I set the default values to %. If I cut down a few at a
time I suddenly get it to work. Is there a limit to how
many fields you can sen in in one single queary?
I don't know, but it sounds like a possibility. I
personally can't imagine wanting to fill out a query form
with 40 fields myself, though. You might try the query in
Access first to see if you can get it to work.
2. If you can't send as much as 40 fields at the same
time, can you parameterize the query so that you only use
5, but where you can set it up so that you first select
which 5 to include out of the 40?
Do you mean let the user decide whch fields they want to
use in the query? I don't know how you would do that.
3. I also have problems with fields that contain integers.
It might be related to the number of fields as in question
1; I haven't had time to test it thoroughly yet.. The
query for a filed looks like paper_brand >=
paper_brand::. When I bring this into Access it doesn't
like it at all. I
have to add single quotes like: paper_brand >=
':
aper_brand::'.
This page:
http://support.microsoft.com/default.aspx?scid=kb;[LN];306430
shold help you with the syntax.
SELECT * FROM Categories WHERE (CategoryID >=
::CategoryID:
is the example it gives for numeric
queries; you shouldn't need the quotes. What happens if
you verify the query under Custom Query in the wizard?
Kathleen
Best,
Tomas
"Kathleen Anderson [MVP - FrontPage]" skrev:
Tomas:
I used this process a few years ago and got it to work,
but I haven't tried it lately. I'll try and give it a
shot this weekend. What version of FrontPage are you
using?
--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web:
http://www.spiderwebwoman.com/resources/
blog:
http://msmvps.com/spiderwebwoman/category/321.aspx
The link you gave me sounded exactly what I wanted, but
I just can't make it work as described.
FP produces this SQL statement: SELECT * FROM Resultat
WHERE (printer_brand LIKE '%:
rinter_brand::%' AND
printer_model LIKE '%:
rinter_model::%') ORDER BY
ColumnSort:: ASC
The "::ColumnSort::" is the inserted parameter that I
set to a default value of one of my colums.
If I remove the "::ColumnSort::" and replace it with any
column name it works. i.e. it sorts by the selected
column.
Have you or anybody else in this group any idea why?
Best,
Tomas
"Kathleen Anderson [MVP - FrontPage]" skrev:
http://www.fwhn.com/support/frontpage.hta_7.htm
--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web:
http://www.spiderwebwoman.com/resources/
blog:
http://msmvps.com/spiderwebwoman/category/321.aspx
I have created a rather large input form where users
can add data in about 40 fields.
The resultant table becomes quite wide, but that's
another problem. What I really wnat to know if there
is a way to allow the user to click on a column to
sort it. If there were only a few colums I could
create one page per each column to sort by, but with
40 colums...
One thing I thought of was to add 40 radio buttons to
the top of the colums and write some SQL statement
that would look at which radio button that was
activated.
Is this possible? I would appreciate some help since I
am rather new to SQL.
Best,
Tomas
