Delete Record and Send Email

Report · Oct 01, 2009

I know there is somebody out there that can tell me how to do this. Here is my page http://www.gcfa.org/delete_data-3.asp. If you want to test it, type in a number from 1-10.

It will bring up a results page with the database information. This is where I need your help. Since I want to delete this record how can I send an email right before it gets deleted?

Any help is greatly appreciated. Steer me in the right direction someone please. If it won't ever happen, tell me that, just please tell me something.

Report · Oct 01, 2009

If you want to send an email first, you simply insert your code that sends the email prior to the code that executes the delete statement. What exactly is your problem? Do you understand how to write code?

Report · Oct 01, 2009

Thanks for replying, and to answer your question no I don't know how to write the asp code. That is what I am asking for. This is new to me. Can you help?

Report · Oct 01, 2009

OK, I guess I am unclear as to how you created what you have so far without knowing how to write code. If you are relying solely on DW server behaviors, you need to understand that they can only take you so far. Since you require something more than they provide, you are going to need to learn how to write code, or hire someone else to do it for you.

You could start by searching for 'ASP form mail' scripts. That will provide you with the code that will send emails via ASP.

And of course, without seeing your actual code, nobody is going to be able to help you online. If you want to post your existing code, someone can make some suggestions of where to insert your mail script.

But I really believe that hacking code together without understandling the fundamentals of the language is a bad approach.

Report · Oct 01, 2009

I guess maybe its me that has'nt been clear. Yes, I agree with you, I do need to learn all kinds of different language. ASP, ASP.NET and PHP, but I don't have the money to take classes right now so I am trying to pick this up on my own, and with your help and others maybe I will get there someday. Bits and pieces at a time. I don't need the email script. I already have one. See below.

What I am trying to do is send the email right before the delete button execute's. I need help writing, I assume an "if else" statement using asp code. I don't have the code in front of me right now, when I get back to work on Monday I can send it.

Thanks for your time!

Report · Oct 01, 2009

I'll jump in.

First, I also use WA's Universal Email, that's all I've been using for years and it has worked for me in every situation I've encountered, including similar to yours.

Aside from that, if you have the script, have you gotten to work outside the procedure you're attampting now? If not, that's the first step - create an input form to send a "contact us" type email. Get that working on a page of its own. Then study the code on that page, and you should be able to see at least roughly what is going on. If you have any particular questions on certain lines of code and what they do, ask here.

Once you understand that block of script, then it will be much easier to insert it into your delete page.

Post the code here also, as stated.

Report · Oct 01, 2009

>I do need to learn all kinds of different language.

>ASP, ASP.NET and PHP, but I don't have the

>money to take classes right now so I am trying

>to pick this up on my own,

You don't need to take classes to learn a language, but trying to learn on your own by solving specific problems is not the best way either because you miss learning the fundamentals. Instead, I suggest getting a book that includes tutorials. I learned ASP many years ago using this:

http://www.amazon.com/exec/obidos/ASIN/0672316129/srimatsartpage/

I was able to read the whole book and run through all of the tutorials in less than a week. Another good title is:

http://www.amazon.com/Teach-Yourself-Active-Server-Pages/dp/0672318636/ref=pd_cp_b_1

Invest just a few hours a day for a week, and you'll have the answer to your current problem and much, much more.

>I don't have the code in front of me right now,

>when I get back to work on Monday I can send it.

Sounds good, I'll take a look once you post it.

Report · Oct 05, 2009

Here is the code:

Search page

<%@LANGUAGE="VBSCRIPT"%>

<%
Dim rs_Search_Data__MMColParam
rs_Search_Data__MMColParam = "1"
If (Request.Form("URL") <> "") Then
rs_Search_Data__MMColParam = Request.Form("URL")
End If
%>
<%
Dim rs_Search_Data
Dim rs_Search_Data_cmd
Dim rs_Search_Data_numRows

