• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Storing form values to db and sending email

Hi,
I am working on a form which requires form values to be stored in (Access) db and sending an email, by just one click of a button, I was able to do both things separately (one form stores values in db, other sends an email- which is double work), but how do i do it in one file:
Below is my code, and instead of having it in 3 different files, can I just do it all in one place- by just one click of a button:
(Note: The below code is functional with out errors)

resultpage.asp
------------------------------------------------------------------------
<%
Dim N_fname, N_lname, N_Department_Branch, S_fname, S_lname, Y_fname, Y_lname, Y_Department_Branch, Y_Phone, Y_email, Standard, Comments
Dim sConnString, connection, sSQL

N_fname = Request.Form("N_fname")
N_lname = Request.Form("N_lname")
N_Department_Branch = Request.form("N_Department_Branch")
S_fname = Request.form("S_fname")
S_lname = Request.form("S_lname")
Y_fname = Request.form("Y_fname")
Y_lname = Request.form("Y_lname")
Y_Department_Branch = Request.form("Y_Department_Branch")
Y_Phone = Request.form("Y_Phone")
Y_email = Request.form("Y_email")
Standard = Request.form("Standard")
Comments = Request.form("Comments")
      
      
sSQL = "INSERT into Award (N_fname, N_lname, N_Department_Branch, S_fname, S_lname, Y_fname, Y_lname, Y_Department_Branch, Y_Phone, Y_email, Standard, Comments) values ('" & N_fname & "', '" & N_lname & "', '" & N_Department_Branch & "', '" & S_fname & "', '" & S_lname & "', '" & Y_fname & "', '" & Y_lname & "', '" & Y_Department_Branch & "', '" & Y_Phone & "', '" & Y_email & "', '" & Standard &"', '" & Comments & "')"

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("/db/db.mdb")

Set connection = Server.CreateObject("ADODB.Connection")


connection.Open(sConnString)


connection.execute(sSQL)

response.write "Thank you! The Form Information was Inserted Successfully in our Database."

connection.Close
Set connection = Nothing
%>
----------------------------------------------------------
awarddb.asp
---------------------------------
<form action="resultpage.asp" method="post">
<!-- <input type="hidden" name="a" value="send"> -->
<tr>
<td><b>Nominee's First Name:</b></td>
<td><input size="25" name="N_fname" type="text" class="input"  maxlength=50></td>
</tr>
<tr>
<td><b>Nominee's Last Name:</b></td>
<td><input size="25" name="N_lname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><b>Nominee's Department / Branch:</b></td>
  <td><input size="25" name="N_Department_Branch" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
<td><b>Nominee's Supervisor First Name:</b></td>
<td><input size="25" name="S_fname" type="text" class="input" maxlength=25></td>
</tr>
<tr>
  <td><b>Nominee's Supervisor Last Name:</b></td>
  <td><input size="25" name="S_lname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
<td><b>Your First Name:</b></td>
<td><input size="25" name="Y_fname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Last Name:</strong></td>
  <td><input size="25" name="Y_lname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Department:</strong></td>
  <td><input size="25" name="Y_Department_Branch" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Phone:</strong></td>
  <td><input size="25" name="Y_Phone" type="text" class="input" maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Email:</strong></td>
  <td><input size="25" name="Y_email" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Standard: </strong></td>
  <td><select name="Standard" class="input" >
      <option value="Our People">Our People</option>
    <option value="Our Customers">Our Customers</option>
    <option value="Community Banking Experience">Community Banking Experience</option>
      <option value="Communication">Communication</option>
      <option value="Listen & Learn">Listen & Learn</option>
      <option value="Performance">Performance</option>
      <option value="Knowledge">Knowledge</option>
      <option value="Accuracy">Accuracy</option>
      <option value="Accountability">Accountability</option>
      <option value="Responsiveness">Responsiveness</option>
  </select>  </td>
</tr>
<tr>
  <td><strong>Comments: </strong></td>
    <td><textarea name="Comments"></textarea></td>
  </tr>

