Entering Multiple ID's 1

[SamHale]

Good morning,

Hopefully someone can help, and my objective is quite simple.

What I wish to achieve is to have a dynamic list (from items already added), which I can do. Then allow the user to select 'multiple' items from this list, submit the form, and then the ID's of the items selected from the list get entered into the database. Currently I have only been able to add the ID of the first item selected, it just ignored any other items selected.

So then what I would hope to achieve is to be able to create a recordset where the ID contains any of those entered from the list.

I hope that I have been clear.

Many thanks
Sam

<select name="related_news" size="4" multiple="multiple">
<%While (NOT rs_news.EOF)%>
<option value="<%=(rs_news.Fields.Item("news_id").Value)%>">
<%=(rs_news.Fields.Item("news_headline").Value)%>
</option>
<%
rs_news.MoveNext()
Wend
If (rs_news.CursorType > 0) Then
rs_news.MoveFirst
Else
rs_news.Requery
End If
%>
</select>

 

[guitardave78]

The value comes over as an array, so
request.form("related_news")(1)
would be the first selected item

to get each id you would do

for i=1 to Request.Form("related_news").Count
 Response.Write(Request.Form("related_news")(i) & "<br />")
next

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[SamHale]

Hi guys

Many thanks for your replies - I managed to get the above working shortly after posting. So sorry about that.

But I now have another issue with the above.

I've managed to ADD the ID's on the previous form. However, I'm now having problems displaying the recordset based on the entered information.

My related field now looks like '9,10'. On my page I have the following code:

<%
Dim rs_related__MMColParam
rs_related__MMColParam = rs_service.Fields.Item("related").Value
End If
%>

rs_related.Source = "SELECT * FROM tbl_services  WHERE ID like " + Replace(rs_related__MMColParam, ",", ",") + ""

(obviously only the key points)

So I am trying to create a recordset where the ID(s) contain (like) '9,10'. However I receive the following error:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression 'ID like 9,10'.

Can someone tell me where I am going wrong?

Many thanks
Sam

 

[DotNetGnat]

use IN instead of LIKE...something like this...

Select blah from mytable where ID IN (9,10,11)

-DNG

 

[SamHale]

Hi DNG,

This doesn't seem to want to work
I'm guessing due to the '+ Replace(rs_related__MMColParam, ",", ",") +'

I tried a number of ways (without the +'s, without the SV, etc.)

Any other ideas?

rs_related.Source = "SELECT * FROM tbl_services  WHERE ID IN (+ Replace(rs_related__MMColParam, ",", ",") +)"
------------------------------------------------------------------------------------------------^

Many thanks
Sam

 

[DotNetGnat]

try this:

rs_related.Source = "SELECT * FROM tbl_services  WHERE ID IN ("+rs_related__MMColParam+")"

-DNG

 

[SamHale]

Great! Thank you DNG, works fine.

Ok - Hopefully my final issue with the above.

Within my edit page, I now need the list to automatically highlight those items based on the ID's entered previously. So the user does not need to select the related articles every time they edit the article. I figured this would look something like -

<option value="<%=(rs_related.Fields.Item("ID").Value)%>" <% if rs_related.Fields.Item("ID").Value in ("+rs_related__MMColParam+") then %> selected <% end if %>><%=(rs_related.Fields.Item("product_name").Value)%></option>

So if the ID of the 'option' in the list appeared in my related field then it would write 'selected'. But I receive the following error -

Expected 'Then' 

/admin/services/edit_old.asp, line 263 

if rs_related.Fields.Item("ID").Value in ("+rs_related__MMColParam+") then
--------------------------------------^

Any ideas on this one? Much appreciation.

Regards
Sanm

 

[DotNetGnat]

there is a IN clause in SQL but not in ASP/VB...

i am not completely sure what you are trying to do here..but here is a way to do it...

dim myarray
myarray = split(rs_related__MMColParam,",")
<option value="<%=(rs_related.Fields.Item("ID").Value)%>" <% 
for i=0 to UBOUND(myarray)
if rs_related.Fields.Item("ID").Value= myarray(i) then %> selected <% end if next %>><%=(rs_related.Fields.Item("product_name").Value)%></option>

-DNG

 

[SamHale]

Hi DNG,

Thanks for this - However, I'm receiving an error message :

Microsoft VBScript compilation error '800a0401' 

Expected end of statement 

/admin/services/edit.asp, line 351 

end if next
-------^

What I am trying to do is -

I have an add product page, on this page you can add related products. There is a list containing all the products already added into the system. When submitted the products ID's of the selected items in the list get added into the database. e.g. 1,2,4,7

I then have an edit product page, so they can edit any products they have added - what I need is for the related links list to automatically highlight the products entered, i.e. Product 1, Product 2, Product 4, etc.

Many thanks
Sam

 

[SamHale]

Any idea anyone?
(sorry for the push)

 

[emozley]

Your 'end if' and 'next' should be on separate lines.

 

[SamHale]

Thanks emozley, this got the page to display.
However, the code doesn't work unfortunately

DNG - From your suggestion, a list with 1 item is displayed and not select, even though it's ID was in the related field

Any other suggestions. This is starting to get to me.

Here is the code I am working with and a breakdown -

[COLOR=red]This is the recordset for the product they're editing[/color]

Dim rs_service
Dim rs_service_numRows

Set rs_service = Server.CreateObject("ADODB.Recordset")
rs_service.ActiveConnection = MM_covers_STRING
rs_service.Source = "SELECT *  FROM tbl_services  WHERE ID = "+Request.QueryString("ID")+""
rs_service.CursorType = 0
rs_service.CursorLocation = 2
rs_service.LockType = 1
rs_service.Open()

rs_service_numRows = 0

[COLOR=red]This is the recordset to display ALL other products in the databse (so they have ALL the products to select for a related product)[/color]

Dim rs_service2
Dim rs_service2_numRows

Set rs_service2 = Server.CreateObject("ADODB.Recordset")
rs_service2.ActiveConnection = MM_covers_STRING
rs_service2.Source = "SELECT *  FROM tbl_services"
rs_service2.CursorType = 0
rs_service2.CursorLocation = 2
rs_service2.LockType = 1
rs_service2.Open()

rs_service2_numRows = 0

[COLOR=red]This is my session variable to receive the ID's of the related products already entered[/color]

Dim rs_related__MMColParam
rs_related__MMColParam = rs_service.Fields.Item("related").Value

[COLOR=red]Here is my list as it is in simple format[/color]

<select name="related" size="3" multiple>
<% 
While ((Repeat1__numRows <> 0) AND (NOT rs_service2.EOF)) 
%>
<option value="<%=(rs_service2.Fields.Item("ID").Value)%>"><%=(rs_service2.Fields.Item("name").Value)%></option>
<% 
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rs_service2.MoveNext()
Wend
%>
</select>

[COLOR=red]So the above is currently just displaying ALL of the products. What I need to do is highlight ("selected") automatically, those products that ID appears in my session variable created earlier.[/color]

I appreciate all your help.

Regards
Sam

 

[SamHale]

Should I give up or find an alternative?

 

[emozley]

There's an excellent example here of having a list with multiple select items.

http://www.codeave.com/asp/code.asp?u_log=125

The selections are submitted to the script which builds a SQL statement accordingly. It should be easy to modify the SQL statement to enter the selections into the database.

E.