Correct on the missing End If for each IF, and you should start w/ a Dim for any/all variables
- my code was partial
You started this thread posting your Select code for the SQL statement
- where were you putting that?
- obviously you need more than just the Select and the If code I posted in a VBscript for an ASP page
Post a snippet of your original code to access the DB
| Stefan,
|
| Sorry, but I can't see how and where to put the VBscript code. At the top of
| the page, no! Doesn't work. I get a white page with "Page cannot be shown"
| error message. If I put the code inside the Webbot area just before SELECT it
| is immediately overwritten when the page is saved.
|
| I also wonder, shouldn't there be a "Dim strWhere"? and an "End if" at the
| end?
|
| I have programmed a lot in lower level languages, but VBscript is totally
| new to me.
|
| Best,
|
| Tomas
|
| "Stefan B Rusynko" skrev:
|
| > Yes it is VBscript (ASP)
| > <%
| > 'The code here
| > %>
| >
| > Insert it before your SELECT statement which was also "ASP" code
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > To find the best Newsgroup for FrontPage support see:
| >
http://www.net-sites.com/sitebuilder/newsgroups.asp
| > _____________________________________________
| >
| >
| > | Stefan,
| > |
| > | I assume this is ASP code, but how do I insert it on an FP generated page? I
| > | have tried adding "<%" and "%>" around the code and putting at the top of the
| > | page, but that doesn't work. I hardly have any experiemce with ASP, so please
| > | be specific.
| > |
| > | Best,
| > |
| > | Tomas
| > |
| > | "Stefan B Rusynko" skrev:
| > |
| > | > Build your query based on the form entry values being blank (>0) or not
| > | > - below presumes your form field names are same as your DB field names and you are searching for all fields w/ an entry as
ANDs
| > | >
| > | > strWhere = ""
| > | > If Len Request.QueryString("user_name") > 0 Then
| > | > strWhere = "user_name LIKE '%::" Request.QueryString("user_name") & "::%'"
| > | > Else
| > | > strWhere = strWhere ' Above for First Field Only
| > | > If Len Request.QueryString("country") > 0 Then
| > | > strWhere = strWhere & " AND country LIKE '%::" Request.QueryString("country") & "::%'"
| > | > Else
| > | > strWhere = strWhere ' Above for All Other Fields
| > | >
| > | > And so on for each field, then:
| > | >
| > | > SELECT * FROM Results WHERE strWhere
| > | >
| > | >
| > | > --
| > | >
| > | > _____________________________________________
| > | > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > | > "Warning - Using the F1 Key will not break anything!" (-;
| > | > To find the best Newsgroup for FrontPage support see:
| > | >
http://www.net-sites.com/sitebuilder/newsgroups.asp
| > | > _____________________________________________
| > | >
| > | >
| > | > | A follow up question: Can you test on both the entry in a search field and a
| > | > | value in in a database table? You see, I tried this and it didn't produce the
| > | > | desired results:
| > | > |
| > | > | "SELECT * FROM Results WHERE ((user_name LIKE '%::user_name::%' OR
| > | > | (user_name IS NULL AND '%::user_name::%' IS NULL) AND..."
| > | > |
| > | > | What I want to achieve is to match user_name and '%::user_name::%', even
| > | > | when both have no entries (NULL). I get no errors, but when entering nothing
| > | > | in the user_name field I don't get the records containing NULL.
| > | > |
| > | > | Best,
| > | > |
| > | > | Tomas
| > | > |
| > | > | "Tomas" skrev:
| > | > |
| > | > | > I am not sure I see how. There are 40 fields and maybe 15 of them have
| > | > | > dropdown boxes where I can ensure that there is some entry. The other 25 are
| > | > | > either text fields or memo fields.
| > | > | >
| > | > | > If I enter nothing in any of the search fields I want all records to be
| > | > | > returned, but if I enter something in even only one field I don't want to
| > | > | > receive all records that contain NULL in every field. Instead I want to
| > | > | > rceive those records that match the one field I have entered something for
| > | > | > and where all other fields are "dont care". Since there are so many
| > | > | > combinations of these 25 fields I get the felling that it is not 2 queries I
| > | > | > have to write but 25*24*23...!!
| > | > | >
| > | > | > If I enter user_name = George as a search criteria then I only want records
| > | > | > with George as user_name and not those records that contain NULL in this
| > | > | > field. But then there is country, printer_brand, printer_model and a host of
| > | > | > others that can contain a valid entry or NULL and you should be able to
| > | > | > search on any or all at the same time.
| > | > | >
| > | > | > Best,
| > | > | >
| > | > | > Tomas
| > | > | >
| > | > | > "Jens Peter Karlsen[FP MVP]" skrev:
| > | > | >
| > | > | > > You would need two different searches. Check if the value of user_name
| > | > | > > is empty to determine if you need to search for NULLs or not.
| > | > | > >
| > | > | > > Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.
| > | > | > >
| > | > | > > > -----Original Message-----
| > | > | > > > From: Tomas [mailto:
[email protected]]
| > | > | > > > Posted At: 12. december 2004 12:53
| > | > | > > > Posted To: microsoft.public.frontpage.programming
| > | > | > > > Conversation: How to handle NULL fields resulting from a user
| > | > | > > > entering no data?
| > | > | > > > Subject: How to handle NULL fields resulting from a user
| > | > | > > > entering no data?
| > | > | > > >
| > | > | > > >
| > | > | > > > I have an input form with 40 fields where I can't expect the
| > | > | > > > user to enter something in all of them.
| > | > | > > >
| > | > | > > > When searching this database I get problems with the NULL
| > | > | > > > values. I got help in this forum and wrote an SQL statement
| > | > | > > > like this: "SELECT * FROM Results WHERE ((user_name LIKE
| > | > | > > > '%::user_name::%' OR user_name IS NULL) AND..." It does work,
| > | > | > > > but returns all records with NULL in a field even if I search
| > | > | > > > for a specific word. If I don't have the "OR user_name IS
| > | > | > > > NULL" I get nothing if only one of the 40 fields is NULL and
| > | > | > > > I don't enter a search word that exists in that particular
| > | > | > > > field, which is very usaul. So, without the "OR..." I get no
| > | > | > > > records at all. All fields in the search form are set to "%".
| > | > | > > >
| > | > | > > > What I want to achieve is that the "%" should treat all
| > | > | > > > entries, including NULL, the same way.
| > | > | > > >
| > | > | > > > How do I best handle this?:
| > | > | > > >
| > | > | > > > 1. Could I use javascript to check all form fields before
| > | > | > > > submitting the data and replace any NULL with e.g. "-"? How
| > | > | > > > would that script look like?
| > | > | > > > Would it have any negative consequencies?
| > | > | > > > 2. Is there a way to design the query so that NULL is treated
| > | > | > > > in the same way as any other entry?
| > | > | > > >
| > | > | > > > Best,
| > | > | > > >
| > | > | > > > Tomas
| > | > | > > >
| > | > | > >
| > | >
| > | >
| > | >
| >
| >
| >
