|
|
 |
|
Lesson 21: Working with ASP & MS Access |
|
Lesson 21: Working with ASP & MS Access
Author: S.Andrews
Difficulty: Medium
Requires: ASP
Demo: Demo
Download: Lesson21.zip
|
|
|
Summary:
Learn to add, remove and modify data from an Access database.
Intro:
There have been a lot of requests for this topic, and it
won last weeks voting, so here it is.
The code:
Let's start coding! First you will need a populated database.
You can download the source code for this demo,
and use the included database.
And now to the script:
One key thing to remember, ASP is pretty versatile; one ASP page can really
be one or more viewable web pages. The page moddata.asp, actually results
in four different web pages, depending on the variable 'Actionvar' - add,
update, delete, and view.
Try it yourself with this sample, call it somepage.asp:
<%
IF Len(Request.QueryString("page"))=0 THEN
%>
<HTML><BODY>
You are on page1<BR>
<A HREF="somepage.asp?page=2">Page 2</A>
</BODY></HTML>
<%
ELSEIF Request.QueryString("page")="2" THEN
%>
<HTML><BODY>
You are on page 2<BR>
<A HREF="somepage.asp?page=3">Page 3</A>
</BODY></HTML>
<%
ELSEIF Request.QueryString("page")="3" THEN
%>
<HTML><BODY>
You are on page 3<BR>
<A HREF="somepage.asp">Page 1</A>
</BODY></HTML>
<%
END IF
%>
Try this script online!
As you can see, one ASP page, actually resulted in 3 viewable web
pages.
Let's define some functions.
Len() - counts the number of characters in a string
Len("string") = 6
Trim() - removes leading and trailing spaces from a string
Trim(" string ")="string"
rs.EOF - recordset.end of file (the end of the recordset is reached)
First you will need to open your database connection.
Following is a DNS-LESS connection:
set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access
Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" &
server.mappath("pricelist.mdb")
conn.Open DSNtemp
The difference in adding, deleting, modifying records is in the
SQLstmt.
To add data, use the following SQLstmt:
SQLstmt="INSERT INTO
[table] ([field name1],[field name2], etc...)
SQLstmt=SQLstmt & VALUES('"
& [variable 1] & '","' & [variable 2] etc... '")"
SQLstmt = "INSERT INTO Prices
(Item,Price,Ordernumber)"
SQLstmt = SQLstmt & " VALUES ('" &
request.form("Item") & "','" & request.form("Price") & "','" &
request.form("Ordernumber") & "')"
conn.execute(SQLstmt)
To update records, you use the
following:
UPDATE [Table] SET [field name]= '" &[variable]
SQLstmt = "UPDATE Prices
SET "
SQLstmt = SQLstmt & "Item='" & TRIM(Request.Form("Item")) &
"', "
SQLstmt = SQLstmt & "Price='" & TRIM(Request.Form("Price"))
& "', "
SQLstmt = SQLstmt & "Ordernumber=" &
TRIM(Request.Form("Ordernumber"))
SQLstmt= SQLstmt &
" WHERE ID= " &
TRIM(Request.Form("Recordid"))<
BR>
conn.execute(sqlstmt)
To Delete Records, use the
following:
sqlstmt = "DELETE *
FROM [table] WHERE [field name]=" & [some variable]
sqlstmt = "DELETE * FROM Prices WHERE
ID=" & request.querystring("Recid")
conn.execute(sqlstmt)
Remember to close out your database connection
with:
conn.close
set conn = nothing
set sqlstmt = nothing
I have buildt a sample application to show you the basics of the
SQL statements used with Access. The application is an online
pricelist where you can add, delete and update all the items.
It's important that the path you specify in the DSN-less connection
is correct, and that you have write access to that directory. (Often
cgi-bin folder has the correct attributes). The code will not be explained further, as
I belive that you will learn best by experimeting with the code.
I've also added a feature so that any SQL statement generated and used in the
application is outputted on the top of the page.
Good luck!
Download the code: Lesson21.zip
Where to go next:
Check out the other lessons.
| |
|
|
|
|
