?
Solved

ASP Vbscript Multi Select Listbox

Posted on 2011-09-12
11
Medium Priority
?
2,099 Views
Last Modified: 2012-05-12
Hello Experts, I have a multiple select listbox on an ASP Vbscript form. The page is the update page, so the listbox should load with all of the options, and the selected options already selected...

I've attempted to use the code below -

<%
Dim RsRelatedProductOptions__MMColParam
RsRelatedProductOptions__MMColParam = "0"
If (Request("ID") <> "") Then 
  RsRelatedProductOptions__MMColParam = Request("ID")
End If
%>
<%
Dim RsRelatedProductOptions
Dim RsRelatedProductOptions_cmd
Dim RsRelatedProductOptions_numRows

Set RsRelatedProductOptions_cmd = Server.CreateObject ("ADODB.Command")
RsRelatedProductOptions_cmd.ActiveConnection = MM_GolfConnection_STRING
RsRelatedProductOptions_cmd.CommandText = "SELECT ID, [Product-Name] FROM dbo.[Direct-Product] WHERE [Display-TGI-Direct] = 'Y' AND ID <> ? ORDER BY [Product-Name]" 
RsRelatedProductOptions_cmd.Prepared = true
RsRelatedProductOptions_cmd.Parameters.Append RsRelatedProductOptions_cmd.CreateParameter("param1", 5, 1, -1, RsRelatedProductOptions__MMColParam) ' adDouble

Set RsRelatedProductOptions = RsRelatedProductOptions_cmd.Execute
RsRelatedProductOptions_numRows = 0
%>
<%
Dim RsRelated__MMColParam
RsRelated__MMColParam = "0"
If (Request("ID") <> "") Then 
  RsRelated__MMColParam = Request("ID")
End If
%>
<%
Dim RsRelated
Dim RsRelated_cmd
Dim RsRelated_numRows

Set RsRelated_cmd = Server.CreateObject ("ADODB.Command")
RsRelated_cmd.ActiveConnection = MM_GolfConnection_STRING
RsRelated_cmd.CommandText = "Select  R.RelatedProductID as ID from dbo. [Direct-Related-Products] R  where R.ProductID = ?" 
RsRelated_cmd.Prepared = true
RsRelated_cmd.Parameters.Append RsRelated_cmd.CreateParameter("param1", 5, 1, -1, RsRelated__MMColParam) ' adDouble

Set RsRelated = RsRelated_cmd.Execute
RsRelated_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<select name="category" size="4" multiple="multiple" id="category">
  <%
While (NOT RsRelatedProductOptions.EOF)
%>
    <option value="<%=(RsRelatedProductOptions.Fields.Item("ID").Value)%>"
<%    
        do While NOT RsRelated.EOF

            If (Not isNull((RsRelated.Fields.Item("ID").Value))) Then
                If (CStr(RsRelatedProductOptions.Fields.Item("ID").Value) = CStr((RsRelated.Fields.Item("ID").Value))) Then
                    Response.Write(" selected=""selected"" ")
                    exit do
                end if
            End if        
     
        RsRelated.MoveNext()
        loop
 %>
    ><%=(RsRelatedProductOptions.Fields.Item("Product-Name").Value)%></option>
 <%
 
  RsRelatedProductOptions.MoveNext()
Wend
%>
</select>
</body>
</html>
<%
RsRelatedProductOptions.Close()
Set RsRelatedProductOptions = Nothing
%>
<%
RsRelated.Close()
Set RsRelated = Nothing
%>

Open in new window


But to no avail;

The page in question is here

For the ID value 39 --

this ;

Select  R.RelatedProductID as ID from dbo. [Direct-Related-Products] R  where R.ProductID = 39

Produces the following results -

ID
16
17
18
19

this; SELECT ID, [Product-Name] FROM dbo.[Direct-Product] WHERE [Display-TGI-Direct] = 'Y' AND ID <> 39 ORDER BY [Product-Name]

