Bringing a VBS and ASP script together

[Fox]

I merged and modified these script which work perfectly fine
as long as I use server.execute to access the VBS
part (which is itself in another ASP file). When these
I use a session variable to pass an email address from
the database. I have had timeout problems
and buffer problems and as I understand it, maybe
a problem with changing the session variable too many
times in one session. So I tried putting the two scripts
on one page. When I do I get the error

Microsoft VBScript compilation error '800a03ea'
Syntax error
/tournament-services/fbba/editor/cdo_setmail.asp, line 24
SUB sendmail( fromWho, toWho, Subject, Body )

Note that this is a large email list for a World Karate
Tournament and is not a spam list. It has about 20,000
recipients and I have never done anything like this
hence the problems.

How can I get these to work together on the same page?
Here is the first script with the server.execute commented out
since I do not want to use it if I can get this all on one page.
I am hoping that doing that will end the other error problems.
The first sript will have the second replace the server.execute.

FIRST SCRIPT BEGIN:

Server.ScriptTimeout = 6000
Set objEmail= Server.CreateObject("ADODB.Connection")
objEmail.Open "DSN=Tournaments;",2

Set CompetitorEmail = Server.CreateObject("ADODB.Recordset")
sqltext = "SELECT Email FROM Vernon_email Where Email like '%@%' Order By
Email;"

CompetitorEmail.Open sqltext, objEmail
Do While not CompetitorEmail.EOF
If not address = CompetitorEmail("Email") then
'assign who to send to
session("fromWho") = CompetitorEmail("email")

'cycle through mail sender
'server.execute("CDO-vernon.asp")
'the second script was placed here
end if

address = CompetitorEmail("Email")
CompetitorEmail.MoveNext
Loop
CompetitorEmail.close
Set CompetitorEmail = Nothing
END FIRST SCRIPT

SECOND SCRIPT STARTS
'I will remove the building of the HTML body.

toWho = "(e-mail address removed)"
Subject = "Karate Tounament NASKA/FST/FBBA: OSU! The SuperStar
Invitational - Vernon Jones Jr."

'it is this SUB that causes the syntax alert
SUB sendmail( fromWho, toWho, Subject, Body )
Dim objCDO
Dim iConf
Dim Flds
Const cdoSendUsingPickup = 2
Set objCDO = Server.CreateObject("CDO.Message")
Set iConf = Server.CreateObject("CDO.Configuration")

Set Flds = iConf.Fields
With Flds
.Item(cdoSendUsingMethod) = cdoSendUsingPickup
.Item("smtpserverpickupdirectory")= "c:\Inetpub\mailroot\pickup"
.Item(cdoFlushBuffersOnWrite) = true
.item(Response.Buffer) = False
.Item(cdoSMTPServer) = "mailforward.connexions.net"
.Update


'The HTML Body is built here

End With
Set objCDO.Configuration = iConf
objCDO.From = fromWho
objCDO.To = toWho
objCDO.Subject = Subject
objCDO.HTMLbody = strHTML
'objCDO.AddAttachment
objCDO.Send
END SUB

If toWho <> "" THEN
sendMail fromWho, toWho, Subject, Body

END IF
'Cleanup
Set ObjCDO = Nothing
Set iConf = Nothing
Set Flds = Nothing

END SECOND SCRIPT

As mentioned above, when I put this second
script into the middle of the first, the SUB errors
as incorrect syntax. I am really over my head here
and do not know what can be done to rectify this.

Thanks for any pointers,

Fox

 

[Ray at]

Can you show the code combined that is causing the error?

Ray at home

 

[Fox]

Thanks for taking a look Ray!
Since the CDO part is not something I am familiar
with, I do not know how to combine the sections
in the one script. Here is what it would look like with
a raw combining.
Fox

Server.ScriptTimeout = 6000
Set objEmail= Server.CreateObject("ADODB.Connection")
objEmail.Open "DSN=Tournaments;",2

Set CompetitorEmail = Server.CreateObject("ADODB.Recordset")
sqltext = "SELECT Email FROM Vernon_email Where Email like '%@%' Order By
Email;"
CompetitorEmail.Open sqltext, objEmail

Do While not CompetitorEmail.EOF
If not address = CompetitorEmail("Email") then
'assign who to send to
toWho = CompetitorEmail("email")

fromWho = (e-mail address removed)
Subject = "Karate Tounament NASKA/FST/FBBA: OSU! The SuperStar
Invitational - Vernon Jones Jr."

'it is this SUB that causes the syntax alert
SUB sendmail( fromWho, toWho, Subject, Body )
Dim objCDO
Dim iConf
Dim Flds
Const cdoSendUsingPickup = 2
Set objCDO = Server.CreateObject("CDO.Message")
Set iConf = Server.CreateObject("CDO.Configuration")

