This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C1A46F.6393CBC0

Content-Type: text/plain



I know this can be done but I cannot accomplish this since I do not code

enough on a daily basis in VB to figure the last step out nor have I seen

any example of this problem of coding on any web sites.

 

Goal: On a web page that is accessing an access db I want to populate a

dropdown list with distinct values from category1; then after they select

the major category it does another query that populates another dropdown box

with distinct values from category2 using category1 as the selection

criteria, then after selecting their category2 item it does another query

then selects all the distinct values that match category1 and catgory2 and

then and shows in the third dropdown category selection box all the possible

selections for the items that matched category1 and category2.

 

Right now I just have the three dropdowns doing distinct queries which list

all the possible distinct values in each category. This can result in a

query where there is no possible match is returned. I want to ensure that

they can always get a valid selection result set. I also want to allow them

to select on just any 1 category and wildcard the other two.

 

I want to have it post back onto itself after each selection.

 

url to site where it is:

<http://www.taphilo.com/cgi-scripts/photosearch.asp>

http://www.taphilo.com/cgi-scripts/photosearch.asp to see how it works

currently in production.

 

This is what I have so far:

 

<%

Session.timeout = 2

if Request("REQUESTTYPE") <> "POST" then

  dim dbtable

 dim dbfield

  dim dbfield2

    dim dbfield3

 dim dbwhere

 dim dbwhere2

 dim dbwhere3

 'dbtable = Photographs

 'dbfield = Category

 'dbfield2 = subCategory

 'dbfield3 = detailedCategory

 dbtable = Session("dbtable")

  dbfield = Session("dbfield")

  dbfield2 = Session("dbfield2")

     dbfield3 = Session("dbfield3")

  dbwhere = Session("dbwhere")

  dbwhere2 = Session("dbwhere2")

  dbwhere3 = Session("dbwhere3")

 

%>

   <form ACTION=photosearch.asp method=POST>

   <table>

   <input type=HIDDEN NAME=table value="Photographs">

   <input type=HIDDEN NAME=field value="Category">

   <input type=HIDDEN NAME=field2 value="SubCategory">

   <input type=HIDDEN NAME=field3 value="DetailedCategory">

<tr><td><b>Category:</b></td>

    <td><select id="Category" name="where" size="1">

<%

'declare our variables

Dim conn,sql,rs

'connect to db and open subcategory query

Set conn=Server.CreateObject("ADODB.Connection")

conn.open "DSN=photo"

sql="SELECT distinct Category from Photographs order by Category asc"

Set rs=conn.execute(sql)

   Do While Not rs.eof

         Response.Write "<option>" & rs("Category") & "</option>"

         rs.movenext

       Loop

   conn.close

   Set conn=Nothing

   Set rs=Nothing

%>

    </select>

   </td></tr>

<tr><td><b>Sub Category:</b></td>

 

    <td><select id="SubCategory" name="where2" size="3">

<%

Response.Write "<option>" & "" & "</option> selected"

'connect to db and open subcategory query

Set conn=Server.CreateObject("ADODB.Connection")

conn.open "DSN=photo"

sql="SELECT distinct SubCategory from Photographs order by SubCategory asc"

Set rs=conn.execute(sql)

   Do While Not rs.eof

         Response.Write "<option>" & rs("SubCategory") & "</option>"

         rs.movenext

       Loop

   conn.close

   Set conn=Nothing

   Set rs=Nothing

%>

</select>

</td></tr>

   <tr><td><b>Detailed Category:</b></td>

    <td><select id="DetailedCategory" name="where3" size="3">

<%

Response.Write "<option>" & "" & "</option> selected"

'connect to db and open detailedcategory query

Set conn=Server.CreateObject("ADODB.Connection")

conn.open "DSN=photo"

sql="SELECT distinct DetailedCategory from Photographs order by

DetailedCategory asc"

Set rs=conn.execute(sql)

   Do While Not rs.eof

         Response.Write "<option>" & rs("DetailedCategory") & "</option>"

         rs.movenext

       Loop

   conn.close

   Set conn=Nothing

   Set rs=Nothing

%>

</select>

 

      </td></tr>

     </table>

     <input type=HIDDEN NAME=REQUESTTYPE value="POST">

     <input type=Submit value="Query Database">

     <hr />

     </form>

   <%

 

   else

