SQL and ASP HELP!!!!

Question

Hi all,

I've been working on this script for the last week or so and i'm having major problems trying to understand why it's not working right.

I've got some checkboxes link to email address and all i need is for the corresponding username to go with that email. This script is for users that have registered for different newsletters.

Here is my coding

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

emails = Request("emaillist")

emails = Replace( emails, "'", "''" )
emails = Replace( emails, ", ", "','" )

strSQL = "SELECT name, email FROM emails WHERE email IN ('" & emails & "')"
Set rs = conn.Execute(strSQL)

namesAndEmails = rs.GetRows()
rs.close

for lnRowCounter = 0 To Ubound(namesAndEmails,2) 
For lnColumnCounter = 0 To Ubound(namesAndEmails,1)
Response.Write namesAndEmails(lnColumnCounter, lnRowCounter)
Response.write "</P>"
Next
Next

This is part of the whole script, i've changed it around a bit and included the for...next for debugging.

Now for the problem, as shown in the SELECT statement 'name, email', the result completely ignores the email and give me the names only.

I've tried the SQL statement direct and it works perfect showing both name and email together but not in my ASP page. I even tried putting a * in it's place.

strSQL = "SELECT * FROM emails WHERE email IN ('" & emails & "')"

Will return the users id, name, and a few other item's from the DB but not the name and emails together, why?????

It's asp with a SQL Server database

Regards

Rick

Test Results

The values from strSQL when it's set as this:

SELECT name, email 
FROM emails 
WHERE email IN ('[email protected]','[email protected]') 

This in SQL will give me the following answer

name | email 
jo   | [email protected] 
fred | [email protected] 

In asp the answer will be

[email protected] 
[email protected] 

I can't figure out why in SQL it will show the name and email but in ASP it will only show the email and NOT the name.

I've even tried

strSQL = "SELECT * FROM emails WHERE email IN ('[email protected]','[email protected]')

and this will produce in ASP all the results EXCEPT name!!!!!

Answer 1

I'm not 100% sure, but I guess you must swap the rank parameter of the ubound()

for lnRowCounter = 0 To Ubound(namesAndEmails,1)
For lnColumnCounter = 0 To Ubound(namesAndEmails,2)

Answer 2

Try

For i = LBound(namesAndEmails, 2) To UBound(namesAndEmails , 2)
    Name = namesAndEmails(0, i)
    Email = namesAndEmails(1, i)
    Response.Write Name & " " & Email
Next

Answer 3

I might not be understanding this completely, but if all you want to do is to output a list of names with their respective email addresses, you could try simplifying to this:

name = rs("name")
email = rs("email")

do while rs.eof <> true
    response.write(name & " " & email & "<br />")
    rs.movenext
loop

…at least for testing purposes. Alternatively, you could concatenate the name and email in the SQL statement into one column:

SELECT name + '|' + email as nameemail FROM emails WHERE email IN ('" & emails & "')

...will give you "name|email" that you can easily string manipulate.

Answer 4

What if you "View Source" of the output webpage. Sometimes I've had errors that I couldn't see because it came out looking like an invalid tag that the renderer would silently ignore.