<tr>
<td colspan="2">
  <div align="center">
    <input name="submit" type="submit" class="input2" value=" SEND ">
  </div></td>
</tr>
</form>

-----------------------------------------------
awardEmail.db
----------------------------------------------
<%

if request.form("a") = "send" then
      Set objMessage = CreateObject("CDO.Message")
      objMessage.Subject = "Nomination"
      objMessage.From = "cdsfdsfnl@blue.com"
      'objMessage.To = "xxx@blue.com"
      objMessage.To = "xyz@blue.com"

      objMessage.HTMLBody = "Submitted by: "&request.form("Y_fname") &" " & vbcrlf&_
      ""&request.form("Y_lname")&"<br>" & vbcrlf&_
      "Department / Branch: "&request.form("Y_Department_Branch")&"<br>" & vbcrlf&_
      "Phone: "&request.form("Y_Phone")&"<br>" & vbcrlf&_
      "Email: "&request.form("Y_email")&"<br>" & vbcrlf&_
      "Standard: "&request.form("Standard")&"<br>" & vbcrlf&_
      "Comments: "&request.form("Comments")&"<br><br>" & vbcrlf&_
      "Nominee's First Name: "&request.form("N_fname")&"<br>" & vbcrlf&_
      "Nominee's Last Name: "&request.form("N_lname")&"<br>" & vbcrlf&_
      "Nominee's Department / Branch: "&request.form("N_Department_Branch")&"<br>" & vbcrlf&_
      "Nominee's Supervisor-First Name: "&request.form("S_fname")&"<br>" & vbcrlf&_
      "Nominee's Supervisor-Last Name: "&request.form("S_lname")&"<br>" & vbcrlf&_

       ""

      objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
      objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.202.11.233"
      objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
      objMessage.Configuration.Fields.Update

If Not blockAlerts Then
objMessage.Send
else
End If

Set objMessage = Nothing

response.redirect("resultpage.asp")

else
%>
<%'=N_fname%>
<table width="300" border="0" cellpadding="5" cellspacing="0">
<form action="" method="post">
<input type="hidden" name="a" value="send">
<tr>
<td><b>Nominee's First Name:</b></td>
<td><input size="25" name="N_fname" type="input"  maxlength=50></td>
</tr>
<tr>
<td><b>Nominee's Last Name:</b></td>
<td><input size="25" name="N_lname" type="input"  maxlength=25></td>
</tr>
<tr>
  <td><b>Nominee's Department / Branch:</b></td>
  <td><input size="25" name="N_Department_Branch" type="input" maxlength=25></td>
</tr>
<tr>
<td><b>Nominee's Supervisor First Name:</b></td>
<td><input size="25" name="S_fname" type="input"  maxlength=25></td>
</tr>
<tr>
  <td><b>Nominee's Supervisor Last Name:</b></td>
  <td><input size="25" name="S_lname" type="input"  maxlength=25></td>
</tr>
<tr>
<td><b>Your First Name:</b></td>
<td><input size="25" name="Y_fname" type="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Last Name:</strong></td>
  <td><input size="25" name="Y_lname" type="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Department:</strong></td>
  <td><input size="25" name="Y_Department_Branch" type="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Phone:</strong></td>
  <td><input size="25" name="Y_Phone" type="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Email:</strong></td>
  <td><input size="25" name="Y_email" type="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Standard: </strong></td>
  <td><select name="Standard" type="input" >
    <option value="Our People">Our People</option>
    <option value="Our Customers">Our Customers</option>
    <option value="Community Banking Experience">Community Banking Experience</option>
      <option value="Communication">Communication</option>
      <option value="Listen & Learn">Listen & Learn</option>
      <option value="Performance">Performance</option>
      <option value="Knowledge">Knowledge</option>
      <option value="Accuracy">Accuracy</option>
      <option value="Accountability">Accountability</option>
      <option value="Responsiveness">Responsiveness</option>
  </select>  </td>
</tr>
<tr>
  <td><strong>Comments: </strong></td>
    <td><textarea name="Comments" type="input" ></textarea></td>
  </tr>

