Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Other Programming > VBScript
VBScript For questions and discussions related to VBScript.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VBScript section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 30th, 2004, 10:35 AM
Registered User
Join Date: Jan 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Mail Loop Will Randomly Stop Working

I have a vbScript that is being kicked off in a MS SQL 7 DTS package that will inform new members when they have signed up, it done in batches and not real time. The setup is such:
*The script is supposed to scan a binary field and send emails with a membership ID to the appropriate email address when the field is true
*If any of the fields are false, exit the script and notify administrator
*Every record is set to true
*I am using the SMTP engine on IIS 5 on a Win2k Server box

The issue:
*The script sometimes works, but it apparently will randomly stop looping and only notify the first record in the table

The script:
Option Explicit

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Dim objConnection, objRecordset

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    objConnection.Open "DSN=email;"
    objRecordset.CursorLocation = adUseClient
    objRecordset.Open "SELECT * FROM email", objConnection, _
    adOpenStatic, adLockOptimistic

Dim recip, mem_id, objEmail, flag, message, subject, dsg

        While Not objRecordset.EOF

                If flag = false Then
                        message="This script did not detect any flagged fields. Please cross check the database to ensure accuracy"
                  subject="Script Error"
                ' objRecordset.GetString
                ' Wscript.Echo recip
                ' Wscript.Echo subject
                      ' Wscript.Echo message

                      Set objEmail = CreateObject("CDO.Message")
                            objEmail.From = "test@test.org"
                            objEmail.To = recip
                            objEmail.Subject = subject
                            objEmail.Textbody = message
                             set objEmail = Nothing
        End If



        While Not objRecordset.EOF

             If flag = true Then
                            recip=objRecordset ("email")
                subject="INTA Membership Account Activation"
               'Wscript.Echo recip
               'Wscript.Echo subject
                     'Wscript.Echo mem_id

            Set objEmail = CreateObject("CDO.Message")
                      objEmail.From = "dg@inta.org"
                      objEmail.To = recip
                      objEmail.Subject = subject
                      objEmail.Textbody = mem_id
            set objEmail = Nothing

                    End If


set objRecordset = Nothing
set objConnection = Nothing

David S. Goldstein
"Alpha Male" At Large
Reply With Quote
  #2 (permalink)  
Old February 2nd, 2004, 08:18 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post

In the 2nd loop try moving the line "objRecordset.MoveNext" outside of the "flag=true" If block - otherwise it will just be stuck in a loop as soon as it finds a record where flag = false.

Alternatively, why do you bother looping thro the records twice? Why can't you process both the flag=false and the flag=true cases in a single loop?

Reply With Quote

Similar Threads
Thread Thread Starter Forum Replies Last Post
Stop button won't stop loop JDShaffer Visual Basic 2008 Essentials 3 May 23rd, 2008 03:22 PM
Excel Worksheet Functions Stop Working Arron Edwards Excel VBA 1 May 13th, 2008 01:12 AM
Reporting Services stop working every morning! aldaris BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 January 19th, 2006 05:15 PM
Loop through records- stop if certain value met SoC Classic ASP Basics 2 July 28th, 2005 08:22 PM
Stop E-mail SannOo Forum and Wrox.com Feedback 7 June 15th, 2004 05:06 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.