|
|
Adding Data to Access Database
To add data to a database table, you need an existing database plus the table to add the data to. Let us assume that you have Access Database file name FeedBack.mdb in the same folder as this file with the following table:
tblFeeds
| Field Name | Data Type | Field Size |
|---|
| user_id | Autonumber | 8 |
| Name | Text | 45 |
| Comments | Text | 200
|
To add a data to the table tblFeeds, first we will have to create a form to enter the data and then open the database and make the connection.
Here is the form to enter the data before adding to the database:
| Form Example | Result |
|---|
<html>
<head>
<title> Adding to database example </title>
<script type="text/javascript">
<!--
function validate()
{
if(document.form.name.value=="")
{
alert("Name is missing");
return
false;
}
if(document.form.comments.value.length<8)
{
alert("Not enough comments entered");
return false;
}
else
{
return true;
}
}
//-->
</script>
</head>
<body>
<form name="form" method="post" action="save.asp">
Name: <input type="text" name="name" maxlength="45"> <br>
Comments: <textarea cols="20" rows="8" name="comments" maxlength="200"> </textarea><br>
<input type="submit" name="Save" value="Submit" onClick="return validate();">
</form>
</body>
</html>
|
This form takes name and comments and passes to save.asp file
to be processed. Blank value will not pass to the save.asp file since we are preventing that using
javascript. Enter some data and hit the submit button.
Save the file as EnterData.html or EnterData.asp.
|
Now, you insert the new record to the database using the information provided
through EnterData.asp. Here is the code to do this:
| save.asp |
|---|
<%
Dim Conn
Dim Rs
Dim sql
'Create an ADO connection and recordset object
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
'Set an active connection and select fields from the database
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb")
sql= "SELECT name, comments FROM tblFeeds;"
'Set the lock and cursor type
Rs.CursorType = 2
Rs.LockType = 3
Rs.Open sql, Conn 'Open the recordset with sql query
Rs.AddNew 'Prepare the database to add a new record and add
Rs.Fields("name") = Request.Form("name")
Rs.Fields("comments") = Request.Form("comments")
Rs.Update 'Save the update
Rs.Close
Set Rs = Nothing
Set Conn = Nothing
%> |
The third field (user_no) of our table is auto generated and
sequentially will accommulate it self on each addition of new record.
You redirect the user to another page when the record is added to the database
using
<%response.redirect("view.asp")%>
After the data is added to the database, the next thing you may want do is view to see what is added.
The code is very similar.
This code bellow displays the fields.
| view.asp |
|---|
<%
Dim Conn
Dim Rs
Dim sql
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb")
sql= "SELECT name, comments FROM tblFeeds;"
Rs.Open sql, Conn
Do While not Rs.EOF
Response.Write ("============================================="&"<br>")
Response.Write ("Name: " & "<font color='red'>" & Rs("name") & "</font>")
Response.Write ("<br>")
Response.Write ("Comment: " & "<font color='red'>" & Rs("comments") & "</font>")
Response.Write ("<br>")
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
Set Conn = Nothing
%>
View this file
|
|
Update Database