Add to Database

In This Site

In This Section

The number one source for making your website sell!

Sending Form Results to a Database Using ASP Code

In this tutorial, we'll learn how to send form results to a database.

Please keep in mind that it is under construction and this tutorial IS NOT yet ready for use.

Step 1 - Sending the Form Results to the Database

This first step will show you how to add a few simple fields to a Microsoft Access database. For this example, we have a database connection called FPTutorials and a table called tAddRecordsTutorial. We will use this for our sample coding example here.

In order to send your form results to a database table. You need to do several things.

Declare the variables that you will be sending to the database
Open a connection with your database
Submit the new data to the database
Close the database connection

Tell the script that you will declare all variables by using Option Explicit and then declare your variables. Also declare the variables that will be a part of the database connection and record adding script. These include RS, Conn, strConn and Sqlstmt.

<%option explicit%>
<%
DIM FirstName,LastName,Company,FurtherInfo,Date,Details
DIM RS,Conn,strConn,Sqlstmt
%>

Begin your web page by placing the html, head and body tags.

<html>
<head>
</head>
<body>

Assign form values to variables. The Trim code takes the form data and removes any extra spaces at the beginning and end of the field value.

<%
FirstName = Trim(Replace(Request.Form("FirstName"),"""",""""""))
LastName = Trim(Replace(Request.Form("LastName"),"""",""""""))
Company = Trim(Replace(Request.Form("Company"),"""",""""""))
FurtherInfo = Trim(Replace(Request.Form("FurtherInfo"),"""",""""""))
Date = Trim(Replace(Request.Form("Date"),"""",""""""))
Details = Trim(Replace(Request.Form("Details"),"""",""""""))
%>

Open your database Connection. Change the Server.MapPath to match YOUR database. If you use the default fpdb directory that FrontPage recommends, then you will need to change just the name of the database connection.

<%
set conn = server.createobject("adodb.connection")
strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("fpdb/FPTutorials.mdb")
conn.open strConn

Build the SQL statement that will add the records to your table. Our table is called tAddRecordsTutorial. You should change this to reflect your database table.

SQLstmt = "INSERT INTO tAddRecordsTutorial
(FirstName,LastName,Company,FurtherInfo,Date,Details)"
SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & "'" & FirstName & "',"
SQLstmt = SQLstmt & "'" & LastName & "',"
SQLstmt = SQLstmt & "'" & Company & "',"
SQLstmt = SQLstmt & "'" & FurtherInfo & "',"
SQLstmt = SQLstmt & "'" & Date & "',"
SQLstmt = SQLstmt & "'" & Details & "'"
SQLstmt = SQLstmt & ")"

Execute the SQL Statement.

Set RS = conn.execute(SQLstmt)

This code helps you to determine the cause of any coding or database errors. If no errors occur, you will get the message: "Your record has been added."

If err.number>0 then
response.write "VBScript Errors Occured:" & ""
response.write "Error Number=" & err.number & ""
response.write "Error Descr.=" & err.description & ""
response.write "Help Context=" & err.helpcontext & ""
response.write "Help Path=" & err.helppath & ""
response.write "Native Error=" & err.nativeerror & ""
response.write "Source=" & err.source & ""
response.write "SQLState=" & err.sqlstate & ""
end if
IF conn.errors.count> 0 then
response.write "Database Errors Occured" & ""
response.write SQLstmt & ""
for counter= 0 to conn.errors.count
response.write "Error #" & conn.errors(counter).number & ""
response.write "Error desc. -> " & conn.errors(counter).description & ""
next
else
response.write ""
response.write "The record has been added."
response.write "Thank you"
end if

Close your database connection.

Conn.Close
set conn=nothing
set rs = nothing

Now, redirect your user to a confirmation page.

Response.Redirect("thank_you_page.asp?updated=true")
%>

End your database code.

<%Response.end%>

</body>
</html>

<%
conn.close
set conn = nothing
%>

Give It a Try

ID: 26

FirstName: Ward

LastName: Cameron

Company: Ward Cameron Enterprises

FurtherInfo: True

Date: 9/22/2005

Details: test

These tutorials are part of an upcoming training course called "FrontPage Magic - How To Create A Database Driven Website For Non-Programmers". Stay tuned for more details on this exciting new product.