<tr>
<td colspan="2">
  <div align="center">
    <input name="Action" type="submit" class="input2" value=" SEND ">
  </div></td>
</tr>
</form>
0
grafixgirl
Asked:
grafixgirl
  • 3
1 Solution
 
bugs021997Commented:
I have clubbed all three sections in one....Now you need to store the page as awarddb.asp thats it. Once everything is successful it is forwarded to thankyou.asp page....


----------------------------------------------------------
awarddb.asp
---------------------------------
<form action="awarddb.asp?Update=Y$" method="post">
<!-- <input type="hidden" name="a" value="send"> -->
<tr>
<td><b>Nominee's First Name:</b></td>
<td><input size="25" name="N_fname" type="text" class="input"  maxlength=50></td>
</tr>
<tr>
<td><b>Nominee's Last Name:</b></td>
<td><input size="25" name="N_lname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><b>Nominee's Department / Branch:</b></td>
  <td><input size="25" name="N_Department_Branch" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
<td><b>Nominee's Supervisor First Name:</b></td>
<td><input size="25" name="S_fname" type="text" class="input" maxlength=25></td>
</tr>
<tr>
  <td><b>Nominee's Supervisor Last Name:</b></td>
  <td><input size="25" name="S_lname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
<td><b>Your First Name:</b></td>
<td><input size="25" name="Y_fname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Last Name:</strong></td>
  <td><input size="25" name="Y_lname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Department:</strong></td>
  <td><input size="25" name="Y_Department_Branch" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Phone:</strong></td>
  <td><input size="25" name="Y_Phone" type="text" class="input" maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Email:</strong></td>
  <td><input size="25" name="Y_email" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Standard: </strong></td>
  <td><select name="Standard" class="input" >
      <option value="Our People">Our People</option>
    <option value="Our Customers">Our Customers</option>
    <option value="Community Banking Experience">Community Banking Experience</option>
      <option value="Communication">Communication</option>
      <option value="Listen & Learn">Listen & Learn</option>
      <option value="Performance">Performance</option>
      <option value="Knowledge">Knowledge</option>
      <option value="Accuracy">Accuracy</option>
      <option value="Accountability">Accountability</option>
      <option value="Responsiveness">Responsiveness</option>
  </select>  </td>
</tr>
<tr>
  <td><strong>Comments: </strong></td>
    <td><textarea name="Comments"></textarea></td>
  </tr>

<tr>
<td colspan="2">
  <div align="center">
    <input name="submit" type="submit" class="input2" value=" SEND ">
  </div></td>
</tr>
</form>

<%
If request.querystring("Update") = "Y$" and request.querystring("Update") <> "" then

Dim N_fname, N_lname, N_Department_Branch, S_fname, S_lname, Y_fname, Y_lname, Y_Department_Branch, Y_Phone, Y_email, Standard, Comments
Dim sConnString, connection, sSQL

N_fname = Request.Form("N_fname")
N_lname = Request.Form("N_lname")
N_Department_Branch = Request.form("N_Department_Branch")
S_fname = Request.form("S_fname")
S_lname = Request.form("S_lname")
Y_fname = Request.form("Y_fname")
Y_lname = Request.form("Y_lname")
Y_Department_Branch = Request.form("Y_Department_Branch")
Y_Phone = Request.form("Y_Phone")
Y_email = Request.form("Y_email")
Standard = Request.form("Standard")
Comments = Request.form("Comments")
     
     
sSQL = "INSERT into Award (N_fname, N_lname, N_Department_Branch, S_fname, S_lname, Y_fname, Y_lname, Y_Department_Branch, Y_Phone, Y_email, Standard, Comments) values ('" & N_fname & "', '" & N_lname & "', '" & N_Department_Branch & "', '" & S_fname & "', '" & S_lname & "', '" & Y_fname & "', '" & Y_lname & "', '" & Y_Department_Branch & "', '" & Y_Phone & "', '" & Y_email & "', '" & Standard &"', '" & Comments & "')"

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("/db/db.mdb")

