asked on

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">

</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>









</body>

</html>

SOLUTION

Duy Pham

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ad3408

ASKER

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.

ASKER CERTIFIED SOLUTION