• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Display info based on dropdown menu selection

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
AdrienneSperber
Asked:
AdrienneSperber
  • 4
  • 3
1 Solution
 
Paul MacDonaldDirector, Information SystemsCommented:
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
 
Scott Fell, EE MVEDeveloperCommented:
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
 
AdrienneSperberTech Support CoordinatorAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Scott Fell, EE MVEDeveloperCommented:
Then the only thing we need to worry about is the 2nd page.  What is the code for that page.
0
 
AdrienneSperberTech Support CoordinatorAuthor Commented:
   
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
 
Scott Fell, EE MVEDeveloperCommented:
In your, Sub GetInventorybyColorID(Product_id,Color_id,objconn) you are not using Color_id anywhere.
0
 
AdrienneSperberTech Support CoordinatorAuthor Commented:
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
 
AdrienneSperberTech Support CoordinatorAuthor Commented:
i got it working, this is what helped me get it working so i chose it as the answer, thanks!
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now