Set rs_Search_Data_cmd = Server.CreateObject ("ADODB.Command")
rs_Search_Data_cmd.ActiveConnection = MM_BishopsLogin_STRING
rs_Search_Data_cmd.CommandText = "SELECT URL FROM dbo.DeptData WHERE URL = ?"
rs_Search_Data_cmd.Prepared = true
rs_Search_Data_cmd.Parameters.Append rs_Search_Data_cmd.CreateParameter("param1", 200, 1, 255, rs_Search_Data__MMColParam) ' adVarChar

Set rs_Search_Data = rs_Search_Data_cmd.Execute
rs_Search_Data_numRows = 0
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>GCFA: Update Data Files</title>
<link href="samples/OneColumn.css" rel="stylesheet" type="text/css" />

</head>

</div>
<div id="mainContent">
    <h3 align="left"> </h3>
    <p> </p>
    <form action="delete_data_results-3.asp" method="get" name="Search_for_URL" target="_top" id="Search_for_URL">
<strong>Please enter the address of the URL you wanting to delete:</strong> <br />
<br />
<table align="left">
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">URL:</td>
      <td><input name="URL" type="text" id="URL" value="" size="75" maxlength="1000" /></td>
    </tr>

    <tr valign="baseline">
      <td nowrap="nowrap" align="right"> </td>
      <td> </td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right"> </td>
      <td><input name="Search_for_URL" type="submit" id="Search_for_URL" value="Search for data file now!" />      </td>
    </tr>
</table>
<p> </p>
<p><br />
      <br />
      <br />
      <br />
      <br />
</p>



</form> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
</div>
<div id="footer">
    <p align="center">This site is maintained by the General Council on Finance and Administration <script language="JavaScript">
var today_date= new Date()
var year=today_date.getFullYear()
document.write(year)
//--> </script> ©</p>
</div>
</div>

</body>
</html>
<%
rs_Search_Data.Close()
Set rs_Search_Data = Nothing
%>

Results page

<%@LANGUAGE="VBSCRIPT"%>

<%
Dim MM_editAction
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

' boolean to abort record edit
Dim MM_abortEdit
MM_abortEdit = false
%>
<%
' *** Delete Record: construct a sql delete statement and execute it

If (CStr(Request("MM_delete")) = "form1" And CStr(Request("MM_recordId")) <> "") Then

If (Not MM_abortEdit) Then
    ' execute the delete
    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_BishopsLogin_STRING
    MM_editCmd.CommandText = "DELETE FROM dbo.DeptData WHERE URL = ?"
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 200, 1, 500, Request.Form("MM_recordId")) ' adVarChar
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    ' append the query string to the redirect URL
    Dim MM_editRedirectUrl
    MM_editRedirectUrl = "success_deleting_page.asp"
    If (Request.QueryString <> "") Then
      If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
        MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
      Else
        MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
      End If
    End If
    Response.Redirect(MM_editRedirectUrl)
End If

End If
%>
<%
Dim rs_search_results__MMColParam
rs_search_results__MMColParam = "1"
If (Request.QueryString("URL") <> "") Then
rs_search_results__MMColParam = Request.QueryString("URL")
End If
%>
<%
Dim rs_search_results
Dim rs_search_results_cmd
Dim rs_search_results_numRows

Set rs_search_results_cmd = Server.CreateObject ("ADODB.Command")
rs_search_results_cmd.ActiveConnection = MM_BishopsLogin_STRING
rs_search_results_cmd.CommandText = "SELECT Department, SubpageTitle, LinkDescription, URL, DateLastModified FROM dbo.DeptData WHERE URL = ?"
rs_search_results_cmd.Prepared = true
rs_search_results_cmd.Parameters.Append rs_search_results_cmd.CreateParameter("param1", 200, 1, 50, rs_search_results__MMColParam) ' adVarChar

