Solved

Display info based on dropdown menu selection

Posted on 2013-06-18
8
316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Session on Html 8 42
html form layout 4 36
JQuery on multiple lines 3 16
Trying to understand why my Index is so large 12 18
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 style transparent/translucent elements using alpha transparency in CSS Start with a normal styled element, such as a div.: Define its "background-color" property as "rgba (255, 255, 255, .5): The numbers in…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

726 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