SQL help in a asp.net web application

I have an application that I'm using to send bulk emails. The email information is coming from an sql server table. After the email is sent, I'm updating the table to mark it sent. I'm trying to remove duplicate emails from the sql query and that is where I'm running into problems. I'm attaching a screenshot of the table design as well as the page that I'm working on. I appreciate your help.


  Here is the code below.









<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" Debug="True" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Web.Mail" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>Administration | Resources | Austin Apartment Store </title>



<link rel="stylesheet" href="global.css" />

<style type="text/css">
<!--
.sent {
      font-family: Verdana, Arial, Helvetica, sans-serif;
      font-size: 13px;
      font-weight: bold;
      
}h1 {font-family: Verdana, Arial, Helvetica, sans-serif;font-size: 13pt;font-weight: bold;color: #FFFFFF;}
  A.foot3:link {font-weight: bold; text-decoration: none; color: #FFFFFF; font-size:13px; font-family: Verdana, Arial, Helvetica, sans-serif}
  A.foot3:active { font-weight: bold; text-decoration: none; color: red; font-size:13px; font-family: Verdana, Arial, Helvetica, sans-serif}
  A.foot3:visited { color: #FFFFFF; text-decoration: none; font-size:13px; font-weight: bold; font-family:  Verdana, Arial, Helvetica, sans-serif}
  A.foot3:hover {font-weight: bold ; text-decoration: underline; font-size:13px ; font-family: Verdana, Arial, Helvetica, sans-serif }
-->
</style>

<script src="global.js" type="text/javascript"></script>

<script language="VB" runat="server">

Private Sub Page_Load(s as object, e as EventArgs)
      If Not IsPostBack Then
            Dim Sent As String = Request.QueryString("sent")
            
            If Sent <> "" Then
                  litSent.Text = "EMAIL SENT"
            End If
            
            LeadsBind()
      End If
End Sub

Sub LeadsBind()
      Dim EmailCheck As String = Request.QueryString("submit")
      
      If EmailCheck <> "" Then
            EmailSend()
      End If
      
        Dim SQL As String = "SELECT * FROM AAS_User_Registrations WHERE Date_Time < DATEADD(hour, -24, getdate()) AND Coalesce(Email1, 0) = 0   ORDER BY Date_Time DESC"
      
      'DECLARE .NET CLASS VARIABLES
      Dim objConn As SqlConnection
            
      'SET DATABASE CONNECTION
      Dim DBConnection As string
        DBConnection  = System.Configuration.ConfigurationManager.ConnectionStrings("connectionString").ConnectionString
            
            
        objConn = New SqlConnection(DBConnection)
       
      
      Dim objDA As New SqlDataAdapter(SQL, objConn)
      Dim objDS As New DataSet()
      
        objDA.Fill(objDS)
       
       
      
      rptrLeads.DataSource = objDS
        rptrLeads.DataBind()
       
        If rptrLeads.Items.Count = 0 Then
            Response.Redirect("Email2.aspx")
        End If
    End Sub

Sub EmailSend()
      'DECLARE YEAR FOR COPYRIGHT
      Dim CurYear As String = Now.Year
      Dim YearString As String
      
      If CurYear = "2006" Then
            YearString = "2006"
      Else
            YearString = "2006-" & CurYear
      End If
      
        Dim SQL As String = "SELECT Registration_ID, Name, Username FROM AAS_User_Registrations WHERE Date_Time < DATEADD(hour, -24, getdate())  AND Coalesce(Email1, 0) = 0 ORDER BY Date_Time DESC"
      Dim objConn As SqlConnection
      Dim DBConnection As string
        DBConnection  = System.Configuration.ConfigurationManager.ConnectionStrings("connectionString").ConnectionString
      objConn = New SqlConnection(DBConnection)
      Dim objCmd As New SqlCommand(SQL, objConn)
      objConn.Open()
      Dim objRdr As SqlDataReader = objCmd.ExecuteReader()
      
      While objRdr.Read()
      
      Dim strName As String = objRdr.Item("Name")
      Dim Username As String = objRdr.Item("Username")
      
      Dim UserEmailBody As String = "<html><head>" & _
      "<div style=""padding-right:8px;padding-left:8px;font-family:sans-serif; font-size:17px; "">" & _
      "<p>Hello " + strName + ",</p>" & _
      "<p>How is your apartment search going? If you haven’t found an  apartment yet, our website <a style=""color:#0076a3"" href=""http://www.austinapartmentstore.com"">www.austinapartmentstore.com</a> has almost 500 for you to choose from.  From  downtown Austin to" & _
       " downtown Hutto, we have apartments in all price ranges and  styles. </p>" & _
      "<p>If you need personalized help, we are just a phone call away  at (512) 828-4470. If you didn’t know already, our service is completely free.  All that we ask is that you include the name Austin Apartment Store on guest  cards and applications.</p>" & _
      "<p>Austin Apartment Store is now offering $100 gift cards when  you rent an apartment using our service. Please visit our gift card page or call  the office for details.</p>" & _
      "<p>If we can be of any help at all, please don’t hesitate to  call.</p>" & _
      "<p>Best of luck,</p>" & _
      "<p>Aaron Davis<br />" & _
       "Owner/Broker<br />" & _
        "Austin Apartment Store<br />" & _
        "512-828-4470</p>" & _
      "</body>" & _
      "</html>"
     

      
      
      'EMAIL TO USER
      Dim user_registration As New System.Net.Mail.MailMessage()
      




'Dim  recipient As MailAddress= new MailAddress(txtToEmail.Text)myMessage.To.Add(recipient)



                user_registration.Subject = "Get a Apartment Fast and Easy Through Austin Apartment Store"
                user_registration.Body = UserEmailBody
                        user_registration.From = new System.Net.Mail.MailAddress("Austin Apartment Store <aaron@austinapartmentstore.com>")
                        Dim  recipient As System.Net.Mail.MailAddress= new System.Net.Mail.MailAddress(Username)
                        user_registration.To.Add(recipient)
                user_registration.IsBodyHtml = True

                   Dim smtp As New Net.Mail.SmtpClient
                   
                smtp.Send(user_registration)
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      Dim RecSQL As String = "UPDATE AAS_User_Registrations SET Email1 = '1',Email1DateSent = getdate() WHERE Registration_ID = '" & objRdr.Item("Registration_ID") & "'"
      Dim objConn1 As SqlConnection
      Dim DBConnection1 As string
        DBConnection1  = System.Configuration.ConfigurationManager.ConnectionStrings("connectionString").ConnectionString
      objConn1 = New SqlConnection(DBConnection)
      Dim objCmd1 As New SqlCommand(RecSQL, objConn1)
      objConn1.Open()
      objCmd1.Executenonquery()
      
      objConn1.close()
      
      '
      
      End While
      objConn.close()
        Response.Redirect("Email2.aspx")
End Sub

</script>

</head>

<body>

                  <h1>
                Email 1</h1>
                  
                  <h2><a href="Email1.aspx?submit=1" title="SEND MASS EMAIL">SEND MASS EMAIL</a></h2>
                  
                  <p><a href="/admin" class="foot3">Back</a>
                  
                  <p class="sent"><asp:Literal id="litSent"  runat="server" /></p>
                  
                  <table style="width: 98%; margin: 10px" cellspacing="0" cellpadding="0">
                              <tr style="background: #417eb4">
                                    <td style="height: 30px; width: 175px; color: #ffffff; vertical-align: middle; padding-left: 10px; border-top: 1px solid #dceffe; border-bottom: 1px solid #386c9b; border-left: 1px solid #dceffe"><strong>Registration Date</strong></td>
                                    <td style="width: 175px; vertical-align: middle; border-top: 1px solid #dceffe; border-bottom: 1px solid #386c9b; color: #ffffff; padding-left: 25px"><strong>Name</strong></td>
                                    <td style="width: 135px; vertical-align: middle; border-top: 1px solid #dceffe; border-bottom: 1px solid #386c9b; color: #ffffff; padding-left: 10px;"><strong>IP</strong></td>
                                    <td style="width: 230px; vertical-align: middle; border-top: 1px solid #dceffe; border-bottom: 1px solid #386c9b; color: #ffffff; padding-left: 20px"><strong>Email</strong></td>
                                    <td style="width: 150px; vertical-align: middle; border-top: 1px solid #dceffe; border-bottom: 1px solid #386c9b; color: #ffffff; padding-left: 10px"><strong>Phone</strong></td>
                                    
                                    
                              </tr>
                              
                              <asp:Repeater id="rptrLeads" runat="server">
                                          <ItemTemplate>
                                                <tr style="background-image: url('/images/layout/bg_unit_row.gif'); background-repeat: repeat-x">
                                                      <td style="width: 175px; height: 85px; vertical-align: middle; border-left: 1px solid #dceffe; padding-left: 10px">
                                                            <%# DataBinder.Eval(Container.DataItem,"Date_Time","{0:MMM dd, yyyy}")%>
                                                      </td>
                                                      <td style="width: 175px; vertical-align: middle; padding-left: 25px">
                                                            <%# Container.DataItem("Name") %>
                                                      </td>
                                                      <td style="width: 135px; vertical-align: middle; padding-left: 10px">
                                                            <a href="http://www.dnsstuff.com/tools/whois.ch?ip=<%# Container.DataItem("IP") %>" rel="x" title="WhoIs Record"><%# Container.DataItem("IP") %></a>
                                                      </td>
                                                      <td style="width: 230px; vertical-align: middle; padding-left: 20px">
                                                            <a href="mailto:<%# Container.DataItem("Username") %>" title="Email"><%# Container.DataItem("Username") %></a>
                                                      </td>
                                                      <td style="width: 150px; vertical-align: middle">
                                                            <%# Container.DataItem("Phone") %>
                                                      </td>
                                                      
                                                      
                                                </tr>
                                          </ItemTemplate>
                              </asp:Repeater>
                        </table>
            
      
                        
      
            
      


<!-- END MAIN CONTENT -->
      
</body>

</html>
ad3408Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duy PhamFreelance IT ConsultantCommented:
What is the error you got then?

I suspect that the problem is that table rows are locked by DataReader and you won't be able to update those rows while DataReader is still open.

Try to fill records for sending email into DataTable and immediately close the DataReader, then loop through DataTable rows to send emails and then update Email1 flag when each email is sent successfully.
ad3408Author Commented:
Hello,

The problem is that my sql query is pulling duplicate emails from the table. If I had an sql query that would not pull repeat emails, the application would work fine. I'm not receiving an error. Here is my SQL query:  "SELECT * FROM AAS_User_Registrations WHERE Date_Time < DATEADD(hour, -24, getdate()) AND Coalesce(Email1, 0) = 0   ORDER BY Date_Time DESC". Thank you for your help.
Duy PhamFreelance IT ConsultantCommented:
As I see in your code, you use below query to get email address for sending:
SELECT Registration_ID, Name, Username FROM AAS_User_Registrations WHERE Date_Time < DATEADD(hour, -24, getdate())  AND Coalesce(Email1, 0) = 0 ORDER BY Date_Time DESC

Open in new window


Try to change it to
SELECT DISTINCT Registration_ID, Name, Username FROM AAS_User_Registrations WHERE Date_Time < DATEADD(hour, -24, getdate())  AND Coalesce(Email1, 0) = 0 ORDER BY Date_Time DESC

Open in new window


If you still get duplicated emails (Username), then it seems that you have duplicated Username but different Registration_ID or Name. In this case, you have to decide to choose to send email for only one of Registrations having the same Username (for example: send email for last Registration only).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ad3408Author Commented:
Yes that is the case. I have duplicate emails with different registration ids. Thank you though.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.