a relational insert from asp to Access DB 2

[fooobee]

Can someone give me an example of how would I do a relationsal insert into an access db.

I created a request form that allows users to request journal articles (more than one.) I created 2 access tables related to each other by RequestId(autonumber). One table logs the user info (firstname, lastname, etc.) The other table logs journalrequest.

Thanks for your help.

 

[travisbrown]

'your first insert to the user info table
Set commInsert = Server.CreateObject("ADODB.Connection")
commInsert.Open 'your DB connection string.

commInsert.Execute("INSERT INTO table(field1, field2) VALUES('val1', 'val2');") 

Set rsNewID = commInsert.Execute("SELECT @@IDENTITY")

intNewID = rsNewID(0) 
rsNewID.Close
Set rsNewID = Nothing
commInsert.Close
Set commInsert = Nothing

You now have a variable (intNewID) with the inserted ID. You can execute another command and use this as the foreign key insert value. In SQL Server, you'd do this all in a Stored Procedure using Scope_Identity().

[/code]

 

[DotNetGnat]

what version of Access database are you using? i guess whether @@Indentity works for older versions of access or not...also using @@Identity is inefficient...here is the sample code which retrieves the ID of last inserted record

<%
Set RS = Server.CreateObject("ADODB.RecordSet")
' Open the table
RS.Open "table_name", connection_name, adOpenKeySet, adLockPessimistic, adCmdTable
' Add a new record
RS.AddNew
RS("field_name") = request.form("field_name")

' Update the record
RS.update
' Retrive the ID
unique_ID=RS("ID")
' Close the RecordSet
RS.Close
Set RS = Nothing
%>

-DNG

 

[travisbrown]

DGN: I don't know what version @@identity was implemented in. I know it works in 97 and up, but wouldn't it be an MDAC version or jet driver thing? I'm one of those only-use-access-when-I-have-to sort of people. (Except for reports. Wouldn't it be nice if there was a built in reporting feature for SQL Server QA or EM?)

What's the efficiency gain of using your method over @@Identity? I've seen it, but didn't know it was any better. Always assumed it was just a requery of the db and pulling the top ID. I was wary because I thought it could get an out of scope ID from another user, etc.

 

[DotNetGnat]

instead of my explaining why @@identity is inefficient...take a look at these threads and see what experts have to say:

thread183-1112931
thread183-1114184
thread183-1113791

-DNG

 

[travisbrown]

oh, yeah I agree. I've read all those. When you said inefficient, I thought you meant that performance was poorer, or it otherwise affected the efficiency of the procedure. I think I misunderstood you because I'd say that @@identity can be ineffective, not inefficient when dealing with triggers, etc. Potato/tomato.

I don't think that it would happen in this case because the insert is a single call function wiht no triggers

Does rs.Update and returning an rs.field do what Scope_Identity() does, or is it open to the same ineffectiveness as @@identity? When I read that code, to me it looks like you are calling the last id. Is there a break in scope between rsAddNew and RS.update/unique_ID=RS("ID"), or because we keep the rs open, are we still in scope?

I'm not trying to be argumentative, just trying to understand the best method. I suggested @@identity because I didn't know there was a better way in asp/access and we wouldn't run into the dangers of triggers, etc.

Is your method more like Scope_Identity()?

 

[DotNetGnat]

@@identity is being used less and less by programmers and scope identity is becoming popular.

@@identity has quirks and does NOT guarantee against DUPLICATE id's. However, scope identity DOES.

and yes you were correct....the addNew method provides the same precision scope method does

-DNG

 

[travisbrown]

Cool. Good to know.

I've long been a proponent of Scope_Identity(). I should get a t-shirt made or something.

Cheers.