Send email to current user [Archive]

View Full Version : Send email to current user

RedBravo

10-11-2005, 12:35 AM

Hello,
Can somone have a look at this script and tell me what i am doing wrong please.

What I am trying to do is send a confirmation email to a customer after they have placed an order - but i can't successfully get the current users email address from the table

<%
SUB sendNewUserMail( fromWho, toWho, Subject, Body )
Dim ObjSendMail
Set ObjSendMail = Server.CreateObject("CDO.Message")
ObjSendMail.To = toWho
ObjSendMail.Subject = Subject
ObjSendMail.From = fromWho
ObjSendMail.TextBody = Body
ObjSendMail.Send
Set ObjSendMail = Nothing
END SUB

' Send Email Notification
Set Con = Server.CreateObject( "ADODB.Connection" )
Set RS = Server.CreateObject ("ADODB.Recordset")
sqlString = "SELECT user_email FROM Users " &_
"WHERE user_id=" & userID
Set RS = Con.Execute( sqlString )
fromWho = "sales@mycompany.com"
toWho = RS( "user_email" )
Subject = "Thank you for your order!"
Body = "Thank you for ordering through " &_
"Our company!" & vbNewline &_
"Your order has been successfully placed."
sendNewUserMail fromWho, toWho, Subject, Body
%>

neocool00

10-11-2005, 07:05 PM

What error are you getting?

RedBravo

10-12-2005, 12:31 AM

This is error message:

Microsoft VBScript compilation error '800a03ea'

Syntax error Set RS = Con.Execute( sqlString )

neocool00

10-12-2005, 04:46 PM

Before that line of code, do a Response.Write "<p>" & sqlString & "</p>" and post what's gets written to the screen. Additionally, what datatype is "user_id" in your table?

RedBravo

10-13-2005, 12:29 AM

Data type in table is text.

I put response.write before and after line of code

1. Before;
sqlString = "SELECT user_email FROM Users " &_
Response.Write "<p>" & sqlString & "</p>"
"WHERE user_id=" & userID

Error Message
Microsoft VBScript compilation error '800a0401'
Expected end of statement

2. After:
sqlString = "SELECT user_email FROM Users " &_
"WHERE user_id=" & userID
Response.Write "<p>" & sqlString & "</p>"

Error Message:
SELECT user_email FROM Users WHERE user_id=
ADODB.Connection error '800a0e78'
Operation is not allowed when the object is closed.

Freon22

10-13-2005, 04:31 AM

Error Message:
SELECT user_email FROM Users WHERE user_id=
ADODB.Connection error '800a0e78'
Operation is not allowed when the object is closed.

By the looks of it userID is empty. You need to find where you are filling the varaible userID at in your code. Then find out why its not being filled.

Just to add a little here, if nothing is returned then your recordset will stay closed. Since it never opened you get a Operation is not allowed when the object is closed. Because you are trying to use a closed object on this line.

toWho = RS( "user_email" )

What you should do is check to see if something was returned before try to use the recordset and before you call your mailing sub.

You may want to do something like this.

' Send Email Notification
Set Con = Server.CreateObject( "ADODB.Connection" )
Set RS = Server.CreateObject ("ADODB.Recordset")
sqlString = "SELECT user_email FROM Users WHERE user_id=" & userID
Set RS = Con.Execute( sqlString )
If Not RS.EOF Then
fromWho = "sales@mycompany.com"
toWho = RS( "user_email" )
Subject = "Thank you for your order!"
Body = "Thank you for ordering through " &_
"Our company!" & vbNewline &_
"Your order has been successfully placed."
sendNewUserMail fromWho, toWho, Subject, Body
Else
Response.Write("Sorry but no email address was returned")
End If

Or write it so if userID is empty then nothing is called. But I think I would still check to make sure something was returned anyway. You never know, the email address may not be in your database then you would get an error again.

RedBravo

10-13-2005, 06:10 AM

Thanks Freon22,

I'll give that a try and get back to you

RedBravo

10-13-2005, 01:31 PM