Produces the following results -

ID      Product-Name
40      final Test Hopefully &#163;&#163;$
16      Product1
17      Product2
18      Product3
19      Product4

So Products 1 - 4 should be highlighted, but are not...

Please can you advise as to what is wrong with my code....

Many thanks
0
Comment
Question by:garethtnash
[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
  • 6
  • 4
11 Comments
 
LVL 15

Expert Comment

by:pateljitu
ID: 36523207
Hi @garethtnash,

Bit confused on your earlier post http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_27297659.html, was the issue not resolved as you did accept the solution.

Looks like the IF condition is not successful, please try the code as attached to view what comparison happens in DO LOOP


<%    
        do While NOT RsRelated.EOF

Response.Write(CStr(RsRelatedProductOptions.Fields.Item("ID").Value) &" = "& CStr((RsRelated.Fields.Item("ID").Value)) &" <br>")            
            If (Not isNull((RsRelated.Fields.Item("ID").Value))) Then
                If (CStr(Trim(RsRelatedProductOptions.Fields.Item("ID").Value)) = CStr(Trim((RsRelated.Fields.Item("ID").Value)))) Then
                    Response.Write(" selected=""selected"" ")
                    exit do
                end if
            End if        
     
        RsRelated.MoveNext()
        loop
 %>

Open in new window

0
 
LVL 33

Accepted Solution

by:
Big Monty earned 2000 total points
ID: 36523318
i would put the selected options into an array, then loop through each array item and do the check for each option. it doesnt seem all that efficient, but as long as you dont have 100's of options, you'll be ok:

 
<%
arrRelated = RsRelated.GetRows()   '-- this'll put the recordset into an array
%>

<select name="category" size="4" multiple="multiple" id="category">
  <%
While (NOT RsRelatedProductOptions.EOF)
%>
    <option value="<%=(RsRelatedProductOptions.Fields.Item("ID").Value)%>"
<%    
        for each productID in arrRelated
            If (productID <> "" ) Then
                If ( CStr(RsRelatedProductOptions.Fields.Item("ID").Value) = CStr( productID ) ) Then
                    Response.Write(" selected=""selected"" ")
                end if
            End if        
         next     
       %>
    ><%=(RsRelatedProductOptions.Fields.Item("Product-Name").Value)%></option>
 <%
 
  RsRelatedProductOptions.MoveNext()
Wend
%>
</select>

Open in new window

0
 

Author Comment

by:garethtnash
ID: 36536491
Thanks BigDaddy works a treat, but you say it isn't very efficient,.. is there a more efficient way?

Thank you
0
Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

 
LVL 33

Expert Comment

by:Big Monty
ID: 36539363
not unless you want to get into caching the contents of the select box, but even that can be a pain the butt. if you dont have a lot of items in the box, i wouldnt worry about it.
0
 

Author Closing Comment

by:garethtnash
ID: 36542960
Thank you - perfect :)
0
 

Author Comment

by:garethtnash
ID: 36930196
Hello Big Daddy,

Sorry I need to request your help again...

I'm building a copy of the script that you wrote above, however whilst it works perfectly your script includes --

"<%    
        for each productID in arrRelated
            If (productID <> "" ) Then
"

And I'm having difficulty understanding where productID comes from, as my RsRelated doesnt include a productID it includes -

Select  R.RelatedProductID as ID from dbo. [Direct-Related-Products] R  where R.ProductID = ?"

The reason I ask is that i had a different version of the above, previously written, for a category option --

