Solved

Display info based on dropdown menu selection

Posted on 2013-06-18
8
312 Views
Last Modified: 2013-06-20
Right now I have a sub procedure that displays all inventory for a specific item.  There is a dropdown where the customer can choose an item color.  I want to display the inventory based on this dropdown choice.  Right now the page reloads after you choose the color and it displays ALL inventory.  How do i change it to display only the inventory for the color chosen?


Sub GetInventorybyColorID(Product_id,Color_id,objconn)
    Dim objRS
    Dim objComm
    Dim Param
    Dim intrecordcount
    Dim stylecount
    set objComm = server.Createobject("ADODB.Command")
    set objRS = server.createobject("ADODB.Recordset")
    Set Param = server.CreateObject ("ADODB.Parameter")
    set objcomm.Activeconnection = objConn
    objcomm.CommandText = "GetInventorybyProductID"
    objcomm.CommandType = adCmdStoredProc
    Set Param = objComm.CreateParameter("@Product_id", adBigInt, adParamInput)
    objComm.Parameters.Append Param
    Param.Value = Product_id
    set objRS = objcomm.execute
   
    if objRS.eof then EXIT SUB
   
    intrecordcount = objRS.recordcount
    stylecount = 0
   
    response.Write "<tr><td><font size=""2pt"">Available Inventory: </font></td>"
   
    while not objRS.eof
   
    stylecount = stylecount + 1
   
    response.Write "<td>"& vbCRLF
   
    if Color_id <> ""  then 'test

    if isnull(objRS.fields("Color_value")) then
    else
        response.write ("<font size=""2pt"">"&objRS.fields("Color_value")&" - </font>")
    end if
    if isnull(objRS.fields("Size_value")) then
    else
        response.write ("<font size=""2pt"">"&objRS.fields("Size_value")&" - </font>")
    end if
    response.Write ("<font size=""2pt"">"&objRS.fields("Inventory")&"</font>")
   
    end if    'test

    if intrecordcount = stylecount then
        response.Write "</td></tr>"
    else
        response.Write "</td></tr><tr><td>"
    end if
   
    objRS.movenext
    wend
   
    response.Write "<tr><td colspan=""2""><hr color=""dfdfdf"" /></td></tr>"& vbCRLF
   
    Set ObjRS = Nothing
      'Set objConn = Nothing
      Set objComm.ActiveConnection = Nothing
      Set objComm = Nothing      
      Set Param = Nothing
end Sub
0
Comment
Question by:AdrienneSperber
  • 4
  • 3
8 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39257353
Does the dropdown list revert to a default value when the page refreshes?

Ultimately what you want is to check for IF IsPostBack in Page.Load and, if the page is posting back (vs loading for the first time), don't repopulate the dropdown list, and do query the database for the items of the selected color.
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 39257363
It looks like your pulling data from your SP.  You just need to adjust that to filter for color.

let's say your page refresh is by a get putting the color in the url.

<%
' assume you have a list of colors by id to keep things consistent.  You have a table of colors that has the id's and matched colors.
color=0
if request.querystring("color")<>"" then
   'lets make sure we have a number and not some potentially bad data
    if isnumeric(request.querystring("color")) then
         color=request.querystring("color")
    end if
end if

if color<>0 then
    sql="select * from products"
    else
    sql="select * from products where color='"&color&"'"
end if
%>

Open in new window

0
 
LVL 9

Author Comment

by:AdrienneSperber
ID: 39257568
This is my current SP..sorry very new at this, need all the help i can get!

@Product_id bigint
AS
declare @IsSize bigint
declare @IsColor bigint

select @IsSize = Size_id, @IsColor = Color_id from tbl_Invtry where Product_id = @Product_id

if @IsSize is null
      if @IsColor is null
            begin
            select Size_id, Color_id, Invtry, Size_value = null, Color_value = null
            from tbl_Invtry
            where Product_id = @Product_id
            end
      else
            begin
            select a.Size_id, a.Color_id, a.Invtry, Size_value = null, b.Color_value
            from tbl_Invtry a, tbl_Color b
            where a.Product_id = @Product_id
            and a.Color_id = b.Color_id
            end
else
      if @IsColor is null
            begin
            select a.Size_id, a.Color_id, a.Inventory, b.Size_value, Color_value = null
            from tbl_Invtry a, tbl_Sizes b
            where a.Product_id = @Product_id
            and a.Size_id = b.Size_id
            end
      else
            begin
            select a.Size_id, a.Color_id, a.Invtry, b.Size_value, c.Color_value
            from tbl_Invtry a, tbl_Sizes b, tbl_Color c
            where a.Product_id = @Product_id
            and a.Size_id = b.Size_id
            and a.Color_id = c.Color_id
            end



I also have a Request.query that looks for color_id in the url after a selection is made and redirects to a new page i made where i'd like to display the inventory which includes color size in most cases as well as amount left.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39257774
Then the only thing we need to worry about is the 2nd page.  What is the code for that page.
0
 
LVL 9