OK, here's where my limited coding skills become increasingly apparent - even the "Else Response.Write("Sorry but no email address was returned")
End If" won't work for me now.

The email address, username and password are being passed to this script from a form, so maybe you are correct about the email not being in the table - it may not be being written there until after the email part of the script.

What do you think?

This is the script before the email bit;


<%
'==========================
' Common Functions
'==========================
FUNCTION fixQuotes( theString )
fixQuotes = REPLACE( theString, "'", "''" )
END FUNCTION
SUB addCookie( theName, theValue )
Response.Cookies( theName ) = theValue
Response.Cookies( theName ).Expires = "July 31, 2010"
Response.Cookies( theName ).Path = "/"
Response.Cookies( theName ).Secure = FALSE
END SUB
FUNCTION checkpassword( byVal username, byVal password, byRef Con )
sqlString = "SELECT user_id FROM users " &_
"WHERE user_username='" & username & "' " &_
"AND user_password='" & password & "'"
SET RS = Con.Execute( sqlString )
IF RS.EOF THEN
checkpassword = - 1
ELSE
checkpassword = RS( "user_id" )
addCookie "username", username
addCookie "password", password
END IF
END FUNCTION

FUNCTION SELECTED( firstVal, secondVal )
IF cSTR( firstVal ) = cSTR( secondVal ) THEN
SELECTED = " SELECTED "
ELSE
SELECTED = ""
END IF
END FUNCTION

SUB errorForm( errorMSG, backpage )
%>
<html>
<head><title>Problem</title></head>
<body bgcolor="lightyellow">

<center>
<table width="500" border=1
cellpadding=5 cellspacing=0>
<tr>
<td>
<font face="Arial" size="3" color="darkblue"><b>
There was a problem with the information you entered:
</b></font>
<font size="2" color="red"><b>
<br><%=errorMSG%>
</b></font>
<br>
<form method="post" action="<%=backpage%>">
<input name="error" type="hidden" value="1">
<% formFields %>
<input type="submit" value="Return">
</form>
</td>
</tr>
</table>
</center>

</body>
</html>
<%
Response.End
END SUB

SUB formFields
FOR each item in Request.Form
%>
<input name="<%=item%>" type="hidden"
value="<%=Server.HTMLEncode( Request( item ) )%>">
<%
NEXT
END SUB

'===========================
' Registration Functions
'===========================
SUB sendNewUserMail( fromWho, toWho, Subject, Body )
Dim ObjSendMail
Set ObjSendMail = Server.CreateObject("CDO.Message")
ObjSendMail.To = toWho
ObjSendMail.Subject = Subject
ObjSendMail.From = fromWho
ObjSendMail.TextBody = Body
ObjSendMail.Send
Set ObjSendMail = Nothing
END SUB

' Send Email Notification
Set Con = Server.CreateObject( "ADODB.Connection" )
Set RS = Server.CreateObject ("ADODB.Recordset")

sqlString = "SELECT user_email FROM Users " &_
"WHERE user_id=" & userID

Set RS = Con.Execute( sqlString )

fromWho = "thegiftcompany@thegiftcompany.com.au"
toWho = RS( "user_email" )
Subject = "Thank you for your order!"
Body = "Thank you for ordering through " &_
"our company!" & vbNewline &_
"Your order has been successfully placed."
sendMail fromWho, toWho, Subject, Body

Freon22

10-13-2005, 04:07 PM

This is what I see! You have three functions and three subs on that page. You are only calling one of the subs "SendNewUserMail".

What I don't see is where you are filling the variable "userID" at. I am guessing that when you used "If Not RS.EOF Then" in your code you didn't get an error, but did you see ("Sorry but no email address was returned") printed on your screen? Or do you have a "Response.Redirect" at the bottom of the page or some other type of redirect to send the user to another page? If you do then you would never see the message on the screen and would not get an email ether.

The first thing you have to solve is this "userID"
Is "userID" empty or does it have a number in it.

You can also test your mailing code like this just to see if it is working. When I rewrite any code for testing I just comment out the code that I was using that way I don't lose it.

