Add New Record in Access Database

d1004 · Aug 21, 2002

I try to add a new record into my Access table (OBJECT ), but somehow it didn’t work. The table that I tried to add into has a one to many relationship with another table (LESSON), where LESSON table is one and OBJECT is many. Does that mean I have to reference or write out a join SQL statement to join the tables in my ASP page in order for it to add.
When I delete the relationship, it added okay. But once I connect the table back, the page doesn’t run. Here is my code, and I hope somebody can help me with it. Thank you!!!

<%
Option Explicit
Dim strConnect
%>

<html>
<head>
<title> Add Record </title>
</head>
<body>
<%
Dim objRS, intObjectID, objConn
Dim adOpenDynamic, adLockOptimistic, adCmdTable
adOpenDynamic = 2
adLockOptimistic = 3
adCmdTable = 2

Set objConn = Server.CreateObject ("ADODB.Connection&quot

Set objRS = Server.CreateObject ("ADODB.Recordset&quot

strConnect = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/db/database.mdb&quot

&";" &_
"Persist Security Info = False"

objConn.Open(strConnect)
objRS.open "OBJECT", objConn, 2, 3, 2

objRS.MoveLast
intObjectID = ObjRS ("ObjectID&quot

+ 1

objRS.AddNew
objRS ("ObjectID&quot

= intObjectID
objRS ("Entry&quot

= Request.Form("entry&quot

objRS ("Title&quot

= Request.Form("title&quot

objRS ("Language&quot

= Request.Form("language&quot

objRS ("Description&quot

= Request.Form("description&quot

objRS ("Version&quot

= Request.Form("version&quot

objRS ("Status&quot

= Request.Form("status&quot

objRS ("Date&quot

= Request.Form("date&quot

objRS ("Format&quot

= Request.Form("format&quot

objRS ("Duration&quot

= Request.Form("duration&quot

objRS ("InteractivityLevel&quot

= Request.Form("interactivityLevel&quot

objRS ("EndUserSpecification&quot

= Request.Form("endUser&quot

objRS ("Difficulty&quot

= Request.Form("difficulty&quot

objRS ("Copyright&quot

= Request.Form("copyright&quot

objRS ("Annotation&quot

= Request.Form("comment&quot

objRS ("Taxon&quot

= Request.Form ("taxon&quot

objRS ("LessonID&quot

= Request.Form("lessonID&quot

objRS ("LearningObjectiveID&quot

= Request.Form("objective&quot

objRS ("AuthorID&quot

= Request.Form("authorID&quot

objRS.Update
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
Response. Redirect "/index.html"
%>

</body>
</html>

khorjak · Aug 21, 2002

It sounds like you're trying to add a record into OBJECT that has no parent in LESSON. Make sure Request.Form("lessonID&quot

is a valid LessonID in your LESSON table.
To err is human, but to really foul things up requires a computer.
- Farmers' Almanac, 1978

d1004 · Aug 21, 2002

I make sure that the record get enter into the LESSON table first before I add a new record into the OBJECT table. But somehow, it wouldn't let me add.

Funka · Aug 21, 2002

are you adding the record to the LESSON table on a separate page, before you call this code? khorjak's explanation sounds reasonable -- i would try debugging by response.writing whatever is in request.form("lessonID&quot

instead of performing the insert, run your page, and verify that this lessonID does indeed exist beforehand.

good luck!

d1004 · Aug 22, 2002

The LESSON table already have all the records in there. I used the LessonID in the Lesson table as the foreign key in my OBJECT table. Basically, the user that enter the the information will know before hand what LessonID they need to enter in order to match with what is in the LESSON table.

d1004 · Aug 22, 2002

Thank you for your suggestion, but I figure it out. There was a typo.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Add New Record in Access Database

d1004

Programmer

khorjak

Programmer

d1004

Programmer

Funka

Programmer

d1004

Programmer

d1004

Programmer

Similar threads

Part and Inventory Search

Sponsor