发送电子邮件到特殊的电子邮件
我有一个经典的ASP页面,让用户搜索性能和结果页,然后电子邮件谁具有匹配性的地产代理,让他们知道,有兴趣的性质租户。发送电子邮件到特殊的电子邮件
有些代理商可能有超过1个属性,在这种情况下,代理只需要接收一封电子邮件,而不是为他们每个属性的电子邮件。目前我的代码正在做后者,我需要它只发送1封电子邮件给每个代理。
在下面的代码中,我有2个记录集; rspropertyresults显示所有匹配的属性,然后我有rsemailagents用于收集代理的电子邮件地址并向他们发送电子邮件。
我想知道如果任何人可以看到我怎么能修改SQL查询rsemailagents每剂送出了1个电子邮件?
<%
[...]
rsemailagents.Source = "SELECT *"
rsemailagents.Source = rsemailagents.Source& "FROM ("
rsemailagents.Source = rsemailagents.Source& " SELECT ContentID"
rsemailagents.Source = rsemailagents.Source& " FROM ("
rsemailagents.Source = rsemailagents.Source& " SELECT ContentID"
rsemailagents.Source = rsemailagents.Source& " FROM VWTenantPropertiesResults"
rsemailagents.Source = rsemailagents.Source& " WHERE ContentStreet = '" & Replace(rsemailagents__varReqStreet, "'", "''") & "'"
rsemailagents.Source = rsemailagents.Source& " UNION ALL"
rsemailagents.Source = rsemailagents.Source& " SELECT ContentID"
rsemailagents.Source = rsemailagents.Source& " FROM VWTenantPropertiesResults"
rsemailagents.Source = rsemailagents.Source& " WHERE ContentTown = '" & Replace(rsemailagents__varReqTown, "'", "''") & "'"
rsemailagents.Source = rsemailagents.Source& " UNION ALL"
rsemailagents.Source = rsemailagents.Source& " SELECT ContentID"
rsemailagents.Source = rsemailagents.Source& " FROM VWTenantPropertiesResults"
rsemailagents.Source = rsemailagents.Source& " WHERE ContentTrimmedPostCode LIKE '" & Replace(varPostcode, "'", "''") & "%'"
rsemailagents.Source = rsemailagents.Source& " ) qi"
rsemailagents.Source = rsemailagents.Source& " GROUP BY"
rsemailagents.Source = rsemailagents.Source& " ContentID"
rsemailagents.Source = rsemailagents.Source& " HAVING COUNT(*) >= 2"
rsemailagents.Source = rsemailagents.Source& " ) q "
rsemailagents.Source = rsemailagents.Source& "JOIN VWTenantPropertiesResults r "
rsemailagents.Source = rsemailagents.Source& "ON r.ContentID = q.ContentID "
rsemailagents.Source = rsemailagents.Source& "WHERE ContentBedrooms BETWEEN " & Replace(rsemailagents__varBedroomsNoMin, "'", "''") & " "
rsemailagents.Source = rsemailagents.Source& "AND " & Replace(rsemailagents__varBedroomsNoMax, "'", "''") & " "
rsemailagents.Source = rsemailagents.Source& "AND ContentPrice BETWEEN " & Replace(rsemailagents__varPriceMin, "'", "''") & " "
rsemailagents.Source = rsemailagents.Source& "AND " & Replace(rsemailagents__varPriceMax, "'", "''") & " " & varSQL & " "
rsemailagents.Source = rsemailagents.Source& "ORDER BY"
rsemailagents.Source = rsemailagents.Source& " ContentPrice " & Replace(rsemailagents__varSortWay, "'", "''") & " "
rsemailagents.Source = rsemailagents.Source& "GROUP BY CustomerEmail"
[...]
%>
在我已经设置了objMail.To如下我的电子邮件代码;
objMail.To = (rsemailagents.Fields.Item("CustomerEmail").Value)
这是VWTenantPropertiesResults代码;
SELECT dbo.VWResidentialLettings.ContentID, dbo.VWResidentialLettings.ContentTitle, dbo.VWResidentialLettings.ContentBriefText,
dbo.VWResidentialLettings.ContentDetails, dbo.VWResidentialLettings.ContentHouseNo, dbo.VWResidentialLettings.ContentStreet,
dbo.VWResidentialLettings.ContentStreet2, dbo.VWResidentialLettings.ContentTown, dbo.VWResidentialLettings.ContentArea,
dbo.VWResidentialLettings.ContentCounty, dbo.VWResidentialLettings.ContentPostCode, dbo.VWResidentialLettings.ContentReference,
dbo.VWResidentialLettings.ContentPrice, dbo.VWResidentialLettings.ContentPricePeriod, dbo.VWResidentialLettings.ContentPriceText,
dbo.VWResidentialLettings.ContentPropertyType, dbo.VWResidentialLettings.PropertyTypeTitle, dbo.VWResidentialLettings.ContentPropertyListType,
dbo.VWResidentialLettings.PropertyListTypeTitle, dbo.VWResidentialLettings.PricePeriodTitle, dbo.VWResidentialLettings.BedRoomNoTitle,
dbo.VWResidentialLettings.ContentBedRooms, dbo.VWResidentialLettings.ContentFurnishing, dbo.VWResidentialLettings.FurnishTypeTitle,
dbo.VWPropertyAgentsActiveSubscriptions.CustomerID, dbo.VWPropertyAgentsActiveSubscriptions.CustomerName,
dbo.VWPropertyAgentsActiveSubscriptions.CustomerBusiness, dbo.VWPropertyAgentsActiveSubscriptions.CustomerAddress1,
dbo.VWPropertyAgentsActiveSubscriptions.CustomerAddress2, dbo.VWPropertyAgentsActiveSubscriptions.CustomerCity,
dbo.VWPropertyAgentsActiveSubscriptions.CustomerArea, dbo.VWPropertyAgentsActiveSubscriptions.CustomerRegion,
dbo.VWPropertyAgentsActiveSubscriptions.CustomerPostalCode, dbo.VWPropertyAgentsActiveSubscriptions.CustomerPhone,
dbo.VWPropertyAgentsActiveSubscriptions.CustomerFax, dbo.VWPropertyAgentsActiveSubscriptions.CustomerURL,
dbo.VWPropertyAgentsActiveSubscriptions.CustomerEmail, dbo.VWResidentialLettings.CountyTitle, dbo.VWResidentialLettings.ContentCreated,
dbo.VWResidentialLettings.ContentUpdated, dbo.VWResidentialLettings.ContentStatus, dbo.VWPropertyImagesList.DocumentFile,
dbo.VWPropertyImagesList.DocumentTitle, dbo.VWResidentialLettings.ContentTrimmedPostCode, dbo.VWPropertyImagesList.ContentStatusTitle
FROM dbo.VWResidentialLettings INNER JOIN
dbo.VWPropertyAgentsActiveSubscriptions ON
dbo.VWResidentialLettings.ContentParentID = dbo.VWPropertyAgentsActiveSubscriptions.CustomerID LEFT OUTER JOIN
dbo.VWPropertyImagesList ON dbo.VWResidentialLettings.ContentID = dbo.VWPropertyImagesList.ContentID
WHERE (dbo.VWResidentialLettings.ContentStatus = 2)
当我在数据库中运行这个查询,它只是返回CustomerEmail列,别无其他;
SELECT DISTINCT CustomerEmail
FROM (
SELECT ContentID
FROM (
SELECT ContentID
FROM VWTenantPropertiesResults
WHERE ContentStreet = 'Hull'
UNION ALL
SELECT ContentID
FROM VWTenantPropertiesResults
WHERE ContentTown = ''
UNION ALL
SELECT ContentID
FROM VWTenantPropertiesResults
WHERE ContentTrimmedPostCode LIKE 'HU7'
) qi
GROUP BY
ContentID
HAVING COUNT(*) >= 2
) q
JOIN VWTenantPropertiesResults r
ON r.ContentID = q.ContentID
WHERE ContentBedrooms BETWEEN 1 AND 10 AND ContentPrice BETWEEN 1 AND 10
把您的查询,此查询
SELECT email
FROM agents
WHERE id IN
(
SELECT agentID
FROM …
)
这将只选择一次每个代理的IN
条款。
@Quassnoi难道是这样? SELECT CustomerEmail FROM VWTenantPropertiesResults WHERE客户id IN( \t SELECT内容识别 \t FROM( \t \t SELECT内容识别 “ \t \t FROM VWTenantPropertiesResults” \t \t WHERE ContentStreet =“”和替换(rsemailagents__varReqStreet, “ '”, “ '”')& “'” \t \t UNION ALL“ \t \t SELECT内容识别” \t \t FROM VWTenantPropertiesResults” \t \t WHERE ContentTown =“ “和替换(rsemailagents__varReqTown, ” '“, ” '“')& ”'“ \t \t UNION ALL” \t \t SELECT内容识别 “ \t \t FROM VWTenantPropertiesResults” \t \t [...] – doubleplusgood 2009-09-23 11:30:18
如果您的电子邮件包含在'VWTenantPropertiesResults'中,您可以在查询中选择“DISTINCT CustomerEmail'。你可以请张贴你的餐桌布局吗? – Quassnoi 2009-09-23 11:37:27
我已经发布了以上用于获取结果的视图。我尝试使用SELECT DISTINCT CustomerEmail但出现以下错误;用于SQL Server的Microsoft OLE DB提供程序错误“80040e14”关键字'SELECT'附近的语法不正确。 541行 – doubleplusgood 2009-09-23 11:42:06
好吧......
尝试创建一个子查询将组的所有特性在一个单一的线。
您可以通过查询造就了一批这样做:
select
contentID , count(*) hits
from
VWTenantPropertiesResults
where
propertyValue in ('A','B','C')
group by
contentID
而且,你很容易受到SQL注入攻击,因为你创建你的SQL不正确转义它。考虑这里的参数化查询或存储过程。
您还可以创建查询的方式很复杂的。您可以用10多个步骤构建一个字符串,并将其分配给属性。考虑在这里使用一个StringBuilder,因为字符串是不可改变的,这可能会导致性能问题(尤其是当这样的事情在循环使用......如果您需要更多然后5个concats,再想使用StringBuilder)
您拥有别名'q'的子查询只有一个字段ContentID。除非你有非常奇怪的命名约定,那不是电子邮件地址。因此,您告诉它“选择CustomerEmail From(没有名为CustomerEmail的字段的类似表格的对象)”。这不可能工作,我很惊讶,它会返回错误信息以外的任何内容。
试着看无论是“IN”子句或“存在”的条款。
也许你应该只发布SQL语句本身,而不是周围的代码。难道你不能只是将SQL踢到存储过程或使用类似DatabaseSpy的东西来测试它? – 2009-09-23 11:24:32
请问您可以发布您的表格结构吗? – Quassnoi 2009-09-23 11:36:03
你能输出并发布给出错误的整个查询吗?我的意思是查询本身,而不是它生成的代码。 – Quassnoi 2009-09-23 11:49:06