' Send Email Notification
'Set Con = Server.CreateObject( "ADODB.Connection" )
'Set RS = Server.CreateObject ("ADODB.Recordset")
'sqlString = "SELECT user_email FROM Users WHERE user_id=" & userID
'Set RS = Con.Execute( sqlString )
'If Not RS.EOF Then
fromWho = "sales@mycompany.com"
'toWho = RS( "user_email" )
toWho = "YourEmailAddress" 'We comment out the other toWho and our own address to see if we get a email
Subject = "Thank you for your order!"
Body = "Thank you for ordering through " &_
"Our company!" & vbNewline &_
"Your order has been successfully placed."
sendNewUserMail fromWho, toWho, Subject, Body
'Else
'Response.Write("Sorry but no email address was returned")
'End If

Now run the page, did you get an email? If you did then you know that mailing code is good and that for some reason you are not get an email address through the recordset.

RedBravo

10-13-2005, 04:25 PM

Email worked fine to specified address - so email address is not being passed from recordset

neocool00

10-13-2005, 04:31 PM

Data type in table is text.
If user_id is of type text in the database, then you'll need to change your sql statement to this, otherwise it will never work.
sqlString = "SELECT user_email FROM Users " &_
"WHERE user_id='" & userID & "'"

Freon22

10-13-2005, 05:43 PM

Good catch neocool00 :thumbsup:
sqlString = "SELECT user_email FROM Users " &_
"WHERE user_id='" & userID & "'"

RedBravo

10-14-2005, 12:09 AM

Thanks for spotting that,
it certainly gets a lot further now but this gives another error in "cart.asp" - - expected "END" on last line, instead of "End if".

I changed this to "END" - but then error message says should be "END if"




<%

' Get Product ID
productID = TRIM( Request( "pid" ) )

' Get Login Information
username = TRIM( Request( "username" ) )
password = TRIM( Request( "password" ) )
register = TRIM( Request( "register" ) )
contact = TRIM( Request( "contact") )
email = TRIM( Request( "email") )
error = TRIM( Request( "error" ) )

' Check For New Registration
IF register <> "" AND error = "" THEN
addUser
END IF

' Get User ID
userID = checkpassword( username, password, Con )

IF userID > 0 THEN
%>

<% ELSE %>

<%
END if
%>

RedBravo

10-14-2005, 02:01 PM

Still playing around with this but still get compilation error '800a03f4' expect 'if' in last line of code of cart.asp

Would I be correct in assuming that something has not been closed properly and is being passed to cart.asp when it shouldn't?

I moved the END SUB from just after "Set ObjSendMail = Nothing" to just after "sendMail fromWho, toWho, Subject, Body" but this made no difference at all

Freon22

10-14-2005, 02:55 PM

Hi RedBravo,
Your trouble is now here.

IF userID > 0 THEN
%>

<% ELSE %>

<%
END if
%>

Ether in addCart.asp or login.asp you have failed to close something. It maybe a End If that you forgot. Let me show you how it works.

I made three simple test pages to show you.

'This is Testing1.asp

<%@ Language=VBScript %>
<% Option Explicit %>

<html>
<head>
<title>Untitled</title>
</head>

<body>
<%
Dim userID
userID = 1
IF userID > 0 THEN
%>

<% ELSE %>

<%
END if
%>
</body>
</html>

'This is Testing2.asp

<%
Response.Write("This is testing2 - asp")
Dim test2
test2 = 2
If test2 = 2 Then
Test2 = 2 * 2
Response.Write("<br>" & test2)
End If
%>
<br>
Html test2 page 2

'This is Testing3.asp

<%
Response.Write("This is testing3 - asp")
Dim test3
test3 = 2
If test3 = 2 Then
Test3 = 2 * 2
Response.Write("<br>" & test3)
'I have left out the End If on testing3.asp

%>
<br>
Html test page 3

How you can see on testing1.asp I have two include files and the code is only calling testing2.asp. But there is a error on testing3.asp which I am not calling with my code. Now when I run testing1.asp here is the error I get.

