Intro
:
Learn to add, remove and modify data from an Access database
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) |
