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

ASP Vbscript Multi Select Listbox

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
garethtnash
Asked:
garethtnash
  • 6
  • 4
1 Solution
 
pateljituCommented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
garethtnashAuthor Commented:
Thanks BigDaddy works a treat, but you say it isn't very efficient,.. is there a more efficient way?

Thank you
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
garethtnashAuthor Commented:
Thank you - perfect :)
0
 
garethtnashAuthor Commented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
garethtnashAuthor Commented:
Thank you :)
0
 
garethtnashAuthor Commented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
garethtnashAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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