Solved

Display info based on dropdown menu selection

Posted on 2013-06-18
8
307 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 33

Expert Comment

by:paulmacd
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Then the only thing we need to worry about is the 2nd page.  What is the code for that page.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Author Comment

by:AdrienneSperber
Comment Utility
   
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
Comment Utility
In your, Sub GetInventorybyColorID(Product_id,Color_id,objconn) you are not using Color_id anywhere.
0
 
LVL 9

Author Comment

by:AdrienneSperber
Comment Utility
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
Comment Utility
i got it working, this is what helped me get it working so i chose it as the answer, thanks!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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 demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
In this tutorial viewers will learn how to embed Flash content in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <object> tag to embed Flash content.: To specify that the object is Flash content, d…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

763 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

10 Experts available now in Live!

Get 1:1 Help Now