update database from a website form

[karendiane]

I'm looking for code or help with a process to take info from a website form, add it to a file to be emailed at day's end to me, then within Access run a routine to update the database with the new info and create some reports. I'm pretty new at coding; thanks for your help.

 

[BukHix]

karendiane,

Does it have to wait until the end of the day to be emailed to you. If not you can add it to the database and get the email at the same time. Here is just a little demo code for that so you can see the syntax.

<%@ Language="VBScript" %>
<% Option Explicit %>

<%
	Dim msg, mailer, fname, init, lname, cdate 
	'------------------------------------------------------------>
	' Get the data from the htm forms 
	cdate = Now()
	fname = Request.Form("FirstName")
	init = Request.Form("Inital")
	lname = Request.Form("LastName")
	
	' ------------------------------------------------------------>
	' Format data into a message and get ready for email
	msg = msg & "<h4><B>Time Stamp: </B>" & cdate & "</h4><P><B>Name:</B>" 
	msg = msg & lname & ", " & fname & "<BR>" &	"<B>Email Address: </B> "
	
	' ------------------------------------------------------------>
	' Connect to the database and add the data to table
	connStr="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
		& Server.MapPath("/path/database.mdb")
	
	Set Rs=Server.CreateObject("ADODB.Recordset")
	Rs.Open "tblYourTable", connStr, , adLockOptimistic

	Rs.AddNew			
	fname = Rs("FirstName")
	lname = Rs("LastName")
	init = Rs("Initial")
	cdate = Rs("TimeStamp")		
	Rs.Update
	
	' ------------------------------------------------------------>
	' Send the email out using Jmail
	Set Mailer = Server.CreateObject("JMail.SMTPMail")
	Mailer.Sender = "webmaster@YourDomain.com"
	Mailer.SenderName = "Your Name"
	Mailer.AddRecipient "YourEmailAdd@domain.com" 
	Mailer.Subject = "Client Application from the web"
	Mailer.ContentType = "text/html"
	Mailer.Body = msg
	Mailer.Execute
	Set Mailer = Nothing
	Rs.Close
	Set Rs = Nothing
%>

This is just an example post back if you have more questions.

 

[karendiane]

Thanks so much for your help - I'm starting to get an idea of what's involved.

The reason the form data needs to go to a file to be emailed and updated at day's end is the database is not on the website computer - it's in the company's office.

Pardon the dumb questions, but where does this code go? In the webpage html, or is this a separate program called by the form?

 

[BukHix]

You can actually do it either way but I like to keep my scripts separate from my HTML. You can do that by naming the script in your form code like this.

<form method="POST" action="script_name.asp">

The script that handles the code would be named script_name.asp in this example. Also keep in mind the script I posted above was just put together real quick as an example so it may not be 100% correct but it is real close and would only need a little modification to work.

The reason the form data needs to go to a file to be emailed and updated at day's end is the database is not on the website computer - it's in the company's office.

I am not sure if this would work in your situation but I have a friend that was in a similar situation as you. I put together a script that would take the data from the database and download it to an excel spreadsheet for him using ASP. He then wrote a macro that would import it directly into the database for him.

By the way the script can be password protected so that others cannot get to it and you can write more code that will flush the data out of the database on the server so you do not continually download the same data.