Set connection = Server.CreateObject("ADODB.Connection")


connection.Open(sConnString)


connection.execute(sSQL)

response.write "Thank you! The Form Information was Inserted Successfully in our Database."

  Set objMessage = CreateObject("CDO.Message")
      objMessage.Subject = "Nomination"
      objMessage.From = "cdsfdsfnl@blue.com"
      'objMessage.To = "xxx@blue.com"
      objMessage.To = "xyz@blue.com"

      objMessage.HTMLBody = "Submitted by: "&request.form("Y_fname") &" " & vbcrlf&_
      ""&request.form("Y_lname")&"<br>" & vbcrlf&_
      "Department / Branch: "&request.form("Y_Department_Branch")&"<br>" & vbcrlf&_
      "Phone: "&request.form("Y_Phone")&"<br>" & vbcrlf&_
      "Email: "&request.form("Y_email")&"<br>" & vbcrlf&_
      "Standard: "&request.form("Standard")&"<br>" & vbcrlf&_
      "Comments: "&request.form("Comments")&"<br><br>" & vbcrlf&_
      "Nominee's First Name: "&request.form("N_fname")&"<br>" & vbcrlf&_
      "Nominee's Last Name: "&request.form("N_lname")&"<br>" & vbcrlf&_
      "Nominee's Department / Branch: "&request.form("N_Department_Branch")&"<br>" & vbcrlf&_
      "Nominee's Supervisor-First Name: "&request.form("S_fname")&"<br>" & vbcrlf&_
      "Nominee's Supervisor-Last Name: "&request.form("S_lname")&"<br>" & vbcrlf&_

       ""

      objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
      objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.202.11.233"
      objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
      objMessage.Configuration.Fields.Update

If Not blockAlerts Then
objMessage.Send
else
End If

Set objMessage = Nothing

response.redirect("thankYou.asp")

connection.Close
Set connection = Nothing

End If
%>
0
 
bugs021997Commented:
I had a typo, you can remove the line response.write "Thank you! The Form Information was Inserted Successfully in our Database." from the code i have pasted above.

Cheers do let me know if you have doubts...
0
 
bugs021997Commented:
I have just tweaked it a bit.....and removed the response.write...


--------------------------------
awarddb.asp
---------------------------------
<form action="awarddb.asp?Update=Y$" method="post">
<!-- <input type="hidden" name="a" value="send"> -->
<tr>
<td><b>Nominee's First Name:</b></td>
<td><input size="25" name="N_fname" type="text" class="input"  maxlength=50></td>
</tr>
<tr>
<td><b>Nominee's Last Name:</b></td>
<td><input size="25" name="N_lname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><b>Nominee's Department / Branch:</b></td>
  <td><input size="25" name="N_Department_Branch" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
<td><b>Nominee's Supervisor First Name:</b></td>
<td><input size="25" name="S_fname" type="text" class="input" maxlength=25></td>
</tr>
<tr>
  <td><b>Nominee's Supervisor Last Name:</b></td>
  <td><input size="25" name="S_lname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
<td><b>Your First Name:</b></td>
<td><input size="25" name="Y_fname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Last Name:</strong></td>
  <td><input size="25" name="Y_lname" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Department:</strong></td>
  <td><input size="25" name="Y_Department_Branch" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Phone:</strong></td>
  <td><input size="25" name="Y_Phone" type="text" class="input" maxlength=25></td>
</tr>
<tr>
  <td><strong>Your Email:</strong></td>
  <td><input size="25" name="Y_email" type="text" class="input"  maxlength=25></td>
</tr>
<tr>
  <td><strong>Standard: </strong></td>
  <td><select name="Standard" class="input" >
      <option value="Our People">Our People</option>
    <option value="Our Customers">Our Customers</option>
    <option value="Community Banking Experience">Community Banking Experience</option>
      <option value="Communication">Communication</option>
      <option value="Listen & Learn">Listen & Learn</option>
      <option value="Performance">Performance</option>
      <option value="Knowledge">Knowledge</option>
      <option value="Accuracy">Accuracy</option>
      <option value="Accountability">Accountability</option>
      <option value="Responsiveness">Responsiveness</option>
  </select>  </td>