Set rs_search_results = rs_search_results_cmd.Execute
rs_search_results_numRows = 0
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>GCFA: Results for URL you are wanting to delete.</title>
<link href="samples/OneColumn.css" rel="stylesheet" type="text/css" />

</head>

</div>
<div id="mainContent"><div id #Nav>
    <div align="center">      </div>
</div>
    <h3 align="left"><br />
    <br />
This is the results for the URL you entered, are you sure you would like to delete this link?</h3>
    <p align="left"> </p>
    <form ACTION="<%=MM_editAction%>" METHOD="POST" id="form1" name="form1">
      <table border="0" cellpadding="2">
        <tr>
          <td>Department:</td>
          <td><%=(rs_search_results.Fields.Item("Department").Value)%></td>
        </tr>
        <tr>
          <td>SubpageTitle:</td>
          <td><%=(rs_search_results.Fields.Item("SubpageTitle").Value)%></td>
        </tr>
        <tr>
          <td>Link Description:</td>
          <td><%=(rs_search_results.Fields.Item("LinkDescription").Value)%></td>
        </tr>
        <tr>
          <td>URL:</td>
          <td><%=(rs_search_results.Fields.Item("URL").Value)%></td>
        </tr>
        <tr>
          <td width="168">Modified:</td>
          <td width="514"><%=(rs_search_results.Fields.Item("DateLastModified").Value)%></td>
        </tr>
        <tr>
          <td colspan="2"> </td>
        </tr>
        <tr>
          <td colspan="2"><label>
          <input type="submit" name="btndelete" id="btndelete" onclick="return confirm ('Are you sure you want to delete?')"value="Delete Now!" />
          </label></td>
        </tr>
</table><br />
<br />
<br />
<input type="hidden" name="MM_delete" value="form1" />
<input type="hidden" name="MM_recordId" value="<%= rs_search_results.Fields.Item("URL").Value %>" />
    </form>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
</div>
<div id="footer">
    <p align="center">This site is maintained by the General Council on Finance and Administration <script language="JavaScript">
var today_date= new Date()
var year=today_date.getFullYear()
document.write(year)
//--> </script>
     ©</p>
</div>
</div>

</body>
</html>
<%
rs_search_results.Close()
Set rs_search_results = Nothing
%>

I took the email code out for now. Here is the code I use to send the email.

<%
Set myMail=CreateObject("CDO.Message")
myMail.Subject="A data file has been updated"
myMail.From="xxx@xxx.xxx"
myMail.To=xxx@xxx.xxx
myMail.HTMLBody= "You have successfully updated the page!"'"URL: " & rs_Success_Updating.Fields.Item("Department").Value & vbcrlf &_
'"SubPage Title: " & rs_Success_Updating.Fields.Item("SubpageTitle").Value & vbcrlf &_
'"Link Description: " & rs_Success_Updating.Fields.Item("LinkDescription").Value & vbcrlf &_
'"Date Last Modified: " & rs_Success_Updating.Fields.Item("DateLastModified").Value & vbcrlf &_

myMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing")=2
'Name or IP of remote SMTP server
myMail.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpserver")="xxx.xxx.xxx"
'Server port
myMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport")=25
myMail.Configuration.Fields.Update
myMail.Send
set myMail=nothing

%>

I only work a few hours each day so my time is limited when trying to learn. I have actually picked up quite a bit considering the time I have invested doing it. I am going to take your advice and buy the ASP book you suggested. Thanks again for all of your help and if you can tell me the solution for this problem it will be appreciated. This is the final question I had for this project I believe and I could move on to something else.

Cheers

Report · Oct 05, 2009

You have three options of where to place the email code. This is assuming you've testing the email code independenty and that it works.

1. On the results page. place it after:

If (CStr(Request("MM_delete")) = "form1" And CStr(Request("MM_recordId")) <> "") Then

Probabaly not preferaable as the email will send before the Delete code executes. If there's a problem with the server at that moment, the record won't get deleted but the email will stll go out.

1. On the results page, place it before:

Response.Redirect(MM_editRedirectUrl)

This ensures all the delete code exectutes before the email script ,and just before the page redirect. Keep in mind that with this and the first one, anytime you change the code that is generated by a server behavior, then that behavior doesn't display the same in the panel and you can pull up the visual wizard to make changes.

3. The other option is to place it on the success page, above any of the markup.

Report · Oct 05, 2009

Thanks for your help. I tried this option

1. On the results page, place it before:

Response.Redirect(MM_editRedirectUrl)

This ensures all the delete code executes before the email script ,and just before the page redirect. Keep in mind that with this and the first one, anytime you change the code that is generated by a server behavior, then that behavior doesn't display the same in the panel and you can pull up the visual wizard to make changes.

It worked as far as deleting the code and sending the email, but it doesn't have any content in the body of the email. I was hoping to display it in the email's body, in other words it was supposed to let me(the email receipt) know what file had been deleted.

Is this possible?

-Teresa

Report · Oct 05, 2009

Yep, you just have to wrap your recordset variables in the mail body with the VB script tags -

<%=(rs_search_results.Fields.Item("SubpageTitle").Value)%>

Report · Oct 06, 2009

Thanks, I will check this out.

Report · Oct 06, 2009

I really do appreciate all your help. :~)

We are getting there, one last question I think. It is sending the email but the only text showing in the body of the email is the date last modified. And its not even the date from the record I need it is actually the first record in the database. Here is the code I used, am I doing something wrong?

myMail.TextBody= "Department:"& rs_Success.Fields.Item("Department").Value & vbcrlf
myMail.TextBody= "Subpage Title:"& rs_Success.Fields.Item("SubpageTitle").Value & vbcrlf
myMail.TextBody= "Link Description:"& rs_Success.Fields.Item("LinkDescription").Value & vbcrlf
myMail.TextBody= "URL:"& rs_Success.Fields.Item("URL").Value & vbcrlf
myMail.TextBody= "Date Last Modified:"& rs_Success.Fields.Item("DateLastModified").Value & vbcrlf

Report · Oct 06, 2009

I don't know what I was thnking on my last post, that's not the issue.

The only problem I see remaining is that you're redefining the myMail.TextBody variable, so it only contains the Date Last Modified when it gets sent. You have to add to it each time:

myMail.TextBody= myMail.TextBody & "Department:"& rs_Success.Fields.Item("Department").Value & vbcrlf
myMail.TextBody= myMail.TextBody & "Subpage Title:"& rs_Success.Fields.Item("SubpageTitle").Value & vbcrlf
myMail.TextBody= myMail.TextBody & "Link Description:"& rs_Success.Fields.Item("LinkDescription").Value & vbcrlf
myMail.TextBody= myMail.TextBody & "URL:"& rs_Success.Fields.Item("URL").Value & vbcrlf
myMail.TextBody= myMail.TextBody & "Date Last Modified:"& rs_Success.Fields.Item("DateLastModified").Value & vbcrlf

Report · Oct 06, 2009

Yeah, it's not working. If you ask me there's not any data to send from the database, so how can I expect to pull the record. It has been deleted, right?

So I was thinking if I sent an email right before the file got deleted. What I mean is I give the user a chance to abort the operation with a confirm yes or no buttons, then pull the data from the form for the emails body, not the database this may work. Am I anywhere near the right answer?

This is why I thought I needed some kind of asp condition statement, but I don't know how to do it. Anyone?

Report · Oct 07, 2009

Store the data from the recordset into variables before deleting if you want to be able to use them later in the email content.

Report · Oct 06, 2009