'query database

      Session("dsn") = photo

      dbtable = Request("table")

      Session("dbtable") = dbtable

 

      dbfield = Request("field")

      Session("dbfield") = dbfield

      dbfield2 = Request("field2")

      Session("dbfield2") = dbfield2

      dbfield3 = Request("field3")

      Session("dbfield3") = dbfield3

 

      dbwhere = Request("where")

      Session("dbwhere") = dbwhere

      dbwhere2 = Request("where2")

      Session("dbwhere2") = dbwhere2

      dbwhere3 = Request("where3")

      Session("dbwhere3") = dbwhere3

 

     Set Conn = Server.CreateObject("ADODB.Connection")

         Set rs = Server.CreateObject("ADODB.RecordSet")

   conn.Open "dsn=photo"

 

         sql="SELECT * FROM " & dbtable

         sql = sql & " WHERE " & dbfield

         sql = sql & " LIKE '%" & dbwhere & "%'"

 

'   Response.Write "<b><FONT SIZE=2 COLOR=BLUE>SQL STATEMENT: </b>" & sql &

"<hr />"

'   Response.Write dbwhere2

'   Response.Write dbfield2

   IF dbwhere2 > "" then

   sql = sql & " AND " & dbfield2

         sql = sql & " LIKE '%" & dbwhere2 & "%'"

   End IF

 

'   Response.Write "<b><FONT SIZE=2 COLOR=BLUE>SQL STATEMENT: </b>" & sql &

"<hr />"

 

   IF dbwhere3 > "" then

   sql = sql & " AND " & dbfield3

         sql = sql & " LIKE '%" & dbwhere3 & "%'"

   End IF

 

   '      % For Debugging, Echo the SQL Statement

  '      Response.Write "<b><FONT SIZE=2 COLOR=BLUE>SQL STATEMENT: </b>" &

sql & "<hr />"

 

          RS.Open sql, Conn

         %>

 

         <p>

         <table BORDER=1>

            <tr>

            <%

 

   '         % Loop through Fields Names and print out the Field Names First

field is always field number 0

'            For i = 0 to RS.Fields.Count - 1 skip index field number 0

            For i = 1 to RS.Fields.Count - 1

            %>

            <td><b><% = RS(i).Name %></b></td>

            <% Next %>

            </tr>

            <%

 

   '         % Loop through rows, displaying each field

            Do While Not RS.EOF

            %>

            <tr>

            <% For i = 1 to RS.Fields.Count - 1 %>

            <td VALIGN=TOP><% = RS(i) %></td>

            <% if i = 13 then %>

              <td VALIGN=TOP><img src="/db/<% = RS(6)%>/<% = RS(7) %>/<% 

RS(12) %>"></td>

            <% end if %>

            <% Next %>

            </tr>

            <%

            RS.MoveNext

            Loop

   '         % Make sure to close the Result Set and the Connection object

            RS.Close

            Conn.Close

            %>

         </table>

 

         <%

 '   Else

  '       end if

end if

   %>

<!-- End Close Data Connection Tag -->

 



 

I came across this code some time ago.  It uses two dropdown lists based 

on the PUBS SqlServer database, dynamically generating the second based on 

selection from the first.  It might be of help:



_______________________________________________________________________



DynamicSelect.asp file contents:



<%@ Language=VBScript %>

<% Option Explicit %>

<!-- #include file=adovbs.inc -->

<!-- #include file=DynamicSelect.inc -->

<%

  DIM Conn, rsPrimary, rsSecondary, intFirstPub

  

  ' Put your connection information here.

  Set Conn = Server.CreateObject("ADODB.Connection")

  Conn.ConnectionString = "PROVIDER=SQLOLEDB" & _

    ";SERVER=variety;UID=sa;PWD=;DATABASE=Pubs"



  Conn.Open

  SET rsPrimary    = Server.CreateObject("ADODB.RecordSet")

  SET rsSecondary  = Server.CreateObject("ADODB.RecordSet")

%>

<HTML>

<HEAD>

</HEAD>



<BODY>

<%

rsPrimary.Open "SELECT pub_id, pub_name FROM publishers ORDER BY 

pub_name", Conn

IF Err.number > 0 THEN

  Response.Write "Unable to open Primary list" & "<BR>"

  Response.Write Err.description

END IF



IF NOT (rsPrimary.BOF AND rsPrimary.EOF) THEN

	intFirstPub = rsPrimary("pub_id")

ELSE

	intFirstPub = 0

END IF



rsSecondary.Open "SELECT pub_id, title_id, title FROM titles ORDER BY 

pub_id, title", Conn 

IF Err.number > 0 THEN

  Response.Write "Unable to open Secondary list" & "<BR>"

  Response.Write Err.description

END IF



FillArray "arrTitles", rsSecondary, "pub_id", "title_id", "title" 

%>

<CENTER>

<H2>Dynamic Select List</H2>

<FORM>

<TABLE WIDTH=100%>

<TR>

  <TD>Primary List:<br><%SelectBox 

