Hi guys, I have this bit of ASP code

'Get restaurant ID's from varaible pRestaurant by splitting either side of comma pRestaurantSplit = Split(pRestaurant, ", ")

'Initialise the strSQL variable with an SQL statement to query the database strSQL = "SELECT * FROM Members INNER JOIN Restaurants ON Members.RestaurantLocation=Restaurants.RestaurantLocation WHERE Members.Approved='Yes' AND " for each x in pRestaurantSplit

    strSQL = strSQL + " OR Restaurants.RestaurantID='" & x & "'"
next
strSQL = strSQL + "ORDER BY " & pSort & " " & pOrder & ";"

I need it so that if x is the first item it doesn't have the OR before it or alternatively it it is the last item then write it slightly different. How do I find if it's the first or last item?

Hope that makes sense! Thanks

Instead of using a foreach, use a for so you have an "index" number - and then use simple boolean logic to determine if you're at start/end of list.

jeez my VBS will be rusty, but something like:

numItems = LEN(pRestaurantSplit) for i = 0 to numItems-1 ' use i=0 or i=(numItems-1) to determine first/last next

or instead of splitting, you could replace the delimeter with the sql:

pRestaurant = "1,3,5,7,9"

strRestSQL = replace(pRestaurant,",","' OR Restaurants.RestaurantID='")

strSQL = "SELECT * FROM Members INNER JOIN Restaurants ON Members.RestaurantLocation=Restaurants.RestaurantLocation WHERE Members.Approved='Yes'"

if len(pRestaurant) > 0 then strSQL = strSQL & " AND Restaurants.RestaurantID='" & strRestSQL & "'" end if

Cheers guys, I'll give it a try

why not use IN?

strSQL = "SELECT * FROM Members INNER JOIN Restaurants ON Members.RestaurantLocation=Restaurants.RestaurantLocation WHERE Members.Approved='Yes' AND Restaurants.RestaurantID IN (" & pRestaurant & ") ORDER BY " & pSort & " " & pOrder & ";"

[edit] and this is the anal part of me, naturally you would want to sanitize in put using something like:

^[\d|,]{1,}$

Ahhh poliguin that worked a treat!!! Thank you so much As usual I was taking the longest route possible to get what I needed when there was something so much simpler

Thanks again and to Techno and Jamie too

At least I have learnt a few things through all this

Originally posted by: poliguin [edit] and this is the anal part of me, naturally you would want to sanitize in put using something like:

^[\d|,]{1,}$

Seal the perimeter! There's an intruder in the Back End!

Originally posted by: mclarkson

i <3 that comic.

as a random side, for quite a while I've been overly paranoid about XSS and SQL injection. The funny caveat for that is that I knew about one part of previous work where I wasn't so good about coding against it. Fast forward a little bit and I find out that the place I knew was totally vulnerable took an entire site down due to the vulnerability (and it isn't exactly a small site, but also not a huge site). One of those items you learn and live from.

Originally posted by: mclarkson

No self-respecting database architect would name the table as a plural. It should be "Student".