HELP-selecting from list-box passing to sql query

Results 1 to 2 of 2

Thread: HELP-selecting from list-box passing to sql query

  1. #1
    Join Date
    Dec 1969
    Posts
    569

    Default HELP-selecting from list-box passing to sql query

    Right now my form displays 10 iterations of a drop-down box that is populated by a query - each box has same options. I need some help with either design or code or both. <BR> The way I see my form is ... multiple drop-down boxes with the same options - but I would like a choice to be made before they can proceed to another selection. Once a choice is selected I need to know which choice they made and feed that to another query that gets the price for the choice made. Once the first choice and price are put on screen, then they can goto list-box 2 and make another choice if needed. <BR> Can you help with code???

  2. #2
    Join Date
    Dec 1969
    Posts
    9

    Default RE: HELP-selecting from list-box passing to sql query

    Brian, here is a code example that I just whipped up for you. I used Notepad to write it, and Personal Web Server/WIN98/Access 97 to test it. I will also give you my web address so that you can download a zip file of the ASP files and Access Database. To test you need to set up and ODBC Driver pointing to this Access database and just make sure that the name you give the ODBC driver matches the name in the ASP .con string (currently it is called ListBoxDB so preferrably name it that). This code basically builds the first box, based on the choice made on that submits and displays the second box, and based on the choice made there will submit again and display a third box. Feel free to post with any questions. You can find source and database on my website at www.imaginasean.com/ASPExamples/199901/source.zip I&#039ll have the working example there as soon as my ISP wakes up and changes a setting for me. That link will be www.imaginasean.com/ASPExamples/199901/default.asp<BR><BR>Here is the source for the ASP file:<BR><BR>&#060;html><BR>&#060;head><BR>&#060;t itle>&#060;/title><BR>&#060;/head><BR>&#060;SCRIPT LANGUAGE="JavaScript"><BR>function ChangeProduct()<BR>{<BR> document.ClothingForm.MODE.value = &#039CHANGEDPRODUCT&#039;<BR> document.ClothingForm.submit();<BR>}<BR>function ChangeSize()<BR>{<BR> document.ClothingForm.MODE.value = &#039CHANGEDSIZE&#039;<BR> document.ClothingForm.submit();<BR>}<BR>function ChangeColor()<BR>{<BR> // Might resubmit so that a confirm button would be added to the form <BR>}<BR>&#060;/SCRIPT><BR>&#060;body><BR>&#060;% Dim con<BR> Dim strSQL<BR> Dim rsProducts<BR> Dim rsSizes<BR> Dim rsColors<BR><BR> if Request.Form("MODE")="" then <BR> <BR> Set con = Server.CreateObject("ADODB.Connection")<BR> Set rsProducts = Server.CreateObject("ADODB.Recordset")<BR><BR> con.Open "ListBoxDB"<BR> strSQL = "SELECT * FROM Products"<BR><BR> rsProducts.Open strSQL, con &#037;&#062;<BR>&#060;form method="POST" action="default.asp" name="ClothingForm"><BR>&#060;input type="hidden" name="MODE" value=""><BR> Product: &#060;select name="lstProduct" size="1" onChange="ChangeProduct();"><BR> &#060;% Do until rsProducts.EOF &#037;&#062;<BR> &#060;option value="&#060;%=rsProducts("ProductID")&#037;&#062; ">&#060;%=rsProducts("Description")&#037;&#062;&#0 60;/option><BR> &#060;% rsProducts.MoveNext<BR> Loop <BR> rsProducts.close<BR> con.close<BR> set rsProducts = nothing<BR> set con = nothing &#037;&#062;<BR> &#060;/select><BR>&#060;/form><BR>&#060;% end if &#037;&#062;<BR>&#060;% if Request.Form("MODE")="CHANGEDPRODUCT" then &#037;&#062;<BR>&#060;% <BR> Set con = Server.CreateObject("ADODB.Connection")<BR> Set rsProducts = Server.CreateObject("ADODB.Recordset")<BR> Set rsSizes = Server.CreateObject("ADODB.Recordset")<BR><BR> con.Open "ListBoxDB"<BR> strSQL = "SELECT * FROM Products"<BR><BR> rsProducts.Open strSQL, con &#037;&#062;<BR>&#060;form method="POST" action="default.asp" name="ClothingForm"><BR>&#060;input type="hidden" name="MODE" value=""><BR> Product: &#060;select name="lstProduct" size="1" onChange="ChangeProduct();"><BR> &#060;% Do until rsProducts.EOF &#037;&#062;<BR> &#060;option &#060;% if Trim(Request.Form("lstProduct"))=Trim(rsProducts(" ProductID")) then &#037;&#062; selected &#060;% end if &#037;&#062; value="&#060;%=rsProducts("ProductID")&#037;&#062; ">&#060;%=rsProducts("Description")&#037;&#062;&#0 60;/option><BR> &#060;% if Trim(Request.Form("lstProduct"))=Trim(rsProducts(" ProductID")) then<BR> strPrice = CStr(rsProducts("Cost"))<BR> end if<BR> rsProducts.MoveNext<BR> Loop <BR> strSQL = "SELECT * FROM ProductSizes WHERE ProductID=" & Trim(Request.Form("lstProduct"))<BR> rsSizes.Open strSQL, con<BR> &#037;&#062;<BR> &#060;/select>&#060;font color=#FF0000> $&#060;%=strPrice&#037;&#062;&#060;/FONT><BR><BR> Sizes: &#060;select name="lstSizes" size="1" onChange="ChangeSize();"><BR> &#060;% Do until rsSizes.EOF &#037;&#062;<BR> &#060;option value="&#060;%=rsSizes("Size")&#037;&#062;">&#060; %=rsSizes("Size")&#037;&#062;&#060;/option><BR> &#060;% rsSizes.MoveNext<BR> Loop <BR> rsSizes.close<BR> rsProducts.close<BR> con.close<BR> set rsProducts = nothing<BR> set rsSizes = nothing<BR> set con = nothing &#037;&#062;<BR> &#060;/select><BR>&#060;/form><BR>&#060;% end if &#037;&#062;<BR>&#060;% if Request.Form("MODE")="CHANGEDSIZE" then &#037;&#062;<BR>&#060;% <BR> Set con = Server.CreateObject("ADODB.Connection")<BR> Set rsProducts = Server.CreateObject("ADODB.Recordset")<BR> Set rsSizes = Server.CreateObject("ADODB.Recordset")<BR> Set rsColors = Server.CreateObject("ADODB.Recordset")<BR><BR> con.Open "ListBoxDB"<BR> strSQL = "SELECT * FROM Products"<BR><BR> rsProducts.Open strSQL, con &#037;&#062;<BR>&#060;form method="POST" action="default.asp" name="ClothingForm"><BR>&#060;input type="hidden" name="MODE" value=""><BR> Product: &#060;select name="lstProduct" size="1" onChange="ChangeProduct();"><BR> &#060;% Do until rsProducts.EOF &#037;&#062;<BR> &#060;option &#060;% if Trim(Request.Form("lstProduct"))=Trim(rsProducts(" ProductID")) then &#037;&#062; selected &#060;% end if &#037;&#062; value="&#060;%=rsProducts("ProductID")&#037;&#062; ">&#060;%=rsProducts("Description")&#037;&#062;&#0 60;/option><BR> &#060;% if Trim(Request.Form("lstProduct"))=Trim(rsProducts(" ProductID")) then<BR> strPrice = CStr(rsProducts("Cost"))<BR> end if<BR> rsProducts.MoveNext<BR> Loop <BR> strSQL = "SELECT * FROM ProductSizes WHERE ProductID=" & Trim(Request.Form("lstProduct"))<BR> rsSizes.Open strSQL, con<BR> &#037;&#062;<BR> &#060;/select>&#060;font color=#FF0000> $&#060;%=strPrice&#037;&#062;&#060;/FONT><BR><BR> Sizes: &#060;select name="lstSizes" size="1" onChange="ChangeSize();"><BR> &#060;% Do until rsSizes.EOF &#037;&#062;<BR> &#060;option &#060;% if Trim(Request.Form("lstProduct"))=Trim(rsSizes("Pro ductID")) then &#037;&#062; selected &#060;% end if &#037;&#062; value="&#060;%=rsSizes("Size")&#037;&#062;">&#060; %=rsSizes("Size")&#037;&#062;&#060;/option><BR> &#060;% if Trim(Request.Form("lstProduct"))=Trim(rsSizes("Pro ductID")) then<BR> strSize = CStr(rsSizes("Size"))<BR> end if<BR> rsSizes.MoveNext<BR> Loop <BR><BR> strSQL = "SELECT * FROM ProductColors WHERE ProductID=" & Trim(Request.Form("lstProduct"))<BR> rsColors.Open strSQL, con<BR> &#037;&#062;<BR> &#060;/select>&#060;font color=#FF0000> &#060;%=strSize&#037;&#062;&#060;/FONT><BR><BR> Colors: &#060;select name="lstColors" size="1" onChange="ChangeColor();"><BR> &#060;% Do until rsColors.EOF &#037;&#062;<BR> &#060;option value="&#060;%=rsColors("Color")&#037;&#062;">&#06 0;%=rsColors("Color")&#037;&#062;&#060;/option><BR> &#060;% rsColors.MoveNext<BR> Loop <BR><BR> rsColors.close<BR> rsSizes.close<BR> rsProducts.close<BR> con.close<BR> set rsColors = nothing<BR> set rsProducts = nothing<BR> set rsSizes = nothing<BR> set con = nothing &#037;&#062;<BR> &#060;/select><BR>&#060;/form><BR>&#060;% end if &#037;&#062;<BR><BR>&#060;/body><BR>&#060;/html><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
  •