[thelist] ASP-Access recordset/command update

Karen J. Bowen karen at miinx.com.au
Sun Apr 28 21:17:01 CDT 2002


I am having trouble with a newsletter application I've written, and I
don't know if it's just the syntax or if it can't be done as I'm trying.

The problem is with the ASP page that sends the newsletter.  After
grabbing the list of users from the database, I loop through them
sending each a newsletter.  This works fine, but I also want to then
record the date & time the newsletter was sent to each user, and this I
don't seem to be able to do.

Here's the relevant code:
------------------------------------
' get the user details
Set oConn = OpenDb()
Set oRS = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT sub_id, name, email, format_html FROM tblSubscribers;"
set oRS=oConn.execute(sSQL)

' Then mail a newsletter to each
Do While Not oRS.EOF
    Mailer.AddRecipient oRS.Fields("name"), oRS.Fields("email")

    ' ... newsletter formatted here ...

    mailSuccess = Mailer.SendMail

    ' If the send worked, add the date to the database record
    If mailSuccess Then
       Set oCmd = Server.CreateObject("ADODB.Command")
       Set oCmd.ActiveConnection = oConn
       sSql = "UPDATE tblSubscribers SET last_nl_sent = #" & Now() & "#
WHERE sub_id = " & oRS.Fields("sub_id") & ";"
       oCmd.CommandText = sSql
       oCmd.CommandType = adCmdText
       oCmd.Execute
       Set oCmd = Nothing
    End If

    oRS.MoveNext

Loop
------------------------------------

So I'm trying to create, then destroy, an ADODB Command object for each
record in the recordset...  but it's not working.

I am wondering if this is just not the way to do this.  Can anyone see a
problem with this code, and/or is there a better way to be doing this?

Any help greatly appreciated.

Thanks,
Karen
------------
Miinx Design & Development
e :: karen at miinx.com.au
p :: 03 9534 2659
w :: www.miinx.com.au




More information about the thelist mailing list