Microsoft VBScript compilation error '800a03f6'

Expected 'End'

/Math/testing1.asp, line 19

The error code say nothing about the error being on testing3.asp it say the error is on testing1.asp. If I close the If statement on testing3.asp then the code works. When you use include files this way you have to be very careful when writing your code.

Edit:
I need to clarify the include files. If the last vbscript code on your page ends with a End If and any of your include files are missing or have a typo error in closing a statement. The error message will most of the point to the last End If that you have on the main page. So you may want to check ALL of you include files that has vbscript coding in them.

RedBravo

10-14-2005, 10:18 PM

Thanks for info Freon22,

I will go through the coding now and see if i can find where the problem lies.

but quick question - all these scripts worked perfectly before i started on the email thing in storefuctions.asp - can adding a new element to this script force a compilation error in another?

Thanks
Rob

neocool00

10-17-2005, 08:55 PM

I wouldn't think so. It sounds like there is a missing End If on one of the included files.

RedBravo

10-25-2005, 03:32 PM

Hi Guys,
been out of town for a while - only got just got back onto this.

the 'If Not RS.EOF Then' (line 5 below) is the one causing 'Expected END' problem with the other script.

When this line is commented out - don't get the error 'Expected END' message anymore but get this one instead:
ADODB.Connection error '800a0e78'
Operation is not allowed when the object is closed.

Set Con = Server.CreateObject( "ADODB.Connection" )
Set RS = Server.CreateObject ("ADODB.Recordset")
sqlString = "SELECT user_email FROM Users WHERE user_id='" & userID & "'"
Set RS = Con.Execute( sqlString )
'If Not RS.EOF Then
fromWho = "sales@mycompany.com"
toWho = RS( "user_email" )
Subject = "Thank you for your order!"
Body = "Thank you for ordering through us blah blah" &_
sendNewUserMail fromWho, toWho, Subject, Body
'Else
'Response.Write("Sorry but no email address was returned")
'End If

Freon22

10-25-2005, 04:56 PM

Set Con = Server.CreateObject( "ADODB.Connection" )
Set RS = Server.CreateObject ("ADODB.Recordset")

I hate to ask this but I have searched out that error message and it all points to a locked database or empty recordset. So the question is are you setting the path and opening the database connection? Sorry for asking this but I don't see were you are.

Dim Con
Set Con = Server.CreateObject("ADODB.Connection")
Con.ConnectionString= "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("**/****.mdb")
Con.Open

Let me know.

RedBravo

10-26-2005, 12:32 AM

Hi Freon22,

I know that i've made a very basic mistake here somewhere - it's driving me nuts, but i don't think this is where it is.

I make the connection as an include at the start.

and this is the connection:
<%
'DSN Less Connection to the Database
strDBPath = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=D:\wwwroot\companycom\database\DB.mdb"
' Create an ADO Connection to connect to the database.
Set Con = Server.CreateObject("ADODB.Connection")
Con.Open strDBPath
%>

Freon22

10-26-2005, 01:20 AM

Sorry for asking that but I am running out of ideas. I read about this same problem. The coder ending up fixing the problem by coping and renaming his database.

http://www.ecommercetemplates.com/support/topic.asp?TOPIC_ID=13708

http://www.aspfaq.com/show.asp?id=2307

http://www.google.com/search?hl=en&lr=&q=ADODB.Connection+error+%27800a0e78%27&btnG=Search

RedBravo

10-27-2005, 03:09 AM

Thanks anyway Freon22, It is a bit of a tricky one - i looked it up too and some info says it might be because its returning an empty string.
As this info is being passed from a form into the database I just wonder if it might be because the email subroutine is executing before the data is saved into the database.

plasterx

10-28-2005, 09:47 AM

Hi there,i suppose your email reply is an autoresponder ya?

RedBravo

10-28-2005, 11:39 PM

yeah. It's supposed to fire off a confirmation email to the customer when they place an order - the problem is not sending the email, it's getting the customers email address.