asked on

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

pateljitu

Hi @garethtnash,

Bit confused on your earlier post https://www.experts-exchange.com/questions/27297659/ASP-Multiple-Select-List-Box.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

ASKER CERTIFIED SOLUTION

Big Monty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garethtnash

ASKER

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

Thank you

Big Monty

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.

garethtnash

ASKER

Thank you - perfect :)

garethtnash

ASKER

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

Big Monty

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!

garethtnash

ASKER

Thank you :)

garethtnash

ASKER

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

Big Monty

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>

garethtnash

ASKER

Thank you