|
 |
access_asp thread: HELP...FOR NEXT LOOP...........(i think!)
Message #1 by "Dale James Wright" <dwright@c...> on Wed, 2 Oct 2002 16:47:06
|
|
Hi everyone, any chance of telling me what i am doing wrong with this
code...
I am fairly new to vb and ASP (about 7 months of learning)
I am trying to send an email to all users that have had a call closed, ie
if there is date in a column that matches toady's date, it will send an
email...It sends an email but shows all the records....ie it will send a
separate email to the user, but showing ALL records in that column....
here is the code
<%
Dim oConn
Dim oRS
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
Calls.Forename, CallsHistory.Action, Calls.Surname, Calls.Email,
CallsHistory.Notes, Calls.DateClosed "
SQLtxt = SQLtxt & " FROM Calls LEFT JOIN CallsHistory ON
Calls.Callid = CallsHistory.FKey "
SQLtxt = SQLtxt & " WHERE (((CallsHistory.Action)=4))
ORDER BY Calls.CallID;"
oRS.Open SQLtxt,"DSN=sunrise_db"
If oRS("DateClosed") = Date() Then
Do While Not oRS.EOF
Set objMail = Server.CreateObject("CDONTS.NewMail")
HTML = HTML & "<HTML>"
HTML = HTML & "<HEAD>"
HTML = HTML & "<BODY bgcolor=white>Does
this work?"
HTML = HTML & "" & ors("Forename")
& "<br><br>"
HTML = HTML & "</BODY>"
HTML = HTML & "</HTML>"
objMail.From = "me@m..."
objMail.Subject = "Does it work"
objMail.BodyFormat = 0
objMail.MailFormat = 0
objMail.To = oRS("Email")
objMail.Body = HTML
objMail.Send
Set objMail = Nothing
oRS.MoveNext
Loop
oRs.Close
Response.Write "the email has been
sent"
Else
Response.Write "The email hasnt
been sent"
End if
%>
Any help would be appreciated....
Cheers Dale
Message #2 by "Larry Woods" <larry@l...> on Wed, 2 Oct 2002 09:05:03 -0700
|
|
Just a QUICK glance, but I would change "LEFT" to "INNER" in the
SQL statement and try again. Comment out the 'send' temporarily
and put in a response.write to display what is being sent.
Larry Woods MCSD,MCT
l.woods, inc.
> -----Original Message-----
> From: Dale James Wright [mailto:dwright@c...]
> Sent: Wednesday, October 02, 2002 4:47 PM
> To: Access ASP
> Subject: [access_asp] HELP...FOR NEXT LOOP...........(i think!)
>
>
> Hi everyone, any chance of telling me what i am doing
> wrong with this
> code...
>
> I am fairly new to vb and ASP (about 7 months of learning)
>
> I am trying to send an email to all users that have
> had a call closed, ie
> if there is date in a column that matches toady's
> date, it will send an
> email...It sends an email but shows all the
> records....ie it will send a
> separate email to the user, but showing ALL records in
> that column....
>
> here is the code
>
> <%
>
> Dim oConn
> Dim oRS
>
> Set oConn = Server.CreateObject("ADODB.Connection")
> Set oRS = Server.CreateObject("ADODB.Recordset")
>
> SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
> Calls.Forename, CallsHistory.Action, Calls.Surname,
> Calls.Email,
> CallsHistory.Notes, Calls.DateClosed "
> SQLtxt = SQLtxt & " FROM Calls LEFT JOIN
> CallsHistory ON
> Calls.Callid = CallsHistory.FKey "
> SQLtxt = SQLtxt & " WHERE
> (((CallsHistory.Action)=4))
> ORDER BY Calls.CallID;"
>
> oRS.Open SQLtxt,"DSN=sunrise_db"
>
> If oRS("DateClosed") = Date() Then
>
> Do While Not oRS.EOF
>
> Set objMail
> Server.CreateObject("CDONTS.NewMail")
> HTML = HTML & "<HTML>"
> HTML = HTML & "<HEAD>"
> HTML = HTML & "<BODY
> bgcolor=white>Does
> this work?"
> HTML = HTML & "" &
> ors("Forename")
> & "<br><br>"
> HTML = HTML & "</BODY>"
> HTML = HTML & "</HTML>"
>
> objMail.From = "me@m..."
> objMail.Subject = "Does it work"
>
> objMail.BodyFormat = 0
> objMail.MailFormat = 0
>
> objMail.To = oRS("Email")
> objMail.Body = HTML
> objMail.Send
>
> Set objMail = Nothing
>
> oRS.MoveNext
>
> Loop
>
> oRs.Close
>
> Response.Write
> "the email has been
> sent"
> Else
>
> Response.Write
> "The email hasnt
> been sent"
>
> End if
> %>
>
>
> Any help would be appreciated....
>
> Cheers Dale
>
Message #3 by "Dale James Wright" <dwright@c...> on Wed, 2 Oct 2002 17:51:40
|
|
Thanks for the reply larry, but that dont make a bit difference.....The
emails are being sent to the users, but it is also sending other records,
for instance, say there is 2 records, with different email addresses and
different information, once the condition (date = now) is true and the
records match this, an email is sent to each email address whom meets this
criteria....It should just show the RECORD FOR THAT PERSON, and not the
next record, that is why i think it has something to do with the DO WHILE
NOT Loop..... I would be grateful if anyone has any ideas on this, as my
head is getting really really done in now....
Regards Dale
> Hi everyone, any chance of telling me what i am doing wrong with this
c> ode...
> I am fairly new to vb and ASP (about 7 months of learning)
> I am trying to send an email to all users that have had a call closed,
ie
i> f there is date in a column that matches toady's date, it will send an
e> mail...It sends an email but shows all the records....ie it will send a
s> eparate email to the user, but showing ALL records in that column....
> here is the code
> <%
> Dim oConn
> Dim oRS
>
> Set oConn = Server.CreateObject("ADODB.Connection")
> Set oRS = Server.CreateObject("ADODB.Recordset")
>
> SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
C> alls.Forename, CallsHistory.Action, Calls.Surname, Calls.Email,
C> allsHistory.Notes, Calls.DateClosed "
> SQLtxt = SQLtxt & " FROM Calls LEFT JOIN CallsHistory ON
C> alls.Callid = CallsHistory.FKey "
> SQLtxt = SQLtxt & " WHERE (((CallsHistory.Action)=4))
O> RDER BY Calls.CallID;"
> oRS.Open SQLtxt,"DSN=sunrise_db"
>
> If oRS("DateClosed") = Date() Then
>
> Do While Not oRS.EOF
>
> Set objMail = Server.CreateObject("CDONTS.NewMail")
> HTML = HTML & "<HTML>"
> HTML = HTML & "<HEAD>"
> HTML = HTML & "<BODY bgcolor=white>Does
t> his work?"
> HTML = HTML & "" & ors("Forename")
&> "<br><br>"
> HTML = HTML & "</BODY>"
> HTML = HTML & "</HTML>"
>
> objMail.From = "me@m..."
> objMail.Subject = "Does it work"
>
> objMail.BodyFormat = 0
> objMail.MailFormat = 0
> objMail.To = oRS("Email")
> objMail.Body = HTML
> objMail.Send
>
> Set objMail = Nothing
>
> oRS.MoveNext
>
> Loop
>
> oRs.Close
>
> Response.Write "the email has been
s> ent"
> Else
>
> Response.Write "The email hasnt
b> een sent"
>
> End if
%> >
>
A> ny help would be appreciated....
> Cheers Dale
Message #4 by "Larry Woods" <larry@l...> on Wed, 2 Oct 2002 09:54:34 -0700
|
|
Well, I see another possibility. After opening the recordset you
are testing only the FIRST record to see if the date equals
today. You have NOT sorted the recordset by date--but by CallID
so if the date in the FIRST record is today then you will send
mail. It "looks like" you should be testing each record for the
today's date WITHIN the loop, not outside it--but I don't know
your application so I might be way off.
Hope this helps...
Larry Woods
> -----Original Message-----
> From: Dale James Wright [mailto:dwright@c...]
> Sent: Wednesday, October 02, 2002 5:52 PM
> To: Access ASP
> Subject: [access_asp] Re: HELP...FOR NEXT
> LOOP...........(i think!)
>
>
> Thanks for the reply larry, but that dont make a bit
> difference.....The
> emails are being sent to the users, but it is also
> sending other records,
> for instance, say there is 2 records, with different
> email addresses and
> different information, once the condition (date = now)
> is true and the
> records match this, an email is sent to each email
> address whom meets this
> criteria....It should just show the RECORD FOR THAT
> PERSON, and not the
> next record, that is why i think it has something to
> do with the DO WHILE
> NOT Loop..... I would be grateful if anyone has any
> ideas on this, as my
> head is getting really really done in now....
>
> Regards Dale
>
>
>
>
> > Hi everyone, any chance of telling me what i am
> doing wrong with this
> c> ode...
>
> > I am fairly new to vb and ASP (about 7 months of learning)
>
> > I am trying to send an email to all users that have
> had a call closed,
> ie
> i> f there is date in a column that matches toady's
> date, it will send an
> e> mail...It sends an email but shows all the
> records....ie it will send a
> s> eparate email to the user, but showing ALL records
> in that column....
>
> > here is the code
>
> > <%
>
> > Dim oConn
> > Dim oRS
> >
> > Set oConn = Server.CreateObject("ADODB.Connection")
> > Set oRS = Server.CreateObject("ADODB.Recordset")
> >
> > SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
> C> alls.Forename, CallsHistory.Action, Calls.Surname,
> Calls.Email,
> C> allsHistory.Notes, Calls.DateClosed "
> > SQLtxt = SQLtxt & " FROM Calls LEFT JOIN
> CallsHistory ON
> C> alls.Callid = CallsHistory.FKey "
> > SQLtxt = SQLtxt & " WHERE
> (((CallsHistory.Action)=4))
> O> RDER BY Calls.CallID;"
>
> > oRS.Open SQLtxt,"DSN=sunrise_db"
> >
> > If oRS("DateClosed") = Date() Then
> >
> > Do While Not oRS.EOF
> >
> > Set objMail
> Server.CreateObject("CDONTS.NewMail")
> > HTML = HTML & "<HTML>"
> > HTML = HTML & "<HEAD>"
> > HTML = HTML & "<BODY
> bgcolor=white>Does
> t> his work?"
> > HTML = HTML & "" &
> ors("Forename")
> &> "<br><br>"
> > HTML = HTML & "</BODY>"
> > HTML = HTML & "</HTML>"
> >
> > objMail.From = "me@m..."
> > objMail.Subject = "Does it work"
> >
> > objMail.BodyFormat = 0
> > objMail.MailFormat = 0
>
> > objMail.To = oRS("Email")
> > objMail.Body = HTML
> > objMail.Send
> >
> > Set objMail = Nothing
> >
> > oRS.MoveNext
> >
> > Loop
> >
> > oRs.Close
> >
> > Response.Write
> "the email has been
> s> ent"
> > Else
> >
> > Response.Write
> "The email hasnt
> b> een sent"
> >
> > End if
> %> >
>
> >
> A> ny help would be appreciated....
>
> > Cheers Dale
>
>
Message #5 by "Carl E. Olsen" <carl-olsen@m...> on Wed, 2 Oct 2002 17:42:45 -0500
|
|
Larry is correct. You need to test each record from inside the loop.
Until you get to a oRS.MoveNext, you don't have the next record.
Also, there is an oConn dimensioned and then never opened. You might as
well skip the oConn and just let the oRS.Open make an implicit
connection.
Carl Olsen, MCSE
http://carl-olsen.com/
> -----Original Message-----
> From: Larry Woods [mailto:larry@l...]
> Sent: Wednesday, October 02, 2002 11:55 AM
> To: Access ASP
> Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
>
> Well, I see another possibility. After opening the recordset you
> are testing only the FIRST record to see if the date equals
> today. You have NOT sorted the recordset by date--but by CallID
> so if the date in the FIRST record is today then you will send
> mail. It "looks like" you should be testing each record for the
> today's date WITHIN the loop, not outside it--but I don't know
> your application so I might be way off.
>
> Hope this helps...
>
> Larry Woods
>
> > -----Original Message-----
> > From: Dale James Wright [mailto:dwright@c...]
> > Sent: Wednesday, October 02, 2002 5:52 PM
> > To: Access ASP
> > Subject: [access_asp] Re: HELP...FOR NEXT
> > LOOP...........(i think!)
> >
> >
> > Thanks for the reply larry, but that dont make a bit
> > difference.....The
> > emails are being sent to the users, but it is also
> > sending other records,
> > for instance, say there is 2 records, with different
> > email addresses and
> > different information, once the condition (date = now)
> > is true and the
> > records match this, an email is sent to each email
> > address whom meets this
> > criteria....It should just show the RECORD FOR THAT
> > PERSON, and not the
> > next record, that is why i think it has something to
> > do with the DO WHILE
> > NOT Loop..... I would be grateful if anyone has any
> > ideas on this, as my
> > head is getting really really done in now....
> >
> > Regards Dale
> >
> >
> >
> >
> > > Hi everyone, any chance of telling me what i am
> > doing wrong with this
> > c> ode...
> >
> > > I am fairly new to vb and ASP (about 7 months of learning)
> >
> > > I am trying to send an email to all users that have
> > had a call closed,
> > ie
> > i> f there is date in a column that matches toady's
> > date, it will send an
> > e> mail...It sends an email but shows all the
> > records....ie it will send a
> > s> eparate email to the user, but showing ALL records
> > in that column....
> >
> > > here is the code
> >
> > > <%
> >
> > > Dim oConn
> > > Dim oRS
> > >
> > > Set oConn = Server.CreateObject("ADODB.Connection")
> > > Set oRS = Server.CreateObject("ADODB.Recordset")
> > >
> > > SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
> > C> alls.Forename, CallsHistory.Action, Calls.Surname,
> > Calls.Email,
> > C> allsHistory.Notes, Calls.DateClosed "
> > > SQLtxt = SQLtxt & " FROM Calls LEFT JOIN
> > CallsHistory ON
> > C> alls.Callid = CallsHistory.FKey "
> > > SQLtxt = SQLtxt & " WHERE
> > (((CallsHistory.Action)=4))
> > O> RDER BY Calls.CallID;"
> >
> > > oRS.Open SQLtxt,"DSN=sunrise_db"
> > >
> > > If oRS("DateClosed") = Date() Then
> > >
> > > Do While Not oRS.EOF
> > >
> > > Set objMail
> > Server.CreateObject("CDONTS.NewMail")
> > > HTML = HTML & "<HTML>"
> > > HTML = HTML & "<HEAD>"
> > > HTML = HTML & "<BODY
> > bgcolor=white>Does
> > t> his work?"
> > > HTML = HTML & "" &
> > ors("Forename")
> > &> "<br><br>"
> > > HTML = HTML & "</BODY>"
> > > HTML = HTML & "</HTML>"
> > >
> > > objMail.From = "me@m..."
> > > objMail.Subject = "Does it work"
> > >
> > > objMail.BodyFormat = 0
> > > objMail.MailFormat = 0
> >
> > > objMail.To = oRS("Email")
> > > objMail.Body = HTML
> > > objMail.Send
> > >
> > > Set objMail = Nothing
> > >
> > > oRS.MoveNext
> > >
> > > Loop
> > >
> > > oRs.Close
> > >
> > > Response.Write
> > "the email has been
> > s> ent"
> > > Else
> > >
> > > Response.Write
> > "The email hasnt
> > b> een sent"
> > >
> > > End if
> > %> >
> >
> > >
> > A> ny help would be appreciated....
> >
> > > Cheers Dale
> >
> >
>
>
> to unsubscribe send a blank email to leave-access_asp-
> 1112135Q@p...
Message #6 by "Ken Schaefer" <ken@a...> on Thu, 3 Oct 2002 13:51:47 +1000
|
|
Carl E. Olsen wrote:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Also, there is an oConn dimensioned and then never opened.
: You might as well skip the oConn and just let the oRS.Open
: make an implicit connection.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO, YOU SHOULD NOT DO THIS.
I can't stress this enough.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q191572
INFO: Connection Pool Managament by ADO Objects Called From ASP
Notice that implicit connections are *not* pooled?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html
/pooling2.asp
Pooling the Microsoft Data Access Components
Explains connection/resource pooling in ADO - required reading for everyone
who works with ASP/ADO.
Always explicitly open connections to your databases. Always use these
explicit connections in your recordset and command objects. Do not
implicitly open connections to your databases.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Carl E. Olsen" <carl-olsen@m...>
Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
: Larry is correct. You need to test each record from inside the loop.
: Until you get to a oRS.MoveNext, you don't have the next record.
:
: Also, there is an oConn dimensioned and then never opened. You might as
: well skip the oConn and just let the oRS.Open make an implicit
: connection.
:
: Carl Olsen, MCSE
: http://carl-olsen.com/
:
:
:
: > -----Original Message-----
: > From: Larry Woods [mailto:larry@l...]
: > Sent: Wednesday, October 02, 2002 11:55 AM
: > To: Access ASP
: > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
: >
: > Well, I see another possibility. After opening the recordset you
: > are testing only the FIRST record to see if the date equals
: > today. You have NOT sorted the recordset by date--but by CallID
: > so if the date in the FIRST record is today then you will send
: > mail. It "looks like" you should be testing each record for the
: > today's date WITHIN the loop, not outside it--but I don't know
: > your application so I might be way off.
: >
: > Hope this helps...
: >
: > Larry Woods
: >
: > > -----Original Message-----
: > > From: Dale James Wright [mailto:dwright@c...]
: > > Sent: Wednesday, October 02, 2002 5:52 PM
: > > To: Access ASP
: > > Subject: [access_asp] Re: HELP...FOR NEXT
: > > LOOP...........(i think!)
: > >
: > >
: > > Thanks for the reply larry, but that dont make a bit
: > > difference.....The
: > > emails are being sent to the users, but it is also
: > > sending other records,
: > > for instance, say there is 2 records, with different
: > > email addresses and
: > > different information, once the condition (date = now)
: > > is true and the
: > > records match this, an email is sent to each email
: > > address whom meets this
: > > criteria....It should just show the RECORD FOR THAT
: > > PERSON, and not the
: > > next record, that is why i think it has something to
: > > do with the DO WHILE
: > > NOT Loop..... I would be grateful if anyone has any
: > > ideas on this, as my
: > > head is getting really really done in now....
: > >
: > > Regards Dale
: > >
: > >
: > >
: > >
: > > > Hi everyone, any chance of telling me what i am
: > > doing wrong with this
: > > c> ode...
: > >
: > > > I am fairly new to vb and ASP (about 7 months of learning)
: > >
: > > > I am trying to send an email to all users that have
: > > had a call closed,
: > > ie
: > > i> f there is date in a column that matches toady's
: > > date, it will send an
: > > e> mail...It sends an email but shows all the
: > > records....ie it will send a
: > > s> eparate email to the user, but showing ALL records
: > > in that column....
: > >
: > > > here is the code
: > >
: > > > <%
: > >
: > > > Dim oConn
: > > > Dim oRS
: > > >
: > > > Set oConn = Server.CreateObject("ADODB.Connection")
: > > > Set oRS = Server.CreateObject("ADODB.Recordset")
: > > >
: > > > SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
: > > C> alls.Forename, CallsHistory.Action, Calls.Surname,
: > > Calls.Email,
: > > C> allsHistory.Notes, Calls.DateClosed "
: > > > SQLtxt = SQLtxt & " FROM Calls LEFT JOIN
: > > CallsHistory ON
: > > C> alls.Callid = CallsHistory.FKey "
: > > > SQLtxt = SQLtxt & " WHERE
: > > (((CallsHistory.Action)=4))
: > > O> RDER BY Calls.CallID;"
: > >
: > > > oRS.Open SQLtxt,"DSN=sunrise_db"
: > > >
: > > > If oRS("DateClosed") = Date() Then
: > > >
: > > > Do While Not oRS.EOF
: > > >
: > > > Set objMail
: > > Server.CreateObject("CDONTS.NewMail")
: > > > HTML = HTML & "<HTML>"
: > > > HTML = HTML & "<HEAD>"
: > > > HTML = HTML & "<BODY
: > > bgcolor=white>Does
: > > t> his work?"
: > > > HTML = HTML & "" &
: > > ors("Forename")
: > > &> "<br><br>"
: > > > HTML = HTML & "</BODY>"
: > > > HTML = HTML & "</HTML>"
: > > >
: > > > objMail.From = "me@m..."
: > > > objMail.Subject = "Does it work"
: > > >
: > > > objMail.BodyFormat = 0
: > > > objMail.MailFormat = 0
: > >
: > > > objMail.To = oRS("Email")
: > > > objMail.Body = HTML
: > > > objMail.Send
: > > >
: > > > Set objMail = Nothing
: > > >
: > > > oRS.MoveNext
: > > >
: > > > Loop
: > > >
: > > > oRs.Close
: > > >
: > > > Response.Write
: > > "the email has been
: > > s> ent"
: > > > Else
: > > >
: > > > Response.Write
: > > "The email hasnt
: > > b> een sent"
: > > >
: > > > End if
: > > %> >
: > >
: > > >
: > > A> ny help would be appreciated....
: > >
: > > > Cheers Dale
Message #7 by "Dale James Wright" <dwright@c...> on Thu, 3 Oct 2002 10:31:16
|
|
Guys, that still doesnt work, mybe i aint explaining myself well enough....
What i am trying to do, is to send an email to users in the database whos
date closed field matches todays date....This email will inform them that
there IT related call has been closed...An email is being sent to all
users whos date closed field matches the criteia, but instead of showing
just THEIR RECORD information, it is also showing OTHER Users RECORDS in
the email......So the first user gets an email confirming that the call
has been closed WITH JUST THEIR INFO....THe next user GETS AN EMAIL
CONTAINING THEIR INFO and OTHER users RECORD/INFO as well.... Still not
making Sense? Sorry.... THat was why i was thinking it had something to do
wid the WHILE LOOP....
THanks for the help...
Dale
Message #8 by "Carl E. Olsen" <carl-olsen@m...> on Thu, 3 Oct 2002 06:46:43 -0500
|
|
Hi Ken,
I'm reading "Beginning ASP Databases" published by Wrox. It says it's
okay to use the implicit connection. You have not explained why that is
not a good practice. You have also not supplied the corrected code. In
other words, I'm going to follow the book until I get an explanation.
Carl Olsen, MCSE
Des Moines, Iowa
> -----Original Message-----
> From: Ken Schaefer [mailto:ken@a...]
> Sent: Wednesday, October 02, 2002 10:52 PM
> To: Access ASP
> Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
>
> Carl E. Olsen wrote:
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : Also, there is an oConn dimensioned and then never opened.
> : You might as well skip the oConn and just let the oRS.Open
> : make an implicit connection.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> NO, YOU SHOULD NOT DO THIS.
>
> I can't stress this enough.
>
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;q191572
> INFO: Connection Pool Managament by ADO Objects Called From ASP
> Notice that implicit connections are *not* pooled?
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-
> us/dnmdac/html
> /pooling2.asp
> Pooling the Microsoft Data Access Components
> Explains connection/resource pooling in ADO - required reading for
> everyone
> who works with ASP/ADO.
>
> Always explicitly open connections to your databases. Always use these
> explicit connections in your recordset and command objects. Do not
> implicitly open connections to your databases.
>
> Cheers
> Ken
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Carl E. Olsen" <carl-olsen@m...>
> Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
>
>
> : Larry is correct. You need to test each record from inside the
loop.
> : Until you get to a oRS.MoveNext, you don't have the next record.
> :
> : Also, there is an oConn dimensioned and then never opened. You
might as
> : well skip the oConn and just let the oRS.Open make an implicit
> : connection.
> :
> : Carl Olsen, MCSE
> : http://carl-olsen.com/
> :
> :
> :
> : > -----Original Message-----
> : > From: Larry Woods [mailto:larry@l...]
> : > Sent: Wednesday, October 02, 2002 11:55 AM
> : > To: Access ASP
> : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
think!)
> : >
> : > Well, I see another possibility. After opening the recordset you
> : > are testing only the FIRST record to see if the date equals
> : > today. You have NOT sorted the recordset by date--but by CallID
> : > so if the date in the FIRST record is today then you will send
> : > mail. It "looks like" you should be testing each record for the
> : > today's date WITHIN the loop, not outside it--but I don't know
> : > your application so I might be way off.
> : >
> : > Hope this helps...
> : >
> : > Larry Woods
> : >
> : > > -----Original Message-----
> : > > From: Dale James Wright [mailto:dwright@c...]
> : > > Sent: Wednesday, October 02, 2002 5:52 PM
> : > > To: Access ASP
> : > > Subject: [access_asp] Re: HELP...FOR NEXT
> : > > LOOP...........(i think!)
> : > >
> : > >
> : > > Thanks for the reply larry, but that dont make a bit
> : > > difference.....The
> : > > emails are being sent to the users, but it is also
> : > > sending other records,
> : > > for instance, say there is 2 records, with different
> : > > email addresses and
> : > > different information, once the condition (date = now)
> : > > is true and the
> : > > records match this, an email is sent to each email
> : > > address whom meets this
> : > > criteria....It should just show the RECORD FOR THAT
> : > > PERSON, and not the
> : > > next record, that is why i think it has something to
> : > > do with the DO WHILE
> : > > NOT Loop..... I would be grateful if anyone has any
> : > > ideas on this, as my
> : > > head is getting really really done in now....
> : > >
> : > > Regards Dale
> : > >
> : > >
> : > >
> : > >
> : > > > Hi everyone, any chance of telling me what i am
> : > > doing wrong with this
> : > > c> ode...
> : > >
> : > > > I am fairly new to vb and ASP (about 7 months of learning)
> : > >
> : > > > I am trying to send an email to all users that have
> : > > had a call closed,
> : > > ie
> : > > i> f there is date in a column that matches toady's
> : > > date, it will send an
> : > > e> mail...It sends an email but shows all the
> : > > records....ie it will send a
> : > > s> eparate email to the user, but showing ALL records
> : > > in that column....
> : > >
> : > > > here is the code
> : > >
> : > > > <%
> : > >
> : > > > Dim oConn
> : > > > Dim oRS
> : > > >
> : > > > Set oConn = Server.CreateObject("ADODB.Connection")
> : > > > Set oRS = Server.CreateObject("ADODB.Recordset")
> : > > >
> : > > > SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
> : > > C> alls.Forename, CallsHistory.Action, Calls.Surname,
> : > > Calls.Email,
> : > > C> allsHistory.Notes, Calls.DateClosed "
> : > > > SQLtxt = SQLtxt & " FROM Calls LEFT JOIN
> : > > CallsHistory ON
> : > > C> alls.Callid = CallsHistory.FKey "
> : > > > SQLtxt = SQLtxt & " WHERE
> : > > (((CallsHistory.Action)=4))
> : > > O> RDER BY Calls.CallID;"
> : > >
> : > > > oRS.Open SQLtxt,"DSN=sunrise_db"
> : > > >
> : > > > If oRS("DateClosed") = Date() Then
> : > > >
> : > > > Do While Not oRS.EOF
> : > > >
> : > > > Set objMail
> : > > Server.CreateObject("CDONTS.NewMail")
> : > > > HTML = HTML & "<HTML>"
> : > > > HTML = HTML & "<HEAD>"
> : > > > HTML = HTML & "<BODY
> : > > bgcolor=white>Does
> : > > t> his work?"
> : > > > HTML = HTML & "" &
> : > > ors("Forename")
> : > > &> "<br><br>"
> : > > > HTML = HTML & "</BODY>"
> : > > > HTML = HTML & "</HTML>"
> : > > >
> : > > > objMail.From = "me@m..."
> : > > > objMail.Subject = "Does it work"
> : > > >
> : > > > objMail.BodyFormat = 0
> : > > > objMail.MailFormat = 0
> : > >
> : > > > objMail.To = oRS("Email")
> : > > > objMail.Body = HTML
> : > > > objMail.Send
> : > > >
> : > > > Set objMail = Nothing
> : > > >
> : > > > oRS.MoveNext
> : > > >
> : > > > Loop
> : > > >
> : > > > oRs.Close
> : > > >
> : > > > Response.Write
> : > > "the email has been
> : > > s> ent"
> : > > > Else
> : > > >
> : > > > Response.Write
> : > > "The email hasnt
> : > > b> een sent"
> : > > >
> : > > > End if
> : > > %> >
> : > >
> : > > >
> : > > A> ny help would be appreciated....
> : > >
> : > > > Cheers Dale
>
>
>
> to unsubscribe send a blank email to leave-access_asp-
> 1112135Q@p...
Message #9 by "Ken Schaefer" <ken@a...> on Fri, 4 Oct 2002 13:28:22 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Carl E. Olsen" <carl-olsen@m...>
Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
: Hi Ken,
:
: I'm reading "Beginning ASP Databases" published by Wrox. It says it's
: okay to use the implicit connection. You have not explained why that is
: not a good practice. You have also not supplied the corrected code. In
: other words, I'm going to follow the book until I get an explanation.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Why don't you just go and *read* the links I posted. There is *detailed*
explanations of what "connection pooling" is, and why you should use it. Why
do you want me to spend my time typing up an explanation of what is already
explained, in great detail, on the Microsoft website?!?
The KB article shows *very* clearly that implicitly created connections are
*not* pooled. When you create an implicit connection you have no reference
to the connection, so you can't return it to the connection pool. Instead it
hangs around, unused, but consuming resources, until it eventually times out
(after 60 seconds).
I know that the Wrox book you have says you can use implicit connections,
but it is a very old book. My first copy of Professional ASP v1 said that
you could put connection objects into session scope as well. Does that mean
that it's a good idea? No - development techniques have moved on since then.
If you want actual code, then it's quite simple:
<%
Dim objConn ' as ADODB.Connection
Dim objRS ' as ADODB.Recordset
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strDBConnString
Set objRS = Server.CreateObject("ADODB.Recordset")
' Note, we use a connection *object* here
objRS.Open strSQL, objConn
'
'
objRS.Close
Set objRS = Nothing
' Now we release objConn back to connection pool for reuse
objConn.Close
Set objConn = Nothing
%>
Cheers
Ken
: > -----Original Message-----
: > From: Ken Schaefer [mailto:ken@a...]
: > Sent: Wednesday, October 02, 2002 10:52 PM
: > To: Access ASP
: > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
: >
: > Carl E. Olsen wrote:
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : Also, there is an oConn dimensioned and then never opened.
: > : You might as well skip the oConn and just let the oRS.Open
: > : make an implicit connection.
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: >
: > NO, YOU SHOULD NOT DO THIS.
: >
: > I can't stress this enough.
: >
: > http://support.microsoft.com/default.aspx?scid=kb;EN-US;q191572
: > INFO: Connection Pool Managament by ADO Objects Called From ASP
: > Notice that implicit connections are *not* pooled?
: >
: > http://msdn.microsoft.com/library/default.asp?url=/library/en-
: > us/dnmdac/html
: > /pooling2.asp
: > Pooling the Microsoft Data Access Components
: > Explains connection/resource pooling in ADO - required reading for
: > everyone
: > who works with ASP/ADO.
: >
: > Always explicitly open connections to your databases. Always use these
: > explicit connections in your recordset and command objects. Do not
: > implicitly open connections to your databases.
: >
: > Cheers
: > Ken
: >
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > From: "Carl E. Olsen" <carl-olsen@m...>
: > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
: >
: >
: > : Larry is correct. You need to test each record from inside the
: loop.
: > : Until you get to a oRS.MoveNext, you don't have the next record.
: > :
: > : Also, there is an oConn dimensioned and then never opened. You
: might as
: > : well skip the oConn and just let the oRS.Open make an implicit
: > : connection.
: > :
: > : Carl Olsen, MCSE
: > : http://carl-olsen.com/
: > :
: > :
: > :
: > : > -----Original Message-----
: > : > From: Larry Woods [mailto:larry@l...]
: > : > Sent: Wednesday, October 02, 2002 11:55 AM
: > : > To: Access ASP
: > : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
: think!)
: > : >
: > : > Well, I see another possibility. After opening the recordset you
: > : > are testing only the FIRST record to see if the date equals
: > : > today. You have NOT sorted the recordset by date--but by CallID
: > : > so if the date in the FIRST record is today then you will send
: > : > mail. It "looks like" you should be testing each record for the
: > : > today's date WITHIN the loop, not outside it--but I don't know
: > : > your application so I might be way off.
: > : >
: > : > Hope this helps...
: > : >
: > : > Larry Woods
: > : >
: > : > > -----Original Message-----
: > : > > From: Dale James Wright [mailto:dwright@c...]
: > : > > Sent: Wednesday, October 02, 2002 5:52 PM
: > : > > To: Access ASP
: > : > > Subject: [access_asp] Re: HELP...FOR NEXT
: > : > > LOOP...........(i think!)
: > : > >
: > : > >
: > : > > Thanks for the reply larry, but that dont make a bit
: > : > > difference.....The
: > : > > emails are being sent to the users, but it is also
: > : > > sending other records,
: > : > > for instance, say there is 2 records, with different
: > : > > email addresses and
: > : > > different information, once the condition (date = now)
: > : > > is true and the
: > : > > records match this, an email is sent to each email
: > : > > address whom meets this
: > : > > criteria....It should just show the RECORD FOR THAT
: > : > > PERSON, and not the
: > : > > next record, that is why i think it has something to
: > : > > do with the DO WHILE
: > : > > NOT Loop..... I would be grateful if anyone has any
: > : > > ideas on this, as my
: > : > > head is getting really really done in now....
: > : > >
: > : > > Regards Dale
: > : > >
: > : > >
: > : > >
: > : > >
: > : > > > Hi everyone, any chance of telling me what i am
: > : > > doing wrong with this
: > : > > c> ode...
: > : > >
: > : > > > I am fairly new to vb and ASP (about 7 months of learning)
: > : > >
: > : > > > I am trying to send an email to all users that have
: > : > > had a call closed,
: > : > > ie
: > : > > i> f there is date in a column that matches toady's
: > : > > date, it will send an
: > : > > e> mail...It sends an email but shows all the
: > : > > records....ie it will send a
: > : > > s> eparate email to the user, but showing ALL records
: > : > > in that column....
: > : > >
: > : > > > here is the code
: > : > >
: > : > > > <%
: > : > >
: > : > > > Dim oConn
: > : > > > Dim oRS
: > : > > >
: > : > > > Set oConn = Server.CreateObject("ADODB.Connection")
: > : > > > Set oRS = Server.CreateObject("ADODB.Recordset")
: > : > > >
: > : > > > SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
: > : > > C> alls.Forename, CallsHistory.Action, Calls.Surname,
: > : > > Calls.Email,
: > : > > C> allsHistory.Notes, Calls.DateClosed "
: > : > > > SQLtxt = SQLtxt & " FROM Calls LEFT JOIN
: > : > > CallsHistory ON
: > : > > C> alls.Callid = CallsHistory.FKey "
: > : > > > SQLtxt = SQLtxt & " WHERE
: > : > > (((CallsHistory.Action)=4))
: > : > > O> RDER BY Calls.CallID;"
: > : > >
: > : > > > oRS.Open SQLtxt,"DSN=sunrise_db"
: > : > > >
: > : > > > If oRS("DateClosed") = Date() Then
: > : > > >
: > : > > > Do While Not oRS.EOF
: > : > > >
: > : > > > Set objMail
: > : > > Server.CreateObject("CDONTS.NewMail")
: > : > > > HTML = HTML & "<HTML>"
: > : > > > HTML = HTML & "<HEAD>"
: > : > > > HTML = HTML & "<BODY
: > : > > bgcolor=white>Does
: > : > > t> his work?"
: > : > > > HTML = HTML & "" &
: > : > > ors("Forename")
: > : > > &> "<br><br>"
: > : > > > HTML = HTML & "</BODY>"
: > : > > > HTML = HTML & "</HTML>"
: > : > > >
: > : > > > objMail.From = "me@m..."
: > : > > > objMail.Subject = "Does it work"
: > : > > >
: > : > > > objMail.BodyFormat = 0
: > : > > > objMail.MailFormat = 0
: > : > >
: > : > > > objMail.To = oRS("Email")
: > : > > > objMail.Body = HTML
: > : > > > objMail.Send
: > : > > >
: > : > > > Set objMail = Nothing
: > : > > >
: > : > > > oRS.MoveNext
: > : > > >
: > : > > > Loop
: > : > > >
: > : > > > oRs.Close
: > : > > >
: > : > > > Response.Write
: > : > > "the email has been
: > : > > s> ent"
: > : > > > Else
: > : > > >
: > : > > > Response.Write
: > : > > "The email hasnt
: > : > > b> een sent"
: > : > > >
: > : > > > End if
: > : > > %> >
: > : > >
: > : > > >
: > : > > A> ny help would be appreciated....
: > : > >
: > : > > > Cheers Dale
: >
: >
: >
: > to unsubscribe send a blank email to leave-access_asp-
: > 1112135Q@p...
:
:
:
Message #10 by "Carl E. Olsen" <carl-olsen@m...> on Thu, 3 Oct 2002 22:54:15 -0500
|
|
The book I'm reading explains connection pooling. Read the original
post, and then read my reply. I said that the connection was not being
used, so it was useless. The way the code was written:
oRS.Open sql, "DSN=something"
did not use the connection.
It should have been:
oRS.Open sql, oConn
I don't need to send somebody to a web site to explain it.
Carl
> -----Original Message-----
> From: Ken Schaefer [mailto:ken@a...]
> Sent: Thursday, October 03, 2002 10:28 PM
> To: Access ASP
> Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Carl E. Olsen" <carl-olsen@m...>
> Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
>
>
> : Hi Ken,
> :
> : I'm reading "Beginning ASP Databases" published by Wrox. It says
it's
> : okay to use the implicit connection. You have not explained why
that is
> : not a good practice. You have also not supplied the corrected code.
In
> : other words, I'm going to follow the book until I get an
explanation.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Why don't you just go and *read* the links I posted. There is
*detailed*
> explanations of what "connection pooling" is, and why you should use
it.
> Why
> do you want me to spend my time typing up an explanation of what is
> already
> explained, in great detail, on the Microsoft website?!?
>
> The KB article shows *very* clearly that implicitly created
connections
> are
> *not* pooled. When you create an implicit connection you have no
reference
> to the connection, so you can't return it to the connection pool.
Instead
> it
> hangs around, unused, but consuming resources, until it eventually
times
> out
> (after 60 seconds).
>
> I know that the Wrox book you have says you can use implicit
connections,
> but it is a very old book. My first copy of Professional ASP v1 said
that
> you could put connection objects into session scope as well. Does that
> mean
> that it's a good idea? No - development techniques have moved on since
> then.
>
> If you want actual code, then it's quite simple:
>
> <%
> Dim objConn ' as ADODB.Connection
> Dim objRS ' as ADODB.Recordset
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open strDBConnString
>
> Set objRS = Server.CreateObject("ADODB.Recordset")
>
> ' Note, we use a connection *object* here
> objRS.Open strSQL, objConn
> '
> '
> objRS.Close
> Set objRS = Nothing
>
> ' Now we release objConn back to connection pool for reuse
> objConn.Close
> Set objConn = Nothing
> %>
>
> Cheers
> Ken
>
>
> : > -----Original Message-----
> : > From: Ken Schaefer [mailto:ken@a...]
> : > Sent: Wednesday, October 02, 2002 10:52 PM
> : > To: Access ASP
> : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
think!)
> : >
> : > Carl E. Olsen wrote:
> : >
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : Also, there is an oConn dimensioned and then never opened.
> : > : You might as well skip the oConn and just let the oRS.Open
> : > : make an implicit connection.
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : >
> : > NO, YOU SHOULD NOT DO THIS.
> : >
> : > I can't stress this enough.
> : >
> : > http://support.microsoft.com/default.aspx?scid=kb;EN-US;q191572
> : > INFO: Connection Pool Managament by ADO Objects Called From ASP
> : > Notice that implicit connections are *not* pooled?
> : >
> : > http://msdn.microsoft.com/library/default.asp?url=/library/en-
> : > us/dnmdac/html
> : > /pooling2.asp
> : > Pooling the Microsoft Data Access Components
> : > Explains connection/resource pooling in ADO - required reading for
> : > everyone
> : > who works with ASP/ADO.
> : >
> : > Always explicitly open connections to your databases. Always use
these
> : > explicit connections in your recordset and command objects. Do not
> : > implicitly open connections to your databases.
> : >
> : > Cheers
> : > Ken
> : >
> : >
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > From: "Carl E. Olsen" <carl-olsen@m...>
> : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
think!)
> : >
> : >
> : > : Larry is correct. You need to test each record from inside the
> : loop.
> : > : Until you get to a oRS.MoveNext, you don't have the next record.
> : > :
> : > : Also, there is an oConn dimensioned and then never opened. You
> : might as
> : > : well skip the oConn and just let the oRS.Open make an implicit
> : > : connection.
> : > :
> : > : Carl Olsen, MCSE
> : > : http://carl-olsen.com/
> : > :
> : > :
> : > :
> : > : > -----Original Message-----
> : > : > From: Larry Woods [mailto:larry@l...]
> : > : > Sent: Wednesday, October 02, 2002 11:55 AM
> : > : > To: Access ASP
> : > : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
> : think!)
> : > : >
> : > : > Well, I see another possibility. After opening the recordset
you
> : > : > are testing only the FIRST record to see if the date equals
> : > : > today. You have NOT sorted the recordset by date--but by
CallID
> : > : > so if the date in the FIRST record is today then you will send
> : > : > mail. It "looks like" you should be testing each record for
the
> : > : > today's date WITHIN the loop, not outside it--but I don't know
> : > : > your application so I might be way off.
> : > : >
> : > : > Hope this helps...
> : > : >
> : > : > Larry Woods
> : > : >
> : > : > > -----Original Message-----
> : > : > > From: Dale James Wright [mailto:dwright@c...]
> : > : > > Sent: Wednesday, October 02, 2002 5:52 PM
> : > : > > To: Access ASP
> : > : > > Subject: [access_asp] Re: HELP...FOR NEXT
> : > : > > LOOP...........(i think!)
> : > : > >
> : > : > >
> : > : > > Thanks for the reply larry, but that dont make a bit
> : > : > > difference.....The
> : > : > > emails are being sent to the users, but it is also
> : > : > > sending other records,
> : > : > > for instance, say there is 2 records, with different
> : > : > > email addresses and
> : > : > > different information, once the condition (date = now)
> : > : > > is true and the
> : > : > > records match this, an email is sent to each email
> : > : > > address whom meets this
> : > : > > criteria....It should just show the RECORD FOR THAT
> : > : > > PERSON, and not the
> : > : > > next record, that is why i think it has something to
> : > : > > do with the DO WHILE
> : > : > > NOT Loop..... I would be grateful if anyone has any
> : > : > > ideas on this, as my
> : > : > > head is getting really really done in now....
> : > : > >
> : > : > > Regards Dale
> : > : > >
> : > : > >
> : > : > >
> : > : > >
> : > : > > > Hi everyone, any chance of telling me what i am
> : > : > > doing wrong with this
> : > : > > c> ode...
> : > : > >
> : > : > > > I am fairly new to vb and ASP (about 7 months of learning)
> : > : > >
> : > : > > > I am trying to send an email to all users that have
> : > : > > had a call closed,
> : > : > > ie
> : > : > > i> f there is date in a column that matches toady's
> : > : > > date, it will send an
> : > : > > e> mail...It sends an email but shows all the
> : > : > > records....ie it will send a
> : > : > > s> eparate email to the user, but showing ALL records
> : > : > > in that column....
> : > : > >
> : > : > > > here is the code
> : > : > >
> : > : > > > <%
> : > : > >
> : > : > > > Dim oConn
> : > : > > > Dim oRS
> : > : > > >
> : > : > > > Set oConn = Server.CreateObject("ADODB.Connection")
> : > : > > > Set oRS = Server.CreateObject("ADODB.Recordset")
> : > : > > >
> : > : > > > SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
> : > : > > C> alls.Forename, CallsHistory.Action, Calls.Surname,
> : > : > > Calls.Email,
> : > : > > C> allsHistory.Notes, Calls.DateClosed "
> : > : > > > SQLtxt = SQLtxt & " FROM Calls LEFT JOIN
> : > : > > CallsHistory ON
> : > : > > C> alls.Callid = CallsHistory.FKey "
> : > : > > > SQLtxt = SQLtxt & " WHERE
> : > : > > (((CallsHistory.Action)=4))
> : > : > > O> RDER BY Calls.CallID;"
> : > : > >
> : > : > > > oRS.Open SQLtxt,"DSN=sunrise_db"
> : > : > > >
> : > : > > > If oRS("DateClosed") = Date() Then
> : > : > > >
> : > : > > > Do While Not oRS.EOF
> : > : > > >
> : > : > > > Set objMail
> : > : > > Server.CreateObject("CDONTS.NewMail")
> : > : > > > HTML = HTML & "<HTML>"
> : > : > > > HTML = HTML & "<HEAD>"
> : > : > > > HTML = HTML & "<BODY
> : > : > > bgcolor=white>Does
> : > : > > t> his work?"
> : > : > > > HTML = HTML & "" &
> : > : > > ors("Forename")
> : > : > > &> "<br><br>"
> : > : > > > HTML = HTML & "</BODY>"
> : > : > > > HTML = HTML & "</HTML>"
> : > : > > >
> : > : > > > objMail.From = "me@m..."
> : > : > > > objMail.Subject = "Does it work"
> : > : > > >
> : > : > > > objMail.BodyFormat = 0
> : > : > > > objMail.MailFormat = 0
> : > : > >
> : > : > > > objMail.To = oRS("Email")
> : > : > > > objMail.Body = HTML
> : > : > > > objMail.Send
> : > : > > >
> : > : > > > Set objMail = Nothing
> : > : > > >
> : > : > > > oRS.MoveNext
> : > : > > >
> : > : > > > Loop
> : > : > > >
> : > : > > > oRs.Close
> : > : > > >
> : > : > > > Response.Write
> : > : > > "the email has been
> : > : > > s> ent"
> : > : > > > Else
> : > : > > >
> : > : > > > Response.Write
> : > : > > "The email hasnt
> : > : > > b> een sent"
> : > : > > >
> : > : > > > End if
> : > : > > %> >
> : > : > >
> : > : > > >
> : > : > > A> ny help would be appreciated....
> : > : > >
> : > : > > > Cheers Dale
> : >
> : >
> : >
> : > to unsubscribe send a blank email to leave-access_asp-
> : > 1112135Q@p...
> :
> :
> :
>
>
> to unsubscribe send a blank email to leave-access_asp-
> 1112135Q@p...
Message #11 by "Ken Schaefer" <ken@a...> on Fri, 4 Oct 2002 14:55:55 +1000
|
|
You said to do this (in your original reply)
> : You might as well skip the oConn and just let the oRS.Open
> : make an implicit connection
which is bad. I said it was bad, and pointed out webpages that explained why
it was bad.
Now you want me to reread your original reply. I did so, and saw the above.
Again I say, "do not use implicit connections", implicit connections are
*not* pooled.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Carl E. Olsen" <carl-olsen@m...>
Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
: The book I'm reading explains connection pooling. Read the original
: post, and then read my reply. I said that the connection was not being
: used, so it was useless. The way the code was written:
:
: oRS.Open sql, "DSN=something"
:
: did not use the connection.
:
: It should have been:
:
: oRS.Open sql, oConn
:
: I don't need to send somebody to a web site to explain it.
:
: Carl
:
: > -----Original Message-----
: > From: Ken Schaefer [mailto:ken@a...]
: > Sent: Thursday, October 03, 2002 10:28 PM
: > To: Access ASP
: > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > From: "Carl E. Olsen" <carl-olsen@m...>
: > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
: >
: >
: > : Hi Ken,
: > :
: > : I'm reading "Beginning ASP Databases" published by Wrox. It says
: it's
: > : okay to use the implicit connection. You have not explained why
: that is
: > : not a good practice. You have also not supplied the corrected code.
: In
: > : other words, I'm going to follow the book until I get an
: explanation.
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: >
: > Why don't you just go and *read* the links I posted. There is
: *detailed*
: > explanations of what "connection pooling" is, and why you should use
: it.
: > Why
: > do you want me to spend my time typing up an explanation of what is
: > already
: > explained, in great detail, on the Microsoft website?!?
: >
: > The KB article shows *very* clearly that implicitly created
: connections
: > are
: > *not* pooled. When you create an implicit connection you have no
: reference
: > to the connection, so you can't return it to the connection pool.
: Instead
: > it
: > hangs around, unused, but consuming resources, until it eventually
: times
: > out
: > (after 60 seconds).
: >
: > I know that the Wrox book you have says you can use implicit
: connections,
: > but it is a very old book. My first copy of Professional ASP v1 said
: that
: > you could put connection objects into session scope as well. Does that
: > mean
: > that it's a good idea? No - development techniques have moved on since
: > then.
: >
: > If you want actual code, then it's quite simple:
: >
: > <%
: > Dim objConn ' as ADODB.Connection
: > Dim objRS ' as ADODB.Recordset
: >
: > Set objConn = Server.CreateObject("ADODB.Connection")
: > objConn.Open strDBConnString
: >
: > Set objRS = Server.CreateObject("ADODB.Recordset")
: >
: > ' Note, we use a connection *object* here
: > objRS.Open strSQL, objConn
: > '
: > '
: > objRS.Close
: > Set objRS = Nothing
: >
: > ' Now we release objConn back to connection pool for reuse
: > objConn.Close
: > Set objConn = Nothing
: > %>
: >
: > Cheers
: > Ken
: >
: >
: > : > -----Original Message-----
: > : > From: Ken Schaefer [mailto:ken@a...]
: > : > Sent: Wednesday, October 02, 2002 10:52 PM
: > : > To: Access ASP
: > : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
: think!)
: > : >
: > : > Carl E. Olsen wrote:
: > : >
: > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : > : Also, there is an oConn dimensioned and then never opened.
: > : > : You might as well skip the oConn and just let the oRS.Open
: > : > : make an implicit connection.
: > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : >
: > : > NO, YOU SHOULD NOT DO THIS.
: > : >
: > : > I can't stress this enough.
: > : >
: > : > http://support.microsoft.com/default.aspx?scid=kb;EN-US;q191572
: > : > INFO: Connection Pool Managament by ADO Objects Called From ASP
: > : > Notice that implicit connections are *not* pooled?
: > : >
: > : > http://msdn.microsoft.com/library/default.asp?url=/library/en-
: > : > us/dnmdac/html
: > : > /pooling2.asp
: > : > Pooling the Microsoft Data Access Components
: > : > Explains connection/resource pooling in ADO - required reading for
: > : > everyone
: > : > who works with ASP/ADO.
: > : >
: > : > Always explicitly open connections to your databases. Always use
: these
: > : > explicit connections in your recordset and command objects. Do not
: > : > implicitly open connections to your databases.
: > : >
: > : > Cheers
: > : > Ken
: > : >
: > : >
: > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : > From: "Carl E. Olsen" <carl-olsen@m...>
: > : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
: think!)
: > : >
: > : >
: > : > : Larry is correct. You need to test each record from inside the
: > : loop.
: > : > : Until you get to a oRS.MoveNext, you don't have the next record.
: > : > :
: > : > : Also, there is an oConn dimensioned and then never opened. You
: > : might as
: > : > : well skip the oConn and just let the oRS.Open make an implicit
: > : > : connection.
: > : > :
: > : > : Carl Olsen, MCSE
: > : > : http://carl-olsen.com/
: > : > :
: > : > :
: > : > :
: > : > : > -----Original Message-----
: > : > : > From: Larry Woods [mailto:larry@l...]
: > : > : > Sent: Wednesday, October 02, 2002 11:55 AM
: > : > : > To: Access ASP
: > : > : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
: > : think!)
: > : > : >
: > : > : > Well, I see another possibility. After opening the recordset
: you
: > : > : > are testing only the FIRST record to see if the date equals
: > : > : > today. You have NOT sorted the recordset by date--but by
: CallID
: > : > : > so if the date in the FIRST record is today then you will send
: > : > : > mail. It "looks like" you should be testing each record for
: the
: > : > : > today's date WITHIN the loop, not outside it--but I don't know
: > : > : > your application so I might be way off.
: > : > : >
: > : > : > Hope this helps...
: > : > : >
: > : > : > Larry Woods
: > : > : >
: > : > : > > -----Original Message-----
: > : > : > > From: Dale James Wright [mailto:dwright@c...]
: > : > : > > Sent: Wednesday, October 02, 2002 5:52 PM
: > : > : > > To: Access ASP
: > : > : > > Subject: [access_asp] Re: HELP...FOR NEXT
: > : > : > > LOOP...........(i think!)
: > : > : > >
: > : > : > >
: > : > : > > Thanks for the reply larry, but that dont make a bit
: > : > : > > difference.....The
: > : > : > > emails are being sent to the users, but it is also
: > : > : > > sending other records,
: > : > : > > for instance, say there is 2 records, with different
: > : > : > > email addresses and
: > : > : > > different information, once the condition (date = now)
: > : > : > > is true and the
: > : > : > > records match this, an email is sent to each email
: > : > : > > address whom meets this
: > : > : > > criteria....It should just show the RECORD FOR THAT
: > : > : > > PERSON, and not the
: > : > : > > next record, that is why i think it has something to
: > : > : > > do with the DO WHILE
: > : > : > > NOT Loop..... I would be grateful if anyone has any
: > : > : > > ideas on this, as my
: > : > : > > head is getting really really done in now....
: > : > : > >
: > : > : > > Regards Dale
: > : > : > >
: > : > : > >
: > : > : > >
: > : > : > >
: > : > : > > > Hi everyone, any chance of telling me what i am
: > : > : > > doing wrong with this
: > : > : > > c> ode...
: > : > : > >
: > : > : > > > I am fairly new to vb and ASP (about 7 months of learning)
: > : > : > >
: > : > : > > > I am trying to send an email to all users that have
: > : > : > > had a call closed,
: > : > : > > ie
: > : > : > > i> f there is date in a column that matches toady's
: > : > : > > date, it will send an
: > : > : > > e> mail...It sends an email but shows all the
: > : > : > > records....ie it will send a
: > : > : > > s> eparate email to the user, but showing ALL records
: > : > : > > in that column....
: > : > : > >
: > : > : > > > here is the code
: > : > : > >
: > : > : > > > <%
: > : > : > >
: > : > : > > > Dim oConn
: > : > : > > > Dim oRS
: > : > : > > >
: > : > : > > > Set oConn = Server.CreateObject("ADODB.Connection")
: > : > : > > > Set oRS = Server.CreateObject("ADODB.Recordset")
: > : > : > > >
: > : > : > > > SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
: > : > : > > C> alls.Forename, CallsHistory.Action, Calls.Surname,
: > : > : > > Calls.Email,
: > : > : > > C> allsHistory.Notes, Calls.DateClosed "
: > : > : > > > SQLtxt = SQLtxt & " FROM Calls LEFT JOIN
: > : > : > > CallsHistory ON
: > : > : > > C> alls.Callid = CallsHistory.FKey "
: > : > : > > > SQLtxt = SQLtxt & " WHERE
: > : > : > > (((CallsHistory.Action)=4))
: > : > : > > O> RDER BY Calls.CallID;"
: > : > : > >
: > : > : > > > oRS.Open SQLtxt,"DSN=sunrise_db"
: > : > : > > >
: > : > : > > > If oRS("DateClosed") = Date() Then
: > : > : > > >
: > : > : > > > Do While Not oRS.EOF
: > : > : > > >
: > : > : > > > Set objMail
: > : > : > > Server.CreateObject("CDONTS.NewMail")
: > : > : > > > HTML = HTML & "<HTML>"
: > : > : > > > HTML = HTML & "<HEAD>"
: > : > : > > > HTML = HTML & "<BODY
: > : > : > > bgcolor=white>Does
: > : > : > > t> his work?"
: > : > : > > > HTML = HTML & "" &
: > : > : > > ors("Forename")
: > : > : > > &> "<br><br>"
: > : > : > > > HTML = HTML & "</BODY>"
: > : > : > > > HTML = HTML & "</HTML>"
: > : > : > > >
: > : > : > > > objMail.From = "me@m..."
: > : > : > > > objMail.Subject = "Does it work"
: > : > : > > >
: > : > : > > > objMail.BodyFormat = 0
: > : > : > > > objMail.MailFormat = 0
: > : > : > >
: > : > : > > > objMail.To = oRS("Email")
: > : > : > > > objMail.Body = HTML
: > : > : > > > objMail.Send
: > : > : > > >
: > : > : > > > Set objMail = Nothing
: > : > : > > >
: > : > : > > > oRS.MoveNext
: > : > : > > >
: > : > : > > > Loop
: > : > : > > >
: > : > : > > > oRs.Close
: > : > : > > >
: > : > : > > > Response.Write
: > : > : > > "the email has been
: > : > : > > s> ent"
: > : > : > > > Else
: > : > : > > >
: > : > : > > > Response.Write
: > : > : > > "The email hasnt
: > : > : > > b> een sent"
: > : > : > > >
: > : > : > > > End if
: > : > : > > %> >
: > : > : > >
: > : > : > > >
: > : > : > > A> ny help would be appreciated....
: > : > : > >
: > : > : > > > Cheers Dale
: > : >
: > : >
: > : >
: > : > to unsubscribe send a blank email to leave-access_asp-
: > : > 1112135Q@p...
: > :
: > :
: > :
: >
: >
: > to unsubscribe send a blank email to leave-access_asp-
: > 1112135Q@p...
:
:
:
Message #12 by "Carl E. Olsen" <carl-olsen@m...> on Fri, 4 Oct 2002 07:03:06 -0500
|
|
Here's the info from the first web page you provided, which shows that
an implicit connection (which is the same as the examples in the
Beginning ASP Databases book) does return the ADO connection to the
connection pool.
1. Is the ADO connection (Conn) returned to the connection pool?
2. What statement returns the connection to the pool?
|Recordset |Recordset |Command |Command
|created |created |created |created
|with |with |with |with
|Server. |CreateObject |Server.
|CreateObject
|CreateObject | |CreateObject |
-----------------------------------------------------------------------
Explicit |1.Yes |1.Yes |1.Yes |1.Yes
Connection |2.Conn.Close |2.Conn.Close |2.Conn.Close
|2.Conn.Close
created with | | | |
Server. | | | |
CreateObject | | | |
-----------------------------------------------------------------------
Explicit |1.Yes |1.Yes |1.Yes |1.Yes
Connection |2.Conn.Close |2.Conn.Close |2.Conn.Close
|2.Conn.Close
created with | | | |
CreateObject | | |
------------------------------------------------------------------------
---
Implicit |1.No |1.Yes |1.No |1.No
Connection |2.N/A |2.Set |2.N/A |2.N/A
| | Recordset | |
| | = Nothing | |
------------------------------------------------------------------------
---
> -----Original Message-----
> From: Ken Schaefer [mailto:ken@a...]
> Sent: Thursday, October 03, 2002 11:56 PM
> To: Access ASP
> Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
>
> You said to do this (in your original reply)
>
> > : You might as well skip the oConn and just let the oRS.Open
> > : make an implicit connection
>
> which is bad. I said it was bad, and pointed out webpages that
explained
> why
> it was bad.
> Now you want me to reread your original reply. I did so, and saw the
> above.
> Again I say, "do not use implicit connections", implicit connections
are
> *not* pooled.
>
> Cheers
> Ken
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Carl E. Olsen" <carl-olsen@m...>
> Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
>
>
> : The book I'm reading explains connection pooling. Read the original
> : post, and then read my reply. I said that the connection was not
being
> : used, so it was useless. The way the code was written:
> :
> : oRS.Open sql, "DSN=something"
> :
> : did not use the connection.
> :
> : It should have been:
> :
> : oRS.Open sql, oConn
> :
> : I don't need to send somebody to a web site to explain it.
> :
> : Carl
> :
> : > -----Original Message-----
> : > From: Ken Schaefer [mailto:ken@a...]
> : > Sent: Thursday, October 03, 2002 10:28 PM
> : > To: Access ASP
> : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
think!)
> : >
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > From: "Carl E. Olsen" <carl-olsen@m...>
> : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
think!)
> : >
> : >
> : > : Hi Ken,
> : > :
> : > : I'm reading "Beginning ASP Databases" published by Wrox. It
says
> : it's
> : > : okay to use the implicit connection. You have not explained why
> : that is
> : > : not a good practice. You have also not supplied the corrected
code.
> : In
> : > : other words, I'm going to follow the book until I get an
> : explanation.
> : >
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : >
> : > Why don't you just go and *read* the links I posted. There is
> : *detailed*
> : > explanations of what "connection pooling" is, and why you should
use
> : it.
> : > Why
> : > do you want me to spend my time typing up an explanation of what
is
> : > already
> : > explained, in great detail, on the Microsoft website?!?
> : >
> : > The KB article shows *very* clearly that implicitly created
> : connections
> : > are
> : > *not* pooled. When you create an implicit connection you have no
> : reference
> : > to the connection, so you can't return it to the connection pool.
> : Instead
> : > it
> : > hangs around, unused, but consuming resources, until it eventually
> : times
> : > out
> : > (after 60 seconds).
> : >
> : > I know that the Wrox book you have says you can use implicit
> : connections,
> : > but it is a very old book. My first copy of Professional ASP v1
said
> : that
> : > you could put connection objects into session scope as well. Does
that
> : > mean
> : > that it's a good idea? No - development techniques have moved on
since
> : > then.
> : >
> : > If you want actual code, then it's quite simple:
> : >
> : > <%
> : > Dim objConn ' as ADODB.Connection
> : > Dim objRS ' as ADODB.Recordset
> : >
> : > Set objConn = Server.CreateObject("ADODB.Connection")
> : > objConn.Open strDBConnString
> : >
> : > Set objRS = Server.CreateObject("ADODB.Recordset")
> : >
> : > ' Note, we use a connection *object* here
> : > objRS.Open strSQL, objConn
> : > '
> : > '
> : > objRS.Close
> : > Set objRS = Nothing
> : >
> : > ' Now we release objConn back to connection pool for reuse
> : > objConn.Close
> : > Set objConn = Nothing
> : > %>
> : >
> : > Cheers
> : > Ken
> : >
> : >
> : > : > -----Original Message-----
> : > : > From: Ken Schaefer [mailto:ken@a...]
> : > : > Sent: Wednesday, October 02, 2002 10:52 PM
> : > : > To: Access ASP
> : > : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
> : think!)
> : > : >
> : > : > Carl E. Olsen wrote:
> : > : >
> : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : > : Also, there is an oConn dimensioned and then never opened.
> : > : > : You might as well skip the oConn and just let the oRS.Open
> : > : > : make an implicit connection.
> : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : >
> : > : > NO, YOU SHOULD NOT DO THIS.
> : > : >
> : > : > I can't stress this enough.
> : > : >
> : > : >
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q191572
> : > : > INFO: Connection Pool Managament by ADO Objects Called From
ASP
> : > : > Notice that implicit connections are *not* pooled?
> : > : >
> : > : > http://msdn.microsoft.com/library/default.asp?url=/library/en-
> : > : > us/dnmdac/html
> : > : > /pooling2.asp
> : > : > Pooling the Microsoft Data Access Components
> : > : > Explains connection/resource pooling in ADO - required reading
for
> : > : > everyone
> : > : > who works with ASP/ADO.
> : > : >
> : > : > Always explicitly open connections to your databases. Always
use
> : these
> : > : > explicit connections in your recordset and command objects. Do
not
> : > : > implicitly open connections to your databases.
> : > : >
> : > : > Cheers
> : > : > Ken
> : > : >
> : > : >
> : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : > From: "Carl E. Olsen" <carl-olsen@m...>
> : > : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
> : think!)
> : > : >
> : > : >
> : > : > : Larry is correct. You need to test each record from inside
the
> : > : loop.
> : > : > : Until you get to a oRS.MoveNext, you don't have the next
record.
> : > : > :
> : > : > : Also, there is an oConn dimensioned and then never opened.
You
> : > : might as
> : > : > : well skip the oConn and just let the oRS.Open make an
implicit
> : > : > : connection.
> : > : > :
> : > : > : Carl Olsen, MCSE
> : > : > : http://carl-olsen.com/
> : > : > :
> : > : > :
> : > : > :
> : > : > : > -----Original Message-----
> : > : > : > From: Larry Woods [mailto:larry@l...]
> : > : > : > Sent: Wednesday, October 02, 2002 11:55 AM
> : > : > : > To: Access ASP
> : > : > : > Subject: [access_asp] Re: HELP...FOR NEXT
LOOP...........(i
> : > : think!)
> : > : > : >
> : > : > : > Well, I see another possibility. After opening the
recordset
> : you
> : > : > : > are testing only the FIRST record to see if the date
equals
> : > : > : > today. You have NOT sorted the recordset by date--but by
> : CallID
> : > : > : > so if the date in the FIRST record is today then you will
send
> : > : > : > mail. It "looks like" you should be testing each record
for
> : the
> : > : > : > today's date WITHIN the loop, not outside it--but I don't
know
> : > : > : > your application so I might be way off.
> : > : > : >
> : > : > : > Hope this helps...
> : > : > : >
> : > : > : > Larry Woods
> : > : > : >
> : > : > : > > -----Original Message-----
> : > : > : > > From: Dale James Wright
[mailto:dwright@c...]
> : > : > : > > Sent: Wednesday, October 02, 2002 5:52 PM
> : > : > : > > To: Access ASP
> : > : > : > > Subject: [access_asp] Re: HELP...FOR NEXT
> : > : > : > > LOOP...........(i think!)
> : > : > : > >
> : > : > : > >
> : > : > : > > Thanks for the reply larry, but that dont make a bit
> : > : > : > > difference.....The
> : > : > : > > emails are being sent to the users, but it is also
> : > : > : > > sending other records,
> : > : > : > > for instance, say there is 2 records, with different
> : > : > : > > email addresses and
> : > : > : > > different information, once the condition (date = now)
> : > : > : > > is true and the
> : > : > : > > records match this, an email is sent to each email
> : > : > : > > address whom meets this
> : > : > : > > criteria....It should just show the RECORD FOR THAT
> : > : > : > > PERSON, and not the
> : > : > : > > next record, that is why i think it has something to
> : > : > : > > do with the DO WHILE
> : > : > : > > NOT Loop..... I would be grateful if anyone has any
> : > : > : > > ideas on this, as my
> : > : > : > > head is getting really really done in now....
> : > : > : > >
> : > : > : > > Regards Dale
> : > : > : > >
> : > : > : > >
> : > : > : > >
> : > : > : > >
> : > : > : > > > Hi everyone, any chance of telling me what i am
> : > : > : > > doing wrong with this
> : > : > : > > c> ode...
> : > : > : > >
> : > : > : > > > I am fairly new to vb and ASP (about 7 months of
learning)
> : > : > : > >
> : > : > : > > > I am trying to send an email to all users that have
> : > : > : > > had a call closed,
> : > : > : > > ie
> : > : > : > > i> f there is date in a column that matches toady's
> : > : > : > > date, it will send an
> : > : > : > > e> mail...It sends an email but shows all the
> : > : > : > > records....ie it will send a
> : > : > : > > s> eparate email to the user, but showing ALL records
> : > : > : > > in that column....
> : > : > : > >
> : > : > : > > > here is the code
> : > : > : > >
> : > : > : > > > <%
> : > : > : > >
> : > : > : > > > Dim oConn
> : > : > : > > > Dim oRS
> : > : > : > > >
> : > : > : > > > Set oConn = Server.CreateObject("ADODB.Connection")
> : > : > : > > > Set oRS = Server.CreateObject("ADODB.Recordset")
> : > : > : > > >
> : > : > : > > > SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
> : > : > : > > C> alls.Forename, CallsHistory.Action, Calls.Surname,
> : > : > : > > Calls.Email,
> : > : > : > > C> allsHistory.Notes, Calls.DateClosed "
> : > : > : > > > SQLtxt = SQLtxt & " FROM Calls LEFT JOIN
> : > : > : > > CallsHistory ON
> : > : > : > > C> alls.Callid = CallsHistory.FKey "
> : > : > : > > > SQLtxt = SQLtxt & " WHERE
> : > : > : > > (((CallsHistory.Action)=4))
> : > : > : > > O> RDER BY Calls.CallID;"
> : > : > : > >
> : > : > : > > > oRS.Open SQLtxt,"DSN=sunrise_db"
> : > : > : > > >
> : > : > : > > > If oRS("DateClosed") = Date() Then
> : > : > : > > >
> : > : > : > > > Do While Not oRS.EOF
> : > : > : > > >
> : > : > : > > > Set objMail
> : > : > : > > Server.CreateObject("CDONTS.NewMail")
> : > : > : > > > HTML = HTML & "<HTML>"
> : > : > : > > > HTML = HTML & "<HEAD>"
> : > : > : > > > HTML = HTML & "<BODY
> : > : > : > > bgcolor=white>Does
> : > : > : > > t> his work?"
> : > : > : > > > HTML = HTML & "" &
> : > : > : > > ors("Forename")
> : > : > : > > &> "<br><br>"
> : > : > : > > > HTML = HTML & "</BODY>"
> : > : > : > > > HTML = HTML & "</HTML>"
> : > : > : > > >
> : > : > : > > > objMail.From = "me@m..."
> : > : > : > > > objMail.Subject = "Does it work"
> : > : > : > > >
> : > : > : > > > objMail.BodyFormat = 0
> : > : > : > > > objMail.MailFormat = 0
> : > : > : > >
> : > : > : > > > objMail.To = oRS("Email")
> : > : > : > > > objMail.Body = HTML
> : > : > : > > > objMail.Send
> : > : > : > > >
> : > : > : > > > Set objMail = Nothing
> : > : > : > > >
> : > : > : > > > oRS.MoveNext
> : > : > : > > >
> : > : > : > > > Loop
> : > : > : > > >
> : > : > : > > > oRs.Close
> : > : > : > > >
> : > : > : > > > Response.Write
> : > : > : > > "the email has been
> : > : > : > > s> ent"
> : > : > : > > > Else
> : > : > : > > >
> : > : > : > > > Response.Write
> : > : > : > > "The email hasnt
> : > : > : > > b> een sent"
> : > : > : > > >
> : > : > : > > > End if
> : > : > : > > %> >
> : > : > : > >
> : > : > : > > >
> : > : > : > > A> ny help would be appreciated....
> : > : > : > >
> : > : > : > > > Cheers Dale
> : > : >
> : > : >
> : > : >
> : > : > to unsubscribe send a blank email to leave-access_asp-
> : > : > 1112135Q@p...
> : > :
> : > :
> : > :
> : >
> : >
> : > to unsubscribe send a blank email to leave-access_asp-
> : > 1112135Q@p...
> :
> :
> :
>
>
> to unsubscribe send a blank email to leave-access_asp-
> 1112135Q@p...
Message #13 by "Carl E. Olsen" <carl-olsen@m...> on Fri, 4 Oct 2002 07:14:39 -0500
|
|
I will concede that it's best not to use an implicit connection. I did
not notice that the recordset example uses CreateObject, instead of
Server.CreateObject. It's not difficult to use an explicit connection,
and that's what I normally do.
> -----Original Message-----
> From: Carl E. Olsen [mailto:carl-olsen@m...]
> Sent: Friday, October 04, 2002 7:03 AM
> To: Access ASP
> Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
>
> Here's the info from the first web page you provided, which shows that
> an implicit connection (which is the same as the examples in the
> Beginning ASP Databases book) does return the ADO connection to the
> connection pool.
>
> 1. Is the ADO connection (Conn) returned to the connection pool?
> 2. What statement returns the connection to the pool?
>
> |Recordset |Recordset |Command |Command
> |created |created |created |created
> |with |with |with |with
> |Server. |CreateObject |Server.
> |CreateObject
> |CreateObject | |CreateObject |
>
>
-----------------------------------------------------------------------
> Explicit |1.Yes |1.Yes |1.Yes |1.Yes
> Connection |2.Conn.Close |2.Conn.Close |2.Conn.Close
> |2.Conn.Close
> created with | | | |
> Server. | | | |
> CreateObject | | | |
>
>
-----------------------------------------------------------------------
> Explicit |1.Yes |1.Yes |1.Yes |1.Yes
> Connection |2.Conn.Close |2.Conn.Close |2.Conn.Close
> |2.Conn.Close
> created with | | | |
> CreateObject | | |
>
------------------------------------------------------------------------
> ---
> Implicit |1.No |1.Yes |1.No |1.No
> Connection |2.N/A |2.Set |2.N/A |2.N/A
> | | Recordset | |
> | | = Nothing | |
>
------------------------------------------------------------------------
> ---
>
> > -----Original Message-----
> > From: Ken Schaefer [mailto:ken@a...]
> > Sent: Thursday, October 03, 2002 11:56 PM
> > To: Access ASP
> > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
> >
> > You said to do this (in your original reply)
> >
> > > : You might as well skip the oConn and just let the oRS.Open
> > > : make an implicit connection
> >
> > which is bad. I said it was bad, and pointed out webpages that
> explained
> > why
> > it was bad.
> > Now you want me to reread your original reply. I did so, and saw the
> > above.
> > Again I say, "do not use implicit connections", implicit connections
> are
> > *not* pooled.
> >
> > Cheers
> > Ken
> >
> >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > From: "Carl E. Olsen" <carl-olsen@m...>
> > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
> >
> >
> > : The book I'm reading explains connection pooling. Read the
original
> > : post, and then read my reply. I said that the connection was not
> being
> > : used, so it was useless. The way the code was written:
> > :
> > : oRS.Open sql, "DSN=something"
> > :
> > : did not use the connection.
> > :
> > : It should have been:
> > :
> > : oRS.Open sql, oConn
> > :
> > : I don't need to send somebody to a web site to explain it.
> > :
> > : Carl
> > :
> > : > -----Original Message-----
> > : > From: Ken Schaefer [mailto:ken@a...]
> > : > Sent: Thursday, October 03, 2002 10:28 PM
> > : > To: Access ASP
> > : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
> think!)
> > : >
> > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > : > From: "Carl E. Olsen" <carl-olsen@m...>
> > : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
> think!)
> > : >
> > : >
> > : > : Hi Ken,
> > : > :
> > : > : I'm reading "Beginning ASP Databases" published by Wrox. It
> says
> > : it's
> > : > : okay to use the implicit connection. You have not explained
why
> > : that is
> > : > : not a good practice. You have also not supplied the corrected
> code.
> > : In
> > : > : other words, I'm going to follow the book until I get an
> > : explanation.
> > : >
> > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > : >
> > : > Why don't you just go and *read* the links I posted. There is
> > : *detailed*
> > : > explanations of what "connection pooling" is, and why you should
> use
> > : it.
> > : > Why
> > : > do you want me to spend my time typing up an explanation of what
> is
> > : > already
> > : > explained, in great detail, on the Microsoft website?!?
> > : >
> > : > The KB article shows *very* clearly that implicitly created
> > : connections
> > : > are
> > : > *not* pooled. When you create an implicit connection you have no
> > : reference
> > : > to the connection, so you can't return it to the connection
pool.
> > : Instead
> > : > it
> > : > hangs around, unused, but consuming resources, until it
eventually
> > : times
> > : > out
> > : > (after 60 seconds).
> > : >
> > : > I know that the Wrox book you have says you can use implicit
> > : connections,
> > : > but it is a very old book. My first copy of Professional ASP v1
> said
> > : that
> > : > you could put connection objects into session scope as well.
Does
> that
> > : > mean
> > : > that it's a good idea? No - development techniques have moved on
> since
> > : > then.
> > : >
> > : > If you want actual code, then it's quite simple:
> > : >
> > : > <%
> > : > Dim objConn ' as ADODB.Connection
> > : > Dim objRS ' as ADODB.Recordset
> > : >
> > : > Set objConn = Server.CreateObject("ADODB.Connection")
> > : > objConn.Open strDBConnString
> > : >
> > : > Set objRS = Server.CreateObject("ADODB.Recordset")
> > : >
> > : > ' Note, we use a connection *object* here
> > : > objRS.Open strSQL, objConn
> > : > '
> > : > '
> > : > objRS.Close
> > : > Set objRS = Nothing
> > : >
> > : > ' Now we release objConn back to connection pool for reuse
> > : > objConn.Close
> > : > Set objConn = Nothing
> > : > %>
> > : >
> > : > Cheers
> > : > Ken
> > : >
> > : >
> > : > : > -----Original Message-----
> > : > : > From: Ken Schaefer [mailto:ken@a...]
> > : > : > Sent: Wednesday, October 02, 2002 10:52 PM
> > : > : > To: Access ASP
> > : > : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
> > : think!)
> > : > : >
> > : > : > Carl E. Olsen wrote:
> > : > : >
> > : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > : > : > : Also, there is an oConn dimensioned and then never opened.
> > : > : > : You might as well skip the oConn and just let the oRS.Open
> > : > : > : make an implicit connection.
> > : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > : > : >
> > : > : > NO, YOU SHOULD NOT DO THIS.
> > : > : >
> > : > : > I can't stress this enough.
> > : > : >
> > : > : >
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;q191572
> > : > : > INFO: Connection Pool Managament by ADO Objects Called From
> ASP
> > : > : > Notice that implicit connections are *not* pooled?
> > : > : >
> > : > : >
http://msdn.microsoft.com/library/default.asp?url=/library/en-
> > : > : > us/dnmdac/html
> > : > : > /pooling2.asp
> > : > : > Pooling the Microsoft Data Access Components
> > : > : > Explains connection/resource pooling in ADO - required
reading
> for
> > : > : > everyone
> > : > : > who works with ASP/ADO.
> > : > : >
> > : > : > Always explicitly open connections to your databases. Always
> use
> > : these
> > : > : > explicit connections in your recordset and command objects.
Do
> not
> > : > : > implicitly open connections to your databases.
> > : > : >
> > : > : > Cheers
> > : > : > Ken
> > : > : >
> > : > : >
> > : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > : > : > From: "Carl E. Olsen" <carl-olsen@m...>
> > : > : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
> > : think!)
> > : > : >
> > : > : >
> > : > : > : Larry is correct. You need to test each record from
inside
> the
> > : > : loop.
> > : > : > : Until you get to a oRS.MoveNext, you don't have the next
> record.
> > : > : > :
> > : > : > : Also, there is an oConn dimensioned and then never opened.
> You
> > : > : might as
> > : > : > : well skip the oConn and just let the oRS.Open make an
> implicit
> > : > : > : connection.
> > : > : > :
> > : > : > : Carl Olsen, MCSE
> > : > : > : http://carl-olsen.com/
> > : > : > :
> > : > : > :
> > : > : > :
> > : > : > : > -----Original Message-----
> > : > : > : > From: Larry Woods [mailto:larry@l...]
> > : > : > : > Sent: Wednesday, October 02, 2002 11:55 AM
> > : > : > : > To: Access ASP
> > : > : > : > Subject: [access_asp] Re: HELP...FOR NEXT
> LOOP...........(i
> > : > : think!)
> > : > : > : >
> > : > : > : > Well, I see another possibility. After opening the
> recordset
> > : you
> > : > : > : > are testing only the FIRST record to see if the date
> equals
> > : > : > : > today. You have NOT sorted the recordset by date--but
by
> > : CallID
> > : > : > : > so if the date in the FIRST record is today then you
will
> send
> > : > : > : > mail. It "looks like" you should be testing each record
> for
> > : the
> > : > : > : > today's date WITHIN the loop, not outside it--but I
don't
> know
> > : > : > : > your application so I might be way off.
> > : > : > : >
> > : > : > : > Hope this helps...
> > : > : > : >
> > : > : > : > Larry Woods
> > : > : > : >
> > : > : > : > > -----Original Message-----
> > : > : > : > > From: Dale James Wright
> [mailto:dwright@c...]
> > : > : > : > > Sent: Wednesday, October 02, 2002 5:52 PM
> > : > : > : > > To: Access ASP
> > : > : > : > > Subject: [access_asp] Re: HELP...FOR NEXT
> > : > : > : > > LOOP...........(i think!)
> > : > : > : > >
> > : > : > : > >
> > : > : > : > > Thanks for the reply larry, but that dont make a bit
> > : > : > : > > difference.....The
> > : > : > : > > emails are being sent to the users, but it is also
> > : > : > : > > sending other records,
> > : > : > : > > for instance, say there is 2 records, with different
> > : > : > : > > email addresses and
> > : > : > : > > different information, once the condition (date = now)
> > : > : > : > > is true and the
> > : > : > : > > records match this, an email is sent to each email
> > : > : > : > > address whom meets this
> > : > : > : > > criteria....It should just show the RECORD FOR THAT
> > : > : > : > > PERSON, and not the
> > : > : > : > > next record, that is why i think it has something to
> > : > : > : > > do with the DO WHILE
> > : > : > : > > NOT Loop..... I would be grateful if anyone has any
> > : > : > : > > ideas on this, as my
> > : > : > : > > head is getting really really done in now....
> > : > : > : > >
> > : > : > : > > Regards Dale
> > : > : > : > >
> > : > : > : > >
> > : > : > : > >
> > : > : > : > >
> > : > : > : > > > Hi everyone, any chance of telling me what i am
> > : > : > : > > doing wrong with this
> > : > : > : > > c> ode...
> > : > : > : > >
> > : > : > : > > > I am fairly new to vb and ASP (about 7 months of
> learning)
> > : > : > : > >
> > : > : > : > > > I am trying to send an email to all users that have
> > : > : > : > > had a call closed,
> > : > : > : > > ie
> > : > : > : > > i> f there is date in a column that matches toady's
> > : > : > : > > date, it will send an
> > : > : > : > > e> mail...It sends an email but shows all the
> > : > : > : > > records....ie it will send a
> > : > : > : > > s> eparate email to the user, but showing ALL records
> > : > : > : > > in that column....
> > : > : > : > >
> > : > : > : > > > here is the code
> > : > : > : > >
> > : > : > : > > > <%
> > : > : > : > >
> > : > : > : > > > Dim oConn
> > : > : > : > > > Dim oRS
> > : > : > : > > >
> > : > : > : > > > Set oConn = Server.CreateObject("ADODB.Connection")
> > : > : > : > > > Set oRS = Server.CreateObject("ADODB.Recordset")
> > : > : > : > > >
> > : > : > : > > > SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
> > : > : > : > > C> alls.Forename, CallsHistory.Action, Calls.Surname,
> > : > : > : > > Calls.Email,
> > : > : > : > > C> allsHistory.Notes, Calls.DateClosed "
> > : > : > : > > > SQLtxt = SQLtxt & " FROM Calls LEFT JOIN
> > : > : > : > > CallsHistory ON
> > : > : > : > > C> alls.Callid = CallsHistory.FKey "
> > : > : > : > > > SQLtxt = SQLtxt & " WHERE
> > : > : > : > > (((CallsHistory.Action)=4))
> > : > : > : > > O> RDER BY Calls.CallID;"
> > : > : > : > >
> > : > : > : > > > oRS.Open SQLtxt,"DSN=sunrise_db"
> > : > : > : > > >
> > : > : > : > > > If oRS("DateClosed") = Date() Then
> > : > : > : > > >
> > : > : > : > > > Do While Not oRS.EOF
> > : > : > : > > >
> > : > : > : > > > Set objMail
> > : > : > : > > Server.CreateObject("CDONTS.NewMail")
> > : > : > : > > > HTML = HTML & "<HTML>"
> > : > : > : > > > HTML = HTML & "<HEAD>"
> > : > : > : > > > HTML = HTML & "<BODY
> > : > : > : > > bgcolor=white>Does
> > : > : > : > > t> his work?"
> > : > : > : > > > HTML = HTML & "" &
> > : > : > : > > ors("Forename")
> > : > : > : > > &> "<br><br>"
> > : > : > : > > > HTML = HTML & "</BODY>"
> > : > : > : > > > HTML = HTML & "</HTML>"
> > : > : > : > > >
> > : > : > : > > > objMail.From = "me@m..."
> > : > : > : > > > objMail.Subject = "Does it work"
> > : > : > : > > >
> > : > : > : > > > objMail.BodyFormat = 0
> > : > : > : > > > objMail.MailFormat = 0
> > : > : > : > >
> > : > : > : > > > objMail.To = oRS("Email")
> > : > : > : > > > objMail.Body = HTML
> > : > : > : > > > objMail.Send
> > : > : > : > > >
> > : > : > : > > > Set objMail = Nothing
> > : > : > : > > >
> > : > : > : > > > oRS.MoveNext
> > : > : > : > > >
> > : > : > : > > > Loop
> > : > : > : > > >
> > : > : > : > > > oRs.Close
> > : > : > : > > >
> > : > : > : > > > Response.Write
> > : > : > : > > "the email has been
> > : > : > : > > s> ent"
> > : > : > : > > > Else
> > : > : > : > > >
> > : > : > : > > > Response.Write
> > : > : > : > > "The email hasnt
> > : > : > : > > b> een sent"
> > : > : > : > > >
> > : > : > : > > > End if
> > : > : > : > > %> >
> > : > : > : > >
> > : > : > : > > >
> > : > : > : > > A> ny help would be appreciated....
> > : > : > : > >
> > : > : > : > > > Cheers Dale
> > : > : >
> > : > : >
> > : > : >
> > : > : > ---
> > : > : > Change your mail options at http://p2p.wrox.com/manager.asp
or
> > : > : > to unsubscribe send a blank email to leave-access_asp-
> > : > : > 1112135Q@p...
> > : > :
> > : > :
> > : > :
> > : >
> > : >
> > : > to unsubscribe send a blank email to leave-access_asp-
> > : > 1112135Q@p...
> > :
> > :
> > :
> >
> >
> > to unsubscribe send a blank email to leave-access_asp-
> > 1112135Q@p...
>
>
>
> to unsubscribe send a blank email to leave-access_asp-
> 1112135Q@p...
Message #14 by "Carl E. Olsen" <carl-olsen@m...> on Mon, 7 Oct 2002 06:42:32 -0500
|
|
Which books do you recommend?
> -----Original Message-----
> From: Ken Schaefer [mailto:ken@a...]
> Sent: Thursday, October 03, 2002 10:28 PM
> To: Access ASP
> Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Carl E. Olsen" <carl-olsen@m...>
> Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i think!)
>
>
> : Hi Ken,
> :
> : I'm reading "Beginning ASP Databases" published by Wrox. It says
it's
> : okay to use the implicit connection. You have not explained why
that is
> : not a good practice. You have also not supplied the corrected code.
In
> : other words, I'm going to follow the book until I get an
explanation.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Why don't you just go and *read* the links I posted. There is
*detailed*
> explanations of what "connection pooling" is, and why you should use
it.
> Why
> do you want me to spend my time typing up an explanation of what is
> already
> explained, in great detail, on the Microsoft website?!?
>
> The KB article shows *very* clearly that implicitly created
connections
> are
> *not* pooled. When you create an implicit connection you have no
reference
> to the connection, so you can't return it to the connection pool.
Instead
> it
> hangs around, unused, but consuming resources, until it eventually
times
> out
> (after 60 seconds).
>
> I know that the Wrox book you have says you can use implicit
connections,
> but it is a very old book. My first copy of Professional ASP v1 said
that
> you could put connection objects into session scope as well. Does that
> mean
> that it's a good idea? No - development techniques have moved on since
> then.
>
> If you want actual code, then it's quite simple:
>
> <%
> Dim objConn ' as ADODB.Connection
> Dim objRS ' as ADODB.Recordset
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open strDBConnString
>
> Set objRS = Server.CreateObject("ADODB.Recordset")
>
> ' Note, we use a connection *object* here
> objRS.Open strSQL, objConn
> '
> '
> objRS.Close
> Set objRS = Nothing
>
> ' Now we release objConn back to connection pool for reuse
> objConn.Close
> Set objConn = Nothing
> %>
>
> Cheers
> Ken
>
>
> : > -----Original Message-----
> : > From: Ken Schaefer [mailto:ken@a...]
> : > Sent: Wednesday, October 02, 2002 10:52 PM
> : > To: Access ASP
> : > Subject: [access_asp] Re: HELP...FOR NEXT LOOP...........(i
think!)
> : >
> : > Carl E. Olsen wrote:
> : >
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : Also, there is an oConn dimensioned and then never opened.
> : > : You might as well skip the oConn and just let the oRS.Open
> : > : make an implicit connection.
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : >
> : > NO, YOU SHOULD NOT DO THIS.
> : >
> : > I can't stress this enough.
> : >
> : > http://support.microsoft.com/default.aspx?scid=kb;EN-US;q191572
> : > INFO: Connection Pool Managament by ADO Objects Called From ASP
> : > Notice that implicit connections are *not* pooled?
> : >
> : > http://msdn.microsoft.com/library/default.asp?url=/library/en-
> : > us/dnmdac/html
> : > /pooling2.asp
> : > Pooling the Microsoft Data Access Components
> : > Explains connection/resource pooling in ADO - required reading for
> : > everyone
> : > who works with ASP/ADO.
> : >
> : > Always explicitly open connections to your databases. Always use
these
> : > explicit connections in your records | |
View Cart
