Solved

ASP Vbscript Multi Select Listbox

Posted on 2011-09-12
11
1,968 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
  • 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 32

Accepted Solution

by:
Big Monty earned 500 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
 
LVL 32

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
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

 

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 32

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 32

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
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 …
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

747 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

12 Experts available now in Live!

Get 1:1 Help Now