PDA

View Full Version : Update Record in Database onLoad



MrJL
06-30-2002, 07:05 PM
Ok, due to the field in the database giving me fits, I've changed what I was trying to do in a previous post. The new details (and problem) follows...

Ok, the base of my code is Ocean12 ASP Mailing List Manager (http://scripts.ocean12tech.com/). The free version of the script has it's limitations, so I'm attempting to add a feature here & there on my own.

As it stands, when you sign up for the Newsletter, it adds your e-mail to the database and that's it. The next step is I send a mailing to the list and get bounced e-mails for the people that say their e-mail address has a coma in it or that don't give me the "@yahoo.com" portion of their address.

Rather than get 5-10 bounced e-mails at once, I'd rather 'weed them out' as I go. And provide a security feature into the script. The database table looks like this at Download:

ID | EMail | First Name | Last Name | Date Joined

I've added a sixth column to the table, "Conf" which is a Text Field, Max 1 char, either Y or N. I should mentioned, I'm running off of Access. By default, when a user signs up, the Conf field is blank; I'll worry about setting it to N by default later. For all intents and purposes, this means they haven't "Confirmed" their subscription yet.

At this point, an e-mail would be sent to them directing them to a particular URL which will 'confirm' their subscription. I've skipped the automated e-mail for now, because I'm working on the page it directs them to first.

This is where my problem comes in. To keep the the confirmation process somewhat secure, I'm directing them to "confirm.asp?Email=jer@jers-web.com" for instance. I chose this route rather than confirm.asp?ID=blah so that some script kiddie couldn't go thru and just hit every record , since the IDs are incremental.

However, it's returning nothing. A blank page. I have it set up (I think) so that it will display an error if something goes wrong. But, nothing is happening.

The code I'm using is this:


<% @Language=VBScript %>
<% Option Explicit %>

*******Begin Included Code from dsn.asp********
<%
dim dsn
dim Conn
dsn="DBQ=" & Server.Mappath("db.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open dsn
%>
*******End Included Code from dsn.asp********

<%
Dim strSql, infoRS, Email

Email = Request.QueryString("Email")
%>

<% Sub GetImage()
strSql = "SELECT * FROM List WHERE Email = '" & Email & "'"
set infoRS = Conn.Execute(strSql)

IF infoRS.EOF THEN
GetImage()
DisplayBad()
ELSE
UPDATEQUERY = "UPDATE List SET Conf = Y WHERE Email = '" & Email & "'"
DisplayGood()
END IF

End Sub %>

Immediately below that I then have my DisplayGood() & DisplayBad().


<% Sub DisplayGood() %>

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" x-undefined>
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">

</head>

<body>
This is a Good Display.
</form>
</body>

</html>

<!--#include file="dsn2.asp"-->
<% End Sub %>

DisplayBad() is identical, except for the name and it says "This is a Bad Display." But all I'm getting when I hit the page is a blank page. I view the source, and it's just this:


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=windows-1252"></HEAD>
<BODY></BODY></HTML>

Any ideas? I'm at a loss...:confused: :confused:

Thanks!

Jer!

whammy
07-01-2002, 12:05 AM
Is that all of the code? I don't see you calling the Sub GetImage() anywhere...

MrJL
07-01-2002, 12:25 AM
Ok, due to the 'bulkiness' of the prior snippet. I decided to download a Genius's pre written confirm script and see if I could make heads or tails of it. ;);)

So this is where I'm at now...


<% @Language=VBScript %>
<% Option Explicit %>

*******Begin Included Code from dsn.asp********
<%
dim dsn
dim Conn
dsn="DBQ=" & Server.Mappath("db.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open dsn
%>
*******End Included Code from dsn.asp********

<%
Dim strSql, infoRS, Email, TheConfirmString, Conf

Email = Request.QueryString("Email")

TheConfirmString = "SELECT * FROM List WHERE Email = " & Email & " AND Conf = 'N'"
set infoRS = Conn.Execute(TheConfirmString)

Conn.Execute("UPDATE List SET Conf = 'Y' WHERE Email = " & Email & " AND Conf = 'N'")

%>

And the error I'm getting is...


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Email = jleedom@acc-net.com AND Conf = 'N''.

/newsletter2/confirm.asp, line 10

Line ten being the "set infoRS = Conn.Execute(TheConfirmString)" line.

I'm pulling my hair over this, 'cause it shouldn't be this difficult. I just know I'm looking over part of it and not moving part of the pre-written script into my own...

Jer!

yossi
07-01-2002, 12:28 AM
First of all you Must call the "GetImage()" subroutine (by adding: <%GetImage()%> somewhere on the page.

second (and important), take off the "GetImage()" from this routine (where: "IF infoRS.EOF THEN...") because if the Email is not exist in the Database, it will return False, and the routine will never stop (or will stop with an Error).

third advice: if you pass details like "aspPage.asp?name=details", be sure that the "details" do not contain characters like " or & or ? because it will "kill" the rest of the string.
If you do not know what is the content of the "Details" - send it using: 'escape("details")', and when the other page receive it use 'unescape(request("Email"))' to get the source again.

:thumbsup:

yossi
07-01-2002, 12:37 AM
the Email is a STRING so you must use '.
...WHERE Email = '" & Email & "' AND Conf...

:D

MrJL
07-01-2002, 12:41 AM
Bloody mav'lous!!!!!!!

Thanks a lot, m8! :)

Works like a charm now.

Now to get the rest of the script working. Hehehe.

One step at a time, tho. ;)

Jer!

whammy
07-03-2002, 12:56 AM
P.S. Regarding what yossi said about querystrings, in VBScript you can do:

aspPage.asp?name=<% = Server.URLEncode(details) %>

:)