Check if email exists

Question

Hi,

I have a classic ASP page with some code to check if an email exists in the table as follows;

<%
    '' //Check the submitted email against existing ones in the database
    set CmdCheckEmail = server.CreateObject("ADODB.Command")
    CmdCheckEmail.ActiveConnection = MM_dbconn_STRING
    CmdCheckEmail.CommandText = "SELECT COUNT(ReferredEmail) AS 'CountEmail' FROM TenantReferral WHERE ReferredEmail = '" & Request("Email") & "'"
    Response.Write(CmdCheckEmail.CommandText)
    CmdCheckEmail.CommandType = 1
    CmdCheckEmail.CommandTimeout = 0
    CmdCheckEmail.Prepared = true
    CmdCheckEmail.Execute()

    countEmail = CmdCheckEmail("CountEmail")

    set CmdCheckEmail = nothing
    conn.close
    set conn = nothing

    If(countEmail >= 1) Then
     Message = Message & "<p>This email address has already been referred.</p>"
    End If
%>

However, the page is reporting the following error;

SELECT COUNT(ReferredEmail) AS 'CountEmail' FROM TenantReferral WHERE ReferredEmail = '[email protected]'

ADODB.Command error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/default2.asp, line 19

Line 19 is as follows;

countEmail = CmdCheckEmail("CountEmail")

The email does exist in the table and the table simply has the following columns; ReferredEmail and ReferredCode

I wondered if anyone might be able to shed any light on this error?

Thank you.

Answer 1

Note sure what database you are using but try changing your sql to:

SELECT COUNT(ReferredEmail) AS CountEmail FROM TenantReferral WHERE ReferredEmail = '[email protected]'

Then change

CmdCheckEmail.Execute()    
countEmail = CmdCheckEmail("CountEmail")

to

set rs = CmdCheckEmail.Execute()
countEmail = rs("CountEmail")

Also, you have a SQL injection issue with that query. You should be using parameterized queries.

Answer 2

CmdCheckEmail("CountEmail") tries to access the default member of the Command object, which is the parameters collection. However, you don't want to access a parameter but a field of the resulting recordset.

Try this (not tested):

Set rs=CmdCheckEmail.Execute()

countEmail = rs("CountEmail")

Apart from that, beware: This line:

CmdCheckEmail.CommandText = "SELECT COUNT(ReferredEmail) AS 'CountEmail' FROM TenantReferral WHERE ReferredEmail = '" & Request("Email") & "'"

is vulnerable to an SQL injection attack.

Never embed literal strings into SQL statement; use parameters instead. (In this case, you would do that using the Command.Parameters collection.)