rsPrimary, "lstPublishers", "pub_id", "pub_name", "lstTitles", "arrTitles" 

%></TD>

  <TD>Secondary List:<br>

      <%

      rsSecondary.Filter = "pub_id = '" & intFirstPub & "'"

      SelectBox rsSecondary, "lstTitles", "title_id", "title", "", " " 

      rsSecondary.Filter = adFilterNone

      %>

  </TD>

</TR>

</TABLE>

</FORM>

</CENTER>

</BODY>

</HTML>



_________________________________________________________________________

DynamicSelect.inc file contents:





<SCRIPT  RUNAT="server" LANGUAGE="VBScript">

'Updated April 18, 2000



SUB SelectBox(rsOptions, strName, strValue, strDisplay, strSecondary, 

strArray)

   DIM strSelect

   strSelect = vbCRLF & "<SELECT name=" & chr(34) & strName & chr(34) & _

  	" id="  & chr(34) & strName & chr(34) & " size=1 "

   

   IF strSecondary <> "" and strArray <> "" THEN

	  strSelect = strSelect & " OnChange=" & chr(34) & "ChangeOptions

('" & _

	  strName & "', '" & strSecondary & "', '" & strArray & "')" & chr

(34) & ">" & vbCRLF

   ELSE

      strSelect = strSelect & ">" & vbCRLF

   END IF

   Response.Write strSelect

   

   Do Until rsOptions.EOF

	Response.Write "<OPTION value=" & chr(34) & rsOptions.Fields

(strValue) & chr(34) & ">"

	Response.Write Trim(rsOptions.Fields(strDisplay)) & "</OPTION>" & 

vbCRLF

	rsOptions.MoveNext

  LOOP

  rsOptions.MoveFirst

  Response.Write "</SELECT>"

END SUB



'Populate arrOptions as a two dimensional array.

'[n][0] =value, [n][1] =display

SUB FillArray(strArrName, rsSource, strKey, strValue, strDisplay)



'Debugging code

'For Each el in rsSource.Fields

'	Response.Write "<BR>" + CStr(el) + " = " + el.Name 

'Next

'Response.Write "<BR>strKey: " + strKey + vbCRLF

'Response.Write "<BR>strValue: " + strValue + vbCRLF

'Response.Write "<BR>strDisplay: " + strDisplay + vbCRLF



  Response.Write "<BR><SC" & "RIPT LANGUAGE= ""JavaScript"">" & _

	           "var  " & strArrName & " = new Array(); " & vbCRLF

  DIM intRow

  intRow = 0

  DO UNTIL rsSource.EOF

    Response.Write strArrName & "[" & CStr(intRow) & _

                     "] = new Array ('" & _

			   SingleQuote(Trim(rsSource (strKey))) & _

                     "', '" & rsSource(strValue) & _

                     "', '" & SingleQuote(Trim(rsSource(strDisplay))) & _

   "');" & vbCRLF



    intRow = intRow + 1

    rsSource.MoveNext

  Loop

  Response.Write "  </SC" & "RIPT>"



END SUB



FUNCTION SingleQuote(strTarget)

	DIM intPos

    '"Escapes" embedded single quote in a text string.

    intPos = InStr(1, strTarget, "'")

    DO WHILE intPos > 0 

      strTarget = Left(strTarget, intPos - 1) & "\'" & _

                  Right(strTarget, Len(strTarget) - intPos)

      

      'Bump up TWO places because the original single quote has moved.

      intPos = InStr(intPos + 2, strTarget, "'")

    LOOP

    

    SingleQuote = strTarget

END FUNCTION

</script>





<script LANGUAGE="Javascript">

function ChangeOptions(lstPrimary, lstSecondary, strArray) 

{

  var alen    = eval(strArray + ".length")

  var listLen = 0;

  var strKey  = eval("document.forms[0]." + lstPrimary + ".options

[document.forms[0]." + lstPrimary + ".selectedIndex].value")

  eval("document.forms[0]." + lstSecondary + ".options.length = 0");



  for (var i = 0; i < alen; i++) 

  {

   if (eval(strArray + "[i][0] == " + strKey))

    {

    eval("document.forms[0]." + lstSecondary + ".options[listLen] = new 

Option(" + strArray + "[i][2], " + strArray + "[i][1])");

    listLen = listLen + 1;

    }

  }



  if (listLen > 0)

  {eval("document.forms[0]." + lstSecondary + ".options[0].selected = 

true");}

  

  //alert ("document.all is " + document.all);

  //alert ("document.layers is " + document.layers);

  if (document.all == null)  //Not using Internet Explorer

    {history.go(0);}  

}



</script>

Thanks, I'll see if I can incorporate this technique into what I already 

have.