ASP, CDONTS and MoveNext RecordSet

[npala2001]

I am using ASP to grab data from an access database then I am using CDONTS to then send an email using the data that I grabbed with ASP from within the access database. The problem is that I am finding it a little confusing on how to move to the next record within the Query using ASP. Below is the script I am written so far - I am not sure if it is correct. I am a newbie at ASP so can you please speak terminology that I can understand. Thanks guys you have helped me in the past I hope you can help me now.


<% Option Explicit

Dim strSubject
Dim objMsg
Dim strBodies

strSubject = "Subject Line"
strBodies = "The body of the Email"

Set adoCon = Server.CreateObject("ADODB.connection")

adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("name of database.mdb")
Set rs = Server.CreateObject("ADODB.Recordset")
strSQL = "Name of Query"
rs.Open strSQL, adoCon
do while not rs.eof
if rs("The Field Within the Query I am Using") = 1 then
rs.movenext
loop

Set objMsg = Server.CreateObject("CDONTS.NewMsg")
objMsg.From = My Email Address
objMsg.To = rs("The Field Within the Query I am Using")
objMsg.CC = strFrom
objMsg.Subject = strSubject
objMsg.BodyFormat = "0"
objMsg.MailFormat = "0"
objMsg.Body = strBodies
objMsg.Importance = "2"
objMsg.Send

end if rs("The Field Within the Query I am Using") =0

Set objMsg = Nothing
rs.Close
Set rs = Nothing
Set adoCon = Nothing

%>

[Roy Sinclair]

rs.movenext is where the next record in the recordset is called and the loop statement will return you to the do while not rs.eof statement so you need to place your code to send the email depending on the value of the field inside that loop.

The usual order is:

Do while not rs.eof ' execute the following loop until the end of the dataset
' tests and other actions performed on database records go here
rs.movenext ' Get next record from the dataset
loop 'return to the beginning of the loop

[npala2001]

I still dont understand? Also does my script look good so far?

[fractalvibes]

You are mving through the entire recordset
[code]
rs.Open strSQL, adoCon
do while not rs.eof
if rs("The Field Within the Query I am Using") = 1 then
rs.movenext
loop
[\code]

And then setting up to do email. At that point, you are past the eof, so you end up sending 1 email with the values in the very last record.

You need to:
Set objMsg = Server.CreateObject("CDONTS.NewMsg")

do while not rs.eof
if rs("The Field Within the Query I am Using") = 1 then
objMsg.From = My Email Address
objMsg.To = rs("The Field Within the Query I am Using")
objMsg.CC = strFrom
objMsg.Subject = strSubject
objMsg.BodyFormat = "0"
objMsg.MailFormat = "0"
objMsg.Body = strBodies
objMsg.Importance = "2"
objMsg.Send
END IF
rs.movenext
loop

fv

[npala2001]

What does "Option Explicit" mean? How can I get this code to run i.e. do I need a HTML to call this ASP page or what? Sorry for the dumb questions

[fractalvibes]

"Option Explicit" means that you must declare all of your variables as :
Dim myVar1
Dim myVar2
and so forth.

Assuming you have this script on your server, just try browsing to it...

fv

[npala2001]

ohh I see now. I keep getting a "Page cannot be displayed error when I try and run the ASP app. Any ideas. Thanks for your help

[Roy Sinclair]

In Internet Explorer:

Tools > Internet Options > Advanced > Show friendly HTTP error messages -- Clear this checkbox. The name for that checkbox is incorrect, it should read as "Hide useful error messages from the user".

Once you've turned off that checkbox and try your page again you should be able to see a more useful error message which will help you to zero in on the exact problem with your page.

[npala2001]

Thanks I will try that and I will tell you how it goes tommrow