<select name="category" size="4" multiple="multiple" id="category">
<%
While (NOT RsCategoriesOptions.EOF)
%><option value="<%=(RsCategoriesOptions.Fields.Item("ID").Value)%>"<%    
        do While NOT RsSelectedCategories.EOF

            If (Not isNull((RsSelectedCategories.Fields.Item("ID").Value))) Then
                If (CStr(RsCategoriesOptions.Fields.Item("ID").Value) = CStr((RsSelectedCategories.Fields.Item("ID").Value))) Then
                    Response.Write(" selected=""selected""")
                    exit do
                end if
            End if        
     
        RsSelectedCategories.MoveNext()
        loop
 %>><%=(RsCategoriesOptions.Fields.Item("Category-label").Value)%></option>
 <%
   RsCategoriesOptions.MoveNext()
Wend
%></select>

Open in new window


And I've just discovered that this shows selected options, if there are greater than 1 selected, but shows nothing if only 1 option is selected..

I'd like to rewrite this using your example above....

But can't get past the productID reference..

Hope you are there and can help..

Please --

Thank you
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 36931399
the productID is just a variable name used in the for loop, so the statement:

for each productID in arrProductIDs

just means it'll assign each value stored in the array to the variable productID for each iteration of the loop. you can also write the look this way and it does the same thing:

for x = 0 to UBound( arrPoductIDs )
    productID = arrProductIDs( x )
next

so for your categories example, just rename it to something that makes more sense, like categoryID

Hope this helps!
0
 

Author Comment

by:garethtnash
ID: 36932474
Thank you :)
0
 

Author Comment

by:garethtnash
ID: 36987478
Hi Big Daddy,

Sorry to trouble you, I've found a small bug, and could do with your help to iron it out please,

This ...

<%
arrRelated = RsRelated.GetRows()   '-- this'll put the recordset into an array
%>
<select name="related" size="4" multiple="multiple" id="related" class="menulist" >
<%
While (NOT RsRelatedProductOptions.EOF)
%><option value="<%=(RsRelatedProductOptions.Fields.Item("ID").Value)%>"<%    
        for each productID in arrRelated
            If (productID <> "" ) Then
                If ( CStr(RsRelatedProductOptions.Fields.Item("ID").Value) = CStr( productID ) ) Then
                    Response.Write(" selected=""selected""")
                end if
            End if        
         next     
       %>><%=(RsRelatedProductOptions.Fields.Item("Product-Name").Value)%></option>
	   <%
 
  RsRelatedProductOptions.MoveNext()
Wend
%></select>

Open in new window


Works providing RsRelated is not NULL, however there will be occasions when RsRelated IS Null and therefore the select list box should only display the options...

However currently when RsRelated is null I get the following error --

" ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

../formfields/Update-Product/RelatedProducts.asp, line 2"

Is there a quick work around for this? -

Thanks again
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 36989307
try this:

<select name="related" size="4" multiple="multiple" id="related" class="menulist" >

<%
if rsRelated.bof and rsRelated.eof then
%>
.....hard code options here.......
<%
else
arrRelated = RsRelated.GetRows()   '-- this'll put the recordset into an array

While (NOT RsRelatedProductOptions.EOF)
%><option value="<%=(RsRelatedProductOptions.Fields.Item("ID").Value)%>"<%    
        for each productID in arrRelated
            If (productID <> "" ) Then
                If ( CStr(RsRelatedProductOptions.Fields.Item("ID").Value) = CStr( productID ) ) Then
                    Response.Write(" selected=""selected""")
                end if
            End if        
         next    
       %>><%=(RsRelatedProductOptions.Fields.Item("Product-Name").Value)%></option>
         <%
 
  RsRelatedProductOptions.MoveNext()
Wend
end if
%>
</select>
0
 

Author Comment

by:garethtnash
ID: 36991418
Thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In this tutorial viewers will learn how to position items using CSS's three positioning types Create a new HTML document with an internal stylesheet.: Create another div in CSS and name it Absolute : Type "position:absolute;" and "top:10px; left:50p…
In this tutorial viewers will learn how to position overlapping items using z-index in CSS. They will also learn the restrictions on the z-index property.  Create a new HTML document with an internal stylesheet.: Create a div in CSS and name it Red.…
Suggested Courses

752 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