vbscript - ASP using variables from previous page in SQL query

Question

On my first page I have an array defined as:

dim selection
    selection = Array("name", "city")

On the following ASP page I'm trying to call to those same variables for an SQL query:

dim selection
    selection = array(request.form("name"), request.form("city"))

In my SQL query:

sqlstr = "SELECT * from Users where name='" + selection(0) + "' and city= '" + selection(1) + "'

This doesn't work, and I've tried tooling around with it but I can't seem to find a working solution. What am I missing?

Answer 1

Html forms don't do arrays. The best you're gonna get via Request.Form() is a comma-delimited list, and that's if you have multiple form fields with the same name: there is just no way to pass along an entire array "in one piece".

Based on your "I've forced 2 SQL columns into the same select box" comment, I think what you need is something like:

<select name='Person' size='1'>
    <option value='NameID1~AddressID1'>John Smith, 123 Main Street</option>
    <option value='NameID1~AddressID2'>John Smith, 345 Elm Avenue</option>
    ...
</select>

... which you would handle on the submit end by splitting on the delimiter character:

const delimiter = "~"
selection = Request.Form("Person")
If Instr(selection,delimiter) > 0 Then selection = Split(selection,delimiter)

Naturally, you need to choose your delimiter character(s) such that there's no chance it'll occur in your option values.