tags:

views:

167

answers:

4
+1  Q: 

Sending email with recordset data

Hi there, I've got a classic ASP page listing some properties from a database and with each property there is a small contact form - to enable a user to 'request a callback' from the property agent associated with that property.

However, whichever form I complete - the email to the agent always includes details of the first property on the page, rather than the property that the person is requesting a callback for.

This is the code I have on the listings page (along with the contact form);

<%
Dim rspropertyresults
Dim rspropertyresults_numRows

Set rspropertyresults = Server.CreateObject("ADODB.Recordset")
rspropertyresults.ActiveConnection = MM_dbconn_STRING

rspropertyresults.Source = "SELECT * FROM VWTenantPropertiesResults ORDER BY ContentID DESC"

rspropertyresults.CursorType = 0
rspropertyresults.CursorLocation = 2
rspropertyresults.LockType = 1
rspropertyresults.Open()

rspropertyresults_numRows = 0
%>

<% 
sys_message = ""

If (Request.Form("form_submit") <> "") Then
    Response.Write("<h1>Form Submitted</h1>") 
    %><!--#include file="docallback.asp"--><%
End If %>

<body id="propertyresults">
<% If Not rspropertyresults.EOF Or Not rspropertyresults.BOF Then %>
<% 
While ((RepeatProperties__numRows <> 0) AND (NOT rspropertyresults.EOF)) 
%>
<div class="propertydetails">
<ul>
<li class="price"><% If (rspropertyresults("ContentPrice")) <> "" Then %><%= FormatCurrency((rspropertyresults.Fields.Item("ContentPrice").Value), 2, -2, -2, -2) %> PCM<% End If ' price true %></li>
<li class="address"><%=varFullAddress%></li>
<% If (rspropertyresults.Fields.Item("ContentDetails").Value) <> "" AND (rspropertyresults.Fields.Item("ContentDetails").Value) <> "<p><br /></p>" Then %><li><%=StripHTML(rspropertyresults.Fields.Item("ContentDetails").Value)%></li><% End If ' details true %>
</ul>
</div>

<div class="propertyimage">
    <a class="badge-callback" id="badge-callback<%=(rspropertyresults.Fields.Item("ContentID").Value)%>" title="Request Callback"  href="#">Request Callback</a>

    <div id="panel<%=(rspropertyresults.Fields.Item("ContentID").Value)%>">
     <form action="default.asp" name="frmCallback" id="form_callback" method="post">
      <fieldset>
       <legend><h2>Request a callback</h2></legend>
       <dl>
        <dt><label id="name">Name</label></dt>
        <dd><input id="form_input" type="text" name="txtName" /></dd>
        <dt><label id="email_address">Email</label></dt>
        <dd><input id="form_input" type="text" name="txtEmail" /></dd>
        <dt><label id="telephone">Telephone</label></dt>
        <dd><input id="form_input" type="text" name="txtTelephone" /></dd>
        <input type="hidden" name="form_submit" value="submitted" />
        <dt><input type="submit" value="Request a callback"></dt>
       </dl>
      </fieldset>
     </form>
    </div>
</div>

<% 
  RepeatProperties__index=RepeatProperties__index+1
  RepeatProperties__numRows=RepeatProperties__numRows-1
  rspropertyresults.MoveNext()
Wend
%>

I also have a docallback.asp page that handles the email sending;

<%
    fldName      = replace(request.Form("fldName"),"'","")
    fldTelephone    = replace(request.Form("fldTelephone"),"'","")
    fldEmail  = replace(request.Form("fldEmail"),"'","")

    mBody = "<html><style>body,p,td{font-family:arial;font-size:12px;}</style><body>"
    mBody = mBody & "<p>A tenant has requested a call back for further information regarding the following property:</p>"

    If (rspropertyresults.Fields.Item("ContentHouseNo").Value) <> "" Then
    varFullAddress = (rspropertyresults.Fields.Item("ContentHouseNo").Value) &  " "
    End If
    varFullAddress = varFullAddress & (rspropertyresults.Fields.Item("ContentStreet").Value)
    If (rspropertyresults.Fields.Item("ContentStreet2").Value) <> "" Then
    varFullAddress = varFullAddress & " " & (rspropertyresults.Fields.Item("ContentStreet2").Value)
    End If
    If (rspropertyresults.Fields.Item("ContentTown").Value) <> "" Then
    varFullAddress = varFullAddress & " " &  (rspropertyresults.Fields.Item("ContentTown").Value)
    End If
    If (rspropertyresults.Fields.Item("ContentArea").Value) <> "" Then
    varFullAddress = varFullAddress & " " &  (rspropertyresults.Fields.Item("ContentArea").Value)
    End If
    varFullAddress = varFullAddress & " " &  (rspropertyresults.Fields.Item("ContentPostCode").Value)

    mBody = mBody & "<p>" & varFullAddress & "</p>"

    mBody = mBody & "<p>Their details are:</p>"

    If request.Form("fldName") <> "" Then
     mBody = mBody & "<p>Name: " & request.Form("fldName") & "<br/>"
    End If

    If request.Form("fldTelephone") <> "" Then
     mBody = mBody & "Telephone: " & request.Form("fldTelephone") & "<br/>"
    End If

    If request.Form("fldEmail") <> "" Then
     mBody = mBody & "Email: " & request.Form("fldEmail") & "</p>"
    End If

    mBody = mBody & "<p>" & "<strong>" & "http://www."&amp; varSiteDomain & "</strong>" & "</p>"
    mBody = mBody & "</body></html>"

    strMSSchema = "http://schemas.microsoft.com/cdo/configuration/"
    Set oCdoConfg = Server.CreateObject("CDO.Configuration")
    oCdoConfg.Fields.Item(strMSSchema & "sendusing") = 1 
    oCdoConfg.Fields.Item(strMSSchema & "smtpserver") = ""
    oCdoConfg.Fields.Item(strMSSchema & "sendusername") = ""
    oCdoConfg.Fields.Item(strMSSchema & "sendpassword") = ""
    oCdoConfg.Fields.Update  

    set oCdoMsg = server.createobject("CDO.Message")
    oCdoMsg.to = ""
    oCdoMsg.bcc = ""
    oCdoMsg.from = ""
    oCdoMsg.Subject = "A tenant has requested a callback about one of your properties"
    oCdoMsg.HTMLbody = mBody
    Set oCdoMsg.Configuration = oCdoConfg
    oCdoMsg.send
    set oCdoMsg = nothing
    set oCdoConfg = nothing 

    response.Redirect("default.asp")
