need help with a form

Results 1 to 4 of 4

Thread: need help with a form

  1. #1
    kaspar Guest

    Default need help with a form

    Im building a client database site and want to make a form that searches the database on several variables,using listboxes. <BR><BR>The variables consist of values taken from a table. Lets say i want to search using two variables.<BR><BR>1. Sitefase -&#062; 5 choices in a listbox taken from a table.<BR>2. Region -&#062; 10 choices taken from a table.<BR><BR>Using &#039If... then&#039 twice allows me to display clients that are in sitefase 2 and in region 3. But I want to add a choice that enables you to choose &#039all&#039. So you can display all clients in region 3.. and disregard the sitefase.<BR><BR>How do I define the &#039all&#039 choice so that the script will understand that if &#039all&#039 is chosen for sitefase, disregard the "if rs("sitefase") = value then" part.<BR><BR>Or even better is there a way to define &#039all&#039 on the process page so that the statement if rs("sitefase") = "" would read all.<BR>Thanks for your help.<BR><BR>kaspar<BR><BR><BR> <BR>

  2. #2
    Join Date
    Dec 1969

    Default Not a bad question...

    ...but really not advanced at all.<BR><BR>Oh, well, at least 50% of the questions here aren&#039t advanced, so why not...<BR><BR>Just do this on the FORM page:<BR><BR>***************<BR>&#060;FORM ...&#062;<BR>&#060;SELECT Name="Sitefase"&#062;<BR>&#060;OPTION Value=""&#062;All sites<BR>&#060;OPTION&#062;site one<BR>&#060;OPTION&#062;site two<BR>...<BR>&#060;/SELECT&#062;<BR><BR>&#060;SELECT Name="regions"&#062;<BR>&#060;OPTION Value=""&#062;All regions<BR>...<BR>&#060;/SELECT&#062;<BR>*****************<BR><BR>Then on the next page (the ASP/DB processing page):<BR><BR>&#060;%<BR>site = Request("siteFase")<BR>region = Request("Regions")<BR><BR>SQL = "SELECT * FROM table"<BR>Delimiter = " WHERE "<BR>If Len(site) &#060;&#062; 0 Then<BR> &nbsp; SQL = SQL & Delimiter & "sitefase = &#039" & site & "&#039"<BR> &nbsp; Delimiter = " AND "<BR>End If<BR>If Len(region) &#060;&#062; 0 Then<BR> &nbsp; SQL = SQL & Delimiter & "region = &#039" & region & "&#039"<BR> &nbsp; Delimiter = " AND "<BR>End If<BR>... other similar fields, if any ...<BR><BR>Set RS = yourConnection.Execute( SQL )<BR>%&#062;<BR><BR>Do you see it? If the user chooses the "ALL" selection, then you will get the value "" on the ASP page, and you use that as a signal to *NOT* include that field as part of the search criteria.<BR><BR>The trick with "Delimiter" is just a cute way of putting WHERE in front of the first criterion, *no matter which one it is* and the word AND in front of any subsequent criteria.<BR><BR>Hokay?<BR><BR>You could just as easily use some special value (maybe Value="*") and test for that instead of the zero-length value. Your choice.<BR><BR><BR><BR>

  3. #3
    Eddie Campbell Guest

    Default RE: Not a bad question...

    Just one thing to add. If you are building your listboxes from a datatable, you could ensure that record 1 is always ***** All Records ***** then you can test for an ID of 1 instead of the zero-length value.

  4. #4
    Join Date
    Dec 1969

    Default Cute idea, minor mods... assumes that you are building the &#060;SELECT&#062; from a separate table dedicated to that purpose. If you are simply doing a SELECT DISTINCT... from a dynamically maintained table, that probably won&#039t work.<BR><BR>However, it is a clever trick and I will remember it. Two modifications I would make: <BR><BR>(A) Use zero (0) for the "ID" (whatever becomes the VALUE= in the &#060;OPTION...&#062;, that is) instead of 1. I like checking for zero vs. non-zero instead of checking for a specific value. I might even use minus one (-1) instead...and check for &#060; 0 as meaning invalid. If the ID field needs to be non-numeric (e.g., U.S. state abbreviation?), then I&#039d go back to using "". Why not? You can have "" as a value in a DB table.<BR><BR>(B) You might want to put a SPACE character in the front of that "dummy" value, so that it will sort first if you do an<BR>ORDER BY in your SELECT to generate the list. (Using an asterisk works well if all your other values start with alphabetic characters, but if any start with a number then the asterisk will sort after them.)<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts