Passing info from one page to another via email

[LadyDi02]

Hello experts,

I have a classic asp form with oracle 9.2 backend, in which the user fills in information and then hits submit. When he/she hits submit, 2 things happen. Firstly, the information gets inserted into a database table (this is working) and 2ndly there manager receives an email asking for approval of what they filled in. My issue is how do I make the hyperlink in the email (when clicked by the manager) show the information the user submitted on the next page. Basically, is there a way to pass information from one page to another page via an email hyperlink? So that when the manager clicks on the email hyperlink, it opens in a new page and he/she has the option to approve the request or deny the request(via a dropdown). However, all other info. needs to be shown on the form and then the dropdown selection made will update the record in the database. Please if this is doable, could you be kind enough to provide some code. Right now I have created a variable that holds my hyperlink but that’s it I.E Click here to approve the request. Thanks everyone.

 

[ChrisHirst]

Pass it as a querystring.



Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[Tarwn]

After inserting your new record you need to return the id back to your ASP code. This is the first key piece of information.
After you have this working, you will need to build the page you spoke of. Make this page expect an id value in the Request.Querystring collection as Chris suggested.Make sure a value is supplied (maybe print a "I'm sorry, I could not find that record" message if there wasn't an id passed). Now use this value (the id) to select the correct record out of the database and display it as well as the dropdown box you need. in order to keep things synchronized you will likely want to have a form around that dropdown and a hidden input field inside the form to hold the id for the record (so you have it on the next page after they make a selection from the dropdown).

At this point you have the newly created ID returning into your original ASP page and you have a second page that will display appriopriately when the id is passed in the querystring. All that is left is to finish making your ilnk, being sure to embed the id in the querystring, and then a 3rd page to handle the manager's submission from your new page (the dropdown one).

Hope this helps, if you run into any more issues or would like additional detail please let us know,
-T

 

[LadyDi02]

Thanks for the responses everyone. For the life of me I have never ever used request.querystring. I have always used the form method "post" and not "get". I really don't know how to make the change to my page and thought maybe you could give me a hand. Tarwin you mentioned to return the id back to the asp page. Hmmm I'm stuck on what you mean by that. So maybe some code would help if I showed you. Sorry everyone please bear with me. I will not show the form because this is just a regular form with text boxes, some drop down's etc however I have the form coded as follows:

<FORM name="test" action="test.asp" method="post" onSubmit="return checkRequired(this)">

Now my test.asp page is as follow:

<%@ Language="VBScript" %>
<% Option Explicit %>
<!-- #include file="include/adovbs.inc" -->
<%On Error Resume Next%>

<%
'Declare variables
'I removed the variables just to shorten the code
%>
 
<!-- #include file="include/databaseconnection.asp" -->
<!-- #include file="include/handlingerrors.asp" -->
 
<%
'Grab Userid and parse off the \ 

tempStart = instr(Session("User"),"\") 
user = Mid(Session("User"), tempStart + 1, len(Session("User"))- tempStart)
 
'Grab variables from the form.

strRequestedDate = Request.Form("start_date")
strRequestorName = Request.Form("RequestorName")
strRequestorPhone = Request.Form("Extension")
strRequestedEquip = Request.Form("RequestedEquipText")
strPurpose = Request.Form("PurposeText")
strRequiredLength = Request.Form("RequiredLengthText")
strApproverName = Request.Form("manager")
strSendMail = Request.Form("SubmitMail")
strURLLink = "[URL unfurl="true"]http://localhost/test/approval.asp"[/URL] 
strLink = "<a href=" & strURLLink & ">" & strURLLink & "</a>"

' Create an instance of the NewMail object.
If request.Form.Count > 0 Then
 
 oConn.Open
 oCmdUserInfo.ActiveConnection = oConn 
 oCmdUserInfo.CommandType = adCmdStoredProc
 oCmdUserInfo.CommandText = "User_Insert" 
 
 oCmdUserInfo.Parameters.Append oCmdUserInfo.CreateParameter("RequestorName", adVarChar, adParamInput, 400, strRequestorName)
 oCmdUserInfo.Parameters.Append oCmdUserInfo.CreateParameter("Extension", adVarChar, adParamInput, 400, strRequestorPhone)
 oCmdUserInfo.Parameters.Append oCmdUserInfo.CreateParameter("LastModifiedBy", adVarChar, adParamInput, 400, User)
 oCmdUserInfo.Execute
 
 If Err.number <> 0 then
   TrapError Err.description
 End If
 
 oConn1.Open
 oCmdHardware.ActiveConnection = oConn1 
 oCmdHardware.CommandType = adCmdStoredProc
 oCmdHardware.CommandText = "Hardware_Insert" 
 
 oCmdHardware.Parameters.Append oCmdHardware.CreateParameter("DateRequired", adDate, adParamInput, 7, CDate(strRequestedDate))
 oCmdHardware.Parameters.Append oCmdHardware.CreateParameter("Equipment", adVarChar, adParamInput, 400, strRequestedEquip)
 oCmdHardware.Parameters.Append oCmdHardware.CreateParameter("Purpose", adVarChar, adParamInput, 400, strPurpose)
 oCmdHardware.Parameters.Append oCmdHardware.CreateParameter("LoanDuration", adDate, adParamInput, 7, CDate(strRequiredLength))
 oCmdHardware.Parameters.Append oCmdHardware.CreateParameter("Approver", adVarChar, adParamInput, 400, strApproverName)
 oCmdHardware.Parameters.Append oCmdHardware.CreateParameter("LastModifyBy", adVarChar, adParamInput, 400, User)
 oCmdHardware.Execute
 
 If Err.number <> 0 then
    TrapError Err.description
 End If
 
  'close database connection
 oConn.Close
 oConn1.Close
 Set oConn = Nothing
 Set oConn1 = Nothing
 
If Err.number = 0 Then 
 
  'Dimension variables 
  Dim objCDOSYSCon 
 
  'Create the e-mail server object 
 
  Set objCDOSYSMail = Server.CreateObject("CDO.Message") 
  Set objCDOSYSCon = Server.CreateObject ("CDO.Configuration") 
 
  'Out going SMTP server 
  objCDOSYSCon.Fields("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "removed for security reasons" 
  objCDOSYSCon.Fields("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = removed 
  objCDOSYSCon.Fields("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = removed
  objCDOSYSCon.Fields("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")[/URL] = removed
  objCDOSYSCon.Fields.Update 
   
  BodyText = "Hello" & strApproverName  & "Please click the link to goto the approval page" & strLink 
 
  'Update the CDOSYS Configuration 
  Set objCDOSYSMail.Configuration = objCDOSYSCon 
  objCDOSYSMail.From = "xxxx" 
  objCDOSYSMail.TO = "xxxx" 'request.form("Manager")
  objCDOSYSMail.Subject = "Approval Needed"
  objCDOSYSMail.HTMLBody = BodyText
  objCDOSYSMail.Send 
  Response.write "Your request has been sent for Approval"
 End IF
End If
 
 'Close the server mail object 
 Set objCDOSYSMail = Nothing 
 Set objCDOSYSCon = Nothing 
 
%> 
<% ProcessErrors %>

So my code is inserting into 2 tables User and Hardware. Both have ID's that are created through a sequence in oracle. I created 2 tables so I can link the user table id to the hardware table id. If my table designs are wrong or need mods please advise? I was assuming that when a user clicks submit, the id's from the table should be referenced with the same number. I am all ears for advice or help on changing these tables if there is something better. Please help. Thanks.

 

[Tarwn]

I thinkthe best way to handle the querystring portion would be to alter your stored procedures to return the newly created id's. I don't work with Oracle, so unfortunately I can't tell you anything more specific. If you return the value for the id after executing your other statements in each stored procedure then you can capture it in your code using a recordset object by setting a variable = to the Execute result:

Dim myRs
myRs = oCmdUserInfo.Execute()

If the last step of your stored procdure is to select the newly created ID, then it will be accessible in that recordset object, probably as column 0 (or you can alias it to a name when you select it in your stored proc):

Dim userId
If myRs.EOF Then
   ' Recordset pointer is pointing to EOF, no records returned
   Response.Write "An error occurred. User insert not successfull."
   Response.End
End If
userId = myRs(0)

You were correct that Request.Querystring handles GET requests. However, GET requests are simply requests that embed the variables and values in the querystring of a URL. It doesn't require a form to create a GET request, just the URL with querystring values.
So if you wanted to build a link at this point with the userId in it, you could do this (assuming you captured the hardware id like we did the user id above):

%>
Here is a test link:
<a href="mypage.asp?user=<%=userId%>&hardware=<%=hardwareId%>">Clicky Clicky</a>
<%

Now as far as your table design is concerned, the only thing I see missing is some way to link a record frmo the user table to a record in the hardware table. You may want to consider either adding a user id field to the hardware table to hold the id of the requesting user (if you plan on building a new hardware record for every request) or creating a cross reference table between the two that holds the user id and hardware id (if you plan on allowing multiple users request one or more hardware without recreating the hardware or users each time).

-T

 

[LadyDi02]

Tarwyn,

Thank you very much! this is perfect, it was exactly what I was looking for. Thanks so much for the explanation.