Mail bodytext and loop problem

[Dutchguy]

I have the following two tables, that match against each other.
(This system has to be an automatic notifier by e-mail when someones search-criteria in the database matches with the data of vehicles that are for sale).


Example:

Test.Model Test.Type 
BMW       316 
Mercedes  SLK 
Opel      Tigra 
Opel      Calibra 
BMW       520 

Login.Models  Login.Types   Login.Email
BMW           520           email@email.com 
Opel          Astra         jansen@jansen.com

So in this example, there should be an e-mail send to "email@email.com", with the details of the car: BMW 520 from the table "Test".


This is the SQL-statement I use:


Code:

--------------------------------------------------------------------------------
SELECT Test.Model, Test.Type, Login.Models, Login.Types, Login.Minlength,
Login.Maxlength, Test.Loa, Test.Asking_price, Login.Minprice, Login.Maxprice,
Test.Hull_Material, Login.Material, Login.Email

FROM Test, Login

WHERE Test.Model LIKE Login.Models AND Test.Type = Login.Types AND Test.Loa
BETWEEN Login.Minlength AND Login.Maxlength AND Test.Asking_price BETWEEN
Login.Minprice AND Login.Maxprice AND Test.Hull_Material = Login.Material
ORDER BY Test.Model

--------------------------------------------------------------------------------

This query returns me all matching data in the following way:

Test.Model  Login.Models  Login.Email
BMW         BMW           email@email.com 
BMW         BMW           email@email.com
BMW         BMW           email@email.com

So in the above example there are 3 BMW's for sale. The person is searching for a BMW, so it returns 3 matches. (is this right ???)


Then the results are mailed to each person, that has a match

Code:

--------------------------------------------------------------------------------
<%
While ((Repeat1__numRows <> 0) AND (NOT rsSearch.EOF))
%>
<tr>
<td><%=(rsSearch.Fields.Item(&quot;Model&quot.Value)%></td>
<td><%=(rsSearch.Fields.Item(&quot;Type&quot.Value)%></td>
<td><%=(rsSearch.Fields.Item(&quot;Asking_price&quot.Value)%></td>
</tr>
<%
Set mail = Server.CreateObject(&quot;CDONTS.NewMail&quot
mail.From = &quot;info@mijnsite.nl&quot;
mail.To = rsSearch(&quot;Email&quot
mail.BodyFormat = 0
mail.MailFormat = 0
mail.Subject = &quot;subject&quot;
mail.Body = rsSearch(&quot;Model&quot & rsSearch(&quot;Type&quot& rsSearch(&quot;Asking_price&quot
mail.Send
Set mail = Nothing
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsSearch.MoveNext()
Wend
%>

--------------------------------------------------------------------------------






But, now for each match there is an e-mail send to that person, with the details of the car for sale.

I want offcourse that when there is a match, that there is one e-mail send to each person, with all the matches in the bodytext of the email.

How can I achieve this ?

Is my SQL right, I'm worried that that is the problem, is that correct ?

 

[rac2]

Build the message body in the loop. Send the email after the message is completed.

<%
Dim msg

While ((Repeat1__numRows <> 0) AND (NOT rsSearch.EOF)) 
%> 
  <tr> 
    <td><%=(rsSearch.Fields.Item(&quot;Model&quot;).Value)%></td> 
    <td><%=(rsSearch.Fields.Item(&quot;Type&quot;).Value)%></td> 
    <td><%=(rsSearch.Fields.Item(&quot;Asking_price&quot;).Value)%></td> 
  </tr> 
<% 
msg = msg & rsSearch(&quot;Model&quot;) & rsSearch(&quot;Type&quot;)& rsSearch(&quot;Asking_price&quot;)  & VbCrLf
>% 
  <% 
  Repeat1__index=Repeat1__index+1 
  Repeat1__numRows=Repeat1__numRows-1 
  rsSearch.MoveNext() 
Wend 

Set mail = Server.CreateObject(&quot;CDONTS.NewMail&quot;) 
mail.From = &quot;info@mijnsite.nl&quot; 
mail.To = rsSearch(&quot;Email&quot;) 
mail.BodyFormat = 0 
mail.MailFormat = 0 
mail.Subject = &quot;subject&quot; 

mail.Body = msg

mail.Send 
Set mail = Nothing 

%>

 

[Dutchguy]

Thanks for your reply.

It's not completely working in your example.

With your example it is now only sending one e-mail with all th results in the body text. In my testdatabase, there should be two e-mails out, within each e-mail the right search result.

Any ideas ?