email the results of search using CDONTS(can't get results into string

gavray · Dec 14, 2000

hi,

Hi,

I'm trying to take the values of a search which have been called from the database,
by someones search. Then email the results to someone using CDONTS.

I don't know how to get those response.written values and put them into the email string.

the problem lies with the sub procedure email()

I'm using

<%Request("Company&quot

="Company"%> 'this is the problem area'
<%Request("Jobtitle&quot

="Jobtitle"%>
<%Request("region&quot

="region"%>
<%Request("industry&quot

="industry"%>

I'd be gratefull for some help

this is the full code.

<%@ Language=VBScript %>
<%
Status = Request("Status&quot

If Status ="Search" Then

Search

Else If Status="Email" Then

Email

Else

ShowForm()

End If
End If

sub ShowForm()

%>
<html>
<head>
<title>search a job</title>
</head>
<body>
<table><tr><td>
<font color="#1818ac"><b>Search by job sector<br>
<%
Dim oRSv
Set oRSv=Server.CreateObject("adodb.Recordset&quot

oRSv.Open "IndustryTable", "dsn=50on"
oRSv.MoveFirst
%>

<form method="post" action="industrysub.asp?Status=Search" name=form1>
<select name="industry" size="1">
<%
Do While NOT oRSv.EOF
Response.Write "<option value='" & oRSv("industry&quot

& "'>"
Response.Write oRSv("industry&quot

& "</option>"
oRSv.MoveNext
loop
oRSv.Close
Set oRSv=nothing
%>
</select>
</td><td>
<font color="#1818ac"><b>Location & Hours</b></font><br><br>
<%
Dim oRSr
Set oRSr=Server.CreateObject("adodb.Recordset&quot

oRSr.Open "RegionTable", "dsn=50on"
oRSr.MoveFirst
%>

<select name="region" size="1">
<%
Do While NOT oRSr.EOF
Response.Write "<option value='" & oRSr("region&quot

& "'>"
Response.Write oRSr("region&quot

& "</option>"
oRSr.MoveNext
loop
oRSr.Close
Set oRSr=nothing
%>
</select></td>
<td><br><br>
<%
Dim oRSa
Set oRSa=Server.CreateObject("adodb.Recordset&quot

oRSa.Open "HoursTable", "dsn=50on"
oRSa.MoveFirst
%>

<select name="Hours" size="1">
<%
Do While NOT oRSa.EOF
Response.Write "<option value='" & oRSa("Hours&quot

& "'>"
Response.Write oRSa("Hours&quot

& "</option>"
oRSa.MoveNext
loop
oRSa.Close
Set oRSa=nothing
%>
</select>
</td><td><br>
<font color="#1818ac"><b>add keyword for more precise search</b></font><br><br>
<form method="post" action="industrysub.asp?Status=Search" id=form2 name=form2>
<input type="Text" name="Jobdescription">
<input type="IMAGE" SRC="go.gif" border="0" id=IMAGE1 name=IMAGE1>
</form>
</td></tr></table>

<br>
<%
end sub

sub Search()

varindustry=Request.Form("industry&quot

varregion=Request.Form("region&quot

varJobdescription=Request.Form("Jobdescription&quot

varHours=Request.Form("Hours&quot

%>

<%
Dim oRS2
dim iRowCounter
Set oRS2=Server.CreateObject("adodb.Recordset&quot

sSQL="SELECT JobTable.ID, CompanyTable.ID, JobTable.CompanyID, CompanyTable.Company, JobTable.industry, JobTable.Jobtitle, JobTable.region, JobTable.Jobdescription, JobTable.Hours"
sSQL=sSQL & " FROM JobTable"
sSQL=sSQL & " RIGHT JOIN CompanyTable"
sSQL=sSQL & " ON CompanyTable.ID = JobTable.CompanyID"
sSQl=sSQL & " WHERE Hours='" & varHours & "'"
sSQl=sSQL & " AND industry='" & varindustry & "'"
sSQl=sSQL & " AND region='" & varregion & "'"
sSQl=sSQL & " AND Jobdescription LIKE '%" & varJobdescription & "%'"
sSQL=sSQL & " ORDER BY JobTable.Jobtitle"

oRS2.open sSQL, "DSN=50on"

if oRS2.EOF=True then
Response.write "<font color=#1818ac>Sorry, no jobs were found in this sector"

else oRS2.MoveFirst

response.write "<table bgcolor=#babdd9 align=center width=43% border=0>"

response.write "<th bgcolor=white><img src=jobtitletab.gif></th>" & "<th bgcolor=white><img src=sectortab.gif></th>" & "<th bgcolor=white><img src=companytab.gif></th>" & "<th bgcolor=white><img src=regiontab.gif></th>"

Do while Not oRS2.EOF

Response.Write "<tr><td><a href=displayjob2.asp?ID=" & oRS2 ("ID&quot

& ">" & oRS2 ("Jobtitle&quot

& "</a></td>"
Response.Write "<td>" & oRS2 ("industry&quot

& "</td>"
response.write "<td>" & oRS2 ("Company&quot

& "</td>"
response.write "<td>" & oRS2 ("region&quot

& "</td></tr>"

oRS2.MoveNext

Loop
end if
oRS2.Close
set oRS2=Nothing
response.write "</table>"
%>
<table><tr><td>
<form method="post" action="industrysub.asp?Status=Email" id=form3 name=form3>
<input type="hidden" name="Email">
<input type="IMAGE" SRC="go.gif" border="0" id=IMAGE1 name=IMAGE1>
</form>
</table></tr></td>
<%
end sub
%>

<%
sub Email()
%>
<%Request("Company&quot

="Company"%> 'this is the problem area'
<%Request("Jobtitle&quot

="Jobtitle"%>
<%Request("region&quot

="region"%>
<%Request("industry&quot

="industry"%>

<%
dim myString 'hold text

myString = "<HTML><HEAD><title>Job request</title></head>" & vbcrlf
myString = myString & "<body>" & vbcrlf
myString = myString & "<table border=0 width=800>" & vbcrlf

myString = myString & "<tr><td>Dear " & Request("Company&quot

& "<br><br></td></tr>" & vbcrlf

'myString = myString & "<tr><td>Someone wants this job on "& day(now()) & "/" & month(now()) & "/"
'& year(now()) & " at " & hour(now()) & ":"

'if minute (now()) < 10 then

'else

'myString = myString & minute(now())

'end if

myString = myString & "<tr><td>The job was" & Request("Jobtitle&quot

& "</td></tr>" & vbcrlf
myString = myString & "<tr><td>The industry was" & Request("industry&quot

& "</td></tr>" & vbcrlf
myString = myString & "<tr><td>The region was" & Request("region&quot

& "</td></tr>" & vbcrlf

myString = myString & "</table></body></HTML>"
set myMail = server.CreateObject ("CDONTS.Newmail&quot

'send email confirmation

'myAddressString = oRS3("Email&quot

with myMail
.To = "arvin@3la.com"
.from = Application("NotificationFromAddress&quot

.subject = "Job application"
.Body = myString
.MailFormat = 0
.BodyFormat = 0
.send

end with
set myMail = nothing
end sub%>

</font>
</body>
</html>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

email the results of search using CDONTS(can't get results into string

gavray

Programmer

Similar threads

Part and Inventory Search

Sponsor