Checkbox to SQL Database

Question

I have a problem which I'm struggling to fix.

I have a form with many checkboxes containing email addresses, the result of which is sent to another page.

This page will send out newsletters.

This is working at the moment, but what I would like to do is include their name.

From the form received I get the results ([email protected], [email protected] etc...)

I have managed to separate the results and put them into an array, but now I need to access the SQL db and find the names to the email address. This is where I'm struggling. Here is the code that I've tried to use:

name = request.form("list")

If name = "" Then
Response.redirect ("batchselect.asp")
End If

Dim your_name()
Dim mode
Dim mode_a
Dim i
mode=Request("list")
mode_a=split(mode,",")

For i=LBound(mode_a) to UBound(mode_a)
Next

i = i - 1

Redim PRESERVE your_name(i)
For t = 0 to i 

Set conn = Server.CreateObject("ADODB.Connection")
conn.open connStr

strSQL = "SELECT name FROM emails WHERE emails = '" & mode_a(t) & "'"
Set rs = conn.Execute(strSQL)

your_name(t) = rs("name")
Next

If I try and run this I get this error:

Error Type: ADODB.Field (0x800A0BCD) Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I know the problem is in the SQL Query 'mode_a(t)', because if take out the for... next and change this to mode_a(0) or mode_a(1) it works no problem.

I'm using MS SQL and classic asp

Thanks in advance

Answer 1

Not sure why you are doing this:

For i=LBound(mode_a) to UBound(mode_a)
Next

i = i - 1

Surely this would be enough, and you get rid of any ambiguity in the value of i:

i = UBound(mode_a) - 1

Answer 2

If you are not dealing with too many email addresses I would consider trying to use an IN statement with sql and just bring back all the records for the emails. It would be so much quicker and save you a number of db calls.

If you did this then you could just return the email address with the name in the recordset row and use that to match up.

As for your actual question though I would check the value of i and make sure that it is not being set to -1 or some other value that doesn't actually point to an index. Because your error is happening because there are no results being returned. You should consider checking for EOF before you actually use the recordset.