%>

I wondered if anyone might be able to spot why the email is not sending the specific property details in the email?

Apologies for the rather lengthy code.

Thank you.

A: 

Where is the field request.Form("fldName")

Preet Sangha  2009-10-06 11:35:38
A: 

I can't quite tell where you are getting "rspropertyresults" from, there doesn't appear to be a query. You may need to make sure that the data is potentially found on the initial page, is stored and sent over to the doCallBack.asp page (via 's or similar.

In addition, your input boxes don't appear to have the same names on both halves of the form. Maybe there is something I'm missing, but I think you want to use things like:

fldName             = replace(request.Form("txtName"),"'","")
fldTelephone    = replace(request.Form("txtTelephone"),"'","")
fldEmail            = replace(request.Form("txtEmail"),"'","")
Amadiere  2009-10-06 11:38:29
@Amadiere The input boxes have the txtName, txtTelephone and txtEmail as follows; <input id="form_input" type="text" name="txtName" />. There is an SQL query further up the page that gets the items that (at the moment), just selects everything releated to a property (including email address and contentid).
Neil Bradley  2009-10-06 11:45:44
What foriamstu says, if you don't store the ID of the record from the database across the HTTP submit, then it'll be lost and you'll simply be returning the first record in that your query returns. try adding a hidden field to store some reference to the property and then query on that when you get to the emailing part. You definitely need to be doing two queries (or storing all the data across a submit).
Amadiere  2009-10-06 12:09:05
A: 

It doesn't look like you're posting the ContentID to the docallback.asp page in the form, and I'd guess you're not filtering by it when you request the results on the docallback.asp page either. Which means you'll always get the first result (rather than the one you want).

foriamstu  2009-10-06 11:39:53
+1  A: 

You seem to reuse same resultset (rspropertyresults) for both displaying the properties and sending the email.

Since this recordset does not accept the ContentID as a parameter (and you don't send it anyway), this will send the email using the properties on the first record in the recordset.

Add a hidden input ContentID into each of your forms, create an additional recordset on this command:

SELECT  *
FROM    VWTenantPropertiesResults
WHERE   ContentID = @ContentID

, open it and use it to retrieve the details of the property:

cmdEmail = Server.CreateObject("ADODB.Command")
rsEmail = Server.CreateObject("ADODB.Recordset")

With cmdEmail
    .ActiveConnection = MM_dbconn_STRING
    .CommandText = "SELECT * FROM VWTenantPropertiesResults WHERE ContentID = ?"
    .CommandType = 1
    .Parameters.Append .CreateParameter("@ContentID", 3, 1, , request.Form("ContentID"))
End With

rsEmail.Open cmdEmail
Quassnoi  2009-10-06 12:23:03

related questions

Backup SQL Schema Only?
Sql to query a dbs scheme
Timer-based event triggers
Sql query, count and group by
Paging SQL Server 2005 Results
SQL 2005 For XML Explicit - Need help formatting
How do I use T-SQL Group By
What all do I need to escape when sending a (My)SQL query?
Split String in SQL
Datatable vs Dataset
ASP.NET MVC "CRUD" Database Sample
Convert HashBytes to VarChar
Best Book for a new Database Developer
What is the best way to avoid SQL injection attacks?
What language do you use for Postgresql triggers and stored procedures?
What is the best way to handle multiple permission types?
How do I index a database field
Swap unique indexed column values in database.
cx_Oracle - what is the best way to iterate over a result set?
cx_Oracle - How do I access Oracle from Python?
Is there a version control system for database structure changes?
How to export data from SQL Server 2005 to MySQL
ASP.NET Site Maps
Decoding T-SQL CAST in C#/VB.net
Flat File Databases in PHP