myMail.TextBody= myMail.TextBody & "Department:"& rs_Success.Fields.Item("Department").Value & vbcrlf
myMail.TextBody= myMail.TextBody & "Subpage Title:"& rs_Success.Fields.Item("SubpageTitle").Value & vbcrlf
myMail.TextBody= myMail.TextBody & "Link Description:"& rs_Success.Fields.Item("LinkDescription").Value & vbcrlf
myMail.TextBody= myMail.TextBody & "URL:"& rs_Success.Fields.Item("URL").Value & vbcrlf
myMail.TextBody= myMail.TextBody & "Date Last Modified:"& rs_Success.Fields.Item("DateLastModified").Value & vbcrlf

I can take out the myMail part above and just send an email. That works, but I won't have anyway of knowing what file has been deleted. I am getting so confused. I really do appreciate your help on this. I am leaving work for now and I want be back until Thursday. I will give you time to think on this one.

Report · Oct 06, 2009

I think yoiu need to fully test the email function, then add the delete later.

Create a page with the Recordset and email code, and put a Redirect to your confirmation page at the end and beflore the recordset closes.

<%Response.Redirect("somepage.asp")%>

Ths way you can make absolutely sure the email script is sending the recordset data according to the search parameters entered.

Now, for the other issue, I'm not sure. Yes, the record has been deleted, but the actual values from that recordset may still exist until the page unloads. If it is the case that the Fields Items are going away, then you can assign them to new variables after the recordset is declared - those variables will remain .

Example:

Dim departments

departments = rs_Success.Fields.Item("Department").Value

etc...

Then just change rs_Success.Fields.Item("Department").Value

in the email body to departments, and so on . That variable will still be valid regardless of whether the record was deleleted.

Report · Oct 07, 2009

Ok, I will give this a try tomorrow when I have the chance at work. Thanks again for all of your help. :~)

Report · Oct 08, 2009

The email isn't sending any data from the recordset. If I take off this part of the mail script it will send the email.

myMail.HTMLBody= "URL: " & rs_Success_Updating.Fields.Item("Department").Value & vbcrlf &_

"SubPage Title: " & rs_Success_Updating.Fields.Item("SubpageTitle").Value & vbcrlf &_

"Link Description: " & rs_Success_Updating.Fields.Item("LinkDescription").Value & vbcrlf &_

"Date Last Modified: " & rs_Success_Updating.Fields.Item("DateLastModified").Value & vbcrlf &_

I can change it to read this: myMail.HTMLBody= "Hello" and the word Hello will be in my email body. What am I doing wrong?

Report · Oct 08, 2009

First of all, if any of those values in the recordset are null it could cause the entire string to be null. So start by outputting all of the recordset values to the screen to ensure that have valid values. Also, your string ends with the line continuation character. I don't think this matters, but maybe it is failing because there is nothing on the next line? In any case, it's not needed so you can remove it.

Report · Oct 08, 2009

I am sorry but I not totally understanding what you mean when you say "start by outputting all of the recordset values to the screen". Do you mean, make sure they have a value in my database, or do you mean I should bind them to the textbox? I am lost, please help.

Report · Oct 08, 2009

That's for troubleshooting, so yes, you would have to bind the values to a text box or just in the page somewhere. Once you're sure each field returns a value you can remove the binding as long as you continue to test using the same record.

Brenget clarified what I stated and what you had asked about, that the email script is attempting to reference a value that is no longer present because it just got deleted. So you have to declare variables after the recordset code, and use those variables in the email script in place of what is in there now.

If you use the departments example that I posted above to declare and populate the variable, then the email code would read:

myMail.HTMLBody= "URL: " & departments &_ ...and so on.

Just make sure you do that for each one of the variables you need to include in the body of the email.

Report · Oct 09, 2009

Thanks again for all the help. I have a few other things to work first. Once I get the chance I will bind the textboxes so I can check the email script. I have to admit that sometimes it makes it hard for me to understand everything because I have to start and stop project repeatedly.

I will keep in touch and let you know where I am at on this. Thanks again :~)

Report · Oct 09, 2009

It works, you guys were absolutely right. It works like a charm. Thanks again and again for all your help!

Delete Record and Send Email

1 Correct answer