[thelist] On Error Resume Next

[Ken Schaefer]

I agree with Peter. This doesn't seem the optimal way to accomplish this task

a) Implicit connection created for the first recordset
b) SQL injection security issue with the second connection
c) Not very performant (hitting DB in a loop)

d) for error handling: Where do you want to handle the error? If this is for
debugging purposes, then I don't think you need On Error Resume Next. If this
is in Production, then you can turn on error handling where-ever you think
there might be an error, and then code some kind of error handler (If
Err.Number <> 0) where you want to handle the exception. Where's the
exception being raised from? The database, or VBScript? If the DB, you may
wish to handle the error in the DB

Cheers
Ken

:  -----Original Message-----
:  From: thelist-bounces at lists.evolt.org [mailto:thelist-
:  bounces at lists.evolt.org] On Behalf Of Peter Brunone (EasyListBox.com)
:  Sent: Thursday, 4 May 2006 1:49 AM
:  To: thelist at lists.evolt.org; thelist at lists.evolt.org
:  Subject: Re: [thelist] On Error Resume Next
:  
:  
:  		WOW.  You're hitting the database for every single email
:  in your recordset?  That sounds like a huge performance hit.
:  
:     Couldn't you just Response.Write the error message along with the
:  pertinent information and then call Response.End?  I'm not sure why
:  you'd need to write the errors to the DB if you're just doing this to
:  debug.
:     Of course if this is a concern for normal production use, then
:  *maybe* it would be warranted...
:  </mumble>
:  
:  Peter
:  
:  				From: "Rob Smith" rob.smith at lexjet.com
:  
:  > Just out of curiosity, what language is this?
:  
:  VBScript (Classic ASP)
:  
:  (I should learn to explain everything at the first email. I'm getting
:  tired of having to go back and give the bigger picture the second
:  time.)
:  
:  I've got a query that returns all customers who have inside credit
:  with
:  our company. I then do a "mail merge" on the customer's email, company
:  name, rep, and rep email that goes like this:
:  
:  Set email_list = Server.CreateObject("ADODB.Recordset")
:  email_list.ActiveConnection = MM_Commerce30_STRING
:  email_list.CursorType = 0
:  email_list.CursorLocation = 2
:  email_list.LockType = 3
:  email_list.Source = "SELECT shopper.shopper_id,
:  shopper.ContactFName, shopper.ContactLName,
:  shopper.email, site_credit.amount / 100.00 AS amount,
:  employees.employee_email, shopper.Company FROM
:  shopper INNER JOIN site_credit ON shopper.shopper_id
:  = site_credit.shopper_id INNER JOIN employees ON
:  shopper.commission_id = employees.employee_id WHERE
:  (site_credit.amount > 0) AND (shopper.shopper_id NOT IN
:  (SELECT shopper_id FROM site_credit_do_not_remind)) AND
:  (shopper.email IS NOT NULL)"
:  email_list.open
:  
:  set conn = CreateObject("ADODB.Connection")
:  conn.Open = "Provider=...connection info..."
:  while not email_list.eof
:  conn.execute("declare @rc int exec @rc = master.dbo.xp_smtp_sendmail
:  @FROM = N'" & employee_email & "', @FROM_NAME = N'LexJet.com', @TO =
:  N'" & cust_email & "', @CC = N'', @BCC = N'" & employee_email & "',
:  @priority = N'', @subject = N'LexJet Inside Credit Reminder', @type
:  = N'text/plain', @message = N'Dear " & contactfname & " " &
:  contactlname & "," & vbcrlf & vbcrlf & "For your convenience, we are
:  sending you this monthly in-site credit update. Your current credit
:  balance is " & formatcurrency(email_list("amount").value,2) & ". You
:  can
:  apply this balance to future LexJet purchases." & vbcrlf & vbcrlf &
:  company & vbcrlf & formatcurrency(email_list("amount").value,2) &
:  vbcrlf
:  & vbcrlf & "Sincerely," & vbcrlf & vbcrlf & "LexJet" & vbcrlf & vbcrlf
:  &
:  "Click or copy and past the following link into your web browser to
:  unsubscribe: " & vbcrlf &
:  "http://www.lexjet.com/lexjet/creditunsubscribe.asp?mscssid=" &
:  email_list("shopper_id").value & "', @server =
:  N'lesjetcorp01.lexjetcorp.com' select RC = @rc")
:  
:  In test mode I overrode the employee_email and cust_email with my
:  email
:  address and all the 370 or so emails went through just fine. However,
:  when I let the employee_email and cust_email default to what the
:  recordset values the code breaks somewhere along the way on the
:  conn.execute line of code.
:  
:  I could either add myself to the BCC list and see where the last email
:  stops, then isolate that customer. (By nature of that beast, the rest
:  of
:  the list doesn't get notified because the code just stops to a
:  grinding
:  hault.
:  
:  The second preferred choice is to do some kind of On Error Resume Next
:  and just flag those failed emails for further investigation and send
:  the
:  remainder of the record set onwards.
:  
:  That's the whole picture,
:  
:  Rob Smith
:  LexJet
:  rob.smith at lexjet.com
:  http://www.lexjet.com
:  (800)453-9538
:  (941)330-1210 Int'l
:  (941)330-1220 Fax
:  1680 Fruitville Road, 3rd Floor
:  Sarasota, FL 34236
:  --
:  
:  * * Please support the community that supports you.  * *
:  http://evolt.org/help_support_evolt/
:  
:  For unsubscribe and other options, including the Tip Harvester
:  and archives of thelist go to: http://lists.evolt.org
:  Workers of the Web, evolt !