Select record after inserting it

whyulil · April 23rd, 2004, 07:40 AM

Hi there guys,
Ive just come up against this problem again (and im still using access 2000 AAAARRRRRGGGHHHH!). however this time ive found this article on the MS site
http://support.microsoft.com/default...b;EN-US;221931

I think its telling me to do what sal was saying (sorry i didn't acknowledge your reply I must not have seen it) but its saying to include the adovbs.inc file (im guessing its a massive file and might be really slow. is there a more efficient way of doing that?

Also is it possible to do this using the .commandText "insert into <table> (<fields>...) values(<whatever>...)" or not?

Im still only really a beginner but i feel as though im progressing thanks to this forum (and the books :)
Cheers
Andy

Imar · April 23rd, 2004, 07:48 AM

Hi there,

Including the adovbs.inc file isn't that bad. However, there are two alternatives (but only one is acceptable). Check out this page:

http://www.adopenstatic.com/faq/800a0bb9step2.asp

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

whyulil · April 26th, 2004, 04:45 AM

ok this is driving me mad now :)

I have written this page after reading the microsoft article (its basically the same. I must have done something wrong but i can't see it)
this is the kind of result im getting:

ID before Requery = 144
Absolute position =20
ID before setting absolutePosition = 99
the current records id is 98
it should be this: 144
Absolute position =20
server time of inserted record = 4/26/2004 10:32:53 AM

the code ive written is this:

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<%

Dim rsID
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")

objConn.Open MM_connJobs_STRING

'move the cursor to ASP
objRS.CursorLocation = adUseClient

objRS.Open "Select * FROM tbApplicantData", objConn, adOpenStatic, adLockOptimistic

'that has got all the records from the database and we will now add a new one to it
objRS.AddNew
'there are a load of things here but ive taken them out
'for this post as they are not a problem
objRS("fdServerTime") = CStr(now)

objRS.Update

Response.Write " ID before Requery = " & objRS("ID")

rsID = objRS("ID")
response.write "Absolute position =" & objRS.absolutePosition & ""
bookmark = objRS.absolutePosition
objRS.Requery

Response.Write " ID before setting absolutePosition = " & objRS("ID")

objRS.absolutePosition = bookmark

response.write "the current records id is " & objRS("ID") & ""
response.write "it should be this: " & rsID & ""
response.write "Absolute position =" & objRS.absolutePosition & ""
objRS.Filter = "ID = " & rsID

response.write "server time of inserted record = " & objRS("fdServerTime") & ""

objRS.Close
objConn.Close
set objConn = nothing
set objRS = nothing

%>

</body>
</html>

I dont understand why after the requery and setting the absoluteposition of the rs it is giving me the first record in the database instead of the newly inserted one, even though it is at the newly inserted record (as far as i understand it anyway).

Cheers

whyulil · April 26th, 2004, 06:05 AM

well now im feeling really stupid :) my boss found an article on another website which shows you how to do it real easy. Please note this is all done using Access 2k

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
'note that this is an ADODB.Connection not a command
SET commInsert = Server.CreateObject("ADODB.Connection")
commInsert.Open MM_connJobs_STRING

varInsert = "INSERT INTO tbApplicantData(fdServerTime) VALUES('" & CStr(now) & "')"
commInsert.Execute(varInsert)

'this is the important part
SET rsNewID = commInsert.Execute("SELECT @@IDENTITY")
varNewID = rsNewID(0)

rsNewID.Close
SET rsNewID = Nothing
%>
<html>
<head>
<title>Access SELECT @@IDENTITY</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<%=varNewID%>
</body>
</html>