ASP Tutorial
How do I insert data?
Inserting data into a MS Access Database is rather simple. In the previous
chapters, we showed you how to use a simple "Select" statement to ensure you
could get to the data inside your database. In this chapter, we're adding
information to an existing database. We must know the format of the database,
and data types to be able to add information to the database.
Please review the terms below. These terms will be used in this section.
| Insert into table (column1,
column2, ...) Values (value1, value2, ...) |
This SQL statement is the
correct syntax on how to insert data into the table. Using ASP, the SQL
statement may look quite different, but with the inserting of values
stripped out, this is what the statement looks like.
|
| |
|
variable = "1 + 2"
variable = variable " = 3" |
This is a way to keep adding
information to one variable. Note the second line and how it rolls the
previous value of the word "variable" into the next delegation of the
variable. |
To execute the following example script below, you
will need to create either a MS Access, or MS Excel database, and create a DSN.
Here is a short description of what the database should look like. In this
example, we will use MS Access.
Table name: example
| Field |
Data Type |
| ID |
AutoNumber |
| Name |
Text
|
| Email |
Text
|
| Phone |
Text
|
Input a few lines of data into the table. We'll say
that 5 or 6 entries are sufficient. Create a DSN named 'example' using your
Windows 2000 Control Panel [PDF]. Once you have created the DSN, create the
following form, and then the script below it. You'll need to use the form to
submit data into the database.
------------- Copy the following into insert_form.asp -------------
<HTML>
<HEAD>
<TITLE>Inserting Data Script</TITLE>
</HEAD>
<BODY>
<FORM ACTION="insert_data.asp" METHOD="POST">
<TABLE BORDER=1 WIDTH=500>
<TR>
<TD>Name</TD><TD><INPUT TYPE=TEXT NAME=Name></TD>
</TR>
<TR>
<TD>Email</TD><TD><INPUT TYPE=TEXT NAME=Email></TD>
</TR>
<TR>
<TD>Phone</TD><TD><INPUT TYPE=TEXT NAME=Phone></TD>
</TR>
</TABLE>
<INPUT TYPE=SUBMIT>
</FORM>
</BODY>
</HTML>
------------- Copy the following into insert_data.asp -------------
<HTML>
<HEAD>
<TITLE>Inserting Data Script</TITLE>
</HEAD>
<BODY>
<%
' Set your variables from the pervious form
Name = request.form("Name")
Email = request.form("Email")
Phone = request.form("Phone")
' Sending data to database
set conn = server.createobject("adodb.connection")
' Set your connection to the DSN
conn.open "example"
' Inserting data into the database is done with
' the insert into statement, followed by the table
' name and then the field names in parenthesis, separated
' by commas. Then, the values are added to the fields.
SQLstmt = "INSERT INTO example (Name, Email, Phone)"
SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & "'" & Name & "',"
SQLstmt = SQLstmt & "'" & Email & "',"
SQLstmt = SQLstmt & "'" & Phone & "')"
Set RS = conn.execute(SQLstmt)
' This is the same error routine used in the last chapter.
If err.number>0 then
response.write "VBScript Errors Occured:" & "<P>"
response.write "Error Number=" & err.number & "<P>"
response.write "Error Descr.=" & err.description & "<P>"
response.write "Help Context=" & err.helpcontext & "<P>"
response.write "Help Path=" & err.helppath & "<P>"
response.write "Native Error=" & err.nativeerror & "<P>"
response.write "Source=" & err.source & "<P>"
response.write "SQLState=" & err.sqlstate & "<P>"
End If
If conn.errors.count> 0 then
response.write "Database Errors Occured" & "<P>"
response.write SQLstmt & "<P>"
for counter= 0 to conn.errors.count
response.write "Error #" & conn.errors(counter).number & "<P>"
response.write "Error desc. -> " & conn.errors(counter).description & "<P>"
Next
Else
End If
conn.close
%>
Information has been added successfully to the database.
</BODY>
</HTML>
