Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Generating unique link to send via email

Posted on 2006-04-17
8
Medium Priority
?
239 Views
Last Modified: 2011-09-20
OK - I have bitten off more than I can chew, and I hoping for some very specific help here.  I have designed simple .asp pages before that just dump into Access databases.  Now though, things are getting more complicated and I don't know where to even start.  The scenario is: I need to create something where students can sign up for a class, then when they submit their form the email client (Lotus Notes 5.0) is opened with a unique link that point back to the record that they just entered (there is an autonumber field in Access as the primary key, but that can be changed if necessary).  They will then select their manager's email from the Lotus Address book and send it off.  Their manager will receive the email, click on the link, the database results for that particular entry will display, then the manager will update the record by clicking on an approval button.  The .asp page is called TrainingSignUp.asp, the database is TrainingSignUp.mdb, the Primary Key is ID (autonumber in Access DB).

I have generated the appropriate forms and the database, but I don't know what to do On Submit in order to get it to retrieve the unique ID of the record that was just submitted in order to create the specific hyperlink to be emailed to the supervisor.  I also don't know how to get it to just open Lotus Notes instead of automatically sending the email (they won't know their supervisor's email off hand).  

I recognize that this is a loaded question, and that it is waaaaay beyond my skills, but I am hoping an expert will take pity on me and show me what to do!!
0
Comment
Question by:bcad
  • 3
  • 3
7 Comments
 
LVL 22

Expert Comment

by:WMIF
ID: 16473586
here is an article about getting the last id inserted.
http://www.aspfree.com/c/a/ASP-Code/Retrieving-ID-field-after-inserting-a-record-into-your-Microsoft-Access-Database/

as for opening any programs on the client computer, asp has no control over that.  you could prompt the user for their managers email and let them look that up on their own.
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16473790
This would be my advice.

1. Create a manager table that is used to display managers for the user to choose from.  This table could store the manager's email addresses and a unique key for each manager.

2. Add a manager foreign key to your existing table that will store the user's associated manager key.

3. Use CDO to send the email to the selected manager and pass the manager key in the query string.  This eliminates trying to get the user to send an email with Lotus.  They don't even need to know that an email is being sent for that matter.

4. The email sent to the manager will contain a link to a page with ALL of their unapproved requests.  This eliminates the need to even determine the last created ID, and insures that the manager doesn't miss any approvals.  By having the manager foreign key on your table, you can run a query based on the manager key.

If I have time tonight, I will put together a **rough** example.  I am guessing that you may need it based on the number of a's in the phrase:  "waaaaay beyond my skills"

0
 
LVL 12

Accepted Solution

by:
peterxlane earned 2000 total points
ID: 16473925
Here is the basic idea of the first part.  I actually even simplified it even more by simply storing the manager's email right in your existing table, but the ideal solution would have a separate manager table with the associated email addresses.  

<% @Language = VBScript %>
<html>
<head>
<title></title>
</head>
<body>

<form method="post">
      
<table border="1" cellpadding="5" cellspacing="0" align="center">
      <tr>
            <td>Your Name:</td>
            <td><input type="text" name="username" size="30" value="Peter" /></td>
      </tr>
      <tr>
            <td>Your Manager:</td>
            <td>
                  <select name="manager">
                        <option value="manager1@yourcompany.com">Manager One</option>
                        <option value="manager2@yourcompany.com">Manager Two</option>
                        <option value="manager3@yourcompany.com">Manager Three</option>
                  </select>
            </td>
      </tr>
      <tr>
            <td colspan="2" align="center"><input type="submit" value="Submit" name="cmdSubmit" /></td>
      </tr>            
</table>

</form>

<%
If Request.Form <> "" Then
      strUserName = Request.Form("username")
      strManagerEmail = Request.Form("manager")
      strSQL = "INSERT INTO tblYourTable (UserName, strManagerEmail) VALUES ('" & strUserName & "', '" & strManagerEmail & "')"
      Response.Write "<b>Sample of SQL you will need to run: </b>" & strSQL & "<br />"
      'You need to put your database code here to insert form results into database      
      strEmailToSend = "There has been a class sign up request.<br /><a href=""http://yourwebsite.asp?email=" & strManagerEmail & """>Click here to view approval requests</a>"
      strFromAddress = "you@yourcompany.com"
      strSubject = "your subject"
      Call SendMessage(strManagerEmail, strFromAddress, strSubject, strEmailToSend)      
      Response.Write "<h2>Your request has been submitted</h2>"      
End If


Sub SendMessage(strTo, strFrom, strSubject, strBody)
      Set oCDO = Server.CreateObject("CDO.Message")
            With oCDO
                  .To       = strTo
                  .From     = strFrom
                  .Subject  = strSubject
                  .HtmlBody = strBody
                  .Send
            End With
      Set oCDO = Nothing
End Sub
%>


</body>
</html>


Your second page would just read the value passed in the querystring to display only the items in the database that have a matching email address. The manager would then just approve individual items by clicking on them or something like that.  If you are storing the user's email in the database as well, then you could make it so that an email confirmation is sent to the user when the request is approved or denied, by using similar code as above to create the email...

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:bcad
ID: 16476993
Thanks for the help and the example (yes, the multiple aaaa's were definitely a top off as to my skill level!!).  I am going to work on it this morning and get back to you with any questions...
0
 
LVL 1

Author Comment

by:bcad
ID: 16479215
Fabulous!!  What a relief to have something working!  Thanks very much for the sample code peterxlane, that was just what I needed.  I may have other questions along the way that arise out of this, but I will post them seperately.

Insert big sigh of relief here...

Thanks again.
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16479934
glad I could help
0
 
LVL 1

Author Comment

by:bcad
ID: 16579005
Peterxlane;

I have a follow up question about a slightly different way to go about this...should a post a seperate question???

Thanks...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question