SQL script

Report · Nov 05, 2008

This is driving me crazy. I can't figure out why it isn't working. I basically copied an existing script and modified it to use a different table, and I just can't get it to work. It's an MS SQL server. Any help appreciated!!

First, I have a bunch of email addresses in one column of a table and the second column is null. I want the user to submit their email and if it exists, in the null cell next to it, put NO.

The error I get is:
Variable METABEMAIL is undefined.

The error occurred in C:\Inetpub\wwwroot\twinlab\metab-email\unsubscribe2.cfm: line 12

10 : <cfquery name="evitem" datasource="Twinlab-Mail">
11 : Select * From MetabolifeUnsub
12 : Where Email = '#MetabEmail#'
13 : </cfquery>
14 :

The table is called MetabolifeUnsub and the column is called MetabEmail and the other column is NoSpam

File one: emaillist.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

" http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns=" http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Unsubscribe</title>
</head>

<body>
<div align="center">
<h1>Unsubscribe
</h1>
</div>
<form id="form1" name="form1" action="unsubscribe2.cfm">
Enter email
<input type="text" name="email" />
<input type="submit" name="Submit" value="Submit" />
</form>
</body>
</html>

Second file: unsubscribe2.cfm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

" http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns=" http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Unsubscribed!</title>
</head>

<cfquery name="evitem" datasource="Twinlab-Mail">
Select * From MetabolifeUnsub
Where Email = '#MetabEmail#'
</cfquery>

<body>

<CFIF evitem.Email EQUAL '#MetabEmail#'>

<cfquery name="evitem" datasource="Twinlab-Mail">
UPDATE MetabolifeUnsub
SET
NoSpam = 'NO'
Where Email = '#MetabEmail#'
</cfquery>

Your email has been unsubscribed from our list.
<cfelse>
The email address entered does not exist in our system. Please go back and check the spelling of the address entered.
</CFIF>
</body>
</html>

Report · Nov 05, 2008

ETJNY wrote:
> Any help appreciated!!

The field in the form is simply named 'email' and would be in the form
scope. So it would properly be referenced as '#form.email#'

The variable in the query is '#MetabEmail#' and nowhere in the provided
CFML do I see you defining a value for this variable.

You need to reconcile this disparity.

Report · Nov 05, 2008

if MetabEmail is a db column and Email is a form field, then
WHERE Email = '#MetabEmail#'
in your queries should instead be
WHERE MetabEmail = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.email#">

also your <CFIF evitem.Email EQUAL '#MetabEmail#'> serves no purpose and
will throw an error if no matching email is found in your db.
use <cfif evitem.recordcount> instead.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/