</tr>
<tr>
  <td><strong>Comments: </strong></td>
    <td><textarea name="Comments"></textarea></td>
  </tr>

<tr>
<td colspan="2">
  <div align="center">
    <input name="submit" type="submit" class="input2" value=" SEND ">
  </div></td>
</tr>
</form>

<%
If UCASE(trim(request.querystring("Update"))) = "Y$" and trim(request.querystring("Update")) <> "" then

Dim N_fname, N_lname, N_Department_Branch, S_fname, S_lname, Y_fname, Y_lname, Y_Department_Branch, Y_Phone, Y_email, Standard, Comments
Dim sConnString, connection, sSQL

N_fname = Request.Form("N_fname")
N_lname = Request.Form("N_lname")
N_Department_Branch = Request.form("N_Department_Branch")
S_fname = Request.form("S_fname")
S_lname = Request.form("S_lname")
Y_fname = Request.form("Y_fname")
Y_lname = Request.form("Y_lname")
Y_Department_Branch = Request.form("Y_Department_Branch")
Y_Phone = Request.form("Y_Phone")
Y_email = Request.form("Y_email")
Standard = Request.form("Standard")
Comments = Request.form("Comments")
     
     
sSQL = "INSERT into Award (N_fname, N_lname, N_Department_Branch, S_fname, S_lname, Y_fname, Y_lname, Y_Department_Branch, Y_Phone, Y_email, Standard, Comments) values ('" & N_fname & "', '" & N_lname & "', '" & N_Department_Branch & "', '" & S_fname & "', '" & S_lname & "', '" & Y_fname & "', '" & Y_lname & "', '" & Y_Department_Branch & "', '" & Y_Phone & "', '" & Y_email & "', '" & Standard &"', '" & Comments & "')"

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("/db/db.mdb")

Set connection = Server.CreateObject("ADODB.Connection")


connection.Open(sConnString)


connection.execute(sSQL)


  Set objMessage = CreateObject("CDO.Message")
      objMessage.Subject = "Nomination"
      objMessage.From = "cdsfdsfnl@blue.com"
      'objMessage.To = "xxx@blue.com"
      objMessage.To = "xyz@blue.com"

      objMessage.HTMLBody = "Submitted by: "&request.form("Y_fname") &" " & vbcrlf&_
      ""&request.form("Y_lname")&"<br>" & vbcrlf&_
      "Department / Branch: "&request.form("Y_Department_Branch")&"<br>" & vbcrlf&_
      "Phone: "&request.form("Y_Phone")&"<br>" & vbcrlf&_
      "Email: "&request.form("Y_email")&"<br>" & vbcrlf&_
      "Standard: "&request.form("Standard")&"<br>" & vbcrlf&_
      "Comments: "&request.form("Comments")&"<br><br>" & vbcrlf&_
      "Nominee's First Name: "&request.form("N_fname")&"<br>" & vbcrlf&_
      "Nominee's Last Name: "&request.form("N_lname")&"<br>" & vbcrlf&_
      "Nominee's Department / Branch: "&request.form("N_Department_Branch")&"<br>" & vbcrlf&_
      "Nominee's Supervisor-First Name: "&request.form("S_fname")&"<br>" & vbcrlf&_
      "Nominee's Supervisor-Last Name: "&request.form("S_lname")&"<br>" & vbcrlf&_

       ""

      objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
      objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.202.11.233"
      objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
      objMessage.Configuration.Fields.Update

If Not blockAlerts Then
objMessage.Send
else
End If

Set objMessage = Nothing

connection.Close
Set connection = Nothing


'Either it goes to the thankYou.asp page or response.writes a thank you message. Uncomment as per your needs.....

'response.write "Thank you! The Form Information was Inserted Successfully in our Database."

response.redirect("thankYou.asp")



End If
%>
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now