Sending EMail, data from SQL database w/ado

[sharonc]

I have a form that stores data in a sql server database. When I hit the email button, I want just the data for the current form emailed to a client. I can't get my email to pull data from the database and email it as information in the body of the email. Can someone help me? Here is my code:

<%@language=vbscript event=onclick for=Command1%>
<%#include virtual= &quot;C:\Program files\Common files\system\ado\msado15.dll&quot;

Dim Conn, rs, strConn, sqlStatement, strHost

Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot

strConn = &quot;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Forms;Data Source=SEIMS&quot;

'strConn = strConn & server.MapPath(c:\Forms\Forms.mdb) & &quot;;&quot;

strConn = strConn & server.MapPath(lrogram Files\Forms\Forms.mdb) & &quot;;&quot;

Conn.Open

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.open &quot;Contractors&quot;, Conn, adOpenForwardOnly, adLockReadOnly
rs.ActiveConnection = Conn
rs.CursorType = 2 'adOpenDynamic
sqlStatement = &quot;SELECT * FROM qryContractors&quot;

rs.Open sqlStatement

strHost = &quot;mail.aepnet.com&quot;

Set Mail = Server.CreateObject(&quot;Persits.MailSender&quot
Mail.Host = strHost

'format the new mail message
Mail.From = &quot;schapman@aepnet.org&quot;
Mail.AddAddress &quot;schapman@aepnet.org&quot;

' message subject
Mail.Subject = &quot;Contractors Working At Apache&quot;


' message body
Mail.Body = Msg & &quot;ContractorName: &quot; & rs(&quot;ContractorName&quot

'SEND THE MAIL MESSAGE TO ITS RECIPIENTS
mail.Send

If Err <> 0 Then
Response.Write &quot;An error occurred: &quot; & Err.Description
End If

'DEFERENCE THE OBJECT AND DESTROY ITS INSTANCE
set Mail = Nothing
%>

My code will work if I place <script> around it and remove the ado portion. It will mail an email.

 

[BBirkett]

Are you getting any error messages or is the page loading fine? Brett Birkett B.Comp
Systems Analyst

 

[sharonc]

I solved my email problem. Now I get the error message that the datasource is read-only when I try to enter data. Any ideas?

 

[BBirkett]

Are you able to post the code that is doing the insert??

If you are using the same recordset as the one before
(rs.open &quot;Contractors&quot;, Conn, adOpenForwardOnly, adLockReadOnly) then it could be the property adLockReadOnly. That would be my guess anyway.

Do you need this line?? Ive never used it before ->

<%#include virtual= &quot;C:\Program files\Common files\system\ado\msado15.dll&quot;

I just use Server.CreateObject(&quot;ADODB.Connection&quot
without any includes.


Brett Birkett B.Comp
Systems Analyst

 

[sharonc]

This is my code now. This msado15.dll is supposed to contain ado functions that I need to run this code. (I read that somewhere) You will notice that in order to make the mail function work I had to remove the word 'server' things like: Server.CreateObject(&quot;ADODB.Connection&quot If I left the word server in, I would get an error message saying it couldn't handle the 'server'. and if I didn't use runat=server, then my mail wouldn't send.

<SCRIPT language=vbscript event=onclick for=command1 RUNAT=&quot;Server&quot;>
<!-- #include virtual= &quot;G:\Program files\common files\system\ado\msado15.dll&quot;->

Dim Conn, rs, sqlStatement, strHost, Msg

Set Conn = CreateObject(&quot;ADODB.Connection&quot
Conn.Open = &quot;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Forms;Data Source=SEIMS&quot;

sqlStatement = &quot;SELECT * FROM Contractors&quot;
Set rs = CreateObject(&quot;ADODB.Recordset&quot
rs.CursorType = adOpenDynamic
rs.open sqlStatement, Conn

strHost = &quot;mail.aepnet.com&quot;

Set Mail = CreateObject(&quot;Persits.MailSender&quot
Mail.Host = strHost

'format the new mail message
Mail.From = &quot;schapman@aepnet.org&quot;
Mail.AddAddress &quot;schapman@aepnet.org&quot;

' message subject
Mail.Subject = &quot;Contractors Working At Apache&quot;

' message body
Msg = &quot;Contractor Name: &quot; & rs(&quot;ContractorFirstName&quot & &quot; &quot; & rs(&quot;ContractorLastName&quot& chr(10) & _
&quot;Date Entered: &quot; & rs(&quot;EntryDate&quot
Mail.Body = Msg

'SEND THE MAIL MESSAGE TO ITS RECIPIENTS
mail.Send

If Err <> 0 Then
Response.Write &quot;An error occurred: &quot; & Err.Description
End If

'DEFERENCE THE OBJECT AND DESTROY ITS INSTANCE
set Mail = Nothing

</SCRIPT>

 

[BBirkett]

Ok, my suggestions are this:

Get rid of your include directive -

<!-- #include virtual= &quot;G:\Program files\common files\system\ado\msado15.dll&quot;->

Then change all your CreateObject's back to Server.Createobject

I'm guessing that you have installed ASPEmail on your webserver. This is why you needed RUNAT=SERVER.

What type of database are you working with??

If the above doesn't work, put all the code into an ASP page and try that. ie.. don't embed it in a <script> event.

What you are trying to do could possibly be ok, it's just different from anything i have seen before, therefore i can only suggest you try the above, which is how I usually write asp pages that talk with db.

Let me know if the above helps you.


Brett Birkett B.Comp
Systems Analyst

 

[sharonc]

What I'm trying to do is have a form that the client fills out and when they hit the email key, the data from the form is emailed to certain other clients. One of the reasons I was using the script event was because I wanted to use the clickon event when the email button is clicked to send the data. The form is not mailed, just the data. I do have aspemail installed on my web server. Do I need to place it in d:\inetpub\

http://wwwroot?

If I take out the runat=server, do I need to use something different than aspemail?

My database is SQL SErver backend with MSAccess as the front end. I'm using a data Access page for the form.

I'll let you know how it turns out after I make your suggetsions.

Thank you