Set Flds = iConf.Fields
With Flds
.Item(cdoSendUsingMethod) = cdoSendUsingPickup
.Item("smtpserverpickupdirectory")= "c:\Inetpub\mailroot\pickup"
.Item(cdoFlushBuffersOnWrite) = true
.Item(Response.Buffer) = False
'probably not needed since using the pickup
.Item(cdoSMTPServer) = "mailforward.connexions.net"
.Update


strHTML="<html><head>" & vbCRLF
strHTML = strHTML & "<title>KARATE TOURNAMENT: Florida's SuperStar
Invitational (by Vernon Jones Jr.)</title></head>" & vbCRLF
strHTML = strHTML & "<body bgcolor=000000 link=ffffff><center>" & vbCRLF
'rest of HTML body would be here
End With

Set objCDO.Configuration = iConf
objCDO.From = fromWho
objCDO.To = toWho
objCDO.Subject = Subject
objCDO.HTMLbody = strHTML
'objCDO.AddAttachment
objCDO.Send
END SUB

If toWho <> "" THEN
sendMail fromWho, toWho, Subject, Body
END IF

'Cleanup
Set ObjCDO = Nothing
Set iConf = Nothing
Set Flds = Nothing
end if

address = CompetitorEmail("Email")
CompetitorEmail.MoveNext
Loop

CompetitorEmail.close
Set CompetitorEmail = Nothing

 

[Fox]

Some good news !
I think I have it working. I am doing a test, sending to myself.
I'm past 7,000 and it's still going. I already have a question
which I will post after this finshes or crashes. But, in case
anyone/Ray would be starting to help, I wanted to let you know
that the script has been edited and improved to the point
that it is now sending the mail.

I Don't want to waste anyone's time.

Thanks,
Fox

 

[Aaron Bertrand [MVP]]

I think I have it working. I am doing a test, sending to myself.

I'm past 7,000

Good grief, don't use ASP for this.
http://www.aspfaq.com/2291

 

[Fox]

Ok. This is the current state of this script. It seemed to
be working perfectly fine until it did not stop (G)
Apparently it started to loop and did not end at EOF.
I am sure I could do something like put a count in there
to stop it, but that would seem to be cheating when
the EOF should be able to work on its own. Note
that there were near 24,000 emails. I did a trial
run with a small list of 84 emails and it worked fine.

Something strange about the script. I made the entry
for "smtpserverpickupdirectory" and it errored saying
that it needed PICKUP entry and there wasn't one. So
I tried the complete Microsoft schema, with the same
definition(path) as you will see in the script. This it liked
and found it to work. Is there some sensible reason for this ?
When the script was on 2 pages, the entry
"smtpserverpickupdirectory" worked fine.

Can you think of any reason why (maybe bad email address characters)
or see anything in here that would have caused this
process to not stop at EOF ? If not, if I can use something
to stop it by more brute force, can you recommend what
I can most count on?

Thanks Fox

<!--METADATA TYPE="typelib" UUID="CD000000-8B95-11D1-82DB-00C04FB1625D"
NAME="CDO for Windows Library" -->
<!--METADATA TYPE="typelib" UUID="00000205-0000-0010-8000-00AA006D2EA4"
NAME="ADODB Type Library" -->

Server.ScriptTimeout = 6000
Set objEmail= Server.CreateObject("ADODB.Connection")
objEmail.Open "DSN=Tournaments;",2

Set CompetitorEmail = Server.CreateObject("ADODB.Recordset")
sqltext = "SELECT Email FROM Vernon_email Where Email like '%@%' Order By
Email;"
CompetitorEmail.Open sqltext, objEmail

Do While not CompetitorEmail.EOF
If not address = CompetitorEmail("Email") then
'assign who to send to
fromWho = CompetitorEmail("email")

toWho = (e-mail address removed)
Subject = "Karate Tounament NASKA/FST/FBBA: OSU! The SuperStar
Invitational - Vernon Jones Jr."

Dim objCDO
Dim iConf
Dim Flds
'Const cdoSendUsingPickup = 2
Set objCDO = Server.CreateObject("CDO.Message")
Set iConf = Server.CreateObject("CDO.Configuration")

Set Flds = iConf.Fields
With Flds
.Item(cdoSendUsingMethod) = cdoSendUsingPickup

..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirect
ory")="C:\inetpub\mailroot\pickup"
'.Item("smtpserverpickupdirectory")=
"c:\Inetpub\mailroot\pickup"
.Item(cdoFlushBuffersOnWrite) = true
'.Item(Response.Buffer) = False
'probably not needed since using the pickup
.Item(cdoSMTPServer) = "mailforward.connexions.net"
.Update


