Recalling a record insert

T

the other john

I want to insert a record into a DB and then immediately recall what
the ID is. I have heard that there is a way to do this with SQL Server
(although I haven't done it yet). Unfortunately this is for Access.

I've done some workarounds for this in the past but want to know if
there are better ways. What I've done in the past is to recall the
file name or something from the FORM collection against the DB
(assuming this file is unique). Using aspupload, if the overwrite
attribute is set for FALSE I can use that as a way to better ensure the
file name is unique as it renames the file if it is not unique. The
problem is I need to to this without aspupload now.

I had thought of recalling the last record ID from the DB on the FORM
side and putting it in a hidden field but this seems risky. Any
suggestions?

Thanks again!
 
B

Bob Barrows [MVP]

That article needs to be rewritten: the example given will raise an error
due to the inability of Jet to perform batched queries. This example:
<%
fakeValue = 5
set conn = CreateObject("ADODB.Connection")
conn.open "<conn string>"
sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")" & _
VBCrLf & " SELECT @@IDENTITY"
set rs = conn.execute(sql)
response.write "New ID was " & rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%>

needs to be rewritten as:

<%
fakeValue = 5
set conn = CreateObject("ADODB.Connection")
conn.open "<conn string>"
sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")"
conn.execute sql,,129
sql = " SELECT @@IDENTITY"
set rs = conn.execute(sql,,1)
response.write "New ID was " & rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%>
 
T

the other john

Hi Bob,

could you explain these parts? I don't understand the number
references, sorry.
conn.execute sql,,129
set rs = conn.execute(sql,,1)

could you be explicit with these so I know what's happening here?

Thanks again!
 
B

Bob Barrows [MVP]

the said:
Hi Bob,

could you explain these parts? I don't understand the number
references, sorry.

The documentation for all MS technologies, including ADO, can be found here:
http://msdn.microsoft.com/library

The ADO documentation can be found by drilling into the "Win32 and COM
Development" node to "Data Access and Storage" to "Microsoft "ActiveX Data
Objects (ADO)" to "ADO Programmers Reference" to "ADO API Reference".

Specifically, in this case, you are looking for the
Execute Method (Connection object) -
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthcnnexecute.asp
CommandTypeEnum
(http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstcommandtypeenum.asp)
and ExecuteOptionEnum
http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstexecuteoptionenum.asp

129 is the combination of two constants: adCmdText (always tell ADO what the
Command Type is - don't make it guess) and adExecuteNoRecords. The latter
constant tells ADO not to create a recordset because your query is not
returning records. It is always a good idea to tell it this when executing
action queries that return no records.
In this case, you should be reading the Open Method (ADO Recordset)
documentation -
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthrstopen.asp
The 1 is adCmdText. Again, don't make ADO guess what the Command type is.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top