S
Stefan B Rusynko
There is a general problem w/ Nulls in DB
You may find this of interest
http://www.aspfaq.com/show.asp?id=2073
- recommendation is to avoid nulls if possible
| Yepp!!
|
| I only tried it on the last offending field - "media_type" and that returned
| 4 records.
|
| Thanks, Kathleen!
|
| Question remains, though, shouldn't "%" return even empty fields? And, is
| there a risk that this hefty SQL statement will take a lot of processor power
| to execute?
|
| Anyway, time to hit the sack again.
|
| God night!
|
| Tomas
|
| "Kathleen Anderson [MVP - FrontPage]" skrev:
|
| > Try adding some "or" conditions
| >
| > SELECT
| > * FROM Resultat WHERE ((user_name LIKE '%::user_name::%' or user_name IS
| > NULL) AND (country LIKE
| > '%::country::%' or country IS NULL) etc.
| >
| >
| >
| > --
| > ~ Kathleen Anderson
| > Microsoft MVP - FrontPage
| > Spider Web Woman Designs
| > web: http://www.spiderwebwoman.com/resources/
| > blog: http://msmvps.com/spiderwebwoman/category/321.aspx
| >
| >
| >
| > > The SQL for this page: http://www.en.colourfidelity.com/search.asp
| > > is: SELECT * FROM Resultat WHERE (user_name LIKE '%::user_name::%'
| > > AND country LIKE '%::country::%' AND printer_brand LIKE
| > > '%:
rinter_brand::%' AND printer_model LIKE '%:rinter_model::%'
| > > AND paper_brand LIKE '%:aper_brand::%' AND paper_stock LIKE
| > > '%:aper_stock::%' AND use_icc_profile LIKE '%::use_icc_profile::%'
| > > AND ink_brand LIKE '%::ink_brand::%' AND ink_type LIKE
| > > '%::ink_type::%')
| > >
| > > The SQL for this page: http://www.en.colourfidelity.com/search3.asp
| > > is: SELECT * FROM Resultat WHERE (user_name LIKE '%::user_name::%'
| > > AND country LIKE '%::country::%' AND printer_brand LIKE
| > > '%:rinter_brand::%' AND printer_model LIKE '%:rinter_model::%'
| > > AND paper_brand LIKE '%:aper_brand::%' AND paper_stock LIKE
| > > '%:aper_stock::%' AND use_icc_profile LIKE '%::use_icc_profile::%'
| > > AND ink_brand LIKE '%::ink_brand::%' AND ink_type LIKE
| > > '%::ink_type::%' AND media_type LIKE '%::media_type::%').
| > >
| > > See, as soon as I enter the "media_type" field I lose one record
| > > because "media_type" is null for that record.
| > >
| > > If I add the "other_settings" field I will get no records returned,
| > > but this field is also null for all records.
| > >
| > > I set all defaults to "%".
| > >
| > > Tomas
| > >
| > > "Kathleen Anderson [MVP - FrontPage]" skrev:
| > >
| > >> 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
| >
| >
You may find this of interest
http://www.aspfaq.com/show.asp?id=2073
- recommendation is to avoid nulls if possible
| Yepp!!
|
| I only tried it on the last offending field - "media_type" and that returned
| 4 records.
|
| Thanks, Kathleen!
|
| Question remains, though, shouldn't "%" return even empty fields? And, is
| there a risk that this hefty SQL statement will take a lot of processor power
| to execute?
|
| Anyway, time to hit the sack again.
|
| God night!
|
| Tomas
|
| "Kathleen Anderson [MVP - FrontPage]" skrev:
|
| > Try adding some "or" conditions
| >
| > SELECT
| > * FROM Resultat WHERE ((user_name LIKE '%::user_name::%' or user_name IS
| > NULL) AND (country LIKE
| > '%::country::%' or country IS NULL) etc.
| >
| >
| >
| > --
| > ~ Kathleen Anderson
| > Microsoft MVP - FrontPage
| > Spider Web Woman Designs
| > web: http://www.spiderwebwoman.com/resources/
| > blog: http://msmvps.com/spiderwebwoman/category/321.aspx
| >
| >
| >
| > > The SQL for this page: http://www.en.colourfidelity.com/search.asp
| > > is: SELECT * FROM Resultat WHERE (user_name LIKE '%::user_name::%'
| > > AND country LIKE '%::country::%' AND printer_brand LIKE
| > > '%:
rinter_brand::%' AND printer_model LIKE '%:rinter_model::%'| > > AND paper_brand LIKE '%:
aper_brand::%' AND paper_stock LIKE| > > '%:
aper_stock::%' AND use_icc_profile LIKE '%::use_icc_profile::%'| > > AND ink_brand LIKE '%::ink_brand::%' AND ink_type LIKE
| > > '%::ink_type::%')
| > >
| > > The SQL for this page: http://www.en.colourfidelity.com/search3.asp
| > > is: SELECT * FROM Resultat WHERE (user_name LIKE '%::user_name::%'
| > > AND country LIKE '%::country::%' AND printer_brand LIKE
| > > '%:
rinter_brand::%' AND printer_model LIKE '%:rinter_model::%'| > > AND paper_brand LIKE '%:
aper_brand::%' AND paper_stock LIKE| > > '%:
aper_stock::%' AND use_icc_profile LIKE '%::use_icc_profile::%'| > > AND ink_brand LIKE '%::ink_brand::%' AND ink_type LIKE
| > > '%::ink_type::%' AND media_type LIKE '%::media_type::%').
| > >
| > > See, as soon as I enter the "media_type" field I lose one record
| > > because "media_type" is null for that record.
| > >
| > > If I add the "other_settings" field I will get no records returned,
| > > but this field is also null for all records.
| > >
| > > I set all defaults to "%".
| > >
| > > Tomas
| > >
| > > "Kathleen Anderson [MVP - FrontPage]" skrev:
| > >
| > >> 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
| >
| >