Solved

Display info based on dropdown menu selection

Posted on 2013-06-18
8
310 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article describes how to create custom column layout styles for Bootstrap. The article uses 5 columns to illustrate the concept, but the principle can be extended to any number of columns.
In this Micro Tutorial viewers will learn how to create navigation buttons that change on rollover, using CSS (Continuation of the CSS Image Sprite tutorial) Create a parent ID for all the list items       - Specify position: absolute and display: block…
In this tutorial viewers will learn how to embed videos in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <video> tag to insert a video. Define the src as the URL of your video; this is similar to …

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now