strHTML="<html><head>" & vbCRLF
strHTML = strHTML & "<title>KARATE TOURNAMENT: Florida's SuperStar
Invitational (by Vernon Jones Jr.)</title></head>" & vbCRLF
strHTML = strHTML & "<body bgcolor=000000 link=ffffff><center>" & vbCRLF
'rest of HTML body would be here
End With

Set objCDO.Configuration = iConf
objCDO.From = fromWho
objCDO.To = toWho
objCDO.Subject = Subject
objCDO.HTMLbody = strHTML
'objCDO.AddAttachment
objCDO.Send

'Cleanup
Set ObjCDO = Nothing
Set iConf = Nothing
Set Flds = Nothing
end if

address = CompetitorEmail("Email")
CompetitorEmail.MoveNext
Loop

CompetitorEmail.close
Set CompetitorEmail = Nothing

 

[Fox]

Hiya, thanks for the heads up. I've seen that blip before, but had no
alternative
this time. Time was too short. But, I will try to learn a better way next
time.
Fortunately, I generally only have to send hundreds rather than thousands.
This was by special request. But, it is still not done. I need to make sure
that it sends once and then quits (G) Any ideas are welcomed.

General Info for anyone who cares for the stats:
It wrote near 500 per minute for a few minutes and then settled in
at a consistent near 250 per minute for the next almost 30,000.
I just wish it would quit at 23,897 like it was supposed to

Regards,
Fox

 

[Aaron Bertrand - MVP]

(a) use a VBS script, not an ASP page

(b) grab 500 rows at a time, instead of all 23,000...

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

 

[Fox]

I will look into that. But for now, I do not know how to
create a script that will do that. I do not know how to
access SQL server in VBS only in ASP/ADO.
Also, I do not know how to select to get only
500 hundred at a time, If it is easy enough that
you can point me to a place online to read
or if a sample is possible, then I will surely try
this right now.

The client I have to do this for is about to kill me.
Must go out shortly, very shortly.

Regards,
Fox

 

[Fox]

BTW, I have been reading the links from the linked blip
you sent. It is over my head at this point. Hopefully
I can get someone locally that understands to sit
down with me to help me get a bit more advanced.

Regards,
Fox

 

[Aaron Bertrand - MVP]

I do not know how to

access SQL server in VBS only in ASP/ADO.

Same as in ASP, except lose any server. prefixes on object creations, and
remove any instances of ASP intrinsics (e.g. response, session, etc).

Also, I do not know how to select to get only
500 hundred at a time

Does your table have a primary key?

If so, add a column that indicates whether mail has been sent (or, better
yet, stuff all of the rows into a #temp table with such a column).

Then, loop over this pseudo-code


SELECT TOP 500 email_address, othercolumns FROM #tablename WHERE sent = 0
ORDER BY primarykey
' if not rs.eof
' begin transaction
' send mails
UPDATE #tablename SET sent = 1 WHERE primarykey IN (SELECT TOP 500
primarykey FROM #tablename WHERE sent = 0 ORDER BY primarykey)
' commit transaction
' else
' you're done

 

[Fox]

Thanks, actually I basically understand this.
and it is a possibility I can get it to work.
The only place that is confusing me is where
you have the single apostrophes. I understand
them in ASP to be comments. But all of yours
look like commands except the "your're done".
Can you explain shortly about the sectioins with
apostrophe and I think from there I can manage.
Are all but the "you're done" literal commands ?
Remember, I have limited knowledge, sorry !

Thanks,
Fox

 

[Fox]

If you are still there ))
Is "primary key" literal or needing a name.
I have not worked with primary keys.
In comparison I worked with Paradox years
ago and I think it was quite different.

Fox

 

[Aaron Bertrand - MVP]

Is "primary key" literal or needing a name.

Primary key needs a name. Do you have a column where one specific value can
represent exactly one row?

 

[Aaron Bertrand - MVP]

The comments represent pseudo-code. You're going to have to do some of this
work yourself; no offense, but most of us are a little too busy to code your
solution for you, end-to-end. Having limited knowledge should not be used
as such a crutch or excuse... having a requirement, and stumbling along the
way, is the best reason to learn.

 

[Fox]

Thanks for all your help. This is the info I needed. I surely understand
and I thank you and respect your willingness to help as much as you have.
Now I am more sure that I know what I am looking at and I know what
I will need to do (G)

Regards,
Fox

 

[Fox]

Yes, it is the ID column, auto-generated.
Is the column the name ?

I am sorry to be a pest. Believe me, I am not shirking
the work, I have been working on this script for a week
and spending most nights awake as well. I am definitely
trying to learn and trying hard to do so. Your help has
been immeasurable and greatly appreciated. As you
can understand, abandoning all that I finally figured out is
a major change and this process has to be done today.
I am sorry if I appeared to be asking for too much.
I did not mean to.

Thanks,
Fox