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


    objRecordset.MoveFirst
        While Not objRecordset.EOF
        flag=objRecordset("flag")

                If flag = false Then
                  recip="TechSupport@test.org"
                        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
                            objEmail.Send
                             set objEmail = Nothing
                   Wscript.Quit
        End If

         objRecordset.MoveNext

        Wend

    objRecordset.MoveFirst
        While Not objRecordset.EOF
        flag=objRecordset("flag")

             If flag = true Then
                            recip=objRecordset ("email")
                mem_id=objRecordset("mem_id")
                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
                      objEmail.Send
            set objEmail = Nothing
            objRecordset.MoveNext

                    End If


    Wend

objRecordset.Close
objConnection.Close
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
Default

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?

hth
phil
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.