Author Comment

by:AdrienneSperber
ID: 39259382
   
function OnChange(dropdown,Product_id) 
   {
       var myindex = dropdown.selectedIndex
       var SelValue = dropdown.options[myindex].value;
       var baseURL = '/product_as.asp?color_id='+ SelValue + '&product_id=' + Product_id;
       top.location.href = baseURL;

       return true;
   } 'selvalue is the color id pulled from the database
</script>

<%
Dim Product_id
Product_id = request("Product_id")
Dim Color_id
Color_id = request("Color_id")
%>

<%
if Request.QueryString("color_id") > 0 then
response.Redirect "/inventorybycolor.asp?color_id="&Color_id&"&Product_id="&product_id
end 
 
call GetInventorybyColorID(Product_id,Color_id,objconn)

Call CheckProductColor(Product_id,objconn)

Sub GetInventorybyColorID(Product_id,Color_id,objconn)
    Dim objRS
    Dim objComm
    Dim Param
    Dim intrecordcount
    Dim stylecount
    set objComm = server.Createobject("ADODB.Command")
    set objRS = server.createobject("ADODB.Recordset")
    Set Param = server.CreateObject ("ADODB.Parameter")
    set objcomm.Activeconnection = objConn
    objcomm.CommandText = "GetInventorybyProductID"
    objcomm.CommandType = adCmdStoredProc
    Set Param = objComm.CreateParameter("@Product_id", adBigInt, adParamInput)
    objComm.Parameters.Append Param
    Param.Value = Product_id
    set objRS = objcomm.execute
    
    if objRS.eof then EXIT SUB
    
    intrecordcount = objRS.recordcount
    stylecount = 0
    
    response.Write "<tr><td><font size=""2pt"">Available Inventory: </font></td>"
    
    while not objRS.eof
    
    stylecount = stylecount + 1
    
    response.Write "<td>"& vbCRLF
    if isnull(objRS.fields("Color_value")) then
    else
        response.write ("<font size=""2pt"">"&objRS.fields("Color_value")&" - </font>")
    end if
    if isnull(objRS.fields("Size_value")) then
    else
        response.write ("<font size=""2pt"">"&objRS.fields("Size_value")&" - </font>")
    end if
    response.Write ("<font size=""2pt"">"&objRS.fields("Inventory")&"</font>")
    
    if intrecordcount = stylecount then
        response.Write "</td></tr>"
    else
        response.Write "</td></tr><tr><td>"
    end if
    
    objRS.movenext
    wend
    
    response.Write "<tr><td colspan=""2""><hr color=""dfdfdf"" /></td></tr>"& vbCRLF
    
    Set ObjRS = Nothing
	'Set objConn = Nothing
	Set objComm.ActiveConnection = Nothing
	Set objComm = Nothing	
	Set Param = Nothing
end Sub

Sub CheckProductColor(Product_id,objconn)
    Dim objRS
    Dim objComm
    Dim strConnectString 
    Dim Param
    set objComm = server.Createobject("ADODB.Command")
    set objRS = server.createobject("ADODB.Recordset")
    Set Param = server.CreateObject ("ADODB.Parameter")
    set objcomm.Activeconnection = objConn
    objcomm.CommandText = "CheckProductColor"
    objcomm.CommandType = adCmdStoredProc
    Set Param = objComm.CreateParameter("@Product_id", adBigInt, adParamInput)
    objComm.Parameters.Append Param
    Param.Value = Product_id
    set objRS = objcomm.execute
    
    if objRS.eof then EXIT SUB
    
    response.Write "<select name=""Color_id"" onchange=""OnChange(this.form.Color_id,"&Product_id&")"">"
    response.write "<option selected=""Select Color"">Select Color</option>"

    while not objRS.eof
    
    response.Write "<option value="""&clng(objRS.fields("Color_id"))&""">"&trim(objRS.fields("Color_value"))&"</option>"
    objRS.movenext
    wend
    
    response.Write "</select>"
    
    Set ObjRS = Nothing
	'Set objConn = Nothing
	Set objComm.ActiveConnection = Nothing
	Set objComm = Nothing	
	Set Param = Nothing
end Sub

%>

Open in new window

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39262192
In your, Sub GetInventorybyColorID(Product_id,Color_id,objconn) you are not using Color_id anywhere.
0
 
LVL 9

Author Comment

by:AdrienneSperber
ID: 39262670
OK i removed the code in the sub and added

 if request.QueryString("Color_id") <> "" then    'if there is a color selected show that colors inv
        response.Write "color inv"
    end if

Open in new window


so now I just need to pull my data and replace the response.write with it.

I'm at a loss on how to do that, do i need another function to accomplish this?
0
 
LVL 9

Author Closing Comment

by:AdrienneSperber
ID: 39263956
i got it working, this is what helped me get it working so i chose it as the answer, thanks!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
In this tutorial viewers will learn how to embed an audio file in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: : The declaration should display (CODE) HTML5 is supported by the most recent versions of all major browsers…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question